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

> Stand up monthly RANGE declarative partitioning on `created_at`, backfill history in batches while the app writes, then swap names in a single transaction.

- Asked: 2026-06-03
- Answered: 2026-06-06
- Asked by: Furkan
- Tags: veritabani, postgresql, olcekleme
- Source: https://www.muhammetsafak.com.tr/en/just-ask/zero-downtime-migration-to-postgresql-declarative-partitioning/
- Language: en-US
- Author: Muhammet Şafak

---
**Question:** In our SaaS app, the `audit_logs` table that holds user activity logs grows by 50M rows a month; as it grows even simple `select`s have slowed down. I want to split this table into monthly partitions on `created_at` using PostgreSQL's declarative partitioning.

How do I move the live data into this new structure with zero downtime? How are indexes and foreign key dependencies affected by this?


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 `select`s 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.

## Related Reading

- [Is PostgreSQL Enough for Everything?](https://sade.dev/en/journal/is-postgresql-enough-for-everything) — sade.dev
- [Time-series data: TimescaleDB or InfluxDB?](https://www.muhammetsafak.com.tr/en/just-ask/time-series-data-timescaledb-vs-naive-solutions/) — Just Ask
- [Should I use a partial index on a queue table where I only ever scan 'pending' rows?](https://www.muhammetsafak.com.tr/en/just-ask/should-i-use-a-partial-index-on-a-queue-table-where/) — Just Ask
- [How do I rewind to seconds before a disaster with WAL archiving and PITR?](https://www.muhammetsafak.com.tr/en/just-ask/point-in-time-recovery-with-wal-archiving/) — Just Ask