Monitor PostgreSQL HOT Updates and Fillfactor

calendar Mar 29, 2026 / · 4 min read · postgresql performance optimization administration database  ·
Share on: linkedin copy

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 touching any indexes. 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    CASE WHEN array_length(reloptions, 1) IS NULL THEN
 5        '100'
 6    ELSE
 7        (
 8            SELECT substring(reloptions[i] FROM 12)
 9            FROM generate_series(
10                array_lower(reloptions, 1),
11                array_upper(reloptions, 1)
12            ) AS s(i)
13            WHERE reloptions[i] LIKE 'fillfactor%'
14        )
15    END AS fillfactor
16FROM
17    pg_class
18    JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
19WHERE
20    relkind = 'r'
21    AND nspname = 'public'
22ORDER BY
23    schema_name, table_name;

Notes: Works on all supported PostgreSQL versions. Replace 'public' with your schema name. n_tup_hot_upd accumulates since the last statistics reset or server restart.

Code Breakdown

HOT update query:

  • pg_stat_all_tables — A built-in view that tracks statistics for every table, including update counts.
  • n_tup_hot_upd — The number of rows updated using the HOT path. A high number 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.
  • array_length(reloptions, 1) IS NULL — If no storage options are set, the fillfactor defaults to 100.
  • substring(reloptions[i] FROM 12) — Extracts the numeric value from a string like fillfactor=80. The offset of 12 skips the fillfactor= prefix.
  • relkind = 'r' — Limits results to regular tables only.

Key Points

  • A high n_tup_hot_upd count means HOT updates are working well. This is a sign that your fillfactor setting is appropriate for the update pattern.
  • If n_tup_hot_upd is very low compared to total updates on a table, HOT updates are not happening. This may mean pages are always full, leaving no room for in-place updates.
  • Compare n_tup_hot_upd to n_tup_upd (total updates) using pg_stat_all_tables to get the HOT update ratio.
  • A fillfactor of 100 (the default) leaves no free space for HOT updates. Lowering it to 70–90 is common for heavily updated tables.

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 (say, under 50%) on heavily updated workloads are candidates for a lower fillfactor.

To lower the fillfactor on a table:

1ALTER TABLE your_table SET (fillfactor = 80);

This setting only applies to new and updated pages. To apply it to the whole table right away, run a VACUUM FULL (this locks the table) or use pg_repack for a non-locking alternative:

1VACUUM FULL your_table;

Trade-offs to consider:

  • A lower fillfactor means each page holds fewer rows. This increases the total number of pages and can slow down sequential scans.
  • 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 fillfactor of 70–85 is often worth the trade-off.

Additional Considerations

  • Permissions: pg_stat_all_tables is readable by all users. pg_class and pg_namespace are also publicly readable.
  • Statistics reset: n_tup_hot_upd resets after pg_stat_reset() or a server restart. Compare trends over time rather than relying on a single reading.
  • Indexes and HOT updates: HOT updates are only possible when the updated columns are not part of any index. If you update an indexed column, PostgreSQL must update the index too, and the HOT path is not available.

References

Posts in this series