Monitor PostgreSQL HOT Updates and Fillfactor
How to Monitor PostgreSQL HOT Updates and Fillfactor
When PostgreSQL updates a row, it normally writes a new row version and creates a new index entry pointing to it. This is safe, but it adds overhead — especially on tables with many indexes.
HOT updates (Heap Only Tuple) are a smarter path. When a HOT update happens, PostgreSQL writes the new row version on the same data page and links it to the old version, without adding new index entries. This is faster and reduces index bloat. But HOT updates are only possible when there is free space on the same page as the old row.
That is where fillfactor comes in. By leaving some free space on each page (for example, setting fillfactor to 80 instead of 100), you give updates room to stay on the same page and become HOT updates.
Sample Code
Check HOT update counts by table:
1SELECT
2 schemaname,
3 relname,
4 n_tup_hot_upd
5FROM
6 pg_stat_all_tables
7WHERE
8 schemaname = 'public'
9ORDER BY
10 n_tup_hot_upd DESC;
Check the current fillfactor setting for each table:
1SELECT
2 nspname AS schema_name,
3 relname AS table_name,
4 COALESCE(
5 (
6 SELECT substring(reloptions[i] FROM 12)
7 FROM generate_series(
8 array_lower(reloptions, 1),
9 array_upper(reloptions, 1)
10 ) AS s(i)
11 WHERE reloptions[i] LIKE 'fillfactor%'
12 ),
13 '100'
14 ) AS fillfactor
15FROM
16 pg_class
17 JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
18WHERE
19 relkind = 'r'
20 AND nspname = 'public'
21ORDER BY
22 schema_name, table_name;
Notes: Replace 'public' with your schema name. n_tup_hot_upd accumulates since the last statistics reset, or since the last unclean shutdown or crash.
Code Breakdown
HOT update query:
pg_stat_all_tables— A built-in view with one row per table in the current database, including update counts.n_tup_hot_upd— The number of rows updated using the HOT path. A high share of HOT updates is good — it means PostgreSQL is reusing page space efficiently.schemaname = 'public'— Filters to the public schema. Change this to match your application schema.
Fillfactor query:
reloptions— An array of storage options set on the table. Fillfactor is stored here if it has been set explicitly.COALESCE(..., '100')— If the table has nofillfactorentry in its storage options (including when it has other options set), the query reports the default of 100.substring(reloptions[i] FROM 12)— Extracts the numeric value from a string likefillfactor=80. The offset of 12 skips thefillfactor=prefix.relkind = 'r'— Limits results to ordinary tables (r). Partitioned tables (p) are not included.
Key Points
- A high
n_tup_hot_updcount relative to total updates means HOT updates are working well on that table. - If
n_tup_hot_updis very low compared to total updates on a table, HOT updates are not happening. This may mean pages are full, or that updates change indexed columns. - Compare
n_tup_hot_updton_tup_upd(total updates) usingpg_stat_all_tablesto get the HOT update ratio. - A fillfactor of 100 (the default) packs pages completely. HOT updates can still happen, because new row versions can land on pages that have free space, but a lower fillfactor raises the likelihood. Values of 70–90 are a common rule of thumb for heavily updated tables; test on your own workload.
Insights and Explanations
To see the ratio of HOT updates to all updates for each table:
1SELECT
2 schemaname,
3 relname,
4 n_tup_upd AS total_updates,
5 n_tup_hot_upd AS hot_updates,
6 CASE WHEN n_tup_upd > 0
7 THEN round(100.0 * n_tup_hot_upd / n_tup_upd, 1)
8 ELSE 0
9 END AS hot_update_pct
10FROM pg_stat_all_tables
11WHERE schemaname = 'public'
12 AND n_tup_upd > 0
13ORDER BY hot_update_pct ASC;
Tables with a low hot_update_pct on heavily updated workloads are candidates for a lower fillfactor. The 50% cutoff many teams use is a rule of thumb, not a documented threshold.
To lower the fillfactor on a table:
1ALTER TABLE your_table SET (fillfactor = 80);
The command does not rewrite existing data. The new fillfactor governs how later inserts pack pages. To apply it to the whole table right away, run a VACUUM FULL (this takes an ACCESS EXCLUSIVE lock on the table) or use the pg_repack extension, which works online and holds only brief exclusive locks at the start and end:
1VACUUM FULL your_table;
Trade-offs to consider:
- A lower fillfactor means each page holds fewer rows. This increases the total number of pages, so sequential scans read more pages.
- For tables that are read far more often than updated, the default fillfactor of 100 is usually the right choice.
- For tables with frequent updates to the same rows (like status columns or counters), a lower fillfactor such as 70–85 is a common starting point; measure
hot_update_pctbefore and after.
Additional Considerations
- Permissions: All queries here only read system views and catalogs; they change nothing.
- Statistics reset: PostgreSQL keeps these counters across a clean shutdown. They reset after
pg_stat_reset()or after an unclean shutdown, crash, base-backup start or point-in-time recovery. Compare trends over time rather than relying on a single reading. - Indexes and HOT updates: HOT updates are only possible when the update does not change any column referenced by the table's indexes. Summarizing indexes (BRIN) are the exception and do not block the HOT path. If you update a column in a B-tree or other non-summarizing index, PostgreSQL must update the index too, and the HOT path is not available.
References
Posts in this series
- Identify Underutilized PostgreSQL Columns for Optimization
- Find Unused PostgreSQL Indexes with pg_stat_all_indexes
- Monitor PostgreSQL HOT Updates and Fillfactor
- Find PostgreSQL Index Bloat and Wasted Space
- Estimate PostgreSQL Table Bloat with SQL
- Find Missing Indexes in PostgreSQL with SQL
- Monitor PostgreSQL Table Cache Hit Ratio with SQL
- Tune PostgreSQL Background Writer with pg_stat_bgwriter
- Query PostgreSQL I/O Statistics with pg_stat_io
- Summarize the Buffer Cache with pg_buffercache_summary
- Monitor Logical Replication Slot Stats with pg_stat_replication_slots