Database Partitioning Architecture, in depth: range, list, hash and composite schemes, partition pruning, partitioned indexes and the retention lifecycle

What partitioning buys and what it costs

Think of a partitioned table as a routing layer over ordinary tables. Each partition has its own heap, its own indexes, its own statistics and its own vacuum state. That independence is the source of every benefit and every cost.

The costs are just as concrete. Every query that cannot prune touches every partition, so a lookup by a non-key column on a table with 400 partitions performs 400 index probes where an unpartitioned table would perform one. Planning time and memory grow with the number of partitions that survive pruning. Uniqueness can only be enforced within the constraints of the partition key. And someone has to create future partitions before rows arrive for them, forever. OLTP versus OLAP explains the access patterns that make time-based partitioning attractive.

The four schemes

Range partitioning assigns each partition a half-open interval of key values, such as one calendar month of created_at. It suits time series, logs, orders and anything with a natural age. List partitioning assigns explicit values, such as a region or tenant tier, and suits a small, stable set of categories with different retention or placement. Hash partitioning takes a modulus and remainder of a hash of the key and spreads rows evenly; it suits tables with no useful range where the goal is smaller per-partition indexes and parallel maintenance, but it offers no retention benefit and prunes only on equality. Composite partitioning nests one scheme inside another, for example range by month and then hash by tenant_id into eight sub-partitions, and it multiplies the partition count, so use it only when both levels earn their keep.

How pruning actually works

PostgreSQL prunes at two moments. At plan time it compares constant predicates against each partition's bounds and removes those that cannot match. At execution time, available since PostgreSQL 11, it prunes again using values only known once the query runs: bind parameters of a generic prepared statement, results of a subquery, or the outer side of a nested-loop join. EXPLAIN shows the second kind as Subplans Removed. Both are governed by enable_partition_pruning, which is on by default.

Pruning requires the predicate to be on the partition key itself, in a form the planner can compare. WHERE created_at >= $1 prunes; WHERE date_trunc('day', created_at) = $1 does not, because the expression is not the key. Casting across types, wrapping the key in a function, or filtering through an OR with a non-key column all defeat it. The test is simple: run EXPLAIN on the real query shapes your application sends and count the partitions listed.

Two further optimisations exist and are off by default because they cost planning time: enable_partitionwise_join joins matching partitions of two tables partitioned the same way, and enable_partitionwise_aggregate aggregates each partition separately before combining. They help when two large tables share the partition scheme exactly; enable them per session for reporting jobs rather than globally.

Worked example: a 90-day event table

An API gateway writes about 25 million events a day and keeps 90 days, roughly 2.2 billion rows. Queries are dashboards over the last 24 hours, investigations filtered by tenant_id and a time window, and a nightly export of yesterday. Retention was a nightly DELETE that generated tens of gigabytes of WAL, bloated the table and fought autovacuum; vacuum and bloat explains why that pattern degrades.

Every query filters on time, and retention is by time, so range on created_at is the clear choice. Daily partitions give 90 live partitions of about 25 million rows each; monthly partitions give three or four of about 750 million rows. Daily aligns exactly with the retention boundary and keeps each partition's indexes small; monthly would force either keeping up to 30 extra days or deleting inside a partition, which reintroduces the original problem. Daily it is.

CREATE TABLE events (
    tenant_id   bigint       NOT NULL,
    event_id    bigint       NOT NULL,
    created_at  timestamptz  NOT NULL,
    kind        text         NOT NULL,
    payload     jsonb,
    PRIMARY KEY (tenant_id, event_id, created_at)   -- must include the partition key
) PARTITION BY RANGE (created_at);

-- Partitioned index: created on the parent, cascaded to every partition.
CREATE INDEX events_tenant_time ON events (tenant_id, created_at);

CREATE TABLE events_2026_09_30 PARTITION OF events
    FOR VALUES FROM ('2026-09-30 00:00+00') TO ('2026-10-01 00:00+00');

-- A dashboard query: plan-time pruning keeps one or two partitions.
EXPLAIN SELECT kind, count(*)
FROM events
WHERE created_at >= now() - interval '24 hours'
GROUP BY kind;

Note the primary key. A unique or primary key constraint on a partitioned table must include every partition key column, because each partition enforces uniqueness only over its own rows and there is no global index. If the business needs event_id globally unique on its own, generate it from a sequence or a time-ordered identifier and accept that the database checks uniqueness only per partition, or keep a separate unpartitioned lookup table.

Choosing the key and the granularity

Pick the key from two lists: the predicates on your most frequent and most expensive queries, and the unit in which data is deleted, archived or loaded. The best key appears on both lists. If they disagree, retention usually wins, because a slow query can be fixed with an index while a retention design that fights the partition boundary cannot be fixed without repartitioning.

For granularity, three forces pull against each other. Smaller partitions prune more precisely and make retention finer. Larger partitions mean fewer objects, faster planning for queries that span many of them, and fewer files and catalog entries. A practical heuristic: choose the coarsest granularity that still matches your retention step, and check that the number of partitions a typical query touches after pruning stays in single digits. Do not partition small tables at all; if the whole table and its indexes fit comfortably in memory, partitioning adds work and removes nothing.

Indexes and constraints across partitions

An index created on the parent becomes a partitioned index: each partition gets its own local index and new partitions inherit it automatically. There is no single B-tree spanning partitions, so an index lookup on a non-key column probes one local index per surviving partition. Database indexing covers the per-index trade-offs; the partitioning twist is that index count multiplies by partition count.

Creating an index on a large existing partitioned table cannot use CONCURRENTLY on the parent. The standard workaround builds the parent index as an invalid shell and attaches concurrently built partition indexes one at a time, so no step holds a long write lock:

CREATE INDEX events_kind_idx ON ONLY events (kind);          -- invalid shell on the parent

CREATE INDEX CONCURRENTLY events_2026_09_29_kind ON events_2026_09_29 (kind);
ALTER INDEX events_kind_idx ATTACH PARTITION events_2026_09_29_kind;
-- repeat per partition; the parent index becomes valid when every partition is attached

Foreign keys that reference a partitioned table are supported from PostgreSQL 12, but the referenced columns must be covered by a unique constraint, which again must include the partition key. Many teams keep partitioned fact tables as referencing tables only and never the target of a foreign key.

The partition lifecycle: create ahead, attach, detach, drop

Partitions must exist before rows arrive. Without a matching partition an insert fails, unless a default partition exists, and the default partition is a trap: rows silently accumulate there, and later creating a partition for that range requires scanning the default partition to prove no conflicting rows exist, under a lock. Treat a non-empty default partition as an alert, not a safety net. Create partitions several periods ahead with a scheduled job or an extension such as pg_partman, and alert when the furthest future partition is closer than a few days away.

To attach a partition that was bulk loaded as a standalone table, first add a CHECK constraint matching the partition bounds. PostgreSQL can then skip the validation scan during ATTACH PARTITION. To remove old data, DETACH PARTITION ... CONCURRENTLY (PostgreSQL 14 and later) detaches without blocking concurrent queries on the parent, after which the table can be archived, dumped or dropped. The job below is idempotent and safe to run hourly:

import datetime as dt
import psycopg

AHEAD_DAYS, KEEP_DAYS = 7, 90

def day_name(d):
    return f"events_{d:%Y_%m_%d}"

def maintain(conn):
    today = dt.datetime.now(dt.timezone.utc).date()
    with conn.cursor() as cur:
        for i in range(AHEAD_DAYS + 1):
            d = today + dt.timedelta(days=i)
            cur.execute(
                f"CREATE TABLE IF NOT EXISTS {day_name(d)} PARTITION OF events "
                f"FOR VALUES FROM ('{d} 00:00+00') TO ('{d + dt.timedelta(days=1)} 00:00+00')")
        cur.execute(
            "SELECT c.relname FROM pg_inherits i "
            "JOIN pg_class c ON c.oid = i.inhrelid "
            "JOIN pg_class p ON p.oid = i.inhparent "
            "WHERE p.relname = 'events'")
        cutoff = today - dt.timedelta(days=KEEP_DAYS)
        for (name,) in cur.fetchall():
            if name.startswith("events_") and name != "events_default":
                if dt.datetime.strptime(name[7:], "%Y_%m_%d").date() < cutoff:
                    cur.execute(f"ALTER TABLE events DETACH PARTITION {name} CONCURRENTLY")
                    cur.execute(f"DROP TABLE {name}")

# autocommit: DETACH ... CONCURRENTLY cannot run inside a transaction block
with psycopg.connect("dbname=gateway", autocommit=True) as conn:
    maintain(conn)

If a detach is interrupted, the partition is left in a pending state and must be completed with ALTER TABLE ... DETACH PARTITION ... FINALIZE; the job should check for that state and finish it before doing anything else.

Where MySQL differs

InnoDB supports range, list, hash and key partitioning, plus the RANGE COLUMNS and LIST COLUMNS variants for non-integer keys. The constraint rule is stricter in wording but similar in effect: every unique key on the table, including the primary key, must include every column used in the partitioning expression. Partitioned InnoDB tables do not support foreign keys in either direction, which rules partitioning out for many normalised schemas. A table may have at most 8192 partitions, including sub-partitions. Retention uses ALTER TABLE ... DROP PARTITION, and ALTER TABLE ... EXCHANGE PARTITION ... WITH TABLE swaps a partition with a standalone table, which is the MySQL equivalent of bulk-load-then-attach.

Failure modes

Operating it day to day

Monitor four things: the furthest future partition bound, the row count of the default partition, the number of partitions touched by your top queries after pruning, and per-partition size so a skewed day is visible. Keep the list of partitions and their bounds queryable from pg_inherits and pg_class rather than a spreadsheet. Test schema migrations against a copy with the full partition count.

What to do next

1. Write down the top ten query shapes and the retention unit; partition only if the same column appears in both.
2. Pick the coarsest granularity that matches the retention step and prunes typical queries to a few partitions.
3. Design primary and unique keys that include the partition key, and decide how global identifiers stay unique.
4. Build the create-ahead and detach-then-drop job, make it idempotent, and alert on the furthest future bound.
5. Keep the default partition empty and alert the moment it is not.
6. Run EXPLAIN on every real query shape and confirm the partition count after pruning.
7. Rehearse adding an index across all partitions with the ON ONLY and ATTACH pattern on a staging copy.
8. Re-evaluate once a year: if queries stopped filtering on the key, the scheme no longer fits.