Postgres Performance Boost: HOT Updates and Fill Factor

There’s a pretty HOT performance trick in Postgres that doesn’t get a ton of attention. There’s a way for Postgres to only update the heap (the table), avoiding having to update all the indexes. That’s called a HOT update, HOT stands for heap only tuple.

Understanding HOT updates and their interaction with page fill factor can be a really nice tool in the box for getting performance with existing infrastructure. I’m going to review HOT updates and how to encourage them in your Postgres updates.

Heap Only Tuple (HOT) updates

Modern versions of Postgres are able to perform HOT (Heap Only Tuple) updates. A HOT update occurs when a new version of a row can be stored on the same page as the original version, without the need to move the row to a new page.

With HOT updates, if the updated row can still fit on the same data page as the original row, Postgres adds a new row on the same page, while keeping the old row data since it may still be in use by other processes. Postgres also adds a HOT chain link from the old row to the new row, so it can find the new row when a HOT update occurs.

HOT updates and indexes

So the way this normally works in PostgreSQL without HOT updates is if you have a table that is indexed, and one row(tuple) is updated, the update must be applied to the index. For HOT updates, Postgres will skip the update to the index IF you aren’t updating the index key.

By skipping the index update step, HOT updates reduce the amount of disk I/O and CPU processing required for an update operation, leading to better performance, especially for tables with large indexes or frequent updates.

Postgres 16 HOT updates and BRIN indexes

Prior to Postgres 16, any index on an updated column would block updates from being HOT. An update in Postgres 16 makes HOT updates more feasible since BRIN (summarizing) indexes do not contain references to actual rows, just to the pages. This allows columns indexed with BRIN to be updated and still have HOT updates occur. Though some care should be taken to avoid those updates changing the value very much as that could reduce the effectiveness of the BRIN.

Fill Factor

In PostgreSQL, a table is divided into blocks or pages, each capable of holding a fixed amount of data. When a new row is inserted into a table, PostgreSQL tries to fit it into an existing page. If there's not enough space, a new page is allocated. Fill factor determines how much space within each page is initially reserved for future updates.

Postgres uses a default fill factor of 100 for tables. This means that by default, Postgres will try to pack each page as full as possible when initially storing data, without leaving any space for future updates.

For example, consider a table with a fill factor of 70%. When PostgreSQL inserts data to a page, it leaves 30% of the page empty to accommodate future updates to existing rows. When a table has a higher fill factor (closer to 100%), there is less empty space on each data page. This can lead to situations where an update to a row requires more space than is available on the original data page.

By lowering the fill factor, you increase the chances that there will be enough free space on a data page to accommodate HOT updates to existing rows. But be careful, it's essential to strike a balance with the fill factor, as setting it too low can lead to wasted space and increased disk I/O for table scans.

Configuring Fill Factor

You can set the fill factor for a table using the FILLFACTOR parameter in the CREATE TABLE or ALTER TABLE statements. The value ranges from 10 to 100 and represents the percentage of space to be filled initially.

-- Create a table with a fill factor of 70%
CREATE TABLE my_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT
) WITH (FILLFACTOR = 70);

You can alter an existing table to change its fill factor:

-- Alter the fill factor of an existing table
ALTER TABLE my_table SET (FILLFACTOR = 80);

HOT updates and fill factor

HOT updates are particularly effective when the fill factor is set appropriately.

When a table has a lower fill factor, PostgreSQL leaves more empty space on each data page when inserting rows. This empty space allows PostgreSQL to perform updates on the same page, without needing to move the entire row to a new page. Lowering the fill factor in PostgreSQL can force more HOT updates by increasing the likelihood that there will be free space on data pages for new versions of rows.

Read vs write heavy workloads and fill factor

Even though you can lower your fill factor, you don’t want to do that in every case:

• Read heavy workloads: read-heavy tables may benefit from a higher fill factor to reduce the number of pages that need to be read from disk.
• Write heavy workloads: updates may benefit from a lower fill factor to increase the chances of HOT updates

Fill factor settings in the 70, 80, and 90 range, can be reasonable for a read/write database making better use of HOT updates. There are folks who go down to 50 and even lower, but that would be a special case of an unusually heavy update workload.

Measuring HOT updates

To identify Heap Only Tuple (HOT) updates in Postgres, you can query the system catalog pg_stat_user_tables or pg_stat_all_tables to get information about how often updates are being performed on a table and how many of them are HOT updates. Here's a query for that:

SELECT
    relname AS table_name,
    seq_scan AS sequential_scans,
    idx_scan AS index_scans,
    n_tup_ins AS inserts,
    n_tup_upd AS updates,
    n_tup_hot_upd AS hot_updates
FROM
    pg_stat_user_tables
ORDER BY
    hot_updates DESC;

In an ideal world, all updates which can be HOT will be. Developers should take a look at their indexing strategy and the updates that they are doing and try to make those updates eligible for HOT - and when they are, lower the fill factor on those tables to further encourage HOT updates to happen.

It is also worth mentioning that lowering fill factor will increase your measurable bloat. Most bloat queries estimate row size based on table statistics and estimating how much space in data files is unused. In many cases, the unused space from a lower fill factor will be considered bloat. In general, this is just something to note, and won’t cause bloat warnings or concern, unless your fill factor is very very low.

Summary

• HOT updates are when an updated row can fit on the same page as the old row.
• HOT updates improve performance since they reduce IOPs, WAL, index updates and other resources
• Consider your indexing strategy if you’re trying to utilize more HOT updates. You generally cannot have a HOT update if the data updated is indexed, unless that index is BRIN. BRIN working with HOT updates is a new feature in Postgres 16.
• You can increase your chances of an update being HOT by lowering your page fill factor. There can be trade-offs with fill factor and performance, so generally a measure and see approach is best.

co-authored with Stephen Frost