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 no fillfactor entry 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 like fillfactor=80. The offset of 12 skips the fillfactor= prefix.
  • relkind = 'r' — Limits results to ordinary tables (r). Partitioned tables (p) are not included.

Key Points

  • A high n_tup_hot_upd count relative to total updates means HOT updates are working well on that table.
  • 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 full, or that updates change indexed columns.
  • 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) 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_pct before 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