skip to content

Declarative Partitioning Rules

PostgreSQL partitions by RANGE, LIST or HASH with no global indexes, so unique keys must include the partition key. Interviewers probe unique keys, the DEFAULT partition and stale parent stats.

part ofPostgreSQLread it →
on this pageshow

explore

questions

6

In PostgreSQL, which rows does FOR VALUES FROM ('2026-03-01') TO ('2026-04-01') accept, and what happens to a row no partition accepts?

level: juniorimportance: must knowfreq 58%

answer

  1. one end in, one end out
  2. adjacent months share a boundary
  3. parent stores nothing
  4. no partition of relation found for row

basics

~20 s

FROM is inclusive and TO is exclusive, so the partition holds 2026-03-01 up to but not including 2026-04-01. A row that fits no partition fails with 'no partition of relation found for row' unless a DEFAULT partition exists.

solid answer

~40 s

In `CREATE TABLE clicks_2026_03 PARTITION OF clicks FOR VALUES FROM ('2026-03-01') TO ('2026-04-01')`, the lower bound is **inclusive** and the upper bound **exclusive**, so March and April can both use `2026-04-01` as a boundary without overlapping; truly overlapping bounds are refused. The parent `clicks` has no storage: every inserted row is routed to the partition whose bounds contain its `clicked_at`. If none does, the statement fails with `no partition of relation "clicks" found for row` (SQLSTATE `23514`), unless a `DEFAULT` partition exists to catch it. `NULL` is never inside a range, `MINVALUE`/`MAXVALUE` mean unbounded, and the bound literal is evaluated once at creation, so for a `timestamptz` key it is read in that session's `TimeZone`.

code

sql · 18 lines
sql
CREATE TABLE clicks (
    click_id    bigint      NOT NULL,
    clicked_at  timestamptz NOT NULL,
    campaign_id bigint      NOT NULL
) PARTITION BY RANGE (clicked_at);

SET TimeZone = 'UTC';
CREATE TABLE clicks_2026_03 PARTITION OF clicks
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE clicks_2026_04 PARTITION OF clicks
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

INSERT INTO clicks VALUES (1, '2026-04-01 00:00:00+00', 7);  -- routed to clicks_2026_04

SELECT tableoid::regclass AS partition, click_id FROM clicks;

INSERT INTO clicks VALUES (2, '2026-06-02 10:00:00+00', 7);
-- ERROR:  no partition of relation "clicks" found for row

go deeper

for a junior

Recall that FROM is inclusive and TO exclusive, that the parent stores no rows, and that an unmatched row raises 'no partition of relation found for row' unless a DEFAULT partition exists.

for a middle

Explain routing, NULL keys going to the default, row movement on UPDATE, MINVALUE and MAXVALUE, and why a timestamptz bound depends on the creating session's TimeZone.

for a senior

Show the operational habits: create partitions from a UTC session well ahead of time, alert before the newest partition runs out, and keep the key NOT NULL so nothing silently lands in a default.

for a principal

Frame partition creation as an availability dependency of every write path, and decide who owns it: application migrations, a scheduled job, or an extension such as pg_partman.

## Declaring the table and its partitions PostgreSQL's declarative partitioning starts with a **partitioned table** and then one `CREATE TABLE ... PARTITION OF` per partition: ```sql CREATE TABLE clicks (...) PARTITION BY RANGE (clicked_at); CREATE TABLE clicks_2026_03 PARTITION OF clicks FOR VALUES FROM ('2026-03-01') TO ('2026-04-01'); ``` - The parent `clicks` has **no storage of its own**. Every row inserted into it is **routed** to the partition whose bounds contain the row's partition key value. - Each **partition** is an ordinary table with exactly the parent's columns. You cannot add extra columns to one partition. - Behind the scenes partitions are linked to the parent through table inheritance, so the `tableoid` system column tells you which partition a row physically lives in. ## How range bounds work - `FROM` is **inclusive** and `TO` is **exclusive**. `clicks_2026_03` accepts `2026-03-01 00:00:00` and everything up to, but not including, `2026-04-01 00:00:00`. - Adjacent months therefore share a boundary value: March ends `TO ('2026-04-01')` and April starts `FROM ('2026-04-01')` without overlapping. This is why monthly bounds are written as first-of-month to first-of-next-month, never as `'2026-03-31 23:59:59'`. - Genuinely overlapping bounds are refused with `partition "..." would overlap partition "..."`. - `MINVALUE` and `MAXVALUE` mean **unbounded**: `FROM (MINVALUE) TO ('2026-01-01')` holds everything earlier. They are not storable values, unlike the timestamp value `'infinity'`. - With a multi-column range key, bounds compare **row-wise**: for `PARTITION BY RANGE (x, y)`, `FROM (1, 2) TO (3, 4)` accepts `x = 2` with any non-null `y`. - The bound is an expression evaluated **once, when the partition is created**. For a `timestamptz` key, `'2026-03-01'` is interpreted in the creating session's `TimeZone` setting, so a partition created from a session in another zone gets boundaries shifted by that offset. Create partitions from a UTC session, or write the offset explicitly (`'2026-03-01 00:00:00+00'`). ## A row that fits nowhere - With no matching partition, the `INSERT` fails: `ERROR: no partition of relation "clicks" found for row`, with a `DETAIL` that shows the key, and SQLSTATE `23514` (`check_violation`). The whole statement rolls back, including the other rows of a multi-row insert. - With a `DEFAULT` partition, the row is stored there instead. That trades a loud failure for a quiet pile-up, which has its own rules. - `NULL` is never inside a range. A row with a `NULL` `clicked_at` goes to the default partition if there is one and fails otherwise, so declare the key column `NOT NULL`. - An `UPDATE` that changes `clicked_at` to a value in another month **moves** the row into the partition that now matches. ## The bound syntax of the other two methods | Method | Parent declaration | One partition's bound | |---|---|---| | RANGE | `PARTITION BY RANGE (clicked_at)` | `FOR VALUES FROM (...) TO (...)` | | LIST | `PARTITION BY LIST (country_code)` | `FOR VALUES IN ('DE', 'AT', 'CH')`; at most one list partition may also accept `NULL` | | HASH | `PARTITION BY HASH (click_id)` | `FOR VALUES WITH (MODULUS 8, REMAINDER 3)`; no `DEFAULT` partition is allowed | Any of the three can also be given a `DEFAULT` partition with `PARTITION OF clicks DEFAULT`, except `HASH`. ## Things that catch people 1. There is no way to turn an existing ordinary table into a partitioned table in place. You create a new partitioned table and either copy the data in or attach the old table as one of its partitions. 2. A primary key or unique constraint on `clicks` must include `clicked_at`, because PostgreSQL has no index spanning partitions. 3. Partitions are easy to list: `\d+ clicks` in psql shows them with their bounds, and `SELECT * FROM pg_partition_tree('clicks');` returns one row per member of the tree with its parent, level and whether it is a leaf. 4. Creating the partitions ahead of time is your job (or a tool's, such as pg_partman). PostgreSQL never creates a missing partition on insert.

  • How do you see which partition a particular click row was routed to?
    Select the `tableoid` system column cast to `regclass`: `SELECT tableoid::regclass, * FROM clicks WHERE click_id = 1;`. Partitions are inheritance children of the parent behind the scenes, so every row carries the OID of the table it is physically stored in, and the cast prints that partition's name.
  • Can you convert the existing unpartitioned clicks table into a partitioned one with ALTER TABLE?
    No. PostgreSQL cannot turn a regular table into a partitioned table, or back. Create a new partitioned table, then either copy the data into new partitions or attach the old table as one partition covering its date range. Attaching validates the rows against the bound, which a matching CHECK constraint lets it skip.

  • TO is inclusive, so the March partition also takes midnight on April 1.
  • Rows that match no partition are stored in the parent table itself.
  • A RANGE partition can be declared to accept NULL keys.
  • PostgreSQL creates the missing monthly partition automatically when a row needs it.
  • Overlapping bounds are allowed and the partition created first wins.
  • An existing regular table can be converted to a partitioned table in place.
open as a page

In PostgreSQL, why does ALTER TABLE clicks ADD PRIMARY KEY (click_id) fail on a table partitioned by RANGE (clicked_at), and what are your legal options?

level: middleimportance: must knowfreq 64%

basics

~20 s

PostgreSQL has no global indexes: a unique key on a partitioned table is one unique index per partition, so it must include every partition key column. Use PRIMARY KEY (click_id, clicked_at), or partition by the column that must be unique.

open as a page

In PostgreSQL, why does creating the March partition of clicks fail when its DEFAULT partition already holds late March rows, and how do you recover and prevent it?

level: seniorimportance: should knowfreq 40%

basics

~20 s

Adding a partition narrows what the DEFAULT partition may hold, so PostgreSQL scans the default and fails with SQLSTATE 23514 if any row belongs to the new range. Move those rows into a standalone table and attach it, in one transaction.

open as a page

In PostgreSQL, why do the clicks parent's statistics go stale even with autovacuum on, and how does PostgreSQL 18 change the way you refresh them?

level: seniorimportance: should knowfreq 36%

basics

~10 s

Autovacuum never processes a partitioned parent, so its whole-hierarchy statistics exist only after a manual ANALYZE. Schedule ANALYZE on the parent; PostgreSQL 18's ANALYZE ONLY clicks refreshes them without re-analyzing every partition.

open as a page

With pg_partman 5.5 managing the monthly clicks table in PostgreSQL, what do create_partition(), premake, the default table and run_maintenance_proc() do, and what must you monitor?

level: seniorimportance: should knowfreq 42%

basics

~20 s

pg_partman automates declarative partitions: create_partition() registers an existing partitioned table, premake (default 4) keeps future partitions ready, a default partition catches strays, and run_maintenance_proc() or its background worker creates and retires partitions. Monitor default-table rows and maintenance failures.

open as a page

In PostgreSQL 18, why is CREATE UNLOGGED TABLE clicks (...) PARTITION BY RANGE (clicked_at) rejected, and where do UNLOGGED and storage settings live instead?

level: middleimportance: nice to knowfreq 22%

basics

~10 s

Since PostgreSQL 18 a partitioned table cannot be unlogged: it has no storage, and the flag never reached its partitions anyway. Persistence, like fillfactor and autovacuum settings, is set on each leaf partition.

open as a page