An unexpected find that freed 20GB of unused index space in PostgreSQL
1–10 of 81 posts
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#2Is the partial index technique to avoid indexed NULL data as effective for PostgreSQL 13+?
It looks like in v13+ PostgreSQL could create a single leaf for NULL data and just store row pointers within it, which should reduce data sizes at least a bit.
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#3Partial indexes can be useful in any case where one value has much higher cardinality than others.
Indexing boolean columns is often only useful if one of the values is uncommon and the index is partial to only include those uncommon rows.
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#4Summary: if you have an index on a column which is mostly NULL, consider using a partial index covering only the records where it's non-NULL.
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#5Too bad MySQL does not have partial indexes.
We have one huge table I want to add some indexes for specific cases (for max 1% of records) but server will not have enough memory for it if I add those indexes for all records :/
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#6Partial indexes can be useful in any case where one value has much higher cardinality than others. Indexing boolean columns is often only useful if one of the values is uncommon and the index is partial to only include those uncommon rows.
Agreed. To explain why this is the case, consider that table in the story that had 99% NULL values. If you were to try to run "SELECT FROM table WHERE column IS NULL", then Postgresql wouldn't use the index anyway, because it would be faster to just read sequentially through the entire table and filter out the 1% that don't match.
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#7Too bad MySQL does not have partial indexes. We have one huge table I want to add some indexes for specific cases (for max 1% of records) but server will not have enough memory for it if I add those indexes for all records :/
MySQL has pluggable storage engines. TokuDB does what you're after (adds indexes on the fly, as well as alter tables on the fly without overloading the server).
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#8Is the partial index technique to avoid indexed NULL data as effective for PostgreSQL 13+? It looks like in v13+ PostgreSQL could create a single leaf for NULL data and just store row pointers within it, which should reduce data sizes at least a bit.
He actually mentioned index de-duplication earlier: https://hakibenita.com/postgresql-unused-index-size#activati...
If I had to guess, I would say that it doesn't accomplish anything (or as much as you'd think) for null values simply because there is no real data to store in either approach, you just have a bunch of pointers either way.
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#9Is the partial index technique to avoid indexed NULL data as effective for PostgreSQL 13+? It looks like in v13+ PostgreSQL could create a single leaf for NULL data and just store row pointers within it, which should reduce data sizes at least a bit.
Not per se _as effective_, but it will still help a lot. NULL tuples pre-pg13 take ~ 14 bytes each, and 18 bytes when aligned. (= 2 (ItemID, location on page) + 6 (TID) + 2 (t_info) + 4 (NULL bitmap) + 4 bytes alignment). When deduplication is enabled for your index, then your expected tuple size becomes just a bit more than 6 bytes (~ 50 TIDs* in one tuple => 2 (ItemId) + 6 (alt tid) + 2 (t_info) + 4 (null bitmap) + 50 * 6 (heap TIDs) / 50 => ~ 6.28 bytes/tuple).
So, deduplication saves some 65% in index size for NULL-only index-tuples, and the further 35% can be saved by using a partial index (so, in this case, deduplication could have saved 13GB).
*note: last time I checked, REINDEX with deduplication enabled packs 50 duplicates in one compressed index tuple. This varies for naturally grown indexes, and changes with column types and update access patterns.
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#10When I did my Oracle DBA training 15 years ago, I learnt about database reorgs.
It means basically exporting your database (or tables) and importing it again. What happens is that deleted data which doesn't necessarily free up space (Oracle reuses the freed up space sometimes) doesn't get exported.
https://www.iri.com/blog/vldb-operations/database-reorgs-why...
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUEST...