News for hackers. Stuff that matters.

A comment on A Major Postgres Upgrade with Zero Downtime. Parent comment

Re:A Major Postgres Upgrade with Zero Downtime by timacles on Thursday January 30, 2025 @01:11PM (#42880412)
Yeah we just did it with the --link option on a 6TB database and it took like 30 seconds. Something has to be off with their OS settings or disk speeds.

The main challenge with that is running an ANALYZE on all the tables though, that took like 30 minutes during which time the DB was unusable

[ Parent | on HN ]
Re:A Major Postgres Upgrade with Zero Downtime by zonkd1234 on Thursday January 30, 2025 @06:28PM (#42883297)
Was it unusable because cpu/io was maxed out during ANALYZE?
[ Parent | on HN ]
Re:A Major Postgres Upgrade with Zero Downtime by pilif on Thursday January 30, 2025 @11:41PM (#42884750)
Analyze itself isn’t the problem.

After pg_upgrade, no stats will be available for the optimizer which means that any query will more or less sequence-scan all affected tables.

[ Parent | on HN ]
Re:A Major Postgres Upgrade with Zero Downtime by timacles on Friday January 31, 2025 @09:14AM (#42887885)
No, its the lack of stats on the tables, any query hitting a medium to large table would be extremely slow.
[ Parent | on HN ]
Re:A Major Postgres Upgrade with Zero Downtime by pilif on Thursday January 30, 2025 @11:40PM (#42884745)
These days it does analyze in stages where it does multiple passes with increasing stats sampling.

From personal experience, most of the queries become useable after the first stage has completed which on my 8TB database took less than 5 minutes

[ Parent | on HN ]
Re:A Major Postgres Upgrade with Zero Downtime by timacles on Friday January 31, 2025 @09:20AM (#42887943)
We did use the --analyze-in-stages option, I think our data model is just not optimal. We have a lot of high frequency queries hitting very large tables of .5 to 1 billion rows. Proper indexing makes them fast but until all the stats are there, the frontend is unusable.
[ Parent | on HN ]