https://www.cybertec-postgresql.com/en/index-bloat-reduced-in-postgresql-v14/ * EN [github][svg] Blog Cybertec LogoCybertec Logo * Services + Requirement Analysis + PostgreSQL consulting + PostgreSQL migration o Migration from Oracle o Migrating from MySQL / MariaDB o CYBERTEC Migrator + PostgreSQL Infrastructure o Setup & Installation o Kubernetes o Database architecture + PostgreSQL development o Database modeling o Functions & Features + Update & Upgrade + Optimization & Security o PostgreSQL Health Check o Performance Tuning o Enterprise Security o Security Audit + Troubleshooting & Data Recovery + PostgreSQL clustering and HA o Clustering and failover o High availability with Patroni o Synchronous and asynchronous replication o Scaling with PL/Proxy + Spatial Services o GIS Tooling + CYBERTEC Partner Network * Support + Standard PostgreSQL Support o Product Support o 9/5 Basic Support o 24/7 Basic Support o 24/7 Enterprise Support + Advanced PostgreSQL Support o 9/5 Remote DBA o 24/7 Remote DBA o 9/5 Dedicated DBA o 24/7 Cloud-based Support + Support for Reseller o 3rd Level Support o CYBERTEC Partner Network * Products + Our Products o CYPEX - Build Apps & Forms o CYBERTEC PostgreSQL Enterprise Edition o CYBERTEC Migrator o PostgreSQL Transparent Data Encryption o Data Masking for PostgreSQL o PL/pgSQL_sec - Fully encrypted stored procedures + PostgreSQL Tools & Extensions o pg_timetable - Advanced Job Scheduling o pg_show_plans - Monitoring Execution Plans o pgwatch - PostgreSQL Monitoring Tool o pg_squeeze - Shrinks Tables o Walbouncer - Enterprise Grade Partial Replication o PGConfigurator - Visual PostgreSQL Configuration o ora_migrator o Patroni Environment Setup + Assessment Packages o Data Science Assessment Package o Start-Up Assessment Package o Spatial Data Assessment Package + CYBERTEC Partner Network * Training + Course Catalog + Online Training + Customized Training * PostgreSQL + Advantages of PostgreSQL + PostgreSQL Books + Solutions - Who uses PostgreSQL o PostgreSQL for Startups o PostgreSQL for governments and public services o Longlife solutions + Business Cases o Fraud Detection o PostgreSQL for biotech and scientific applications * Data Science + Data Science Overview + Machine Learning + Big Data Analytics * Contact Index bloat reduced in PostgreSQL v14 Posted on 2021-09-08 by Laurenz Albe B-tree bloat index new feature page split performance postgresql Index bloat in bad handsIndex bloat in bad hands (c) Laurenz Albe 2021 PostgreSQL v12 brought more efficient storage for indexes, and v13 improved that even more by adding deduplication of index entries. But Peter Geoghegan is not done yet! PostgreSQL v14 will bring "bottom-up" index entry deletion, which is targeted at reducing unnecessary page splits, index bloat and fragmentation of heavily updated indexes. Why do we get index bloat? In a B-tree index, there is an index entry for every row version ("tuple") in the table that is not dead (invisible to everybody). When VACUUM removes dead tuples, it also has to delete the corresponding index entries. Just like with tables, that creates empty space in an index page. Such space can be reused, but if no new entries are added to the page, the space remains empty. This "bloat" is unavoidable and normal to some extent, but if it gets to be too much, the index will become less efficient: * for an index range scan, more pages have to be scanned * index pages cached in RAM means that you cache the bloat, which is a waste of RAM * fewer index entries per page mean less "fan out", so the index could have more levels than necessary This is particularly likely to happen if you update the same row frequently. Until VACUUM can clean up old tuples, the table and the index will contain many versions of the same row. This is particularly unpleasant if an index page fills up: then PostgreSQL will "split" the index page in two. This is an expensive operation, and after VACUUM is done cleaning up, we end up with two bloated pages instead of a single one. Current features to improve index bloat and performance HOT tuples The creation of HOT tuples is perhaps the strongest weapon PostgreSQL has to combat unnecessary churn in the index. With this feature, an UPDATE creates tuples that are not referenced from an index, but only from the previous version of the table row. That way, there is no need to write a new index entry at all, which is good for performance and completely avoids index bloat. Read more in my article about HOT updates. Killing index tuples When an index scan encounters an entry that points to a dead tuple in the table, it will mark the index entry as "killed". Subsequent index scans will skip such entries even before VACUUM can remove them. Moreover, PostgreSQL can delete such entries when the index page is full, to avoid a page split. See my article on killed index tuples for details. How does v14 reduce index bloat even further? "Bottom-up index tuple deletion" goes farther than the previous approaches: it deletes index entries that point to dead tuples right before an index page split is about to occur. This can reduce the number of index entries and avoid the expensive page split, together with the bloat that will occur later, when VACUUM cleans up. In a way, this performs part of the work of VACUUM earlier, at a point where it is useful to avoid index bloat. A test case To demonstrate the effects of the new feature, I performed a custom pgbench run on PostgreSQL v13 and v14. This is the table for the test: CREATE TABLE testtab ( id bigint CONSTRAINT testtab_pkey PRIMARY KEY, unchanged integer, changed integer ); INSERT INTO testtab SELECT i, i, 0 FROM generate_series(1, 10000) AS i; CREATE INDEX testtab_unchanged_idx ON testtab (unchanged); CREATE INDEX testtab_changed_idx ON testtab (changed); This is the pgbench script called "bench.sql": \set id random_gaussian(1, 10000, 10) UPDATE testtab SET changed = changed + 1 WHERE id = :id; UPDATE testtab SET changed = changed + 1 WHERE id = :id; UPDATE testtab SET changed = changed + 1 WHERE id = :id; UPDATE testtab SET changed = changed + 1 WHERE id = :id; UPDATE testtab SET changed = changed + 1 WHERE id = :id; UPDATE testtab SET changed = changed + 1 WHERE id = :id; UPDATE testtab SET changed = changed + 1 WHERE id = :id; UPDATE testtab SET changed = changed + 1 WHERE id = :id; UPDATE testtab SET changed = changed + 1 WHERE id = :id; UPDATE testtab SET changed = changed + 1 WHERE id = :id; I chose a normal distribution, because in real life there are usually some (recent?) table rows that receive more updates than others. The row is updated ten times, in order to make it more likely that the affected page will have to be split. I run the script 60000 times (10000 iterations by 6 clients) as follows: pgbench -n -c 6 -f bench.sql -t 10000 test Comparing the test results We use the pgstattuple extension to get index statistics with psql: SELECT i.indexrelid::regclass AS index, s.index_size, s.avg_leaf_density FROM pg_index AS i CROSS JOIN LATERAL pgstatindex(i.indexrelid) AS s WHERE indrelid = 'testtab'::regclass; This is what we get for v13: index | index_size | avg_leaf_density -----------------------+------------+------------------ testtab_pkey | 319488 | 66.6 testtab_unchanged_idx | 4022272 | 5.33 testtab_changed_idx | 4505600 | 13.57 (3 rows) For v14, the result is: index | index_size | avg_leaf_density -----------------------+------------+------------------ testtab_pkey | 245760 | 87.91 testtab_unchanged_idx | 532480 | 39.23 testtab_changed_idx | 4038656 | 14.23 (3 rows) We see the biggest improvement in testtab_unchanged_idx. In v13, the index is bloated out of shape, while in v14 it only has 60% bloat (which is not bad for an index). Here we see the biggest effect of the new feature. The UPDATE doesn't scan that index, so there are no killed index tuples, and still "bottom-up deletion" could remove enough of them to avoid a page split in most cases. There is also a measurable improvement in testtab_pkey. Since the UPDATE scans that index, dead index tuples will be killed, and the new feature removes those before splitting the page. The difference to v13 is less pronounced here, since v13 already avoids index bloat quite well. The index testtab_changed_idx cannot benefit from the new feature, since that only addresses the case where the UPDATE doesn't modify the indexed value. In case you wonder why the leaf density is so much lower compared to testtab_unchanged_idx in v13: that is index de-duplication, which can kick in because the index entry is not modified. Will I be able to use this feature after a pg_upgrade? The storage format of the index is unchanged, so this will automatically work after a pg_upgrade of an index created on PostgreSQL v12 or later. If the index was created with an earlier version of PostgreSQL, you will have to REINDEX the index to benefit from the new feature. Remember that pg_upgrade simply copies the index files and does not update the internal index version. Conclusion PostgreSQL v14 continues to bring improvements to B-tree indexes. While this particular one may not be revolutionary, it promises to provide a solid improvement for many workloads, especially those with lots of updates. Laurenz Albe Laurenz Albe is a senior consultant and support engineer at CYBERTEC. He has been working with and contributing to PostgreSQL since 2006. Posted on 2021-09-08 by Laurenz Albe logologo CYBERTEC PostgreSQL International GmbH Romerstrasse 19 2752 Wollersdorf AUSTRIA +43 (0) 2622 93022-0 office@cybertec.at twitter.com/PostgresSupport [github][svg]github.com/cybertec-postgresql [facebook][svg][linkedin][svg][xing][svg][yt_icon_mo][svg] Our Services * Administration * Replication * Consulting * Database Design * Support * Migration * Development SUPPORT CUSTOMERS Go to the support platform >> Newsletter Check out previous newsletters! Stay well informed about PostgreSQL by subscribing to our newsletter. [ ] [ ] Ja, ich mochte regelmassig Informationen uber neue Produkte, aktuelle Angebote und Neuigkeiten rund ums Thema PostgreSQL per E-Mail erhalten. Ich kann diese Zustimmung jederzeit widerrufen. Weitere Informationen finden Sie in der Datenschutzerklarung. Yes, I would like to receive information about new products, current offers and news about PostgreSQL via e-mail on a regular basis. Granting consent to receive the CYBERTEC Newsletter by electronic means is voluntary and can be withdrawn free of charge at any time. Further information can be found in the privacy policy. Yes, I would like to receive information about new products, current offers and news about PostgreSQL via e-mail on a regular basis. Granting consent to receive the Cybertec Newsletter by electronic means is voluntary and can be withdrawn free of charge at any time. Further information can be found in the privacy policy. Tak, chce regularnie otrzymywac wiadomosci e-mail o nowych produktach, aktualnych ofertach i nowosciach dotyczacych PostgreSQL. Wyrazenie zgody na otrzymywanie Newslettera Cybertec droga elektroniczna jest dobrowolne i moze zostac w kazdej chwili bezplatnie odwolane.Wiecej informacji mozna znalezc w polityce prywatnosci. Yes, I would like to receive information about new products, current offers and news about PostgreSQL via e-mail on a regular basis. Granting consent to receive the CYBERTEC Newsletter by electronic means is voluntary and can be withdrawn free of charge at any time. Further information can be found in the privacy policy. Jah, ma soovin saada regulaarselt e-posti teel teavet uute toodete, praeguste pakkumiste ja uudiste kohta PostgreSQLi kohta. Cyberteci uudiskirja elektroonilisel teel vastuvotmiseks nousoleku andmine on vabatahtlik ja seda saab igal ajal tasuta tagasi votta. Lisateavet leiate privaatsuseeskirjadest. Leave this field empty if you're human: [ ] © 2000-2021 CYBERTEC PostgreSQL International GmbH * IMPORTANT INFORMATION ABOUT COVID-19 * Contact * Data protection policy * Imprint * Terms and Conditions [ ]