PostgreSQL HOT Updates: Measure the Fillfactor Tradeoff

Lowering PostgreSQL table fillfactor can create more heap-only tuple (HOT) updates, but a copied value such as 70 or 80 is not a tuning decision. A HOT update needs two conditions at the same time: the statement must avoid changing an index-referenced column, and the new row version must fit on the same heap page as the old one. Reserved page space helps only with the second condition.

The practical decision is therefore measured: identify an update-heavy table, prove which updates are HOT-eligible, compare HOT ratio, WAL and relation growth over the same workload, and keep the lower fillfactor only when reduced write amplification is worth a larger heap. The reproduced PostgreSQL 17.10 lab below held schema and row data constant. Under one full-table update, the fillfactor=70 table produced 45.16% HOT updates versus 0% at 100, wrote about 24% less WAL in this isolated run and grew a smaller secondary index—but used more heap space. Those numbers describe this fixture, not a universal target.

This guide is for developers and PostgreSQL operators who can run SQL and read catalog statistics. It assumes an isolated database or disposable host. Production changes require a representative observation window, a maintenance decision for any rewrite and an independent backup/return path.

HOT Eligibility Comes Before the Fillfactor Number

PostgreSQL uses multiversion concurrency control (MVCC), so an UPDATE creates a new row version rather than overwriting the current tuple in place. Ordinarily, indexes need new entries that can locate that version. PostgreSQL’s current HOT storage documentation defines the optimization precisely: an eligible update does not modify a column referenced by a non-summarizing index, and the new version fits on the original page.

When both conditions hold, the existing index entry can continue pointing into that page and PostgreSQL follows an in-page HOT chain to the visible version. The optimization avoids new index entries and lets intermediate versions be removed during ordinary page pruning. It does not make row versioning, heap writes or vacuum responsibilities disappear.

fillfactor changes the space condition. The CREATE TABLE storage-parameter reference defines table values from 10 through 100, with 100 as the default. A lower value tells inserts to stop packing each page earlier, reserving the remainder for later versions of rows already on that page.

One exception matters on current releases. PostgreSQL excludes summarizing indexes from the ordinary indexed-column restriction; core PostgreSQL currently provides BRIN as the summarizing index method. A B-tree, partial index expression or included column that depends on the changed value can still make the update non-HOT. Voxfor’s guide to an INVALID PostgreSQL index that still adds write overhead reinforces the broader lesson: planner usability and write-maintenance ownership are different questions.

Reproduce Two Page Layouts Without Touching Production

The lab uses Debian 13, PostgreSQL 17.10, an 8 KiB block size, a Unix socket and no TCP listener. Autovacuum is disabled only inside this disposable cluster so it cannot alter the comparison mid-run. Install the matching server, client and contrib packages first; pgstattuple comes from contrib.

Guard the path and start an isolated cluster

Run all Bash blocks in the same root shell on a disposable host. The first block refuses an unmarked pre-existing path, registers cleanup before initialization and starts PostgreSQL only on a private socket.

set -Eeuo pipefail

LAB_ROOT=/tmp/voxfor-pg-hot-122
DATA_DIR="$LAB_ROOT/data"
SOCKET_DIR="$LAB_ROOT/socket"
MARKER="$LAB_ROOT/.voxfor-pg-hot-lab"
PG_BIN=/usr/lib/postgresql/17/bin
PORT=55432

if [[ -e "$LAB_ROOT" ]]; then
  [[ -f "$MARKER" ]] && [[ "$(<"$MARKER")" == voxfor-postgresql-hot-lab-122 ]]
  rm -rf -- "$LAB_ROOT"
fi

install -d -m 0700 -o postgres -g postgres "$LAB_ROOT" "$SOCKET_DIR"
printf '%s\n' voxfor-postgresql-hot-lab-122 >"$MARKER"
chown postgres:postgres "$MARKER"

cleanup() {
  if [[ -f "$MARKER" ]] && [[ "$(<"$MARKER")" == voxfor-postgresql-hot-lab-122 ]]; then
    if [[ -f "$DATA_DIR/postmaster.pid" ]]; then
      runuser -u postgres -- "$PG_BIN/pg_ctl" -D "$DATA_DIR" -m fast stop >/dev/null 2>&1 || true
    fi
    rm -rf -- "$LAB_ROOT"
  fi
}
trap cleanup EXIT

runuser -u postgres -- "$PG_BIN/initdb" -D "$DATA_DIR" \
  --auth=trust --no-locale --encoding=UTF8 >/dev/null
runuser -u postgres -- "$PG_BIN/pg_ctl" -D "$DATA_DIR" \
  -o "-p $PORT -k $SOCKET_DIR -c listen_addresses='' -c fsync=on -c synchronous_commit=on -c autovacuum=off -c shared_buffers=128MB" \
  -w start >/dev/null

PSQL=(runuser -u postgres -- "$PG_BIN/psql" -X -v ON_ERROR_STOP=1 -h "$SOCKET_DIR" -p "$PORT")
printf 'version=%s block_size=%s autovacuum=%s\n' \
  "$("${PSQL[@]}" -Atqc 'SHOW server_version')" \
  "$("${PSQL[@]}" -Atqc 'SHOW block_size')" \
  "$("${PSQL[@]}" -Atqc 'SHOW autovacuum')"

Hold every variable constant except table fillfactor

Both tables receive 120,000 identical rows, the same primary key and the same B-tree on lookup_key. status and payload remain unindexed, so changing status is HOT-eligible at the index condition. A VACUUM (ANALYZE) freezes the initial comparison point; it is not presented as proof that future updates will be HOT.

"${PSQL[@]}" -d postgres -c 'CREATE DATABASE hotlab'
"${PSQL[@]}" -d hotlab <<'SQL'
CREATE EXTENSION pgstattuple;
CREATE SCHEMA hotlab;

CREATE TABLE hotlab.ff100 (
  id bigint PRIMARY KEY,
  lookup_key integer NOT NULL,
  status integer NOT NULL,
  payload text NOT NULL
) WITH (fillfactor = 100, autovacuum_enabled = false);

CREATE TABLE hotlab.ff70 (
  id bigint PRIMARY KEY,
  lookup_key integer NOT NULL,
  status integer NOT NULL,
  payload text NOT NULL
) WITH (fillfactor = 70, autovacuum_enabled = false);

CREATE INDEX ff100_lookup_idx ON hotlab.ff100 (lookup_key);
CREATE INDEX ff70_lookup_idx ON hotlab.ff70 (lookup_key);

INSERT INTO hotlab.ff100
SELECT g, (g % 10000)::integer, 0, repeat(md5(g::text), 4)
FROM generate_series(1, 120000) AS g;
INSERT INTO hotlab.ff70 SELECT * FROM hotlab.ff100;

VACUUM (ANALYZE) hotlab.ff100;
VACUUM (ANALYZE) hotlab.ff70;
SQL

Before generating churn, confirm the actual reloption and physical starting size. A lower table fillfactor is supposed to allocate more heap pages; treating that reserved space as an automatic defect would invalidate the experiment.

"${PSQL[@]}" -d hotlab <<'SQL'
SELECT c.relname,
       COALESCE((SELECT option_value::integer
                 FROM pg_options_to_table(c.reloptions)
                 WHERE option_name = 'fillfactor'), 100) AS fillfactor,
       pg_relation_size(c.oid) AS heap_bytes,
       pg_relation_size(i.indexrelid) AS lookup_index_bytes
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_index i ON i.indrelid = c.oid
JOIN pg_class ix ON ix.oid = i.indexrelid AND ix.relname LIKE '%_lookup_idx'
WHERE n.nspname = 'hotlab'
ORDER BY c.relname;
SQL

At baseline, the packed table used 21,848,064 heap bytes and the 70 table used 31,711,232. Both lookup indexes used 1,368,064 bytes. That is the initial cost of reserving page space before one update is measured.

Run One Eligible Workload and Read Four Signals

pg_stat_reset() is acceptable here because the cluster exists only for this experiment. Do not reset shared production statistics merely to simplify an article query. In production, record counter values and timestamps before and after a representative interval, then calculate deltas.

The input updates every row’s unindexed status field once. It captures inserted WAL bytes from a local LSN boundary, forces the current backend’s statistics to flush and reads HOT counts plus secondary-index size after each table. The two transactions are sequential, so the values compare this controlled fixture rather than simultaneous throughput.

"${PSQL[@]}" -d hotlab <<'SQL'
SELECT pg_stat_reset();
CHECKPOINT;
CREATE TEMP TABLE wal_start AS SELECT pg_current_wal_insert_lsn() AS lsn;

UPDATE hotlab.ff100 SET status = status + 1;
SELECT pg_stat_force_next_flush();
SELECT 'ff100' AS table_name,
       s.n_tup_upd,
       s.n_tup_hot_upd,
       round(100.0 * s.n_tup_hot_upd / NULLIF(s.n_tup_upd, 0), 2) AS hot_pct,
       pg_wal_lsn_diff(pg_current_wal_insert_lsn(), w.lsn)::bigint AS wal_bytes,
       pg_relation_size('hotlab.ff100_lookup_idx') AS lookup_index_bytes
FROM pg_stat_user_tables s CROSS JOIN wal_start w
WHERE s.relname = 'ff100';

TRUNCATE wal_start;
INSERT INTO wal_start SELECT pg_current_wal_insert_lsn();
UPDATE hotlab.ff70 SET status = status + 1;
SELECT pg_stat_force_next_flush();
SELECT 'ff70' AS table_name,
       s.n_tup_upd,
       s.n_tup_hot_upd,
       round(100.0 * s.n_tup_hot_upd / NULLIF(s.n_tup_upd, 0), 2) AS hot_pct,
       pg_wal_lsn_diff(pg_current_wal_insert_lsn(), w.lsn)::bigint AS wal_bytes,
       pg_relation_size('hotlab.ff70_lookup_idx') AS lookup_index_bytes
FROM pg_stat_user_tables s CROSS JOIN wal_start w
WHERE s.relname = 'ff70';
SQL

The 70 result is deliberately not 100% HOT. One full-table update created more new versions than the reserved space could keep on original pages. That outcome is more useful than a perfect demo because it shows why update density, row width, page reuse, concurrency and long-lived snapshots belong in the production decision.

environment postgres=17.10 block_size=8192 autovacuum=off listener=unix_socket_only
baseline ff100 heap_bytes=21848064 lookup_index_bytes=1368064
baseline ff70  heap_bytes=31711232 lookup_index_bytes=1368064
eligible ff100 updates=120000 hot=0 hot_pct=0.00 wal_bytes=76629784 lookup_index_bytes=2269184
eligible ff70  updates=120000 hot=54194 hot_pct=45.16 wal_bytes=58414832 lookup_index_bytes=1810432
indexed_control ff70 updates=20000 hot=0 hot_pct=0.00
after ff100 heap_bytes=43696128 occupied_pct=99.21
after ff70  heap_bytes=50692096 occupied_pct=85.70
verification=accepted
cleanup=lab_absent

Several conclusions are warranted; several are not. In this run, reserved page space increased HOT occurrence, reduced WAL inserted during the eligible update and limited secondary-index growth. The larger heap is the visible cost. Wall-clock speed, storage latency and cache behavior were not isolated, so the receipt does not claim that 70 is faster everywhere.

Adyen’s production write-amplification case study reached a higher HOT ratio and lower WAL with 85 on its own monthly partitions. Crunchy Data similarly recommends a measure-and-observe approach to HOT and fillfactor. Neither result transfers its winning number to a table with different row width, update density, indexes or read traffic. A pganalyze benchmark makes the same point explicitly: individual row size changes the useful fillfactor.

Make an Indexed Update Refuse HOT

The negative control changes lookup_key, which the secondary B-tree references. Even with page reserve available, all 20,000 updates must remain non-HOT. Without this control, a higher ratio could be attributed to the wrong condition.

"${PSQL[@]}" -d hotlab <<'SQL'
SELECT pg_stat_reset();
UPDATE hotlab.ff70
SET lookup_key = lookup_key + 1000000
WHERE id <= 20000;
SELECT pg_stat_force_next_flush();

SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 2) AS hot_pct
FROM pg_stat_user_tables
WHERE schemaname = 'hotlab' AND relname = 'ff70';
SQL

The result was 20,000 updates and 0 HOT updates. Lowering fillfactor cannot fix an indexing decision that makes the real update non-HOT. First inventory columns changed by the application and every index predicate, expression and included column that depends on them. Only then is page reserve worth testing.

Connection pressure can also masquerade as slow update work. If latency rises while sessions queue or connection setup dominates, use Voxfor’s PgBouncer connection-admission workflow before changing page layout. Likewise, erratic wall-clock benchmarks with stable database counters may need the VPS CPU steal-time investigation rather than a storage reloption.

Turn the Lab Into a Production Canary

Read deltas, not lifetime percentages

n_tup_upd and n_tup_hot_upd are cumulative since the relevant statistics reset. A lifetime ratio can hide a workload release, a new index or a changed row shape. Save a baseline, wait through a representative write interval and subtract. Pair the ratio with WAL per accepted transaction, heap/index growth, update latency, read latency and dead-tuple behavior.

Vacuum remains a separate owner. HOT can reduce index maintenance and allow page pruning, but it does not protect against transaction-ID wraparound or a backend holding old snapshots. When database or relation XID age rises, follow the PostgreSQL autovacuum freeze investigation instead of treating a good HOT ratio as maintenance proof.

Prefer a new partition or controlled copy

Changing a table reloption does not rearrange all existing rows into the new target layout. A rewrite can make the setting immediately visible across existing data, but VACUUM FULL and CLUSTER take an ACCESS EXCLUSIVE lock and require extra disk. pg_repack has its own extension, privilege, disk and change-control requirements. For partitioned or time-bounded workloads, a new partition with the candidate setting often creates a safer comparison than rewriting the only production copy.

Select one table or partition where non-indexed updates are common, replay representative row widths and update density in staging, and predefine a return decision. A lower ratio alone is not failure if WAL and index growth improve enough; a higher ratio alone is not success if read I/O, cache residency or storage headroom breach their budgets.

WAL generation and WAL retention are also different. HOT may reduce bytes generated by an update, while an inactive replication consumer can still prevent old segments from being recycled. Voxfor’s stale PostgreSQL replication-slot recovery identifies that retention owner before disk cleanup.

Require one acceptance record

The lab is accepted only when both tables retain 120,000 rows, the same eligible status update produces a higher HOT count at fillfactor=70 than at 100, the indexed-column control records 20,000 updates with zero HOT updates, the receipt preserves WAL and relation-size tradeoffs without claiming a universal winner, the lab database is dropped, the isolated server stops and the guarded path is absent.

For production, replace those fixture thresholds with explicit workload budgets: observation start/end, accepted transactions, HOT delta, WAL bytes per transaction, heap and index growth, update p95, representative read p95, autovacuum progress and remaining disk headroom. Record the exact schema version and application release so the comparison can be repeated after an index or query change.

The topic does not earn a service link. The live VPS page offers root-access compute and NVMe-backed plans, while the managed-hosting page describes general website/server management and names database customization for MySQL, MariaDB and MSSQL rather than this PostgreSQL page-layout canary. Neither offer is the next required action for an operator measuring an existing table, so inserting a commercial sentence would interrupt the decision path.

Remove the Fixture, Keep the Receipt

Rollback for the disposable lab drops only database hotlab, stops the cluster under the marker-guarded data directory and removes exactly /tmp/voxfor-pg-hot-122. In production, reverting the reloption does not undo a completed table rewrite; preserve the pre-change backup, previous DDL, capacity record and application acceptance result, then choose a controlled reverse rewrite or partition return rather than treating one ALTER TABLE as physical recovery.

"${PSQL[@]}" -d postgres -c 'DROP DATABASE hotlab'
runuser -u postgres -- "$PG_BIN/pg_ctl" -D "$DATA_DIR" -m fast stop >/dev/null
rm -rf -- "$LAB_ROOT"
trap - EXIT
[[ ! -e "$LAB_ROOT" ]]
printf 'cleanup=lab_absent\n'

Keep the secret-free receipt after cleanup: PostgreSQL version and block size, table definitions, index inventory, exact update shape, time window, HOT deltas, WAL deltas, relation growth and read/write acceptance. That evidence answers the only useful fillfactor question—whether reserved heap space improves this workload enough to pay for the pages it consumes.