Table Partitioning in PostgreSQL

PostgresTable PartitioningDjangoData Engineering

The problem: table keeps growing

Some datasets arrive on a schedule. Once a year, a new vintage lands — scoped to one region, tens of millions of rows. Next year, another one shows up just as big. The table only ever grows.

Dump it all into one flat table and:

  • Queries slow down. Most queries care about one year and one region, but the planner still scans an index spanning years of data it won’t touch.
  • Maintenance gets expensive. VACUUM/ANALYZE walk the whole table, so a job that took minutes now takes hours.
  • Mistakes are costly to undo. If a load ever needs to be reversed — bad units, a missing filter, a corrupted source file — that’s DELETE FROM ... WHERE year = X: a full table scan, then a wave of dead tuples for autovacuum to mop up, all while the table’s still being queried.

It’s not a Postgres problem, it’s a shape problem: the table’s natural boundaries — one release, one year, one region — don’t match how it’s stored.

Partitioned table in Postgres

Declarative partitioning splits one logical table into physical partitions — by range, list, or hash — on a column that matches how data arrives and gets queried. You still query one table name; each partition underneath has its own storage and indexes.

flowchart TD
    Q[query by region and year] --> T[Dataset]
    T --> P1[(2024)]
    T --> P2[(2025)]
    T --> P3[(2026)]
    S[Staging table] -->|ATTACH PARTITION| T
    T -->|DETACH PARTITION| S

Two things matter:

  • Query pruning. Filtering on the partition key skips every other partition’s index outright — even at execution time (Postgres 11+), so it still holds with a parameter or a join.
  • Metadata-only loads and rollbacks. Attaching a partition normally means scanning every row to confirm it fits. A CHECK constraint on the staging table proves that upfront, so ATTACH PARTITION becomes an instant catalog update, any row count. DETACH PARTITION CONCURRENTLY (Postgres 14+) reverses it just as fast, without blocking readers.

Application code never needs to know partitions exist — reads and writes still target data by name. The only decision that matters is the partition key: match it to how data arrives and how it’s queried, and pruning does the rest.

Building it: Scenario Viewer

Scenario Viewer is built with Django for exploring the Cambium dataset — long-term power-sector scenarios released in yearly vintages, each adding tens of millions rows for one region.

  • Partition key: vintage year. Matches both how the data ships and how the UI queries it. Query times dropped from multi-second scans to milliseconds.
  • Database migrations: django-postgres-extra. Django’s migrations don’t model partitioned tables, but postgres-extra declare the scheme and generate the ATTACH/DETACH migrations for each vintage with PostgresPartitionedModel. Solid in production.
  • Ingest and rollback: attach and detach. Each vintage’s records load into its own new partition table, get validated against the source manifest, then attach — the live table only ever sees a metadata change. A bad vintage comes back out the same way, with DETACH PARTITION CONCURRENTLY — no locking, no row-by-row delete.

Over the years since, the solution has supported hundreds of millions of rows across queries successfully — no slow writes on our end for data ingestion, no complaints from users about data queries. Each new release is just another partition, and the data store scales with it. The annual data update is now a smooth routine, no pains, ever a smile.

Summary

Range-partition by the axis your data arrives and gets queried by, and yearly bulk loads stop being an event: loads become an ATTACH PARTITION, rollbacks become a DETACH PARTITION, and queries prune down to the one partition they need — all metadata operations, none of them scaling with row count. If your table is facing the same growing pains, partitioning is worth a look.

~J.Gu [Jerry]