Optimizing PostgreSQL Updates with HOT and Fillfactor

Introduction

You run an UPDATE query. It should be simple—just change a value, right? But over time, updates seem to slow down, and you’re not sure why.

What if I told you that every update modifies more than just the row itself? Even if you don’t touch an indexed column, PostgreSQL still updates every index on the table. This hidden overhead accumulates, making updates progressively slower.

The good news? PostgreSQL has a built-in optimization called HOT (Heap-Only Tuple) updates that can eliminate index overhead for certain updates—but only if you set things up correctly. Let’s break it down.

How PostgreSQL Handles Updates

MVCC and Tuple Versioning

PostgreSQL uses Multiversion Concurrency Control (MVCC), meaning updates don’t modify rows in place. Instead:

1. The old row is marked as deleted but remains in the table until vacuum removes it.
2. A new row (tuple) is created in another location, with a different ctid (row pointer).

This allows transactions to keep seeing the old row until they finish. But it introduces an unexpected problem: index updates.

Index Overhead: The Hidden Performance Cost

Since the new tuple is stored in a different location, all indexes on the table must be updated to point to the new row, even if the indexed columns weren’t modified.

For tables with many indexes, this leads to:

• More disk I/O – Every update creates a new row version, requiring PostgreSQL to write additional index entries, even if indexed columns remain unchanged.
• Slower updates – As more indexes need to be modified, update operations take longer, increasing query execution time.

If your table has multiple indexes, every update triggers an update across all of them, significantly impacting performance.

How HOT Updates Optimize Performance

HOT (Heap-Only Tuple) updates reduce the overhead of updates by keeping updated rows on the same page. This eliminates the need to modify indexes, making updates significantly faster. For an update to qualify as a HOT update:

• The updated column must not be indexed.
• There must be free space in the same page for the new row.

When both conditions are met:

• PostgreSQL stores the new version on the same page as the old row.
• The original index entry continues to point to the old row, which redirects to the latest version.
• Index modifications are skipped, making updates much faster.

This optimization reduces write amplification, improves update speed, and minimizes index bloat. However, PostgreSQL can only perform HOT updates if there is free space on the page—which is where fillfactor comes in.

Benchmark: Fillfactor and HOT Updates

We tested different fillfactor values to see how they impact HOT update performance. The fillfactor setting controls how much PostgreSQL fills each page during inserts—a lower fillfactor reserves space for future updates, increasing the chance of HOT updates.

For our benchmark, we created a table with 7 columns, three indexes, and 1,000,000 rows. We then updated 30% of the rows and measured the update performance across different fillfactor settings.

Our benchmark showed that lowering fillfactor increases HOT updates, reducing index modifications and improving update speed. At fillfactor 70, all updates were HOT, meaning further reductions wouldn’t provide additional benefits in this case. The full benchmark can be found here.

But while HOT updates can be beneficial, they aren’t always the right choice. Here’s when lowering fillfactor makes sense—and when it doesn’t.

When to Optimize for HOT Updates

If your workload benefits from frequent updates, adjusting fillfactor can help PostgreSQL perform HOT updates, reducing index modifications and improving update speed. Consider these optimizations:

• Lower fillfactor for update-heavy tables – Leaving extra space in each page increases the likelihood of HOT updates, minimizing index modifications and improving performance.
• Avoid indexing frequently updated columns when possible – HOT updates only work when non-indexed columns are updated. If a column is frequently modified, think twice before indexing it.

When HOT Updates Are Not Beneficial

While HOT updates can improve performance, they aren’t always the best option. In some cases, a higher fillfactor is preferable:

• Frequent index updates or large row changes – If updates frequently modify indexed columns or if rows grow in size, HOT updates won’t apply, and lowering fillfactor won’t help.
• High-performance read workloads – A lower fillfactor increases table size, leading to higher memory usage, reduced cache efficiency, and slower full-table scans in analytical queries.
• Rarely updated tables – If updates are infrequent, reserving extra space is wasteful, and keeping fillfactor high ensures better storage efficiency.

Conclusion: Faster Updates with a Simple Tweak

If your PostgreSQL updates are slowing down, index modifications might be adding unnecessary overhead. Our benchmark showed that adjusting fillfactor can significantly improve update performance by enabling HOT updates and reducing index impact. However, lowering fillfactor too much increases table size, so the key is finding the right balance based on your workload.

Before accepting slow updates as inevitable, check your current HOT update percentage:

SELECT relname, n_tup_upd, n_tup_hot_upd, 
       ROUND(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 2) AS hot_update_ratio 
FROM pg_stat_user_tables;

If HOT updates are low, consider adjusting your fillfactor and testing performance—a small tweak might be what you need for faster updates and a more efficient PostgreSQL database.