PostgreSQL New Feature is a series:
- PostgreSQL 17 key features, parameters, and system catalog changes
- PostgreSQL 17 new and improved features
- PostgreSQL 17 Vacuum performance improvements
- PostgreSQL 17 Incremental Backup
This document covers the Vacuum performance improvements in PostgreSQL 17.
Vacuum Performance Improvements
Up to PostgreSQL 16, for implementation reasons, Vacuum could use at most 1GB of memory even if
maintenance_work_mem or autovacuum_work_mem was set higher. As a result, the memory area for tracking dead tuples to process could run out, sometimes causing unnecessary table rescans. Also, the data structure that stored found dead tuples was a simple array, which was not efficient when there were many dead tuples.PostgreSQL 17 changes the Vacuum implementation, including fixes for these problems.
- To make Vacuum more efficient, a Radix Tree structure was introduced, which can cut memory usage by up to 20x compared to before, and the limit on memory available to Vacuum was removed. Vacuum is also faster when processing large volumes of data.
- Dead tuple cleanup and tuple freezing are now handled in one pass, which reduces WAL volume, and Vacuum on tables without indexes is more efficient, which also reduces WAL volume.
Vacuum Performance Test
We compare Vacuum performance between PostgreSQL 16.4 and PostgreSQL 17.2 in the same environment. Only the
autovacuum and maintenance_work_mem parameters are changed for the test; all other parameters use their defaults.The test has 6 cases in total: after creating 100 million rows, we UPDATE 5%, 50%, or 95% of the data, with and without an index. After each UPDATE, we run the Vacuum command with the VERBOSE option to check Vacuum elapsed time and WAL volume, and check memory usage at the OS level. We also check the case where Vacuum memory usage peaks, using 500 million rows.
📢 To check memory usage during Vacuum, we read smaps every 3 seconds using the PID of the session running Vacuum, and use the highest value observed while Vacuum was running.
📢 VACUUM VERBOSE also prints elapsed time and WAL volume for the TOAST table. In this test, no data is long enough to be stored in a TOAST table, so we don't cover TOAST.
Vacuum Performance Test Cases
Case | Scenario | Notes |
Case 1. | 100 million rows + UPDATE 5% of all data | |
Case 2. | 100 million rows + UPDATE 50% of all data | |
Case 3. | 100 million rows + UPDATE 95% of all data | |
Case 4. | 100 million rows + create index + UPDATE 5% of all data | |
Case 5. | 100 million rows + create index + UPDATE 50% of all data | |
Case 6. | 100 million rows + create index + UPDATE 95% of all data | |
Case 7. | 500 million rows + create index + UPDATE 95% of all data | Maximum memory usage during Vacuum |
Creating Vacuum Performance Test Data
The tests below are run identically on PostgreSQL 16 and PostgreSQL 17.
Tables vacuum_tab01 through vacuum_tab06 get 100 million rows each, and indexes are created on vacuum_tab04 through vacuum_tab06 to compare Vacuum performance with and without an index. In addition, table vacuum_tab10 gets 500 million rows to create a situation where Vacuum uses the maximum amount of memory.
Update the data in the table created for each test case.
Check the Vacuum history of the tables before running Vacuum. Since
autovacuum=off and we have not run Vacuum manually, there is no Vacuum history.To check memory usage during Vacuum, run smaps in a new terminal.
Running Vacuum
Run Vacuum for each test case.
The Vacuum results are below. From WAL usage and system usage at the end of the VACUUM VERBOSE output, we check WAL volume and Vacuum elapsed time, and take the highest value from the smaps results collected earlier.
📢 The VACUUM VERBOSE output is explained at the end of this document.
Check the Vacuum history of the tables after running Vacuum. Since Vacuum ran once, vacuum_count=1, and last_vacuum shows the time Vacuum completed.
Vacuum Performance Test Results
The Vacuum results on PostgreSQL 16.4 and PostgreSQL 17.2 are shown in the table below.
Shorter elapsed time: Vacuum elapsed time dropped by up to 30%.
Lower WAL volume: WAL volume during Vacuum dropped by up to 30%.
Lower memory usage: Memory usage during Vacuum dropped by up to 95%.
The tests confirm the Vacuum performance improvements in PostgreSQL 17, and lower memory usage showed the biggest gain. Memory previously used by Vacuum can now go to other work, which reduces memory contention and is expected to improve workload performance.
📢 full_page_write=on was set during the tests, and changing to
full_page_write=off may increase WAL generation.Appendix 1. Reading VACUUM VERBOSE
Adding the VERBOSE option when running Vacuum shows the Vacuum process, statistics, and more. The example below explains how to read it.
① Vacuum operation info
- Vacuum runs on table vacuum_tab04 in the public schema of the postgres database
② Vacuum completion info
- Vacuum finished on table vacuum_tab04 in the public schema of the postgres database, and the index was scanned once to vacuum the related index
index scans: 0suggests the table has no related index
③ Page statistics
0 removed: 0 pages removed
567570 remain: 567,570 pages remain
567570 scanned (100.00% of total): 567,570 pages scanned (100% of total)
④ Tuple statistics
5000000 removed: 5,000,000 tuples removed
100000000 remain: 100,000,000 tuples remain
0 are dead but not yet removable: 0 tuples not yet removable
⑤ XID info
removable cutoff: XID 780 is the cutoff for removable transactions; at the end of the operation, 0 transactions met the removal condition
new relfrozenxid: The new relfrozenxid is 777, 13 transactions ahead of the previous value, which means the range of frozen transaction IDs has expanded
⑥ Frozen Tuple
- 540,541 pages were frozen (95.24% of total) and 95,000,005 tuples were frozen
⑦ Index statistics
- 540,540 pages needed an index scan (95.24% of total), and 4,999,995 entries were removed (index entries related to dead tuples cleaned up)
⑧ Index status
- Shows the status of the vacuum_tab04_idx index
274194 in total: 274,194 pages in total
0 newly deleted: 0 newly deleted pages
0 currently deleted: 0 currently deleted pages
0 reusable: 0 reusable pages
⑨ I/O rate
avg read rate: 113.535 MB/s: Average read rate is 113.535 MB/s
avg write rate: 113.457 MB/s: Average write rate is 113.457 MB/s
⑩ Buffer usage
567660 hits: 567,660 reads from cache
1382447 misses: 1,382,447 reads from disk
1381488 dirtied: 1,381,488 buffers modified by Vacuum
⑪ WAL statistics
2462415 records: 2,462,415 WAL records written
1354334 full page images: 1,354,334 times full page data was written to WAL (only present when full_page_write=on)
10777301340 bytes: Total WAL size is about 10.78GB
⑫ System resource usage
CPU: user: 25.40 s, system: 15.94 s: User CPU time 25.40s, system CPU time 15.94s
elapsed: 95.12 s: Vacuum elapsed time 95.12s
Appendix 2. VACUUM VERBOSE Output for Each Test
PostgreSQL 16.4 VACUUM VERBOSE output
PostgreSQL 17.2 VACUUM VERBOSE output
Recommended articles
