Every UPDATE Leaves a Ghost: MVCC, Bloat, and VACUUM in PostgreSQL

Imagine running a big SELECT * on the order table while another process updates the same records concurrently. Your query completes displaying the original data, and the other sees successful data modification.

This is Multi-Version Concurrency Control (MVCC), a technique where the database creates new copies of rows instead of overwriting them. Using MVCC, transactions get a consistent and isolated snapshot of the data, while ongoing updates happen simultaneously. Readers never block writers and writers never block readers because multiple versions coexist and visibility rules determine which one each transaction can see.

There's a lot of complexity in how Postgres makes this work correctly. Here, we're gonna take a deep dive into how all this works in PostgreSQL. If database transactions are brand-new to you, you might find our Database Transactions article a helpful pre-requisite.

Let's dig in!

Transaction basics

Transactions rely on four guarantees known as ACID: atomicity, consistency, isolation, and durability.

Those guarantees are easier to guarantee when transactions run one at a time. But how do we run thousands of transactions in parallel while safely coordinating access to the same data?

MVCC

Early relational database management systems (RDBMS) like IBM System R solved this with read and write locks: hold a shared read lock to read, hold an exclusive write lock to modify. That kept data safe. It also meant readers blocked writers and writers blocked readers.

PostgreSQL uses Multi-Version Concurrency Control instead. This allows it to maintain more than one version of a row and let each transaction read the version its snapshot allows. Multiple tuple versions can coexist on disk. Snapshot Isolation (SI) decides which version a transaction sees when it runs a query.

PostgreSQL isn't the only database that uses MVCC, and each implements it in a different way. To understand how Postgres does, we must first understand how it stores rows in tuples.

Tuples and visibility

A tuple is a single physical version of a row on disk. One logical row can exist as several tuples at once when PostgreSQL keeps older versions around for MVCC.

For each tuple, PostgreSQL tracks which transaction created it and which transaction deleted it. Each transaction is identified by an unsigned 32-bit integer value called the transaction id (XID). When a tuple is updated, the old version is marked as deleted and a new tuple is created.

For example:

postgres=> CREATE TABLE mytable(id SERIAL PRIMARY KEY, name VARCHAR NOT NULL);
CREATE TABLE
postgres=> INSERT INTO mytable (name) VALUES('First tuple');
INSERT 0 1

postgres=> SELECT * FROM mytable;
 id |     name
----+--------------
  1 | First tuple
(1 row)

We created a table and inserted one row. How does PostgreSQL know which transaction wrote it and which deleted it? Every row carries hidden system columns. xmin records the inserting transaction; xmax records the deleting one. To inspect these system columns, add them to any SELECT:

postgres=> SELECT xmin, xmax, * FROM mytable;
 xmin  | xmax | id |     name
-------+------+----+--------------
 22019 |    0 |  1 | First tuple
(1 row)

The tuple was created by the transaction with the ID 22019. PostgreSQL implicitly creates a transaction for each insert, even when we do not start one explicitly. When using an explicit transaction with BEGIN, the current transaction id can be requested (and assigned, if the transaction doesn't have one yet) by calling the PostgreSQL function pg_current_xact_id().

postgres=> BEGIN;
BEGIN
postgres=*> INSERT INTO mytable (name) VALUES('Second tuple');
INSERT 0 1
postgres=*> SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
              22020
(1 row)

postgres=*> COMMIT;
COMMIT

postgres=> SELECT xmin, xmax, * FROM mytable;
 xmin  | xmax | id |     name
-------+------+----+--------------
 22019 |    0 |  1 | First tuple
 22020 |    0 |  2 | Second tuple
(2 rows)

The transaction that inserts the second tuple has the ID 22020, which is also reflected by the xmin value of this tuple in the table.

This is how PostgreSQL implements MVCC. It tracks which transaction created or deleted a particular row version using these xmin and xmax columns.

Snapshots

A single logical row can have several physical versions on disk. The question is which one your transaction sees, especially when another session commits a change mid-scan.

Snapshot isolation determines data visibility for the current transaction. SQL isolation levels define how much data modified by concurrent transactions is visible to our own.

For example, by executing SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;, we tell PostgreSQL that subsequent reads in this transaction must always return the same data, even if another transaction modifies it in the meantime. PostgreSQL implements this by capturing a snapshot of the system's active transactions the moment the first query in our transaction runs. It uses this snapshot to filter row visibility, ensuring a static view of the data until the transaction ends.

pg_current_snapshot() returns current snapshot information as a string with three colon-separated values: $xmin:$xmax:$xip_list.

• xmin: The earliest transaction ID that is still active. All transactions before this ID have already completed, so their status is settled: committed ones are visible, aborted ones are not.
• xmax: One past the highest transaction ID that had completed when the snapshot was taken. All transaction IDs greater than or equal to xmax had not yet completed (or started) at snapshot time and are therefore invisible.
• xip_list: A comma-separated list of active transactions when the snapshot was taken. These transactions are considered in-progress and their effects are invisible to this snapshot, even if they commit later.

For example, the snapshot 1000:1012:1004,1005,1009 means that all transactions with an id lower than 1000 have already completed (committed or aborted). All transactions with an id equal to or greater than 1012 had not yet completed when the snapshot was taken. The transactions 1004, 1005, and 1009 were still in progress.

The function pg_visible_in_snapshot() can be used to test if a transaction ID is visible in a particular snapshot or not. For example:

-- xid is < xmin -> Visible
postgres=> SELECT pg_visible_in_snapshot('999'::xid8, '1000:1012:1004,1005,1009'::pg_snapshot);
 pg_visible_in_snapshot
------------------------
 t
(1 row)

-- xid is between xmin and xmax but in xip list -> Invisible
postgres=> SELECT pg_visible_in_snapshot('1004'::xid8, '1000:1012:1004,1005,1009'::pg_snapshot);
 pg_visible_in_snapshot
------------------------
 f
(1 row)

-- xid is between xmin and xmax but not in xip list -> Visible
postgres=> SELECT pg_visible_in_snapshot('1006'::xid8, '1000:1012:1004,1005,1009'::pg_snapshot);
 pg_visible_in_snapshot
------------------------
 t
(1 row)

-- xid >= xmax -> Invisible
postgres=> SELECT pg_visible_in_snapshot('1012'::xid8, '1000:1012:1004,1005,1009'::pg_snapshot);
 pg_visible_in_snapshot
------------------------
 f
(1 row)

In the following example, Session 1 starts under REPEATABLE READ, grabs its transaction id and snapshot, and scans mytable:

-- Session 1
postgres=> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN

postgres=*> SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
              22067
(1 row)

postgres=*> SELECT pg_current_snapshot();
 pg_current_snapshot
---------------------
 22067:22067:
(1 row)

postgres=*> SELECT xmin, xmax, * FROM mytable;
 xmin  | xmax  | id |     name
-------+-------+----+--------------
 22019 |     0 |  1 | First tuple
 22020 |     0 |  2 | Second tuple
(2 rows)

The output of pg_current_snapshot() shows that our transaction ID (22067) is the oldest active boundary in the system, and the active transaction list (xip_list) is completely empty. Transaction IDs come from a server-wide counter that advances with all activity on the server (other sessions, autovacuum, and background work).

Don't confuse snapshot xmin with tuple xmin. The snapshot fields describe which transactions were active when the snapshot was taken, the tuple columns record which transaction created or deleted that specific row version.

Next, we open a parallel connection in Session 2, perform an update on row 2, and immediately commit the change:

-- Session 2
postgres=> BEGIN;
BEGIN
postgres=*> UPDATE mytable SET name = 'Second tuple updated' WHERE id = 2;
UPDATE 1
postgres=*> SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
              22069
(1 row)

postgres=*> COMMIT;
COMMIT

postgres=> SELECT xmin, xmax, * FROM mytable;
 xmin  | xmax  | id |         name
-------+-------+----+----------------------
 22019 |     0 |  1 | First tuple
 22069 |     0 |  2 | Second tuple updated
(2 rows)

Back in Session 1, run the same table scan again and the changes made by transaction 22069 are not shown. Session 1 continues to see the original tuple text:

-- Session 1
postgres=*> SELECT xmin, xmax, * FROM mytable;
 xmin  | xmax  | id |     name
-------+-------+----+--------------
 22019 |     0 |  1 | First tuple
 22020 | 22069 |  2 | Second tuple
(2 rows)

However, the xmax value for row 2 has changed to 22069. Because an update in PostgreSQL is physically a DELETE followed by an INSERT, Session 2 wrote its transaction ID into the xmax slot of the old row to mark it as deleted.

Session 1 reads the xmax value, but transaction 22069 committed after Session 1's snapshot was taken (22067:22067:), so the MVCC visibility rules treat the deletion as invisible and return the original row.

Subtransactions

Subtransactions in PostgreSQL are transactions embedded inside an existing parent transaction. They are created using the SAVEPOINT command and are typically used to isolate errors within a complex workflow. If an operation fails inside a subtransaction, only the modifications made up to that last SAVEPOINT are rolled back, allowing the main parent transaction to continue.

Subtransaction IDs

Just like top-level transactions, each subtransaction is assigned a unique transaction ID from the same global counter sequence. Consequently, when a row is inserted inside a subtransaction block, its xmin value registers the ID of the subtransaction rather than the parent transaction.

After the snapshot demo above, reset the table so the row ids and transaction ids line up cleanly. Close any still-open snapshot session first (COMMIT or ROLLBACK in Session 1), or TRUNCATE will sit there forever waiting for the lock.

postgres=> TRUNCATE mytable RESTART IDENTITY;
TRUNCATE TABLE
postgres=> INSERT INTO mytable (name) VALUES ('First tuple'), ('Second tuple');
INSERT 0 2

Now start a parent transaction, insert one row before a savepoint and one after:

postgres=> BEGIN;
BEGIN
postgres=*> SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
              22071
(1 row)

postgres=*> INSERT INTO mytable (name) VALUES ('Third tuple');
INSERT 0 1
postgres=*> SAVEPOINT s1;
SAVEPOINT
postgres=*> INSERT INTO mytable (name) VALUES ('Fourth tuple');
INSERT 0 1
postgres=*> SELECT xmin, xmax, * FROM mytable;
 xmin  | xmax | id |      name
-------+------+----+---------------
 22070 |    0 |  1 | First tuple
 22070 |    0 |  2 | Second tuple
 22071 |    0 |  3 | Third tuple
 22072 |    0 |  4 | Fourth tuple
(4 rows)

The two seed rows share xmin 22070 because they were inserted in one auto-committed statement. The third tuple was written before SAVEPOINT s1, so its xmin is the parent transaction id 22071. The fourth tuple was written after the savepoint, so its xmin is 22072, the subtransaction id. pg_current_xact_id() reports the parent id (22071) even after the savepoint; the tuple headers record which subtransaction actually wrote each row.

Rolling back to the savepoint removes only the subtransaction work:

postgres=*> ROLLBACK TO s1;
ROLLBACK
postgres=*> SELECT id, name FROM mytable;
 id |      name
----+---------------
  1 | First tuple
  2 | Second tuple
  3 | Third tuple
(3 rows)

postgres=*> COMMIT;
COMMIT

Row 4 disappears and row 3 remains. Only when the parent transaction commits do these tuples become visible to other sessions.

Exception handling with subtransactions

Subtransactions also implement exception handling in PL/pgSQL procedures (PostgreSQL's built-in procedural language).

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100)
);

CREATE OR REPLACE PROCEDURE insert_users() LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');

    BEGIN
        -- This will fail because 'alice' violates the UNIQUE constraint
        INSERT INTO users (username, email) VALUES ('alice', 'duplicate_alice@example.com');

    EXCEPTION
        WHEN unique_violation THEN
            RAISE NOTICE 'Caught a unique constraint violation! Skipping this user.';
    END;

    -- This insert succeeds because the main transaction was not aborted
    INSERT INTO users (username, email) VALUES ('bob', 'bob@example.com');

END;
$$;

When the insert_users procedure is called, the constraint violation is caught properly and only this modification is rolled back while all other modifications are applied.

postgres=> CALL insert_users();
NOTICE:  Caught a unique constraint violation! Skipping this user.
CALL

postgres=> SELECT * from users;
 user_id | username |       email
---------+----------+-------------------
       1 | alice    | alice@example.com
       3 | bob      | bob@example.com
(2 rows)

Since exception handling uses subtransactions automatically, many workloads rely on subtransactions without using them explicitly.

Command IDs

XIDs tell you which transaction wrote a row. They cannot tell you which statement inside that transaction wrote it. If you run UPDATE on a set of rows, the statement could potentially rewrite a row, scan ahead, encounter that same rewritten row, and update it again. Without a finer-grained marker, the statement would chase its own tail forever. Database people call this the Halloween problem, named for the day it was discovered.

To solve this, PostgreSQL uses an incrementing Command ID inside each transaction, starting at 0 and ticking up with every data-modifying SQL statement. When a statement creates a tuple, it stamps it with the current Command ID (shown in the system column cmin). This counter ensures a single running query never reads its own ongoing changes. For example:

postgres=> TRUNCATE mytable RESTART IDENTITY;
TRUNCATE TABLE

postgres=> INSERT INTO mytable (name) VALUES('First tuple');
INSERT 0 1

postgres=> BEGIN;
BEGIN

postgres=*> SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
            2995013
(1 row)

postgres=*> SELECT cmin, xmin, xmax, * FROM mytable;
 cmin |  xmin   | xmax | id |    name
------+---------+------+----+-------------
    0 | 2995012 |    0 |  1 | First tuple
(1 row)

postgres=*> UPDATE mytable SET name = 'Updated first tuple' WHERE id = 1;
UPDATE 1
postgres=*> SELECT cmin, xmin, xmax, * FROM mytable;
 cmin |  xmin   | xmax | id |        name
------+---------+------+----+---------------------
    0 | 2995013 |    0 |  1 | Updated first tuple
(1 row)

postgres=*> UPDATE mytable SET name = 'Two times updated first tuple' WHERE id = 1;
UPDATE 1
postgres=*> SELECT cmin, xmin, xmax, * FROM mytable;
 cmin |  xmin   | xmax | id |             name
------+---------+------+----+-------------------------------
    1 | 2995013 |    0 |  1 | Two times updated first tuple
(1 row)

postgres=*> COMMIT;
COMMIT

The transaction IDs here are larger than the earlier ones because this example was run later, after many more transactions. In the example, transaction XID 2995013 runs multiple updates on a single row in mytable:

• First update: The original row's xmax is set to 2995013 to mark this old tuple as deleted by the current transaction and a new tuple is created. This new tuple has an xmin of 2995013 and a cmin of 0, signaling it was created by the transaction's first data-modifying operation.
• Second update: The transaction updates the same tuple again. So, the last updated tuple is deleted by setting the xmax value and a new tuple is created. The xmin of the new tuple is 2995013 as well because it belongs to the same transaction. However, its cmin value changes to 1. This increment indicates that the tuple was created by the transaction's second data-modifying operation, ensuring correct statement-level visibility.

Table maintenance

As we've seen, UPDATE leaves an old tuple versions behind on disk. When no running transaction needs them anymore, PostgreSQL can clean them up.

You can do this manually by running VACUUM, and PostgreSQL performs autovacuum from time to time. When cleanup falls behind, tables and indexes get bloated (using more disk) and sequential scans slow down. High-churn tables like job queues are especially vulnerable. See Keeping a Postgres queue healthy for what that looks like.

To schedule autovacuum, PostgreSQL needs to know how many dead tuples are in a table. This is tracked in the table statistics. Run:

postgres=> SELECT relname, n_live_tup, n_dead_tup
           FROM pg_stat_user_tables
           WHERE relname = 'mytable';
 relname | n_live_tup | n_dead_tup
---------+------------+------------
 mytable |          1 |          2
(1 row)

After two updates on one row, one live tuple remains and two dead versions sit in the statistics.

VACUUM removes dead tuples and marks their space as reusable within the table. Dead tuples are row versions that no active transaction or snapshot still needs.

Page layout

PostgreSQL tables use fixed 8-kilobyte (8192 bytes) pages. Multiple tuples are stored inside each page using a specific layout:

• Line Pointers (lp): These are placed at fixed array positions starting from the beginning of the page and growing downward.
• Tuple Data: The actual row data is placed at the very end of the page and grows upward.
• The Offset (lp_off): Each line pointer contains a byte offset pointing directly to the exact physical byte position where its corresponding tuple data begins.

Examining the page

The pageinspect extension lets us inspect the details of a Postgres page. Its functions require superuser privileges, so the following runs in a superuser session (note the # prompt instead of >). get_raw_page plus heap_page_items returns line pointers, byte offsets, flags, and each tuple's xmin/xmax. Below we decode page 0 of mytable. Flag 1 means the line pointer is in use (LP_NORMAL).

postgres=# CREATE EXTENSION pageinspect;
CREATE EXTENSION

postgres=# SELECT lp, lp_off, lp_flags, t_xmin, t_xmax FROM heap_page_items(get_raw_page('mytable', 0));
 lp | lp_off | lp_flags | t_xmin  | t_xmax
----+--------+----------+---------+---------
  1 |   8152 |        1 | 2995012 | 2995013
  2 |   8104 |        1 | 2995013 | 2995013
  3 |   8040 |        1 | 2995013 |       0
(3 rows)

The first page of the table holds three tuples, and the xmin and xmax values match the tuple versions we produced in the update from earlier.

Standard VACUUM

To reclaim the space, run VACUUM.

postgres=# VACUUM mytable;
VACUUM

postgres=# SELECT lp, lp_off, lp_flags, t_xmin, t_xmax FROM heap_page_items(get_raw_page('mytable', 0));
 lp | lp_off | lp_flags | t_xmin  | t_xmax
----+--------+----------+---------+--------
  1 |      3 |        2 |         |
  2 |      0 |        0 |         |
  3 |   8128 |        1 | 2995013 |      0
(3 rows)

After VACUUM is done, the two dead tuples are removed. Three things changed in the page structure:

• LP 3 (Live Tuple): The byte offset changed from 8040 to 8128. Because the data for the two dead rows was wiped out, VACUUM defragmented the page by shifting the remaining live data down to the bottom boundary.
• LP 2 (Unused Pointer): The lp_flags value changes to 0 (LP_UNUSED), indicating that this line pointer is currently unused.
• LP 1 (Redirect Pointer): The first tuple version was also cleaned up, but something different happened here than for LP 2. The lp_flags value is set to 2 (LP_REDIRECT) and the offset points to line pointer 3. The updates in the previous section formed a HOT (Heap-Only Tuple) chain because they stayed on the same page and did not change indexed columns. After VACUUM pruned the dead versions, LP 1 became an LP_REDIRECT to LP 3 so the index on id still reaches the live tuple without being rewritten.

VACUUM FULL

VACUUM FULL is for rebuilding tables and indexes completely. VACUUM FULL packs the data sequentially, eliminates all pointer shortcuts, and shrinks the file size. This is a resource-intensive operation that puts an ACCESS EXCLUSIVE lock on the table.

postgres=# VACUUM FULL mytable;
VACUUM
postgres=# SELECT lp, lp_off, lp_flags, t_xmin, t_xmax FROM heap_page_items(get_raw_page('mytable', 0));
 lp | lp_off | lp_flags | t_xmin  | t_xmax
----+--------+----------+---------+--------
  1 |   8128 |        1 | 2995013 |      0
(1 row)

After VACUUM FULL is done, the only stored tuple sits directly under line pointer 1.

The transaction horizon

The cleanup above worked because no other session needed the dead tuples. In practice, that is not always true.

PostgreSQL tracks the oldest transaction that might still need an old row version. That cutoff is the transaction horizon. VACUUM can only remove tuples that became dead before the horizon.

The demo below uses a throwaway horizon_demo table so it does not disturb the mytable state from the walkthrough above. Session 1 starts under REPEATABLE READ, reads the rows, and stays idle:

-- Session 1
postgres=> CREATE TABLE horizon_demo (id SERIAL PRIMARY KEY, name VARCHAR NOT NULL);
CREATE TABLE
postgres=> INSERT INTO horizon_demo (name) VALUES ('alpha'), ('beta'), ('gamma');
INSERT 0 3
postgres=> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
postgres=*> SELECT id, name FROM horizon_demo ORDER BY id;
 id | name  
----+-------
  1 | alpha
  2 | beta
  3 | gamma
(3 rows)

Next, open a parallel connection in Session 2. Update every row, refresh the table statistics, and run VACUUM:

-- Session 2
postgres=> UPDATE horizon_demo SET name = name || '-updated';
UPDATE 3
postgres=> ANALYZE horizon_demo;
ANALYZE
postgres=> SELECT relname, n_live_tup, n_dead_tup
           FROM pg_stat_user_tables
           WHERE relname = 'horizon_demo';
    relname    | n_live_tup | n_dead_tup 
---------------+------------+------------
 horizon_demo  |          3 |          3
(1 row)

postgres=> VACUUM horizon_demo;
VACUUM
postgres=> SELECT relname, n_live_tup, n_dead_tup
           FROM pg_stat_user_tables
           WHERE relname = 'horizon_demo';
    relname    | n_live_tup | n_dead_tup 
---------------+------------+------------
 horizon_demo  |          3 |          3
(1 row)

VACUUM ran, but n_dead_tup did not drop. Session 1's open snapshot still needs the pre-update row versions.

If we return to Session 1 and commit:

-- Session 1
postgres=*> COMMIT;
COMMIT

Session 2 can finish cleanup:

-- Session 2
postgres=> VACUUM ANALYZE horizon_demo;
VACUUM
postgres=> SELECT relname, n_live_tup, n_dead_tup
           FROM pg_stat_user_tables
           WHERE relname = 'horizon_demo';
    relname    | n_live_tup | n_dead_tup 
---------------+------------+------------
 horizon_demo  |          3 |          0
(1 row)

When n_dead_tup climbs but autovacuum looks healthy, find the session holding the horizon open.

SELECT pid, usename, backend_xid, backend_xmin,
       age(backend_xid)  AS xid_age,
       age(backend_xmin) AS xmin_age,
       xact_start,
       age(clock_timestamp(), xact_start) AS transaction_duration,
       state, query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL OR backend_xid IS NOT NULL
ORDER BY GREATEST(age(backend_xid), age(backend_xmin)) DESC
LIMIT 5;

The culprit is whoever holds the oldest backend_xmin (a taken snapshot) or backend_xid (an assigned transaction ID). This is not always the transaction that has been open the longest.

Wrapup

MVCC is why PostgreSQL can serve heavy read traffic and write traffic simultaneously. This gains us great performance in the short term, but means we now have deferred maintenance to manage (dead tuples and vacuum). Now you have an in-depth look at how all this works.