A PostgreSQL Hot Update study on small tables. Fillfactor and Hash Partitioning Strategies.

Can reserving more space for updates make a small, frequently updated PostgreSQL table faster? I compared the default schema, a lower fillfactor, and hash partitioning on a table of approximately 500 MB. The workload focused on updates, with particular interest in heap-only tuple (HOT) updates.

My initial expectation was that leaving more room on each heap page would encourage HOT updates and improve throughput. In the recorded run, however, setting fillfactor to 50 on both the table and its index produced lower throughput and a larger observed buffer footprint than the default schema. Hash partitioning tracked the default closely.

Test environment and scope: I ran these tests locally using Docker containers. These are exploratory results from that environment, not a definitive production benchmark. Shared host resources, container resource limits, and the storage configuration can influence measurements. A repeatable test in an isolated environment may be necessary before drawing a definitive conclusion for this workload.

Why HOT updates and fillfactor matter

An update creates a new row version. PostgreSQL can use HOT when the new version fits on the same heap page and the update does not change columns referenced by non-summarizing indexes. HOT avoids creating new entries in those indexes and allows some obsolete row versions to be reclaimed during normal page access. See the PostgreSQL HOT documentation.

A lower table fillfactor reserves more space on heap pages when they are filled, increasing the opportunity for same-page updates. That trades packing density for update space: the same initial rows can occupy more pages. See table storage parameters.

Table and index fillfactor are separate settings. Reserving space in an index does not create room for HOT updates in the heap. Because this experiment lowered both settings together, it cannot isolate the effect of table fillfactor alone.

What I compared

The original benchmark notes identify PostgreSQL 18.2. The exact Docker image, schema, partition count, workload script, concurrency, host specifications, and PostgreSQL settings are not included here; those details are needed to reproduce the comparison. The version should also be checked against the original run metadata before publication.

The plots cover roughly fourteen minutes and include a startup period. Throughput continues to rise during the recording, so this is not an established steady-state comparison. Only the throughput plots include all three variants; the remaining plots compare fillfactor 50 with the default schema.

The only tweaks in configuration that were used are shared_buffers and max_wal_size, both set to 4GB. The reason behind this is to have both relations to fit in the buffer.

CREATE TABLE small_table (
    group_id       integer NOT NULL,
    user_id         integer NOT NULL,
    team_id         integer NOT NULL,
    activity_log_id integer NOT NULL,
    edited_at       timestamp without time zone NOT NULL,
    PRIMARY KEY (group_id, user_id)
)
-- And set fillfactor only at Heap
-- WITH (FILLFACTOR=50)
;

CREATE INDEX ON small_table (team_id);

\set user_id_rand random(1, 100000)

DO $$
DECLARE
    user_id_ran integer;
    group_id_ran integer;
    team_id_ran integer;
    last_user_id integer;
BEGIN

    SELECT user_id, group_id, team_id
        FROM users WHERE user_id = :user_id_rand
     INTO user_id_ran, group_id_ran, team_id_ran;

    INSERT INTO small_table ("group_id","user_id","team_id","activity_log_id","edited_at") 
        VALUES (group_id_ran,user_id_ran,team_id_ran,nextval('activity_log_id_seq'),now()) 
        ON CONFLICT (group_id, user_id) 
        DO UPDATE SET "activity_log_id"=EXCLUDED."activity_log_id","edited_at"=EXCLUDED."edited_at" ;

END $$ LANGUAGE plpgsql;

TPS and Bloat Comparison (Version 13.23)

What this run shows

• Hash partitioning stayed close to the default in throughput. This run does not show a clear throughput benefit or a large penalty. It does not establish that partitioning has negligible overhead for other queries or workloads.
• Fillfactor 50 generally delivered lower throughput. At the final TPS sample, the default reached 3,940 TPS, hash partitioning 3,840 TPS, and fillfactor 50 reached 3,340 TPS. These are individual samples, not run averages or statistically established differences.
• The buffer footprint diverged substantially. At the final buffer sample, the fillfactor variant had 78,571 buffers versus 10,527 for the default, about 7.5 times as many. Dirty-buffer counts showed a similar endpoint ratio. These counts describe the recorded cache state, not an increase in the configured size of shared_buffers or a direct measurement of physical I/O.
• More early HOT activity did not translate into higher sustained throughput. The fillfactor variant starts with higher recorded HOT activity, but the default overtakes it later. Absolute HOT counts or rates must be interpreted alongside total updates.
• Fewer estimated dead tuples did not coincide with higher throughput. The fillfactor variant finishes with a lower dead-tuple estimate, but that alone does not establish lower cleanup cost or better overall performance.

A larger working set is one possible explanation for the lower throughput. These plots alone cannot distinguish that effect from index layout, checkpoint activity, vacuum behavior, or resource contention on the local host.

Reading the measurements

The original numeric samples are preserved below. TPS and update throughput measure different things: a transaction can contain more than one operation. Buffer counts describe cache occupancy at a point in time, while dirty buffers are pages modified in memory. Neither chart measures storage latency or write bandwidth directly.

For HOT analysis, compare the change in n_tup_hot_upd with the change in n_tup_upd over the same interval. A higher absolute HOT value is not necessarily a higher HOT percentage. PostgreSQL reports n_dead_tup as an estimate, and index blocks hit represent accesses satisfied from PostgreSQL’s buffer cache. The original exporter queries and transformations are not recorded here, so the HOT and index-hit plots should not be treated as verified per-second rates. See the PostgreSQL statistics documentation.

Recorded results

TPS Comparison between v13 and v18

TPS Comparison

Transactions per second for all three variants. Compare the trajectories as well as the endpoints; the rising throughput limits conclusions about steady-state performance.

Update Throughput

Reported updates per second. The default and hash-partitioned variants remain close, while the fillfactor variant generally trails them.

Buffer Cache Total Buffers

Recorded buffer counts for the default and fillfactor variants. The roughly 7.5× difference appears near the end, not throughout the entire run. Without the collection query, the exact relation and fork coverage remains unspecified.

Buffer Cache Dirty Buffers

Dirty-buffer counts diverge late in the run. A large dirty set can indicate pending writeback work, but its size alone does not establish a storage bottleneck.

Statio Index Blocks Hit

Recorded primary-key index cache hits. Lower values are not automatically better: the variants complete different amounts of work, and the exporter transformation is needed to interpret the unit precisely.

HOT Updates Per Table

Recorded HOT activity for each table. To compare HOT efficiency, calculate the HOT percentage using matching total-update samples rather than comparing this chart alone.

Dead Tuples Per Table

Estimated dead tuples for each table. Cleanup timing and the number of completed updates affect this comparison. The supplied series also has a timestamp gap from 20:58:50 to 21:00:00.

What I would test next

To determine whether this result holds beyond the local Docker setup, the next experiment should separate the configuration changes and control the environment:

1. Isolate the runs. Run one variant at a time on a quiet, dedicated host or an otherwise controlled environment. Record Docker CPU and memory limits, volume type, host resources, and whether the load generator shares the database host.
2. Change one setting at a time. Compare default settings with table-only fillfactor changes, then index-only changes. Test less aggressive table values such as 90, 80, and 70 before assuming 50 is appropriate. Build each variant from an equivalent starting dataset with the intended physical layout.
3. Repeat and vary the order. Use consistent warm-up and measurement intervals, repeat each configuration, and report variation across runs. Extend the measurement window to observe checkpoint and autovacuum cycles rather than relying on an endpoint.
4. Measure cost per unit of work. Capture latency percentiles, completed transactions, WAL bytes, I/O timing, checkpoint activity, relation sizes, and interval-based HOT percentages. Aggregate statistics across all leaf partitions when comparing the partitioned table with the unpartitioned table.
5. Publish the reproduction details. Include the image tag or digest, SELECT version() output, DDL, workload and monitoring queries, partition key and count, concurrency, and relevant server settings.

Conclusion

For this local Docker run, the default schema delivered higher throughput than setting both table and index fillfactor to 50, while hash partitioning performed similarly to the default. The lower fillfactor showed more early HOT activity but a much larger buffer footprint later in the run.

That is a useful observation to investigate, not a general recommendation against lowering fillfactor. A definitive conclusion for this workload requires repeated measurements with configuration changes separated and environmental interference controlled; an isolated environment may be needed to establish whether the observed difference is reproducible.