Why do sometimes updates feel slower than inserts in Postgres? The answer lies deep in how Postgres manages data versions behind the scenes.

At the core of Postgres’ ability to handle concurrent transactions efficiently is Multiversion Concurrency Control (MVCC).

In this article, I’ll explore how MVCC works in Postgres and how it impacts both writes and reads, and the performance differences between inserts and updates.

Set up

-- pageinspect extension to view underlying data storage, we will use it soon!
CREATE EXTENSION IF NOT EXISTS pageinspect;

-- Create table store
CREATE TABLE store (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    value INT NOT NULL
);
-- Disable autovacuum for the table
ALTER TABLE store SET (autovacuum_enabled = false);

The set-up involves creating a sample table called “store” and disabling autovacuum. This will let us observe how Postgres handles versioning (MVCC in action) without automatic cleanup, helping us better understand MVCC working.

Overview of MVCC

To understand why updates behave differently from inserts, let’s first look at how MVCC works in Postgres.

MVCC is the mechanism Postgres uses to handle concurrent transactions to the same row while maintaining data consistency and isolation.

Instead of locking rows during reads and writes, Postgres creates multiple versions of a row to allow transactions to operate without blocking each other.

These multiple versions of a row are called Tuples. If a row is inserted, it has 1 tuple associated with it. If the same row is updated, there exists 2 tuples, among which one of them is live and the other is dead.

Here’s how it works:

Write operations

Every insert or update query executes within a transaction, and each transaction is assigned a unique transaction ID. While the process also involves writing to the Write-Ahead Log (WAL) and checkpointing, we will not cover those details in this article.

You can check the current transaction ID in Postgres in the following way:

postgres=# SELECT txid_current();
 txid_current
--------------
          753
(1 row)

postgres=# SELECT txid_current();
 txid_current
--------------
          754
(1 row)

Each row in Postgres has versioning information tied to transaction IDs, which track the rows’ state over time. This versioning is managed through the xmin and xmax values, which can be directly queried.

postgres=# INSERT INTO store(name, value) VALUES('score', 10);
INSERT 0 1

postgres=# SELECT xmin, xmax, * FROM store WHERE id=1;

 xmin | xmax | id | name  | value
------+------+----+-------+-------
  755 |    0 |  1 | score |    10
(1 row)

xmin represents the ID of the transaction that inserted the row (i.e., created the initial tuple).
xmax represents the ID of the transaction that deleted or invalidated the row. Since xmax here is 0, it means the row is live and not deleted or dead.

These values also influence row visibility, which we’ll explore in more detail later in the Read Operation section of the article below.

Let’s take a look at the actual tuple stored in the database to see how MVCC manages row versions internally. We can inspect tuples directly using the pageinspect extension, which allows us to view the raw contents of a table’s underlying pages.

Postgres stores data in the form of pages on disk, and each page contains multiple tuples. The tuple we’re interested in should ideally be in the first page (i.e., page 0). Let’s use pageinspect to query it and see what’s stored:

postgres=# SELECT lp, t_ctid, t_xmin, t_xmax FROM heap_page_items(get_raw_page('store', 0));

 lp | t_ctid | t_xmin | t_xmax
----+--------+--------+--------
  1 | (0,1)  |    755 |      0
(1 row)

We can see that the xmin and xmax values match the values from a regular SELECT query on the table.

lp – This is the line pointer, which acts as an index within the page, pointing to the actual location of the tuple. Since lp = 1, it means this is the first and only tuple in the page.
t_ctid – This is the tuple ID, which points to the physical location of the tuple in the format (page_number, tuple_number). If the row is updated, a new version will be created, and the ctid will point to the new location — more on this later.
xmin – Represents the transaction ID that inserted the row (in this case, transaction 755).
xmax – Represents the transaction ID that deleted or invalidated the row. Since xmax = 0, the row is still live.

A tuple in Postgres consists of both system columns (like xmin, xmax, ctid, etc.) and the actual row data. The system columns help Postgres manage row versions and visibility, which we’ll explore in detail later.

When a write (such as an update) occurs, the existing row isn’t modified directly. Instead, Postgres creates a new row version with the updated values, leaving the old row intact. The old tuple is then marked as dead but remains available for MVCC purposes (such as for supporting concurrent reads).

Let’s update the row and inspect the changes:

postgres=# UPDATE store set value=20 where id=1;
UPDATE 1

postgres=# SELECT xmin, xmax, * FROM store WHERE id=1;

 xmin | xmax | id | name  | value
------+------+----+-------+-------
  756 |    0 |  1 | score |    20
(1 row)

The row now has a new xmin value (756), which represents the transaction ID that created this new tuple. Due to MVCC, we expect that the original row (now a dead tuple) still exists alongside the new one.

We can confirm this by checking the number of live and dead tuples using the pg_stat_all_tables view:

postgres=# SELECT n_live_tup, n_dead_tup, relname FROM pg_stat_all_tables WHERE relname = 'store';

 n_live_tup | n_dead_tup | relname
------------+------------+---------
          1 |          1 | store
(1 row)

As expected, there is 1 live tuple (the updated row) and 1 dead tuple (the original row). Now let’s inspect the underlying page to see both tuples:

Let’s query the page to see the tuples again.

postgres=# SELECT lp, t_ctid, t_xmin, t_xmax FROM heap_page_items(get_raw_page('store', 0));
 lp | t_ctid | t_xmin | t_xmax
----+--------+--------+--------
  1 | (0,2)  |    755 |    756
  2 | (0,2)  |    756 |      0
(2 rows)

We now see two tuples as expected:

The first tuple has xmin = 755 (from the original insert) and xmax = 756, indicating that it was invalidated by transaction 756 (the update).
The second tuple is the new version created by the update, with xmin = 756 and xmax = 0 (indicating it’s still valid).
Notice that the t_ctid of the original tuple has changed from (0,1) to (0,2). This is because t_ctid points to the most recent version of the row. The format (page_number, line_pointer) means that the latest version of the row is on page 0, line pointer 2.

Heap Only Tuples (HOT)

This behaviour is part of a Postgres optimisation called Heap-Only Tuples (HOT), where on an UPDATE instead of immediately updating the index to point to the new tuple (which would be costly), Postgres updates the old tuple’s t_ctid to point to the new version. This creates a chain of tuples — the index leads to an old tuple and the old tuple points to the new one and so on.

Postgres relies on the vacuum process to clean up the dead tuple and update the index later.
You can read more about HOT in the official Postgres documentation:
👉 https://www.postgresql.org/docs/current/storage-hot.html

Vacuum Process

You may have noticed that vacuuming was disabled in the earlier example. This was intentional, as it allowed us to observe the dead tuples and the HOT chain, which would otherwise have been cleaned up by the vacuum process.

Vacuum plays a critical role in Postgres by permanently removing dead tuples, reclaiming storage, and updating indexes to eliminate HOT chains — all of which help improve read performance. It also helps prevent table bloat by freeing up storage space occupied by dead tuples.

Vacuum usually is configured to run automatically, but can also run manually. Let’s try vacuuming our table which contains 1 dead tuple.

postgres=# SELECT n_live_tup, n_dead_tup, relname FROM pg_stat_all_tables WHERE relname = 'store';

n_live_tup | n_dead_tup | relname
------------+------------+---------
          1 |          1 | store
(1 row)
--- We have 1 dead tuple
--- Manually vacuum 'store' table
postgres=# VACUUM store;
VACUUM
--- We endup with 0 dead tuples
postgres=# SELECT n_live_tup, n_dead_tup, relname FROM pg_stat_all_tables WHERE relname = 'store';
 n_live_tup | n_dead_tup | relname
------------+------------+---------
          1 |          0 | store
(1 row)
postgres=# SELECT lp, t_ctid, t_xmin, t_xmax FROM heap_page_items(get_raw_page('store', 0));
 lp | t_ctid | t_xmin | t_xmax
----+--------+--------+--------
  1 |        |        |
  2 | (0,2)  |    756 |      0
(2 rows)

We can see only one tuple when inspecting the page, located at lp 2, as the old tuple at lp 1 has been removed.

However, vacuuming a table comes with a cost. It consumes CPU and memory, which can create a performance bottleneck by using resources that could otherwise be dedicated to reads and writes. Additionally, the time required for vacuuming can vary from seconds to hours, depending on the volume of cleanup needed.

Vacuum is critical for Transaction Wraparound. You can read more about it here 👉 https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

Impact on Read Performance

MVCC affects not just writes but also read performance. When a query is executed, Postgres needs to determine which row version is valid, which adds some overhead:

1. Row Visibility Checks

Each time a row is read, Postgres checks its xmin and xmax values to determine if it’s the latest version. This adds some processing time, especially when there are multiple row versions due to frequent updates.

2. Table Bloat

When an update creates a new row version, the old version remains in the table until a VACUUM operation removes it. Over time, this increases table size and slows down reads, as Postgres may need to scan multiple row versions to find the valid one.

Frequent VACUUM and AUTOVACUUM help reduce bloat and improve read performance.

3. Updates vs Inserts in Write-Heavy Workloads

Read latencies can vary depending on whether you are updating existing rows or inserting new ones:

Inserts — Directly add a new row without affecting existing ones.
Updates — Create a new row version and mark the old one as dead, increasing table size and requiring more work during reads to find the latest version.

For updates, Postgres may need to adjust index pointers since a new tuple is created for each update. However, if the update qualifies for a HOT update — meaning the index does not need to be updated immediately, which improves efficiency.

However, HOT updates create chains of tuples in the heap, which increases the work for VACUUM. Postgres needs to follow the chain to find the latest valid version during reads, and once VACUUM removes old versions, the index may still need to be adjusted to reflect the latest state.

Frequent autovacuum helps clean up dead rows and maintain consistent performance. If you notice slower reads in a write-heavy workload, reducing update frequency or adjusting your data model might help.

This isn’t to suggest that you should avoid updates altogether. However, if you experience higher read latencies in a write-heavy workload, it’s worth considering whether high update frequency is contributing to the issue. Optimising your data model to reduce or avoid excessive updates could help mitigate this problem and improve overall performance.

For extremely write-heavy cases, a wide-column store like Cassandra or ScyllaDB, which are optimised for high write throughput, could provide better performance.

Impact on Storage space

Due to MVCC, Postgres consumes more disk space the more updates we do, and vacuum is responsible for reclaiming that space.

Let’s demonstrate this with an example, I am going to insert 1 million records and perform a few updates. We will also look at the storage space taken by the table at each step.

postgres=# truncate store;
TRUNCATE TABLE

--- Insert a Million records
postgres=# DO $$
BEGIN
    FOR i IN 1..1000000 LOOP
        INSERT INTO store (name, value)
        VALUES ('Name_' || i, i * 10);
    END LOOP;
END $$;
DO
postgres=# SELECT COUNT(*) FROM store;
  count
---------
 1000000
(1 row)

Let’s look at the space occupied by this table.

postgres=# SELECT
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM
    pg_catalog.pg_statio_user_tables
WHERE
    relname = 'store';

table_name | total_size
------------+------------
 store      | 71 MB
(1 row)

These 1 million records take up about 71 MB on disk. Let’s update all the rows and check the store space (we still have auto vacuum disabled).

postgres=# UPDATE store SET value=2000;
UPDATE 1000000

postgres=# SELECT
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM
    pg_catalog.pg_statio_user_tables
WHERE
    relname = 'store';
 table_name | total_size
------------+------------
 store      | 142 MB
(1 row)

As expected, the size of the table has doubled due to each row having 2 tuples (1 dead and 1 live) after the update statement.

Let’s manually vacuum the table and see if the storage space reduces.

postgres=# VACUUM store;
VACUUM

postgres=# SELECT
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM
    pg_catalog.pg_statio_user_tables
WHERE
    relname = 'store';

table_name | total_size
------------+------------
 store      | 142 MB
(1 row)

postgres=# SELECT n_live_tup, n_dead_tup, relname FROM pg_stat_all_tables WHERE relname = 'store';

n_live_tup | n_dead_tup | relname
------------+------------+---------
    1000000 |          0 | store
(1 row)

Interestingly, after vacuuming the table, the storage space remains at 142 MB, but the number of dead tuples has dropped to zero, confirming that vacuum successfully cleaned up the tuples.

This is the expected behaviour of vacuum — it doesn’t reduce the total storage space but instead marks the freed space as reusable. This means that any new data will be written into the existing storage space rather than increasing the overall disk usage.

However, in some cases, this might not be ideal. If you want to actually reclaim the disk space, you can run a VACUUM FULL operation:

postgres=# VACUUM FULL store;
VACUUM

postgres=# SELECT
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM
    pg_catalog.pg_statio_user_tables
WHERE
    relname = 'store';
 table_name | total_size
------------+------------
 store      | 71 MB
(1 row)

In this example, VACUUM FULL has reclaimed the physical disk space that was previously marked as reusable or unused, reducing the overall storage size.

Conclusion

We have covered how MVCC works in detail as well as the importance of the vacuum process for performance.

There are still a lot more things that I haven’t touched in this article, and I would encourage you to read about some of these topics (or unless I decide to write about them sometime in the future):

VACUUM ANALYSE operation
Reindex operation
Transaction wraparound
Tuning auto vacuum

Takeaway: MVCC allows Postgres to handle concurrent transactions efficiently but introduces trade-offs. Inserts typically lead to faster reads/performance than updates because updates create new row versions, which can lead to bloat unless properly managed by vacuuming. Understanding MVCC and HOT helps you fine-tune your database for better performance.

Internals of MVCC in Postgres: Hidden Costs of Updates vs Inserts

Set up

Overview of MVCC

Write operations

Heap Only Tuples (HOT)

Vacuum Process

Impact on Read Performance

Impact on Storage space

Conclusion

Resources

Internals of MVCC in Postgres: Hidden Costs of Updates vs Inserts

Set up

Overview of MVCC

Write operations

Heap Only Tuples (HOT)

Vacuum Process

Impact on Read Performance

Impact on Storage space

Conclusion

Resources

Share

Related Stories

Test Driven Generation — Use AI as a pair for programming.

Vuetified .Net MVC

A mobile money app powers digital wallets of multiple telecom service providers