Posts

23% Faster Autovacuum with Parallel Workers in PostgreSQL 19

By -
  Before PostgreSQL 19, autovacuum processed indexes without using parallel vacuum workers, even when a table had multiple large indexes. Although PostgreSQL already supported parallel vacuuming for manually executed VACUUM commands, this capability was not available to autovacuum . For example, manual vacuuming can use parallel workers: vahiddb= # VACUUM (PARALLEL 10) person; VACUUM PostgreSQL 19 introduces Parallel Autovacuum , allowing autovacuum to use parallel workers during index vacuuming and index cleanup. This feature is particularly useful for tables with large indexes and substantial numbers of dead tuples. However, it does not parallelize the table’s heap-scanning phase. Consequently, tables without indexes cannot benefit from this feature, and tables with large indexes are promising candidates. In this article, I will demonstrate the feature using a table containing five million rows and four indexes. I will compare the execution times of autovacuum with parallel worke...
Post a Comment
Read more

Calculating WAL Generation for DML Operations in PostgreSQL

By -
  PostgreSQL provides the EXPLAIN command with the ANALYZE and WAL options to measure the WAL generated by a DML operation. This feature helps database administrators investigate the impact of INSERT, UPDATE, and DELETE statements on WAL generation. In this article, we use EXPLAIN (ANALYZE, WAL) to measure the WAL generated when updating 10 million rows, compare the results with and without an index, and investigate the effect of enabling WAL compression. 1. Test environment The test uses a person table containing 10 million rows . The table occupies approximately 880 MB , and WAL compression is initially disabled. vahiddb = # \dt + List of tables Schema | Name | Type | Owner | Persistence | Access method | Size | Description --------+--------+-------+----------+-------------+---------------+--------+------------- public | person | table | postgres | permanent | heap | 880 MB | ( 1 row ) vahiddb = # show w...
Post a Comment
Read more