by stopachka on 1/29/25, 4:57 PM with 76 comments
by pilif on 1/30/25, 10:42 AM
I really wonder how an in-place `pg_upgrade` of such small amounts of data would take 30+ minutes.
My experience from a less mission-critical situation where 5 minutes of maintenance are absolutely acceptable is that an in-place `pg_upgrade` with `--link` of a 8 TB database takes less than a minute and will not accidentally lose data or fail to properly configure schema search paths or whatever other mess the article was talking about.
I understand that 30 minutes of downtime are not acceptable. But if it's 5 minutes or less, I would seriously consider an offline upgrade using `pg_upgrade`
And if it takes 30 minutes to hard-link less than 1 TB of data files, you should seriously consider changing hosts because that's absolutely unacceptable performance.
by jedberg on 1/29/25, 10:13 PM
If you managed to have a cutover with no noticeable dip in business metrics (aka the users didn't notice) then I'd call that a no-downtime upgrade!
Very clever on the improvement over Lyft's methods. Thanks for the writeup. Now maybe someone can get it down from 3 seconds of pausing. :)
by briffle on 1/30/25, 3:46 PM
I understand that logical replication handles partial replication, and the data warehouse use case, and I understand WHY many of the choices they made for it are there.
I just wish that there was a flag you could set in a subscription that would enable it to be a complete 1:1 copy of the database, including DDL, sequences, etc, without having to do all the workarounds that are necessary now.
Currently if a dev adds a column to a table on the publisher, and forgets to do it on the subscriber, there is no error, no indication, until it actually tries to replicate data for that table.
Then you have to be monitoring for that error, and go figure out what other new tables, columns, etc, might have been added to the publisher that are missed on the subscriber. Its a huge opportunity for problems.
by darth_avocado on 1/29/25, 10:05 PM
Isn’t that….. downtime? Unless you mean downtime to be only when reads are also not available.
by honestSysAdmin on 1/29/25, 10:12 PM
https://github.com/postgresml/pgcat
by n_u on 1/30/25, 4:54 AM
Turn off all writes.
Wait for 16 to catch up
Enable writes again — this time they all go to 16
and instead they used a better algorithm: Pause all writes.
Wait for 16 to catch up.
Resume writes on 16.
These seem pretty similar.1. What is the difference in the algorithm? Is it just that in the "stop the world" approach the client sees their txns fail until "wait for 16 to catch up" is done? Whereas in the latter approach the client never sees their txns fail, they just have a bit more latency?
2. Why does the second approach result in less downtime?
by wenbin on 1/30/25, 2:00 AM
The best we’ve achieved is 0 downtime for read operations and less than 1 minute downtime for write ops [1]
Achieving 0 downtime for write ops is super hard!
[1] https://www.listennotes.com/blog/a-practical-way-to-upgrade-...
by upghost on 1/29/25, 10:09 PM
by xyzzy_plugh on 1/29/25, 8:57 PM
Also, pausing queries does count as downtime. The system was unavailable for that period of time.
by sgarland on 1/30/25, 12:03 AM
> The big bottleneck is all the active connections
For anyone who is unaware, Postgres (and Aurora-compatible Postgres, which sucks but has a great marketing team) uses a process per connection, unlike MySQL (and others, I think) which use a thread per connection. This is inevitably the bottleneck at scale, long before anything else.
I did feel for them here:
> We couldn’t create a blue-green deployment when the master DB had active replication slots. The AWS docs did not mention this. [emphasis mine]
The docs also used to explicitly say that you could run limited DDL, like creating or dropping indices, on the Green DB. I found this to be untrue in practice, notified them, and I see they've since updated their docs. A painful problem to discover though, especially when it's a huge DB that took a long time to create the B/G in the first place.
by mkleczek on 1/30/25, 6:15 AM
by jillyboel on 1/30/25, 9:02 AM
Sounds about right for cloud services.
by nijave on 1/30/25, 2:31 AM
https://www.postgresql.org/docs/current/warm-standby.html#SY...
by paulgb on 1/30/25, 2:22 AM
Nicely done!
by jatins on 1/30/25, 4:24 PM
Doesn't the increase the chances of one bad tenant taking the database down for all other tenants?