Introduction
In this article, we run tests to see the effect of HOT updates and single-page vacuuming.
From the results, we check how outcomes differ depending on the fillfactor setting.
📢 The concepts of HOT update, single-page vacuuming, and fillfactor are explained in 1. Pages and Page Management in this series.
Test Environment
Setup
- Create a table with 10,000 rows of data (
autovacuum_enabled=false)
- Run 1 million row updates
- Check the results after completion
- Repeat the same process while changing the fillfactor 100→75→50→30
Reference Script
1) Create test data
2) Run updates
3) Check test results
Result Analysis
1. Data Processing Performance
First, let's analyze the performance of the update queries we ran.
Performance can be checked with 2 metrics: the first is measuring the total time taken to process 1 million tuples, and the second is checking the "Throughput/sec" result, i.e. how many requests (queries) completed per second.
The results above show that when updates happen continuously, the smaller the fillfactor, the faster updates are processed (Time), and the more data can be processed in the same amount of time (Throughput).
📢 The test showed 176.6 requests (queries) per second. Since each request updates 1000 rows, this means 176,600 rows/sec.
2. How Updates Are Processed
Second, out of the 1 million rows, we count the tuples processed as HOT updates and those processed as regular updates, and calculate the HOT update ratio. HOT update is an optimization that handles updates more efficiently, so this ratio can be used to evaluate database performance.
pg_stat_user_tables : n_tup_hot_upd, n_tup_upd
The smaller the fillfactor, the more space is left in each page for updates, so it is easy to expect a higher HOT update ratio. The actual TEST results confirm that the fillfactor value and the HOT update ratio are inversely proportional.
3. Dead Tuple Volume
A dead tuple is, as the name says, the pre-change version of a tuple, and a growing dead tuple count means wasted space. Dead tuples are usually cleaned up through vacuum work, as follows.
- If autovacuum is
OFF, you need to check dead tuples periodically and manage vacuum manually.
- If autovacuum is
ON, autovacuum cleans up dead tuples periodically, so they do not keep growing. However, autovacuum then runs that much more often, which can add workload.
This TEST suppresses all vacuum-related activity (
autovacuum_enabled=false). In this environment, let's check how many dead tuples are produced for each fillfactor value.pg_stat_user_tables : n_live_tup, n_dead_tup
The results show that although we did not run any vacuum-related work during the TEST, the smaller the fillfactor, the lower the dead tuple count actually was.
This can be interpreted as follows: because of the SPV trigger condition, the smaller the fillfactor, the more often single-page vacuuming ran, and dead tuples were cleaned up in the process.
📢 SPV trigger condition
Single-page vacuuming runs when page space is filled beyond the fillfactor value.
4. Relation Size
Next, let's look at how the size of the created objects differs by fillfactor.
We measure size 2 times: once right after the initial data load and once after 1 million updates, to see the effect of fillfactor and HOT updates.
pg_class : relpages, pg_table_size(oid), pg_indexes_size(oid)
Table Size
First, looking at table size before the updates, the table sizes already differ by fillfactor right after loading 10,000 rows. This is because the larger the fillfactor (the closer to 100%), the less spare space is left for updates, so more data fits into fewer pages.
After the updates, however, the smaller the fillfactor, the smaller the change (increase) in table size, and the absolute sizes are reversed compared with the load time. This is because with a small fillfactor, more space is reserved for updates, and updates reuse existing pages instead of adding new ones.
Of course, the reserved update space alone can hardly explain such a dramatic change in size. To understand it, we have to assume that dead tuples were cleaned up and the existing space was reused, which can be seen as a direct effect of the dead tuple cleanup by SPV described earlier.
Index Size
In contrast, index size after loading 10,000 rows is the same (240 KB) regardless of the table's fillfactor setting. This is because we changed the table fillfactor for each test, while the index used the default (90).
Checking index size after 1 million updates, indexes on tables with a low fillfactor are relatively small. Tuples created by HOT updates do not create additional index entries, which is why the related index does not grow much when the HOT update ratio is high, even for the same volume of updates.
The graph above compares relation size (table + index) before and after the update work.
With inserts only, a large fillfactor uses fewer pages, so the total size is smaller. As updates repeat, however, the larger the fillfactor, the more pages are used and the larger the total size becomes.
5. WAL Volume
📢 WAL stores the change log for transactions.
Finally, let's check how much WAL is generated during updates for each fillfactor setting.
WAL volume can be measured from the difference in WAL LSN before and after the update work. WAL volume gives an estimate of how much change happened in the database during that period. We would expect HOT updates to do fewer writes than regular updates and therefore generate less WAL.
Let's compare the actual HOT update ratio with WAL volume.
pg_current_wal_lsn(), pg_wal_lsn_diff(lsn1 pg_lsn, lsn2 pg_lsn)
The results show that the fillfactor with a low HOT update ratio generated the most WAL of all the settings, and the test directly confirmed that WAL volume decreases as the HOT update ratio rises.
Conclusion
In this article, we adjusted the fillfactor value and checked update performance when HOT updates are in play, the number of dead tuples, relation (table/index) size, and Write-Ahead Log (WAL) volume. The test results are as follows.
- The smaller the fillfactor, the more likely HOT updates are. With a smaller fillfactor, more space remains in each page for updates, so the new tuple version is more likely to be stored on the same page as the old one.
- The smaller the fillfactor, the fewer dead tuples. A smaller fillfactor makes single-page vacuuming run more often, and dead tuples are cleaned up in the process.
- The smaller the fillfactor, the more the initial table size can appear inflated. But as updates repeat, the free space fills up and those updates are more likely to be processed as HOT updates, which ends up being an advantage for space reuse.
- Initial index size is the same regardless of the table's fillfactor. But as updates repeat, HOT updates happen more often on tables with a small fillfactor, and since HOT updates do not create additional index entries, this is also an advantage for index size.
- HOT updates also affect WAL volume. The higher the HOT update ratio, the fewer writes are needed compared with regular updates, so WAL volume actually goes down.
Related Articles
