How do I migrate a table to PostgreSQL partitioning with zero downtime?

Short answer: 50M rows a month is genuinely big — partitioning is the right move here. Use monthly RANGE declarative partitioning on created_at, and do the migration in stages so there’s no downtime.

It’s worth one sanity check first: 50M/month is a real volume, so the answer to “is your data really big or just badly modeled” is clear here — it’s really big. Partitioning is warranted.

1. Split with native declarative RANGE. Create a new partitioned parent (PARTITION BY RANGE (created_at)) and ATTACH the monthly partitions. Unlike the old monolithic table, a query only touches the relevant month; thanks to partition pruning, simple selects get fast again.
2. Do the migration with zero downtime. Stand up the new parent, attach the partitions, then backfill historical data in batches (e.g. 50k-row chunks, off-peak) into the partitions. Meanwhile keep the app writing: either route through a trigger/view into the new structure, or dual-write to both old and new. Once the backfill is done, swap the names in a single transaction — no outage.
3. Indexes are created per-partition. Postgres automatically propagates the index you define on the parent to every partition — so it’s not one global B-tree but a separate index per month. That actually helps you: old months’ indexes don’t weigh down your hot data.
4. Don’t forget the UNIQUE and FK rule. On a partitioned table a global UNIQUE/PK must include the partition key — so your PK becomes (id, created_at). FKs that reference the table are affected too; FKs to partitioned tables work in modern PG, but verify your version.
5. Hand lifecycle to pg_partman. Use pg_partman to auto-create future months’ partitions; for retention, drop old partitions with DETACH + DROP — far cheaper than a DELETE scan, effectively instant.

Bottom line: declarative RANGE on created_at, batched backfill, the partition key in every unique key, and pg_partman for lifecycle. Build the migration around dual-write plus a single-transaction name swap and the live system never stops. Before you go this deep into DB internals, make sure your scaling need is truly “big data” — in this case it is.