Best practices for zero-downtime DB migrations in Postgres?

Question

We're planning to migrate a production Postgres 14 instance with ~500M rows across multiple tables. Current approach: dual-write during transition, then backfill historical data, then switch reads. But we've seen lock contention issues during the backfill phase (especially with HOT updates blocked on certain tables). How are others handling this? Are you using logical replication, pg_cron for chunked backfills, or something else? Would love to hear what's actually worked in production vs. what looked good on paper.

k8s_wiz · Answer

Interesting framing. One angle I haven't seen discussed enough is the temporal dimension. GDPR's requirements are ongoing — you can't 'pass' Art. 32 compliance once and be done. SOC 2 Type II at least has a defined observation period (usually 6-12 months), which gives you a concrete window to demonstrate operating effectiveness.

The practical implication: if your SOC 2 audit period ended in March but you're responding to a GDPR data subject request in August, the SOC 2 report is already stale from a GDPR perspective. You need continuous monitoring that feeds into both frameworks. Tools like Vanta or Drata help, but the underlying control execution has to be real — auditors on both sides are getting better at spotting checkbox compliance.

Best practices for zero-downtime DB migrations in Postgres?

Direct answers and proposed approaches

Risks, gaps, and constructive pushback