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.
1
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
If I understand the documentation the best dedup happens when there are many values close to each other, is that correct?
1
Not really. It's based entirely on being able to store the whole key only once. Duplicates are stored together in general, which the implementation takes advantage of.
If you really want to see how it works, then I recommend using the bt_page_items() function from Postgres 13's pageinspect module: postgresql.org/docs/devel/pag -- The example shows posting list tuples, but you really need to see how a given page changes over time to get the idea.
2