Just upgraded from Postgres 11 to 12 and saw a 35% reduction in the database size on disk.
What is this magic?!?!
Conversation
Yeah, thought something along those lines. But doesn’t it require ‘vacuum [analyse] full’ before it will release unused space?
1
yes-ish. IIRC a normal VACUUM will allow earlier reuse of rows (so tables don't grow as much in the first place), but it doesn't free table space. You really don't want to run VACUUM FULL, but instead do a normal VACUUM on a regular schedule, so updates don't need to grow tables.
2
freeing table space leads to fragmentation, so more lookups in the file system during table scans. Larger database systems work directly on raw disks for that reason.
1
1
FWIW I don't think it was VACUUM - did that manually before the upgrade so it should be accounted for in my measurements. IMO the b-tree index change is probably most of it: "PostgreSQL 12 demonstrated on average a 40% reduction in space utilization"
2
I was hoping that it was the B-Tree stuff! I was the one that worked on that.
1
2
OMG if that's the case you helped keep our small startup from having to upgrade instance sizes, thus saving us $$$.
Beers on me if you're ever in Chicago (and bars are allowed to open again).
1
2
That's fantastic! I haven't been to Chicago in a few years, but I think it's a great town. Perhaps it'll be possible to take you up on that somewhere down the line. 🙂