How to Use HOT Updates and Fillfactor in Large Odoo PostgreSQL Databases

When you work with large Odoo databases, especially during migrations, you quickly realize that PostgreSQL storage behavior directly affects performance. One of the most important storage features is the HOT update, also known as the Heap-Only Tuple update. In a real-world migration for a 120 GB Odoo database, I observed that HOT updates failed frequently because all tables had a fill factor of 100%. This meant that most pages were completely full, leaving no free space for HOT updates.

This blog explains what really happens inside PostgreSQL when you update a row, why fillfactor matters, how HOT updates improve performance, and how adjusting the fill factor can significantly reduce bloat.

What Happens Internally When You Update a Row in PostgreSQL

PostgreSQL uses MVCC (Multi-Version Concurrency Control). This means updates do not modify rows in place. Instead:

1. The old row version is marked as invalid (dead tuple) for future transactions.
2. A new row version is inserted into the same page if possible. This is a HOT update.
3. If there is not enough space on the same page, PostgreSQL inserts the new tuple on a different page, causing a non-HOT update.

A non-HOT update increases bloat faster and requires index updates. A HOT update does not touch any indexes because the updated tuple remains in the same page and maintains the same TID.

When Does PostgreSQL Perform a HOT Update?

A HOT update is possible when:

1. The update does not modify indexed columns.
2. There is enough free space in the same heap page to store the new tuple version.

If the page is full, PostgreSQL must place the updated row in a different page. This breaks HOT update behavior and leads to:

• More index writes
• More page allocations
• More dead tuples
• More aggressive vacuum requirements
• Slower performance in large Odoo tables

This is exactly what happens when the fillfactor is set to 100.

Fill Factor: Why It Matters for HOT Updates

Fillfactor controls how much space PostgreSQL reserves in each page during inserts.

For example:

• Fill factor 100 means fill pages completely
• Fill factor 90 means fill pages only up to 90% and leave 10% space free
• Fill factor 70 leaves 30% space free for future updates
• Lower fill factor increases the chances of HOT updates.

Why HOT Updates Reduce Bloat

When PostgreSQL performs a HOT update:

• The updated tuple stays in the same page
• No new pages are allocated
• No index updates are required
• Dead tuples become reclaimable by vacuum
• Less table bloat builds up

This leads to more efficient storage, especially in write-heavy Odoo models like:

• Account.move
• Mail.message
• Stock.move
• Res.partner
• Sale.order
• Pos_order

Demonstration with pageinspect

In this section, we will show how pages behave before and after adjusting the fill factor.

Step 1: Enable pageinspect

CREATE EXTENSION pageinspect;
# you can inspect the extensions functionalities by using the below command
\dx+ pageinspect

Step 2: Create a sample table

CREATE TABLE hot_demo(
    id SERIAL PRIMARY KEY,
    name TEXT,
    value INTEGER
);

Step 3: Insert a large number of rows

INSERT INTO hot_demo(name, value)
SELECT 'row_' || g, g
FROM generate_series(1, 10000000) g;

Check the size of the table 

Select pg_size_pretty(pg_relation_size('hot_demo'));

Step 4: Inspect the number of pages

SELECT relpages
FROM pg_class
WHERE relname = 'hot_demo';

You get result like this

 relpages 
----------
    63694
(1 row)

You can also use the command below to check the total pages of this relation.

SELECT pg_relation_size('hot_demo')/8192 AS pages;

Step 5: Update rows and observe HOT behavior

UPDATE hot_demo
SET value = value + 1;

After doing this update operation, do a manual analysis to refresh the statistics of this table in PostgreSQL

analyze verbose hot_demo;

Then check the pages of this table again

You can see that the total pages of this table has increased

 relpages 
----------
   127387
(1 row)

Check HOT percentage:

SELECT n_live_tup, n_dead_tup, n_mod_since_analyze
FROM pg_stat_all_tables
WHERE relname = 'hot_demo';

In a table with fillfactor 100, most updates will not be HOT.

Adjusting Fillfactor for Better HOT Updates

You can check the fill factor of a specific table by using this query

SELECT c.relname AS table_name,
       COALESCE(
           split_part(option, '=', 2),
           '100'
       ) AS fillfactor
FROM pg_class c
LEFT JOIN LATERAL (
    SELECT opt AS option
    FROM unnest(c.reloptions) opt
    WHERE opt LIKE 'fillfactor=%'
) o ON true
WHERE c.relname = 'hot_demo';

Change fillfactor:

ALTER TABLE hot_demo SET (fillfactor = 80);

This only affects new or rewritten pages.

So perform:

VACUUM FULL hot_demo;

Now check the relation size again:

SELECT pg_relation_size('hot_demo')/8192 AS pages_after;

The table will have fewer pages filled to the top and more room reserved for HOT updates.

Demonstrate HOT Improvements After Fillfactor Change

Insert again:

UPDATE hot_demo
SET value = value + 1;

Now inspect HOT behavior:

SELECT n_live_tup, n_dead_tup, n_mod_since_analyze
FROM pg_stat_all_tables
WHERE relname = 'hot_demo';

You will notice:

• More HOT updates
• Fewer dead tuples
• Slower bloat growth
• Less index activity

You can inspect heap pages directly:

SELECT lp, lp_flags, lp_off, lp_len
FROM heap_page_items(get_raw_page('hot_demo', 0));

Before adjusting fillfactor, pages were fully packed. After vacuum full + new fillfactor, pages contain reserved free space.

Why Fillfactor Improves Performance in Production

This is especially important during Odoo migrations or heavy transactional usage.

Benefits:

• HOT updates work more frequently
• Reduced index overhead
• Lower bloat
• Better vacuum efficiency
• Better long-term storage stability
• Higher update throughput
• Less IO pressure

Setting a correct fill factor gives PostgreSQL breathing room for future updates.

How to Choose a Good Fillfactor

• For heavy-update tables - fill factor 70–80 is recommended
• For moderate updates - fill factor 85–90
• For mostly static tables - keep fill factor 100

Fillfactor Recognition Query

Use this query to identify tables that should have a lower fill factor:

SELECT
    relname AS table,
    n_dead_tup AS dead_tuples,
    n_live_tup AS live_tuples,
    ROUND((n_dead_tup::numeric / NULLIF(n_live_tup, 0)) * 100, 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY dead_ratio DESC
LIMIT 20;

Tables with a high dead_ratio benefit the most from a lower fillfactor.

Conclusion

HOT updates are one of the most important performance optimization mechanisms inside PostgreSQL. 

When fillfactor is set to 100, HOT updates fail frequently, especially in large transactional Odoo databases.

By adjusting the fill factor and performing a vacuum full, you can:

• Reduce bloat
• Improve update performance
• Reduce IO and index writes
• Accelerate Odoo operations
• Ensure long-term database stability

With the right fillfactor configuration, PostgreSQL avoids unnecessary page allocations and performs more HOT updates, resulting in better performance for large Odoo production systems.