Partitioning

Some tables grow without limit. A table of events, log lines, or sensor readings can reach billions of rows, and at that size everything gets harder: indexes balloon, maintenance jobs run for hours, and deleting old data is expensive. Partitioning addresses this by splitting one huge logical table into many smaller physical ones, while your queries still see a single table (docs).

PostgreSQL’s declarative partitioning lets you define the split rule once. You write to and read from the parent table as usual, and the server routes each row to the right partition behind the scenes.

Everything below was run on PostgreSQL 18.6 (postgres:18.6, aarch64, Docker Desktop on a Mac, September 2026). The examples are sql fences run top to bottom in one empty database; the timings come from a 3-million-row table on this Mac and only their proportions will carry over to your machine.

One logical table, many partitions

You declare a parent table as partitioned and give it a partition key — the column whose value decides which partition a row belongs to. The parent holds no data itself; it is a routing layer over its child partitions.

flowchart TD
  Parent[events - logical parent table] --> P1[events_2025_q1]
  Parent --> P2[events_2025_q2]
  Parent --> P3[events_2025_q3]
  Parent --> P4[events_2025_q4]

One logical table routed across several partitions

There are three partitioning strategies, chosen by how the key maps to partitions.

Strategy	Rows are assigned by…	Good for
RANGE	A value falling in a range, such as a date range	Time-series data partitioned by month
LIST	The key matching one of a fixed set of values	Splitting by region, status, or tenant
HASH	A hash of the key spread evenly across buckets	Even distribution with no natural ranges

Range partitioning a time-series table

The most common case is range partitioning by time. You declare the parent with PARTITION BY RANGE, then create one child partition per period.

-- sql/setup.sql
CREATE TABLE events (
  id         bigint GENERATED ALWAYS AS IDENTITY,
  created_at timestamptz NOT NULL,
  kind       text NOT NULL,
  payload    jsonb
) PARTITION BY RANGE (created_at);

-- one partition per quarter; the lower bound is inclusive, the upper bound exclusive
CREATE TABLE events_2025_q1 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
CREATE TABLE events_2025_q2 PARTITION OF events
  FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

Inserting into events automatically lands each row in the matching partition. tableoid shows which one:

-- sql/route.sql
INSERT INTO events (created_at, kind, payload)
VALUES ('2025-02-14', 'signup', '{"plan": "pro"}'), ('2025-05-01', 'login', NULL);

SELECT tableoid::regclass AS partition, id, created_at::date, kind FROM events ORDER BY id;

INSERT 0 2
   partition    | id | created_at |  kind
----------------+----+------------+--------
 events_2025_q1 |  1 | 2025-02-14 | signup
 events_2025_q2 |  2 | 2025-05-01 | login
(2 rows)

A row that fits no partition is rejected, and the whole statement fails:

-- @expect-error 23514
INSERT INTO events (created_at, kind) VALUES ('2025-08-01', 'late');

ERROR:  no partition of relation "events" found for row
DETAIL:  Partition key of the failing row contains (created_at) = (2025-08-01 00:00:00+00).

The partition key must be part of every unique key

A primary key on a partitioned table has to include the partition key, because each partition can only enforce uniqueness inside itself (docs). A primary key on id alone is refused:

-- @expect-error 0A000
ALTER TABLE events ADD PRIMARY KEY (id);

ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "events" lacks column "created_at" which is part of the partition key.

(id, created_at) works, and every partition gets its own index for it:

-- sql/primary-key.sql
ALTER TABLE events ADD PRIMARY KEY (id, created_at);
\d events_2025_q1

ALTER TABLE
                                Table "public.events_2025_q1"
   Column   |           Type           | Collation | Nullable |           Default
------------+--------------------------+-----------+----------+------------------------------
 id         | bigint                   |           | not null | generated always as identity
 created_at | timestamp with time zone |           | not null |
 kind       | text                     |           | not null |
 payload    | jsonb                    |           |          |
Partition of: events FOR VALUES FROM ('2025-01-01 00:00:00+00') TO ('2025-04-01 00:00:00+00')
Indexes:
    "events_2025_q1_pkey" PRIMARY KEY, btree (id, created_at)

The consequence to remember: uniqueness of id alone is no longer enforced by the database. The identity column still hands out distinct values, but nothing stops an application from inserting a duplicate id with a different created_at.

List and hash partitioning

PARTITION BY LIST assigns rows by exact key values — handy when a natural category exists.

-- sql/list.sql
CREATE TABLE customers (
  id     bigint GENERATED ALWAYS AS IDENTITY,
  region text NOT NULL,
  name   text NOT NULL
) PARTITION BY LIST (region);

CREATE TABLE customers_emea PARTITION OF customers FOR VALUES IN ('eu', 'me', 'africa');
CREATE TABLE customers_amer PARTITION OF customers FOR VALUES IN ('us', 'ca', 'latam');

INSERT INTO customers (region, name) VALUES ('eu', 'Ada'), ('us', 'Linus'), ('latam', 'Grace');
SELECT tableoid::regclass AS partition, region, name FROM customers ORDER BY id;

CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 3
   partition    | region | name
----------------+--------+-------
 customers_emea | eu     | Ada
 customers_amer | us     | Linus
 customers_amer | latam  | Grace
(3 rows)

PARTITION BY HASH spreads rows evenly across a fixed number of buckets when no range or list makes sense. All four remainders (0 to 3 for MODULUS 4) must exist, or some rows have nowhere to go (the bug at the end of the lesson):

-- sql/hash.sql
CREATE TABLE sessions (id bigint NOT NULL, token text NOT NULL) PARTITION BY HASH (id);
CREATE TABLE sessions_0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_1 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_2 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_3 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 3);

INSERT INTO sessions SELECT g, 'token-' || g FROM generate_series(1, 1000) g;
SELECT tableoid::regclass AS partition, count(*) FROM sessions GROUP BY 1 ORDER BY 1;

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 1000
 partition  | count
------------+-------
 sessions_0 |   259
 sessions_1 |   234
 sessions_2 |   276
 sessions_3 |   231
(4 rows)

1000 sessions land in four partitions of 231 to 276 rows: even, not identical.

Partition pruning

The real payoff is partition pruning: when a query’s WHERE clause filters on the partition key, the planner skips every partition that cannot contain matching rows. The first plan below asks for February and reads only Q1. The second filters on a column that is not the key, so it must read every partition. The third repeats the first with enable_partition_pruning switched off, a teaching switch that shows what pruning saves:

-- sql/pruning.sql
EXPLAIN (COSTS OFF)
SELECT count(*) FROM events
WHERE created_at >= '2025-02-01' AND created_at < '2025-03-01';

EXPLAIN (COSTS OFF)
SELECT count(*) FROM events WHERE kind = 'signup';

-- teaching switch: pruning off, same query as the first one
SET enable_partition_pruning = off;
EXPLAIN (COSTS OFF)
SELECT count(*) FROM events
WHERE created_at >= '2025-02-01' AND created_at < '2025-03-01';
RESET enable_partition_pruning;

                                                                         QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate
   ->  Seq Scan on events_2025_q1 events
         Filter: ((created_at >= '2025-02-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2025-03-01 00:00:00+00'::timestamp with time zone))
(3 rows)

                   QUERY PLAN
-------------------------------------------------
 Aggregate
   ->  Append
         ->  Seq Scan on events_2025_q1 events_1
               Filter: (kind = 'signup'::text)
         ->  Seq Scan on events_2025_q2 events_2
               Filter: (kind = 'signup'::text)
(6 rows)

SET
                                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate
   ->  Append
         ->  Seq Scan on events_2025_q1 events_1
               Filter: ((created_at >= '2025-02-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2025-03-01 00:00:00+00'::timestamp with time zone))
         ->  Seq Scan on events_2025_q2 events_2
               Filter: ((created_at >= '2025-02-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2025-03-01 00:00:00+00'::timestamp with time zone))
(6 rows)

RESET

Pruning needs the partition key in the WHERE clause. It also works when the value is only known at run time, as with a prepared statement. Forcing a generic plan (the plan that does not know the parameters) shows it: Subplans Removed: 1 means the executor dropped a partition after seeing the parameters.

-- sql/runtime-pruning.sql
SET plan_cache_mode = force_generic_plan;  -- teaching switch: a plan that does not know the parameter values
PREPARE by_range (timestamptz, timestamptz) AS
  SELECT count(*) FROM events WHERE created_at >= $1 AND created_at < $2;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
EXECUTE by_range('2025-02-01', '2025-03-01');
RESET plan_cache_mode;

SET
PREPARE
                                 QUERY PLAN
----------------------------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   ->  Append (actual rows=1.00 loops=1)
         Subplans Removed: 1
         ->  Seq Scan on events_2025_q1 events_1 (actual rows=1.00 loops=1)
               Filter: ((created_at >= $1) AND (created_at < $2))
(5 rows)

RESET

The default partition, and the trap in it

A DEFAULT partition catches rows that fit nowhere else, which turns the “no partition found” error into silent storage. The catch: once it holds a row, you cannot create a partition whose range would cover that row:

-- sql/default.sql
CREATE TABLE events_default PARTITION OF events DEFAULT;
INSERT INTO events (created_at, kind) VALUES ('2025-08-01', 'late') RETURNING tableoid::regclass AS partition, id;

CREATE TABLE
   partition    | id
----------------+----
 events_default |  4
(1 row)

INSERT 0 1

The row for August landed in events_default (id 4, because the failed insert earlier used up id 3). Creating the Q3 partition is now refused:

-- @expect-error 23514
CREATE TABLE events_2025_q3 PARTITION OF events
  FOR VALUES FROM ('2025-07-01') TO ('2025-10-01');

ERROR:  updated partition constraint for default partition "events_default" would be violated by some row

The way out is to detach the default partition, create the new partition, move the rows across, and re-attach the default, all in one transaction. Note OVERRIDING SYSTEM VALUE: the id column is GENERATED ALWAYS, so copying rows with their existing ids needs it.

-- sql/default-fix.sql
BEGIN;
ALTER TABLE events DETACH PARTITION events_default;
CREATE TABLE events_2025_q3 PARTITION OF events
  FOR VALUES FROM ('2025-07-01') TO ('2025-10-01');
INSERT INTO events OVERRIDING SYSTEM VALUE
  SELECT * FROM events_default WHERE created_at >= '2025-07-01' AND created_at < '2025-10-01';
DELETE FROM events_default WHERE created_at >= '2025-07-01' AND created_at < '2025-10-01';
ALTER TABLE events ATTACH PARTITION events_default DEFAULT;
COMMIT;

SELECT tableoid::regclass AS partition, id, kind FROM events ORDER BY id;

BEGIN
ALTER TABLE
CREATE TABLE
INSERT 0 1
DELETE 1
ALTER TABLE
COMMIT
   partition    | id |  kind
----------------+----+--------
 events_2025_q1 |  1 | signup
 events_2025_q2 |  2 | login
 events_2025_q3 |  4 | late
(3 rows)

Indexes, attach and detach

An index created on the parent is created on every partition, including partitions added later:

-- sql/partition-index.sql
CREATE INDEX events_kind_idx ON events (kind);
SELECT c.relname AS index_name, i.indrelid::regclass AS on_table
FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname LIKE '%kind_idx' ORDER BY 2;
SELECT * FROM pg_partition_tree('events');

CREATE INDEX
       index_name        |    on_table
-------------------------+----------------
 events_kind_idx         | events
 events_2025_q1_kind_idx | events_2025_q1
 events_2025_q2_kind_idx | events_2025_q2
 events_default_kind_idx | events_default
 events_2025_q3_kind_idx | events_2025_q3
(5 rows)

     relid      | parentrelid | isleaf | level
----------------+-------------+--------+-------
 events         |             | f      |     0
 events_2025_q1 | events      | t      |     1
 events_2025_q2 | events      | t      |     1
 events_default | events      | t      |     1
 events_2025_q3 | events      | t      |     1
(5 rows)

ATTACH PARTITION turns an existing table into a partition. PostgreSQL has to prove every row fits the bounds, which normally means scanning the table under a lock; a CHECK constraint that already implies the bounds lets it skip the scan. DETACH PARTITION reverses it:

-- sql/attach.sql
CREATE TABLE events_2025_q4 (LIKE events INCLUDING DEFAULTS INCLUDING INDEXES);
-- a CHECK constraint that matches the bounds lets ATTACH skip the validation scan
ALTER TABLE events_2025_q4 ADD CONSTRAINT q4_bounds
  CHECK (created_at >= '2025-10-01' AND created_at < '2026-01-01');
ALTER TABLE events ATTACH PARTITION events_2025_q4
  FOR VALUES FROM ('2025-10-01') TO ('2026-01-01');

-- sql/detach.sql
ALTER TABLE events DETACH PARTITION events_2025_q4;
SELECT relname, relispartition FROM pg_class WHERE relname = 'events_2025_q4';

ALTER TABLE
    relname     | relispartition
----------------+----------------
 events_2025_q4 | f
(1 row)

DETACH PARTITION ... CONCURRENTLY takes a weaker lock and does not block queries for long, but it has restrictions (docs). One of them we just made: it refuses to run while the table has a default partition.

-- @expect-error 55000
ALTER TABLE events DETACH PARTITION events_2025_q4 CONCURRENTLY;

ERROR:  cannot detach partitions concurrently when a default partition exists

What partitioning buys, measured

The Q4 partition above is tiny. Now a realistic size: big_events is partitioned by month and flat_events holds the same 3 million rows in one ordinary table, both with an index on created_at:

-- sql/big-events.sql
CREATE TABLE big_events (
  id         bigint GENERATED ALWAYS AS IDENTITY,
  created_at timestamptz NOT NULL,
  kind       text NOT NULL
) PARTITION BY RANGE (created_at);

SELECT format('CREATE TABLE big_events_2025_%s PARTITION OF big_events FOR VALUES FROM (%L) TO (%L)',
              to_char(m, 'MM'), m, m + interval '1 month')
FROM generate_series('2025-01-01'::timestamptz, '2025-12-01', interval '1 month') AS m \gexec

CREATE TABLE flat_events (LIKE big_events INCLUDING DEFAULTS);   -- the same rows, one plain table

INSERT INTO big_events (created_at, kind)
SELECT '2025-01-01'::timestamptz + g * interval '10 seconds', 'click' FROM generate_series(0, 2999999) g;
INSERT INTO flat_events SELECT * FROM big_events;
CREATE INDEX ON big_events (created_at);
CREATE INDEX ON flat_events (created_at);
VACUUM ANALYZE big_events;
VACUUM ANALYZE flat_events;
SELECT pg_size_pretty(pg_total_relation_size('flat_events')) AS flat_total,
       pg_size_pretty(pg_total_relation_size('big_events_2025_03')) AS one_month;

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 3000000
INSERT 0 3000000
CREATE INDEX
CREATE INDEX
VACUUM
VACUUM
 flat_total | one_month
------------+-----------
 214 MB     | 19 MB
(1 row)

Queries by key: counting one month.

-- sql/big-query.sql
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF)
SELECT count(*) FROM big_events WHERE created_at >= '2025-06-01' AND created_at < '2025-07-01';

EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF)
SELECT count(*) FROM flat_events WHERE created_at >= '2025-06-01' AND created_at < '2025-07-01';

                                                                               QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate (actual time=17.191..18.694 rows=1.00 loops=1)
   ->  Gather (actual time=16.736..18.688 rows=2.00 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         ->  Partial Aggregate (actual time=16.040..16.040 rows=1.00 loops=2)
               ->  Parallel Seq Scan on big_events_2025_06 big_events (actual time=0.099..11.747 rows=129600.00 loops=2)
                     Filter: ((created_at >= '2025-06-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2025-07-01 00:00:00+00'::timestamp with time zone))
 Planning Time: 0.595 ms
 Execution Time: 18.723 ms
(9 rows)

                                                                                 QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate (actual time=12.368..14.156 rows=1.00 loops=1)
   ->  Gather (actual time=12.232..14.149 rows=3.00 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate (actual time=10.935..10.935 rows=1.00 loops=3)
               ->  Parallel Index Only Scan using flat_events_created_at_idx on flat_events (actual time=0.080..8.002 rows=86400.00 loops=3)
                     Index Cond: ((created_at >= '2025-06-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2025-07-01 00:00:00+00'::timestamp with time zone))
                     Heap Fetches: 0
                     Index Searches: 1
 Planning Time: 0.131 ms
 Execution Time: 14.180 ms
(11 rows)

Pruning does its job (one partition read), yet the partitioned table is not faster: about 19 ms against 14 ms, because the ordinary table’s index already narrows the search to the same rows. Partitioning is not a general speed-up for queries; the documentation says query performance can improve “in certain situations”, particularly when most of the heavily accessed rows are in a small number of partitions.

Retiring old data: where it pays. Deleting March from the flat table versus detaching and dropping March’s partition:

-- sql/drop-vs-delete.sql
\timing on
SELECT pg_current_wal_lsn() AS lsn0 \gset
DELETE FROM flat_events WHERE created_at >= '2025-03-01' AND created_at < '2025-04-01';
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), :'lsn0')) AS wal_for_delete;
VACUUM flat_events;

SELECT pg_current_wal_lsn() AS lsn1 \gset
ALTER TABLE big_events DETACH PARTITION big_events_2025_03 CONCURRENTLY;
DROP TABLE big_events_2025_03;
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), :'lsn1')) AS wal_for_drop;
\timing off

Timing is on.
Time: 0.763 ms
DELETE 267840
Time: 91.629 ms
 wal_for_delete
----------------
 14 MB
(1 row)

Time: 0.285 ms
VACUUM
Time: 42.519 ms
Time: 0.238 ms
ALTER TABLE
Time: 2.380 ms
DROP TABLE
Time: 2.719 ms
 wal_for_drop
--------------
 2143 kB
(1 row)

Time: 0.196 ms
Timing is off.

The DELETE removes 267 840 rows in 92 ms, writes 14 MB of WAL, and leaves dead tuples that VACUUM then needs 43 ms to clean up (and, as the MVCC lesson showed, the file does not shrink). Detaching and dropping the partition took about 5 milliseconds in total, wrote a seventh as much WAL (2 MB against 14 MB), and returned the disk space at once. The gap grows with the size of what you remove.

The documentation’s rule of thumb for when to partition is that “the size of the table should exceed the physical memory of the database server” (docs). A few million rows with good indexes do not need it.

One more thing not to look for: ALTER TABLE ... SPLIT PARTITION and MERGE PARTITIONS do not exist in PostgreSQL 18.6:

-- @expect-error 42601
ALTER TABLE big_events SPLIT PARTITION big_events_2025_04 INTO (
  PARTITION big_events_2025_04a FOR VALUES FROM ('2025-04-01') TO ('2025-04-16'),
  PARTITION big_events_2025_04b FOR VALUES FROM ('2025-04-16') TO ('2025-05-01'));

ERROR:  syntax error at or near "SPLIT"
LINE 1: ALTER TABLE big_events SPLIT PARTITION big_events_2025_04 IN...
                               ^

Reading AI-generated code

An assistant is asked for a partitioned sessions table, spreading rows over four hash partitions. It writes the parent and the first two partitions, and its comment claims all four exist:

🐛 Spot the bug

-- AI comment: "sessions are spread evenly across 4 hash partitions"
CREATE TABLE user_sessions (id bigint NOT NULL, token text NOT NULL) PARTITION BY HASH (id);
CREATE TABLE user_sessions_0 PARTITION OF user_sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_sessions_1 PARTITION OF user_sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);

Show the answer

MODULUS 4 means four buckets, and only remainders 0 and 1 have a partition. PostgreSQL does not complain when the tables are created; it fails when a row hashes to a missing bucket, and the whole statement fails, not just that row:

-- sql/user-sessions.sql
CREATE TABLE user_sessions (id bigint NOT NULL, token text NOT NULL) PARTITION BY HASH (id);
CREATE TABLE user_sessions_0 PARTITION OF user_sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_sessions_1 PARTITION OF user_sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);

-- @expect-error 23514
INSERT INTO user_sessions SELECT g, 'x' FROM generate_series(1, 8) g;

ERROR:  no partition of relation "user_sessions" found for row
DETAIL:  Partition key of the failing row contains (id) = (2).

The fix is to create every remainder. With all four in place the same insert succeeds and rows spread over the buckets:

-- sql/user-sessions-fixed.sql
CREATE TABLE user_sessions_2 PARTITION OF user_sessions FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE user_sessions_3 PARTITION OF user_sessions FOR VALUES WITH (MODULUS 4, REMAINDER 3);
INSERT INTO user_sessions SELECT g, 'x' FROM generate_series(1, 8) g;
SELECT tableoid::regclass AS partition, count(*) FROM user_sessions GROUP BY 1 ORDER BY 1;

CREATE TABLE
CREATE TABLE
INSERT 0 8
    partition    | count
-----------------+-------
 user_sessions_0 |     1
 user_sessions_1 |     3
 user_sessions_2 |     1
 user_sessions_3 |     3
(4 rows)

Mental Model

A partitioned table is a filing cabinet with a label on each drawer. The parent is the label rule; the partitions are the drawers. Pruning is opening only the drawers whose label can match; a bulk delete of a whole drawer is taking it out of the cabinet instead of removing papers one by one. The rule that every unique key must contain the label column is the price of not searching all drawers to check for duplicates.

Tips / gotchas

• Choose the partition key to match your dominant query pattern. If you query by date, partition by date so pruning can kick in.
• Pruning only happens when the partition key appears in the WHERE clause. A query that filters on something else scans all partitions.
• Create future partitions ahead of time, or automate it. An insert with no matching partition fails unless you have a default partition, and a default partition full of rows makes creating the missing partition hard.
• Dropping old data by detaching or dropping a whole partition is far faster and lighter than a row-by-row DELETE.
• Every unique or primary key must include the partition key; plan for that before you partition an existing table.
• Hash partitions need every remainder; range partitions need their gaps covered.

🐘 Same table, smaller problems

Partitioning does not make a table smaller — your data is just as big. It makes each operation smaller by ensuring queries, indexes, and bulk deletes touch only the slice they need. The skill is picking a partition key that matches how you actually query, so the planner can prune everything else away.

Trade-offs

Option	Benefit	Cost
Partitioning a large table	Retiring data in milliseconds instead of a 92 ms delete plus vacuum, less WAL, smaller per-partition indexes and maintenance	Queries must be designed around the partition key; unique keys must include it; more tables to manage; no automatic speed-up (19 ms vs 14 ms above)
Default partition	Nothing is ever rejected	Blocks creating overlapping partitions until it is emptied; hides missing-partition bugs
No partitioning (single table)	Simple query and schema, no partition key to think about	Maintenance keeps getting slower as the table grows; deleting old data means row-by-row DELETE

Common Misconceptions

• “Partitioning makes queries faster.” Only when pruning removes work an index would not; the month count above was no faster.
• “The parent table stores the rows.” It stores none; tableoid shows the partition that does.
• “A primary key on id is still enforced.” It must include the partition key, so id alone is not unique across partitions.
• “A default partition is a safe catch-all.” It stops you creating the partition those rows belong in until you move them.
• “There is a SPLIT PARTITION command.” Not in 18.6 (syntax error above).

What decides which partition a row is stored in?

The order in which rows were inserted The value of the partition key Which connection inserted the row The size of the row in bytes

Partition pruning lets the planner skip partitions only when...

The table has fewer than four partitions The query filters on the partition key in its WHERE clause You disable indexes The query uses ORDER BY

For which workload is partitioning most clearly worthwhile?

A small lookup table of a few hundred rows A huge time-series table queried by date where old data is dropped in bulk A table that is only ever read one row at a time by primary key Any table, since partitioning is always faster

You try ALTER TABLE events ADD PRIMARY KEY (id) on a table partitioned by created_at. What happens?

It succeeds and enforces uniqueness across all partitions It fails: the key must include the partition key column It succeeds but only checks the newest partition It silently creates a non-unique index

A HASH-partitioned table with MODULUS 4 has partitions only for remainders 0 and 1. What happens on INSERT of 8 rows?

Rows for missing remainders are dropped silently They go to the parent table The whole INSERT fails with no partition of relation found for row PostgreSQL creates the missing partitions

Submit

Mark as complete