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
Do you have loads of indexes, most of which are on low cardinality columns? And lots of churn from UPDATEs? That's the kind of DB where the new-to-12 indexing stuff tends to really help.
Yes and yes and yes! Low cardinality everywhere and hundreds of updates every minute.
1
1
The deduplication stuff from Postgres 13 would probably help your app even more (see postgresql.org/docs/devel/btr). You should consider upgrading to Postgres 13 after it is officially released.
1
2
Show replies