Table partitioning

Table partitioning splits one large table into smaller physical tables, called partitions, inside the same database server, while your queries still see one logical table. The database routes each row to a partition by a partition key such as created_at, skips partitions a query cannot need (partition pruning), and lets you remove a month of data with a quick metadata change instead of a giant DELETE.

Picture it first: one register per month

A busy bank branch records every payment in one thick register. By year three it runs to 4,000 pages. Finding last week’s entries means leafing through from the back, and when the auditor says “destroy everything older than a year”, a clerk spends days crossing out old entries one by one, leaving the register just as thick.

So the head clerk switches to one thin register per month, labelled on the spine and kept on one shelf. Same clerk, same counter, same shelf. When a customer asks for September’s payments, the clerk pulls only September. When a month expires, the whole register goes to the record room in one trip.

Map it back: the shelf is the logical table your SQL names, each register is a partition (an ordinary table holding one slice of rows), the spine labels are partition bounds, pulling only September is partition pruning, and sending a register away is detaching or dropping a partition. Notice what did not change: there is still one clerk. Partitioning organises the work on one server; it does not add a second one.

Partitioning is not sharding

Partitioning and sharding both split data by a key, which is why they get confused. The difference is where the pieces live.

With partitioning, every partition sits on the same server: one write-ahead log, one buffer cache, one set of CPUs. Transactions spanning partitions commit atomically, joins work as usual, and the SQL doesn’t change. Shards live on different servers, so you gain write capacity but give up easy joins and single-node transactions; database sharding has the full comparison with replication.

What partitioning buys you is manageability and locality:

• Pruning. A query that filters on the key reads one partition’s small indexes instead of one huge index. The PostgreSQL documentation puts it neatly: partitioning “effectively substitutes for the upper tree levels of indexes”, so the hot parts are more likely to fit in memory.
• Cheap bulk removal. Dropping a partition deletes millions of rows without row-by-row work or vacuum debt.
• Smaller maintenance units. Vacuum and REINDEX run per partition, and old partitions can move to cheaper storage.

What it does not buy you is capacity. Every insert still lands on the same server and the same log; if one primary cannot keep up with your writes, climb the database scaling ladder instead.

Horizontal and vertical partitioning

Everything else in this article is horizontal partitioning: splitting rows by a key. Vertical partitioning splits columns: you move wide or rarely read columns into a side table that shares the primary key.

Take a merchants table read on every payment for its name, status and fee rate, but also holding scanned KYC documents and notes. Split it in two:

• merchants (merchant_id, name, status, fee_bps, …): narrow, hot, many rows per 8 KB page, easily cached.
• merchant_kyc (merchant_id PRIMARY KEY REFERENCES merchants, pan_scan, address_proof, notes): wide, read only during onboarding and audits.

You gain a hot table that fits in memory, and a constantly updated column (a last_seen_at, say) can live in its own narrow table so each update copies a small row version, not a wide one. You pay with a join whenever you need both halves.

PostgreSQL does a version of this automatically, called TOAST. When a row would exceed about 2 kB, it compresses large variable-length values and, if needed, moves them into a hidden TOAST table in roughly 2,000-byte chunks, leaving an 18-byte pointer in the row; an update that doesn’t change a TOASTed value doesn’t rewrite it. In the documentation’s test with stored HTML pages, the main table ended up holding only about 10% of the data. InnoDB’s default DYNAMIC row format similarly moves long VARCHAR, TEXT and BLOB values off-page behind a 20-byte pointer.

TOAST only kicks in for wide rows, so twenty rarely read 100-byte columns still justify a manual split. Moving whole tables into a separate database is a different move: a functional split, further up the scaling ladder.

How a partitioned table works

In PostgreSQL’s declarative partitioning, the table you create with PARTITION BY is a parent with no storage of its own. Its partitions are ordinary tables, each with bounds that say which key values it accepts.

• Writes are routed. An INSERT into the parent is sent to the one partition whose bounds contain the row’s key. If no partition matches and there is no default partition, the insert fails.
• Reads are pruned. The planner compares the WHERE clause with each partition’s bounds and leaves out any partition that provably holds no matching rows.
• Indexes are per partition. An index created on the parent is a template; each partition gets its own physical index.

Declarative partitioning in PostgreSQL

Here is a payments table that keeps 13 months online, partitioned by month:

CREATE TABLE upi_txn (
    txn_id        bigint       NOT NULL,
    created_at    timestamptz  NOT NULL,
    payer_vpa     text         NOT NULL,
    payee_vpa     text         NOT NULL,
    amount_paise  bigint       NOT NULL,
    status        text         NOT NULL,
    PRIMARY KEY (txn_id, created_at)          -- must include the partition key
) PARTITION BY RANGE (created_at);

CREATE TABLE upi_txn_2026_09 PARTITION OF upi_txn
    FOR VALUES FROM ('2026-09-01 00:00+05:30') TO ('2026-10-01 00:00+05:30');
CREATE TABLE upi_txn_2026_10 PARTITION OF upi_txn
    FOR VALUES FROM ('2026-10-01 00:00+05:30') TO ('2026-11-01 00:00+05:30');

CREATE INDEX ON upi_txn (payer_vpa, created_at);   -- created on every partition

Range bounds include the lower value and exclude the upper one, so adjacent partitions can share a boundary and a row stamped exactly at midnight on 1 October lands in October. The index on the parent cascades: every existing partition gets a matching index, and so does every partition created or attached later.

List and hash use the same pattern with a different bound clause:

-- LIST: one partition per group of states, plus a catch-all
CREATE TABLE orders (order_id bigint, state text NOT NULL, placed_at timestamptz)
    PARTITION BY LIST (state);
CREATE TABLE orders_south PARTITION OF orders FOR VALUES IN ('KA', 'TN', 'KL', 'AP', 'TG');
CREATE TABLE orders_west  PARTITION OF orders FOR VALUES IN ('MH', 'GJ', 'GA');
CREATE TABLE orders_other PARTITION OF orders DEFAULT;

-- HASH: four even buckets by wallet
CREATE TABLE wallet_ledger (wallet_id bigint NOT NULL, entry_id bigint, amount_paise bigint)
    PARTITION BY HASH (wallet_id);
CREATE TABLE wallet_ledger_p0 PARTITION OF wallet_ledger FOR VALUES WITH (MODULUS 4, REMAINDER 0);
-- … p1, p2, p3 with REMAINDER 1, 2, 3

A few more pieces complete the toolkit:

• DEFAULT partition. It catches rows that match no other partition (not available for hash). It is convenient, and it has costs: adding a new partition makes PostgreSQL scan the default to check that none of its rows belong in the newcomer, and DETACH … CONCURRENTLY is refused while a default partition exists.
• Sub-partitioning. A partition can itself be partitioned, for example by month and then by hash of payer_vpa. The counts multiply quickly, so the documentation advises restraint.
• ATTACH PARTITION. You can load and check a standalone table, then attach it. Attaching takes only a SHARE UPDATE EXCLUSIVE lock on the parent, but scans the new table to validate its rows unless a matching CHECK constraint already proves they fit.
• DETACH PARTITION … CONCURRENTLY (PostgreSQL 14 and later) turns a partition back into a standalone table without blocking queries on the parent. It runs as two internal transactions and waits for in-flight queries, so it cannot run inside a transaction block; if it is interrupted, DETACH … FINALIZE completes it.

There is no ALTER TABLE … PARTITION BY for an existing table. Converting a live table is a migration, covered under failure modes below.

Range, list or hash

The method decides which rows end up together, and that decides which queries can be pruned and how data can be removed.

Range by time is the common case by a wide margin, because the most valuable thing partitioning does is make old data cheap to remove. Reach for hash when a table is too big for comfortable vacuums and index rebuilds but has no time dimension, and every important query carries the key, such as a ledger looked up by wallet.

Partition pruning

Pruning is where the query-time win comes from, and it is easy to lose by accident.

At plan time, the planner compares constants in the WHERE clause with the partition bounds. Here is the earlier query on PostgreSQL 18 with the session time zone set to Asia/Kolkata:

EXPLAIN SELECT txn_id, amount_paise FROM upi_txn
WHERE payer_vpa = 'asha@upi'
  AND created_at >= '2026-09-15 00:00+05:30' AND created_at < '2026-10-15 00:00+05:30';

 Append  (cost=0.57..98.52 rows=25 width=16)
   ->  Index Scan using upi_txn_2026_09_payer_vpa_created_at_idx on upi_txn_2026_09 upi_txn_1  (cost=0.57..49.21 rows=12 width=16)
         Index Cond: ((payer_vpa = 'asha@upi'::text) AND (created_at >= '2026-09-15 00:00:00+05:30'::timestamp with time zone) AND (created_at < '2026-10-15 00:00:00+05:30'::timestamp with time zone))
   ->  Index Scan using upi_txn_2026_10_payer_vpa_created_at_idx on upi_txn_2026_10 upi_txn_2  (cost=0.57..49.19 rows=13 width=16)
         Index Cond: ((payer_vpa = 'asha@upi'::text) AND (created_at >= '2026-09-15 00:00:00+05:30'::timestamp with time zone) AND (created_at < '2026-10-15 00:00:00+05:30'::timestamp with time zone))

Eleven of the thirteen partitions never appear in the plan. The Append node simply concatenates the two partition scans; see query execution plans for how to read the rest.

At execution time, PostgreSQL can also prune using values the planner could not see: a prepared statement’s parameters, the result of a subquery, or the outer value of a nested-loop join. Partitions removed when execution starts show up as Subplans Removed: N in EXPLAIN; ones skipped later show as (never executed). Both are controlled by enable_partition_pruning, which is on by default.

Pruning fails in three common ways:

• The query does not mention the key. SELECT … WHERE txn_id = 9001 has nothing to compare with the bounds, so it probes the txn_id index of all 13 partitions. That is 13 small B-tree descents instead of one; tolerable at 13 partitions, painful at 1,000. Include created_at when you know it, or use IDs that carry their timestamp so the application can derive it (design a unique ID generator).
• A function wraps the key. WHERE date_trunc('month', created_at) = '2026-09-01' cannot be matched against range bounds. Rewrite it as a half-open range on the bare column.
• The ORM hides it. A repository method that loads by primary key alone quietly turns every lookup into an all-partition probe. Check the plans of your top queries.

Two related settings, enable_partitionwise_join and enable_partitionwise_aggregate, join matching partitions pairwise or aggregate each partition separately. Both are off by default: they make planning costlier, and each per-partition node can use its own work_mem. Enable them per session for analytical queries, not globally on a busy OLTP server.

Retention: drop a partition, don’t delete rows

This is the use case that justifies partitioning on its own. Take the UPI-style table at 10 million payments a day:

• Rows per month: 10M × 30 = 300 million.
• Heap per month: about 200 bytes of columns plus a 24-byte row header and a 4-byte line pointer, rounded to ~250 bytes with alignment: 300M × 250 B ≈ 75 GB, or 75 × 10⁹ ÷ 8,192 ≈ 9.2 million 8 KB pages.
• Indexes per month: three B-trees at roughly 30 to 40 bytes per entry: about 30 GB. A month is therefore about 105 GB, and 13 months about 1.4 TB, with 13 × 30 = 390 GB of indexes.

Now remove the oldest month from an unpartitioned table with DELETE FROM upi_txn WHERE created_at < '2025-10-01 00:00+05:30'. Under MVCC, PostgreSQL does not erase anything; it marks each row as deleted:

• Every one of the 9.2 million pages is modified. Each row gets a WAL record; at an assumed ~55 bytes each that is 300M × 55 B ≈ 16.5 GB. With full_page_writes on (the default), the first change to each page after a checkpoint also logs the whole 8 KB page: up to 9.2M × 8 KB ≈ 75 GB more. Call it about 90 GB of WAL, before compression, and every byte is archived and streamed to every replica, which is where replication lag spikes come from.
• Then VACUUM pays again. It reads the 75 GB of heap pages holding dead rows and makes a full pass over every index on the table, all 390 GB, to remove 900 million dead index entries: about 465 GB of reads, plus more WAL for its own changes.
• The disk does not shrink. Vacuum makes the space reusable inside the table but the files keep their size: 105 GB of bloat until new rows fill the holes.
• It holds a long transaction open for the whole delete, which stops vacuum from cleaning up dead rows anywhere else in the database until it commits.

With monthly partitions the same retention is two statements:

ALTER TABLE upi_txn DETACH PARTITION upi_txn_2025_09 CONCURRENTLY;
-- optional: archive it first, e.g. COPY upi_txn_2025_09 TO PROGRAM '…' or pg_dump -t
DROP TABLE upi_txn_2025_09;

The detach updates a few catalog rows; the drop unlinks the files. That is kilobytes of WAL, no vacuum, and 105 GB back to the filesystem immediately.

A plain DROP TABLE on a partition (or a non-concurrent detach) needs an ACCESS EXCLUSIVE lock on the parent. It is held only briefly, but it queues behind any long-running query and everything queues behind it, so set a short lock_timeout and retry, or detach concurrently first.

Archive before you drop if regulators or dispute teams need old data. A detached partition is an ordinary table: export it with COPY or pg_dump to object storage, or load it into a warehouse for analytics.

Create partitions before you need them. Nobody wants a pager alert at 00:00 on the 1st because inserts have nowhere to go. The pg_partman extension automates the whole lifecycle on top of native partitioning: create_parent() sets up the scheme, premake (default 4) keeps that many future partitions ready, and a retention setting detaches or drops expired ones. A built-in background worker runs the maintenance on a schedule, so you don’t need cron.

SELECT partman.create_parent(
    p_parent_table => 'public.upi_txn',
    p_control      => 'created_at',
    p_interval     => '1 month');

UPDATE partman.part_config
   SET retention = '13 months', retention_keep_table = false
 WHERE parent_table = 'public.upi_txn';

By default retention_keep_table is true, which only detaches expired partitions and leaves them for you to archive; setting it to false drops them outright.

The rules that bite

Four rules surprise most teams that adopt partitioning.

Unique keys must include the partition key

In both PostgreSQL and MySQL, a primary key or unique constraint on a partitioned table must contain every partition key column. The reason is mechanical: there is no global index. Each partition’s index only knows its own rows, so the only way the database can promise uniqueness across the table is if equal keys are guaranteed to land in the same partition.

Every index is local

An index on a partitioned table is really one index per partition. A lookup that cannot be pruned descends each of them, and you cannot build one with CREATE INDEX CONCURRENTLY on the parent. The documented workaround is CREATE INDEX … ON ONLY upi_txn, which creates an invalid parent index, then CREATE INDEX CONCURRENTLY on each partition and ALTER INDEX … ATTACH PARTITION for each one; the parent index becomes valid when the last is attached. Indexes in practice covers concurrent builds in general.

Changing the key moves the row

An UPDATE that changes created_at so that the row no longer fits its partition moves it to the right one, in effect a delete plus an insert. If another session updates or deletes that same row at the same moment, it gets a serialization failure (SQLSTATE 40001) and must retry. Partition on something that never changes after insert: the creation time, not a status column.

Statistics, foreign keys and partition counts

• ANALYZE the parent yourself. Autovacuum vacuums and analyzes each partition but never analyzes the partitioned parent, so queries that rely on whole-table statistics can get poor plans. Run ANALYZE upi_txn after big loads and on a schedule.
• Foreign keys work in both directions in PostgreSQL (references to a partitioned table arrived in version 12), but the referenced key must include the partition key, so a referencing table needs created_at too. MySQL does not allow foreign keys on partitioned InnoDB tables at all.
• Don’t over-partition. The PostgreSQL documentation says the planner handles “up to a few thousand partitions fairly well” if typical queries prune all but a few. Each partition’s metadata is loaded into every session that touches it, so memory and planning time grow with the partitions a query keeps. Never assume more partitions are better.

Partitioning in MySQL

MySQL 8.0 supports partitioning only in the InnoDB and NDB engines, and only horizontally; there is no vertical partitioning. It offers RANGE, LIST, HASH and KEY (where MySQL supplies the hash function), plus RANGE COLUMNS and LIST COLUMNS, which accept dates and strings directly, and linear variants of hash and key.

CREATE TABLE upi_txn (
  txn_id        BIGINT       NOT NULL,
  created_at    DATETIME     NOT NULL,
  payer_vpa     VARCHAR(64)  NOT NULL,
  amount_paise  BIGINT       NOT NULL,
  PRIMARY KEY (txn_id, created_at),
  KEY (payer_vpa, created_at)
)
PARTITION BY RANGE COLUMNS (created_at) (
  PARTITION p2026_09 VALUES LESS THAN ('2026-10-01'),
  PARTITION p2026_10 VALUES LESS THAN ('2026-11-01'),
  PARTITION pmax     VALUES LESS THAN (MAXVALUE)
);

ALTER TABLE upi_txn DROP PARTITION p2026_09;               -- retention
ALTER TABLE upi_txn REORGANIZE PARTITION pmax INTO (       -- open next month
  PARTITION p2026_11 VALUES LESS THAN ('2026-12-01'),
  PARTITION pmax     VALUES LESS THAN (MAXVALUE));

The rules rhyme with PostgreSQL’s, with a few of their own:

• Every unique key, including the primary key, must use every column in the partitioning expression. CREATE TABLE fails otherwise.
• No foreign keys, in either direction, on partitioned InnoDB tables.
• At most 8,192 partitions per table, subpartitions included. Only RANGE and LIST partitions can be subpartitioned, and subpartitions must use HASH or KEY.
• Pruning works for equality, IN lists and ranges on the partitioning column; EXPLAIN lists the partitions it will touch in its partitions column.
• EXCHANGE PARTITION … WITH TABLE swaps a partition with an identical standalone table, which is the MySQL way to archive a month or bulk-load one.

Keeping an empty pmax partition and splitting it with REORGANIZE PARTITION before each month starts is the MySQL equivalent of pre-creating partitions. If pmax already holds rows, the reorganize has to copy them, so split it while it is empty.

Choosing the key and the granularity

The key should satisfy three tests at once:

1. Most important queries filter on it, so they prune.
2. It defines what you delete together, so retention is a drop. The PostgreSQL docs recommend designing so that “all data to be removed at once is located in a single partition”.
3. It never changes after the row is written, so rows never move.

For event-like data (payments, orders, logs, messages), the creation timestamp passes all three. For multi-tenant SaaS data, LIST or HASH by tenant passes the first and third and makes offboarding a tenant a drop; see multi-tenant data.

The granularity is a trade between partition size and partition count. Use the UPI numbers again, keeping 13 months:

Inserts into the payer_vpa index land at random positions, so the whole index of the current partition is hot: 300M entries × ~40 B ÷ 0.9 leaf fill ≈ 13 GB a month, against under half a gigabyte a day.

Rules of thumb that follow:

• Don’t partition small tables. The PostgreSQL docs suggest the benefit appears once a table exceeds the server’s memory.
• Keep the count in the tens to low hundreds for OLTP; warehouses tolerate more, since planning time matters less there.
• Shrink partitions if their write-hot indexes stop fitting in memory, and grow them if frequent queries lack the key.

For this table, weekly is a sound middle: about 57 partitions, a 3 GB hot index, and un-keyed lookups that are slower but bounded. If nearly every query carried a time range, daily would be fine too.

Going deeper: failure modes

The month that wasn’t there. Without a default partition, an insert with no matching partition fails with no partition of relation "upi_txn" found for row. Teams discover this at midnight on the 1st. Pre-create at least a few intervals ahead and alert when fewer than two future partitions exist.

The default partition that quietly filled up. With a default partition, those rows succeed and pile up there instead. Creating the missing month later forces PostgreSQL to scan the default under an ACCESS EXCLUSIVE lock on it, and its presence blocks concurrent detach. Keep the default empty and alert on any row in it.

One hot partition. With time partitioning, every write goes to the newest partition. That is good for caching and bad if you hoped to spread write load: you cannot. For write throughput, see scaling writes and sharding.

Plans that touch everything. A report without a date filter or an ORM lookup by ID visits and locks every partition. With hundreds of partitions, planning and locking can cost more than the query itself.

Converting a live table. PostgreSQL cannot repartition a table in place. The usual route: create the new partitioned parent; add a CHECK (created_at < '2026-11-01 00:00+05:30') constraint to the old table (as NOT VALID, then VALIDATE CONSTRAINT, which does not block writes); pre-build the parent’s indexes on it with CREATE INDEX CONCURRENTLY; attach it as one large historical partition, which skips the validation scan because the constraint already proves the rows fit; and swap names inside a short transaction. New months go to new partitions, and the giant historical one ages out at its retention date. Rehearse it like any online migration.

Cheat sheet

How to say it in the interview

“The transactions table grows by about 300 million rows a month and we keep 13 months, so I’d range-partition it by created_at, weekly or monthly. It’s still one table in one database, so transactions and joins don’t change. Queries carry a time range, so they prune to one or two partitions with small, cache-friendly indexes. Retention becomes detaching and dropping the oldest partition, a metadata operation, instead of a 300-million-row DELETE that writes about 90 GB of WAL and leaves bloat; pg_partman pre-creates future partitions. The costs I accept: the primary key becomes (txn_id, created_at), so IDs must be unique by construction, and lookups without a date probe every partition. And it adds no write capacity; if one primary can’t keep up, that’s a sharding conversation.”

How is partitioning different from sharding? Partitions share one server’s log, memory and transactions, so the application doesn’t change. Shards live on different servers: more write and storage capacity, but no cheap joins or single-node transactions.

Why can’t the primary key be just txn_id? There is no global index. Each partition’s index enforces uniqueness only within that partition, so the database requires the partition key in every unique key so that duplicates must collide in the same partition. Make IDs unique by construction, or keep a separate unpartitioned ID table.

When would you not partition? When the table fits comfortably in memory, when the main queries share no column to partition by, or when the real problem is write throughput. Then it only adds planning overhead and DDL chores.

What happens to a query that doesn’t filter on the partition key? It runs against every partition, probing each local index or scanning each partition, then appends the results. It is still correct, just proportionally slower, which is why the partition count must stay modest.

Daily or monthly partitions? Match the retention unit and the typical query window, then check two numbers: the size of the indexes being written to (they should stay in memory) and the number of partitions an un-keyed query would probe. Weekly is often the compromise.

Where to go next

• Database sharding: splitting data across servers when one primary is no longer enough.
• Database scaling: where partitioning sits on the ladder from query tuning to distributed SQL.
• Vacuum and bloat: why the bulk DELETE above is so expensive in PostgreSQL.
• Indexes in practice: BRIN indexes for time series, and building indexes without blocking writes.
• Query execution plans: reading Append, Subplans Removed and (never executed).
• OLTP vs OLAP, warehouses and columnar storage: where detached history goes for analytics.
• Online schema and data migrations: converting a live table without downtime.

References and further reading

1. PostgreSQL documentation: Table Partitioningpostgresql.orgRange, list and hash partitioning, DEFAULT partitions, attach and detach, partition pruning at plan and execution time, the unique-key rule, and the best-practice guidance on partition counts.
2. PostgreSQL documentation: ALTER TABLEpostgresql.orgATTACH PARTITION validation scans and how a CHECK constraint avoids them; DETACH PARTITION CONCURRENTLY’s two transactions, lock levels and restrictions.
3. PostgreSQL documentation: UPDATEpostgresql.orgRow movement when the partition key changes, and the serialization failure a concurrent writer receives.
4. PostgreSQL documentation: Query Planning configurationpostgresql.orgenable_partition_pruning (on by default) and enable_partitionwise_join / enable_partitionwise_aggregate (off by default) with their memory and planning costs.
5. PostgreSQL documentation: Routine Vacuumingpostgresql.orgAutovacuum does not ANALYZE partitioned parent tables, so parent-level statistics need a manual ANALYZE.
6. PostgreSQL documentation: TOASTpostgresql.orgAutomatic out-of-line storage for wide values: the 2 kB threshold, ~2,000-byte chunks, the 18-byte pointer and the HTML-pages test.
7. PostgreSQL documentation: Write Ahead Log configurationpostgresql.orgfull_page_writes: the whole page is logged on its first change after a checkpoint, used in the DELETE arithmetic.
8. pg_partman documentationgithub.comcreate_parent, premake (default 4), retention and retention_keep_table, and the background worker that runs maintenance.
9. MySQL 8.0 Reference Manual: Partitioningdev.mysql.comRANGE, LIST, HASH, KEY and COLUMNS partitioning, InnoDB and NDB only, no vertical partitioning, partition pruning and DROP PARTITION.
10. MySQL 8.0 Reference Manual: Restrictions and Limitations on Partitioningdev.mysql.comEvery unique key must include all partitioning columns, no foreign keys on partitioned InnoDB tables, and the 8,192-partition limit.