loke.dev
Header image for Why Are Your Postgres Updates Secretly Doubling Your Table Size?

Why Are Your Postgres Updates Secretly Doubling Your Table Size?

Learn how updating indexed columns sabotages the Heap Only Tuple (HOT) optimization, leading to explosive table bloat and unnecessary write amplification.

· 8 min read

Ever wondered why your database disk usage is skyrocketing even though you aren't actually adding any new rows?

You might be running a relatively stable application, but behind the scenes, your 50GB table has ballooned to 120GB. You check your logs, and there are no massive imports—just the usual trickle of UPDATE statements. You run VACUUM, but the space doesn't return to the OS. This isn't a leak in the traditional sense; it’s a fundamental architectural trade-off in how PostgreSQL handles data consistency.

To understand why your table is doubling in size, we have to look at the "hidden" versioning system Postgres uses and a specific optimization called Heap Only Tuples (HOT) that you are likely accidentally breaking.

The "Update is a Delete+Insert" Problem

Most developers assume an UPDATE happens in-place. If you change a user’s last_login_at timestamp, you'd expect Postgres to find that specific byte on the disk and overwrite it.

Postgres doesn't do that.

Because of Multi-Version Concurrency Control (MVCC), Postgres needs to ensure that if Transaction A is reading a row, and Transaction B updates it simultaneously, Transaction A continues to see the "old" version until it commits. To achieve this, Postgres treats every UPDATE as a DELETE of the old row (the "tuple") and an INSERT of a new one.

Here is a quick look at what that looks like internally:

-- Let's create a simple table to track this
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT,
    bio TEXT,
    last_login TIMESTAMP
);

INSERT INTO users (username, bio, last_login) 
VALUES ('dan', 'Just a dev', NOW());

-- When I run this:
UPDATE users SET last_login = NOW() WHERE username = 'dan';

In the background, the original row is marked as "dead," and a completely new row is written to the table. This is where the bloat starts. If you update that row 100 times, you technically have 100 versions of that row on disk until the Autovacuum process comes around to clean up the 99 dead ones.

The Silent Performance Killer: Index Bloat

If every update creates a new row, what happens to the indexes?

This is the "secret" part of the doubling table size. If you have an index on id, username, and last_login, every time you update the last_login column, Postgres has to go into every single index and add a new pointer to the new row.

Even if you only changed the last_login column, the index on username (which didn't change!) now needs a new entry because the physical location of the row on disk (the ctid) has moved. This is called Write Amplification. One row update triggers multiple index writes.

This is why your indexes often grow faster than your actual data.

Meet HOT: The Optimization That Saves Your Disk

Postgres engineers knew this "index update on every row movement" was a massive performance bottleneck. To solve it, they introduced Heap Only Tuples (HOT).

A HOT update allows Postgres to update a row without needing to update the indexes. This is the holy grail of Postgres performance. For a HOT update to occur, two strict conditions must be met:

1. No indexed column is modified.
2. There is enough space on the same "page" (an 8KB block of memory/disk) to store the new row version.

When a HOT update happens, the index still points to the *original* location. When Postgres follows that pointer, it finds a "redirect" at the old location that points to the new version on the same page.

It looks like this:
Index -> Old Tuple (Redirect) -> New Tuple

This is incredibly fast and keeps your indexes lean. But here is the catch: the moment you update a column that has an index on it, HOT is disabled for that transaction.

Breaking HOT: A Practical Example

Let’s see how easy it is to accidentally sabotage this. We’ll use the pg_stat_user_tables view, which is the most important tool for diagnosing this.

-- Setup a table with a fillfactor (we will discuss this later)
CREATE TABLE hot_test (
    id INT,
    val_indexed INT,
    val_plain INT
) WITH (fillfactor = 70);

-- Index only one of the values
CREATE INDEX idx_val_indexed ON hot_test(val_indexed);

-- Insert a dummy row
INSERT INTO hot_test (id, val_indexed, val_plain) VALUES (1, 100, 100);

-- Check the initial stats
SELECT n_tup_upd, n_tup_hot_upd 
FROM pg_stat_user_tables 
WHERE relname = 'hot_test';
-- Result: 0, 0

Now, let’s perform an update on the non-indexed column:

UPDATE hot_test SET val_plain = 200 WHERE id = 1;

SELECT n_tup_upd, n_tup_hot_upd 
FROM pg_stat_user_tables 
WHERE relname = 'hot_test';
-- Result: 1, 1 (This was a HOT update!)

Postgres was able to perform a HOT update because val_plain isn't indexed. The index idx_val_indexed was not touched. Now, let’s update the indexed column:

UPDATE hot_test SET val_indexed = 300 WHERE id = 1;

SELECT n_tup_upd, n_tup_hot_upd 
FROM pg_stat_user_tables 
WHERE relname = 'hot_test';
-- Result: 2, 1 (The total updates increased, but HOT updates stayed at 1)

By updating val_indexed, we forced Postgres to:
1. Create a new row version.
2. Update the idx_val_indexed index.
3. Update every other index on the table, even if those columns didn't change.

If this table had 10 indexes, that one update just triggered 10 index writes and significantly increased the table's "bloat" footprint.

The "Over-Indexing" Trap

We’ve all been there: a query is slow, so we slap an index on every column in the WHERE clause. While this speeds up reads, it turns your UPDATE statements into heavy-duty I/O operations.

I recently consulted for a team that had a metadata JSONB column. They were updating this column every few seconds for things like last_processed_at. They also had a GIN index on the entire JSONB column to allow for arbitrary searching.

The result? Their database was growing by 10GB a day, yet the actual number of users was flat. Because the GIN index was on the column being updated, HOT was never triggered. Every single update was a full "Delete + Insert + Re-index" operation.

The lesson: Be surgical with indexes. If a column is updated frequently (like a counter, a status flag, or a timestamp), think twice before indexing it. If you *must* index it, realize that you are choosing read speed over disk space and write throughput.

The Secret Weapon: Fillfactor

Remember the second condition for HOT? *The new version must fit on the same page.*

By default, Postgres fills its 8KB pages to 100%. When you insert data, it packs it as tightly as possible. This means when you go to update a row, there is often no room left on that page for the new version. Postgres is forced to put the new version on a different page, which immediately breaks the HOT optimization, even if you aren't updating an indexed column.

You can fix this by adjusting the FILLFACTOR.

-- Set fillfactor to 80, leaving 20% of every page empty for updates
ALTER TABLE users SET (fillfactor = 80);

-- You must REINDEX or VACUUM FULL to apply this to existing data
VACUUM FULL users;

By setting a FILLFACTOR of 80, you are telling Postgres: "Only fill 80% of the page with new inserts. Leave 20% for future updates."

This gives the HOT optimization "breathing room" to keep the new row versions on the same page, preventing index bloat and keeping your table size stable.

What's the right number?
- 100 (Default): Best for static tables (read-only).
- 80-90: Good for tables with moderate updates.
- 50-70: Good for tables with heavy update traffic.

Setting it too low will make your table larger initially (since you're leaving holes on the disk), but it will prevent it from "exploding" unpredictably later.

How to Audit Your Database for Bloat

If you suspect your tables are suffering from this, you can run a query to see your HOT update success rate. A low ratio of HOT updates to total updates on a table with many indexes is a red flag.

SELECT 
    relname AS table_name, 
    n_tup_upd AS total_updates, 
    n_tup_hot_upd AS hot_updates,
    ROUND(100 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 2) AS hot_ratio
FROM 
    pg_stat_user_tables
WHERE 
    n_tup_upd > 0
ORDER BY 
    hot_ratio ASC;

If you see a table with 1,000,000 updates and only 10,000 HOT updates (a 1% ratio), you are essentially forcing Postgres to do the maximum amount of work possible for every update.

The "Partial Index" Escape Hatch

Sometimes you need an index on a column for specific queries, but that column is updated frequently. You can mitigate the HOT penalty by using Partial Indexes.

Imagine you have a status column that transitions from pending to processed. You only ever query for WHERE status = 'pending'.

-- Instead of this (which breaks HOT for every status update):
CREATE INDEX idx_status ON orders(status);

-- Do this:
CREATE INDEX idx_status_pending ON orders(status) WHERE status = 'pending';

With the partial index, when an order moves from pending to processed, the entry is removed from the index. If it moves from processed to shipped, and both are outside the index's scope, the update *might* still qualify for HOT optimization (depending on other factors).

Summary: Keeping the Bloat at Bay

Postgres's MVCC is a masterpiece of engineering for concurrency, but it requires you to understand the "cost of moving data." To prevent your tables from doubling in size secretly:

1. Monitor your HOT ratio: Use pg_stat_user_tables to see if your updates are efficient.
2. Be Stingy with Indexes: Don't index columns that change frequently unless the read performance gain is worth the write/space penalty.
3. Tune Fillfactor: For update-heavy tables, lower the fillfactor to 80 or 90 to give HOT updates room to work.
4. Use Partial Indexes: Limit the scope of your indexes so they don't have to be updated for every change to a column.
5. Watch your JSONB: Indexing entire JSONB blobs is a common cause of massive index bloat. Use targeted functional indexes instead.

The next time you see your disk usage creeping up, don't just add more storage. Look at your UPDATE patterns. You might just find that your indexes are fighting against your heap, and a little architectural adjustment is all you need to bring things back under control.