An undocumented Postgres serialization error in Read Committed almost caused us an incident

We partitioned a large config table by active/archived status to speed up a recurring update job. The first time the job ran against the partitioned table, it started failing with tuple to be locked was already moved to another partition due to concurrent update — a genuine Postgres serialization error (SQLSTATE 40001), thrown from a plain SELECT ... FOR UPDATE running in the default Read Committed isolation level. Postgres's documentation does describe serialization failures caused by cross-partition row movement, but only for UPDATE and DELETE. It says nothing about SELECT FOR UPDATE, SELECT FOR SHARE, or MERGE, even though they hit the exact same code path. We reproduce the failure, explain the mechanism (EvalPlanQual and the t_ctid "moved partitions" marker), show that retrying is the only supported fix, and share what happened when we patched Postgres to skip the error unconditionally just to see what it would cost us in the general case.

1. What happened
2. Reproducing it: a minimal proof of concept
  2.1 Setup
  2.2 Without `SKIP LOCKED`
    2.2.1 What just happened behind the scenes?
      2.2.1.1 How `t_ctid` Maintains the Version Chain
      2.2.1.2 Why Cross-Partition Movement Breaks the Chain
  2.3 With `SKIP LOCKED`
3. Is this the best postgres can do?
  3.1 The experiment set up
  3.2 The results for unpatched postgres
  3.3 The results for patched postgres
4. Other possible solutions
5. Conclusion

---

1. What happened

We have a configuration table with several hundred million rows. At any given time, around 15 million of those configs are "active" — the ones actively read and served — and the rest are "archived": historical versions we keep around for audits and rollbacks, but that are almost never touched.

Periodically, we need to roll a batch of active configs forward: select the current version, archive it, insert a new version based on it, and notify a handful of downstream systems that a config changed. We do this with eight parallel jobs, each "claiming" a batch of rows (SELECT FOR UPDATE SKIP LOCKED), and the full run normally takes about an hour.

Because only ~15 million of several hundred million rows are ever actually "hot," we partitioned the table by status — one partition for active, one for archived — so the working set the jobs touch stays small and index-resident, instead of being spread across a table two orders of magnitude larger than it needs to be.

The first time we ran the update job against the newly partitioned table, it went wrong almost immediately. Roughly 12,000 configs got updated — a tiny fraction of the batch — and then, one by one, all eight jobs died with:

ERROR: tuple to be locked was already moved 
to another partition due to concurrent update

We re-ran the job. Same result: a small number of rows processed, then the same error across all workers. At that point it was clear this wasn't a fluke — something about partitioning the table had changed the job's behavior under concurrency in a way we hadn't anticipated.

We were lucky we had a few hours to figure out what was going on, and it did not result in an incident.

2. Reproducing it: a minimal proof of concept

The failure is small enough to reproduce with two Postgres sessions and a two-partition table. Below is the setup and interleaving.

2.1 Setup

CREATE TABLE t
(
    id           int     NOT NULL,
    is_processed boolean NOT NULL
)
    PARTITION BY LIST (is_processed);

CREATE TABLE t_processed PARTITION OF t
    FOR VALUES IN (TRUE);

CREATE TABLE t_unprocessed PARTITION OF t
    FOR VALUES IN (FALSE);

INSERT INTO t
VALUES (1, FALSE);

2.2 Without SKIP LOCKED

Showing the interleaving of the two sessions is much easier without SKIP LOCKED, and I'll go for it first.

Open session 1 and run (don't commit):

BEGIN;

UPDATE t
SET is_processed = TRUE
WHERE id = 1;

Open session 2 and run:

SELECT *
FROM t
WHERE id = 1
    FOR UPDATE;

It will block as session 1 has the row locked. If you COMMIT session 1, you'll see session 2 raises the error:

ERROR:  tuple to be locked was already moved to another partition due to concurrent update

Searching the Postgres source code for the above error message, you'll find the exact line in the heapam_handler.c:

if (ItemPointerIndicatesMovedPartitions(tid))
  ereport(ERROR,
    (errcode(ERRCODE_T_R_SERIALIZATION_FAILURE),
     errmsg("tuple to be locked was already moved to another partition due to concurrent update")));

Curiously, the error code is ERRCODE_T_R_SERIALIZATION_FAILURE, which is defined as serialization_failure (SQLSTATE 40001), per errcodes.txt. Also, per postgres docs:

Both Repeatable Read and Serializable isolation levels can produce errors that are designed to prevent serialization anomalies. As previously stated, applications using these levels must be prepared to retry transactions that fail due to serialization errors. Such an error's message text will vary according to the precise circumstances, but it will always have the SQLSTATE code 40001 (serialization_failure).

It doesn't say anything about Read Committed, yet we clearly see it happened in Read Committed. As a side note, if SELECT FOR UPDATE was executed under REPEATABLE READ/SERIALIZABLE isolation level, the raised error message would be different:

ERROR:  could not serialize access due to concurrent update

2.2.1 What just happened behind the scenes?

Let's first look at the query plan:

EXPLAIN (COSTS OFF)
SELECT *
FROM t
WHERE id = 1
    FOR UPDATE;

                QUERY PLAN
-------------------------------------------
 LockRows
   ->  Append
         ->  Seq Scan on t_unprocessed t_1
               Filter: (id = 1)
         ->  Seq Scan on t_processed t_2
               Filter: (id = 1)

We see that SELECT FOR UPDATE is not an atomic operation: The LockRows node fetches the data from the two partitions, and then attempts to lock them. If the row is already locked by another session, it will block.

But the important part is what happens after the lock is released: The LockRows cannot simply lock what was fetched before, since the data may have changed. Instead, it must re-check the row's current state against the query's WHERE clause. This re-check is what Postgres calls EvalPlanQual (EPQ).

In PostgreSQL, EvalPlanQual (EPQ) is an internal re-evaluation mechanism triggered when a concurrent transaction updates or deletes a tuple that a target UPDATE, DELETE, MERGE, or SELECT FOR UPDATE/SHARE query is currently attempting to modify under the READ COMMITTED isolation level. Once the blocking transaction commits, the current query unblocks, re-fetches the latest committed version of the tuple (via its tuple chain), and re-executes the original query plan tree specifically for that row to verify if it still satisfies the plan's WHERE clause, join conditions, or MERGE branch qualifiers. If the updated tuple passes the re-evaluation, the operation proceeds on the new tuple version — potentially adapting actions in a MERGE clause — and if it no longer matches, the updated row is skipped. This mechanism prevents loss of updates and phantom updates while preserving snapshot-based concurrency without aborting the entire statement.

From the above, the following part is our concern:

re-fetches the latest committed version of the tuple (via its tuple chain)

To understand this, we need to understand how Postgres updates tuples.

When an UPDATE occurs in PostgreSQL, the engine does not modify a tuple in place. Instead, it marks the existing tuple as dead and inserts a new version of the tuple in a new slot (which may be on a different page entirely).

Every tuple (row) stores low-level transaction visibility and structural metadata in its tuple header (HeapTupleHeaderData):

• xmin: The Transaction ID (XID) that created this tuple version.
• xmax: The XID that deleted or updated this tuple version (set to 0 for a live, un-updated tuple).
• t_ctid: A physical item pointer — (page_number, tuple_offset) — that specifies the physical storage location of a tuple.

2.2.1.1 How t_ctid Maintains the Version Chain

Normally, a tuple's t_ctid points directly to itself. However, when an UPDATE takes place:

1. PostgreSQL creates a new tuple version (Tuple B) at a new location, say (0, 2). Tuple B's t_ctid points to itself, (0, 2).
2. PostgreSQL modifies the header of the old tuple (Tuple A) located at (0, 1):

• Sets xmax to the current transaction ID.
• Updates t_ctid from pointing to itself (0, 1) to pointing directly to the new tuple's physical address (0, 2).

[Tuple A @ (0,1)]              [Tuple B @ (0,2)]
+--------------------+        +-----------------+
| xmin: 100          |        | xmin: 101       |
| xmax: 101          |        | xmax: 0         |
| t_ctid: (0,2)  ----+------> | t_ctid: (0,2)   |
+--------------------+        +-----------------+

When a concurrent READ COMMITTED transaction encounters Tuple A, blocks, and then wakes up after Transaction 101 commits, EvalPlanQual uses this pointer chain. It inspects Tuple A's header, sees that xmax committed and that t_ctid points to (0, 2), and follows t_ctid to fetch Tuple B (the latest committed version) for re-evaluation.

2.2.1.2 Why Cross-Partition Movement Breaks the Chain

If an UPDATE alters a partition key such that the row must move from Partition Table A to Partition Table B, PostgreSQL cannot perform a standard single-table heap update:

1. Delete + Insert Execution: PostgreSQL converts the operation into an internal DELETE on Partition Table A followed by an INSERT into Partition Table B.
2. Loss of Pointer Connection: A tuple’s t_ctid can only point to an item pointer within the same underlying table (heap relation). It cannot reference a physical page location in a completely different table file/relation. In this case, Postgres uses some magic block and offset numbers to represent the fact that the tuple moved to a different partition:

#define MovedPartitionsBlockNumber  InvalidBlockNumber // 0xFFFFFFFF
#define MovedPartitionsOffsetNumber 0xfffd

1. Chain Disruption: Because t_ctid on the deleted row in Partition A cannot point across relations to the new row in Partition B, the tuple chain is broken.

When a concurrent transaction unblocks and attempts EvalPlanQual on the old row, it sees that the row was deleted (xmax set) but cannot follow t_ctid to find the replacement row. (This is detected by ItemPointerIndicatesMovedPartitions(tid), as pointed out above). As a result, EPQ fails to re-evaluate the updated row and errs with serialization failure.

2.3 With SKIP LOCKED

Our real jobs use SELECT ... FOR UPDATE SKIP LOCKED, since multiple workers pull from the same batch concurrently and shouldn't block on each other's in-flight rows. One may think that SKIP LOCKED would prevent this, yet it doesn't. Under high concurrency, Postgres will still raise the error.

First, notice that the query plan is the same as above:

EXPLAIN (COSTS OFF)
SELECT *
FROM t
WHERE id = 1
    FOR UPDATE
        SKIP LOCKED;

Plan:

                QUERY PLAN
-------------------------------------------
 LockRows
   ->  Append
         ->  Seq Scan on t_unprocessed t_1
               Filter: (id = 1)
         ->  Seq Scan on t_processed t_2
               Filter: (id = 1)

That is, the LockRows node only differs in that it won't block on locked rows. However, if the row changes after it is fetched but before it is re-evaluated by EPQ, the same error will be raised. To simulate this without creating a highly-concurrent workload, we can use pg_sleep to artificially delay the row.

Open session 1 and run without commit (note that I'm setting is_processed to FALSE, as we had set it to TRUE in the previous section):

BEGIN;

UPDATE t
SET is_processed = FALSE
WHERE id = 1;

Open session 2 and run:

SELECT *, pg_sleep(10)
FROM t
WHERE id = 1
    FOR UPDATE
        SKIP LOCKED;

• If you don't touch session 1, then session 2 will wait for 10 seconds and then returns 0 rows.
• If you commit session 1 before 10 seconds, then session 2 will raise the serialization error after pg_sleep wakes up:

ERROR:  tuple to be locked was already moved to another partition due to concurrent update

3. Is this the best postgres can do?

Let's dig into the Postgres source code to see what's going on:

git log -S 'ItemPointerIndicatesMovedPartitions' --oneline -- '*.c'

Result:

4b760a181ab Remove faulty Assert in partitioned INSERT...ON CONFLICT DO UPDATE.
955f5506863 Fix bug in following update chain when locking a heap tuple
9758174e2e5 Log the conflicts while applying changes in logical replication.
7103ebb7aae Add support for MERGE SQL command
5db6df0c011 tableam: Add tuple_{insert, delete, update, lock} and use.
f16241bef7c Raise error when affecting tuple moved into different partition.

Commit f16241bef7c (April 2018) introduced the serialization failure error:

Raise error when affecting tuple moved into different partition.

When an update moves a row between partitions (supported since 2f178441044b), our normal logic for following update chains in READ COMMITTED mode doesn't work anymore. Cross partition updates are modeled as an delete from the old and insert into the new partition. No ctid chain exists across partitions, and there's no convenient space to introduce that link.

Not throwing an error in a partitioned context when one would have been thrown without partitioning is obviously problematic. This commit introduces infrastructure to detect when a tuple has been moved, not just plainly deleted. That allows to throw an error when encountering a deletion that's actually a move, while attempting to following a ctid chain.

The row deleted as part of a cross partition update is marked by pointing it's t_ctid to an invalid block, instead of self as a normal update would. That was deemed to be the least invasive and most future proof way to represent the knowledge, given how few infomask bits are there to be recycled (there's also some locking issues with using infomask bits).

External code following ctid chains should be updated to check for moved tuples. The most likely consequence of not doing so is a missed error.

Can we find a case where not throwing an error is not a problem? In fact, we do! In our case, explained in the beginning of the article, we always target one partition. So, our SELECT FOR UPDATE was filtering by partition key; something like this:

SELECT *
FROM t
WHERE (not is_processed)
ORDER BY id
LIMIT 100 FOR UPDATE
    SKIP LOCKED;

If the row was moved to another partition, then obviously the WHERE clause would not match, and EPQ could skip the row rather than raise the error. However, that may have considerably complicated the EPQ logic, because it would now have to make sure that the filter condition matches the partition key. But let's say we simply assume this is the case, and patch the Postgres source code to skip the error. What is the performance impact?

3.1 The experiment set up

I'm using the latest master branch of Postgres, which is at commit 3d00537feb5 as of this writing. I compiled it on a MacBook Air M3 with 24 GB of RAM. No specific configuration changes were made to compilation settings or GUCs.

To benchmark the performance, I wrote a script (available here) which does the following: Creates a partitioned table as above, fills it with 100K rows, and then runs N concurrent jobs, each of which locks 100 rows and updates their is_processed flag to TRUE. If a serialization failure occurred, or if there are more unprocessed rows, the jobs retry.

The script assumes postgres connection parameters are set in the environment. Example usage for eight concurrent jobs (YMMV depending on your machine's performance and postgres configuration / version):

./postgres-cross-partition-update.sh 8
job 1: done, iterations=126, errors_ignored=18
job 6: done, iterations=124, errors_ignored=24
job 4: done, iterations=124, errors_ignored=22
job 5: done, iterations=127, errors_ignored=18
job 3: done, iterations=124, errors_ignored=23
job 8: done, iterations=130, errors_ignored=13
job 2: done, iterations=125, errors_ignored=23
job 7: done, iterations=128, errors_ignored=17
----
all 8 jobs finished in 7.931638000s

3.2 The results for unpatched postgres

Here are the results for the unpatched version of Postgres:

# of Jobs	Total Errors	Total Time
1	0	27.2
2	0	14.4
3	0	10.3
4	0	8.9
5	3	8.2
6	5	8.2
7	37	7.5
8	109	7.7
9	169	8.0
10	182	8.1

Clearly, more errors were raised with higher concurrency. Also, with 7 or 8 jobs, the total time was optimal.

3.3 The results for patched postgres

Patching is simple: Just return TM_Deleted instead of raising the serialization failure error:

diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index bf87430cf01..dae42af23b7 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -313,9 +313,7 @@ tuple_lock_retry:
            for (;;)
            {
                if (ItemPointerIndicatesMovedPartitions(tid))
-                   ereport(ERROR,
-                           (errcode(ERRCODE_T_R_SERIALIZATION_FAILURE),
-                            errmsg("tuple to be locked was already moved to another partition due to concurrent update")));
+                   return TM_Deleted;

                tuple->t_self = *tid;
                if (heap_fetch(relation, &SnapshotDirty, tuple, &buffer, true))

Notice this is not always correct; as we explained above, it is only correct if the WHERE clause matches the partition key. However, we simply want to measure the maximum performance impact of this patch.

Compiling the patched version of Postgres, and running the benchmark again, we get the following results:

# of Jobs	Total Errors	Total Time
1	0	27.0
2	0	14.4
3	0	10.2
4	0	8.9
5	0	8.2
6	0	7.5
7	0	7.3
8	0	7.2
9	0	7.3
10	0	7.4

Unsurprisingly, the number of errors is always zero. The performance boost in the last case is around 10%, and it will be more noticeable with higher concurrency. Whether this warrants a patch is a question for the Postgres community.

4. Other possible solutions

Sharding the workload across multiple jobs is a simple way to mitigate the problem. If each job is responsible for a subset of the rows, they will never attempt to update the same row concurrently. This can be done by adding a MOD to the WHERE clause (assuming N is the number of jobs, and job_id is the job's ID ranging from 0 to N-1):

SELECT *
FROM t
WHERE 
    (not is_processed)
AND
    MOD(id, N) = job_id
...

In this solution, locking the rows may not be needed, unless there are other concurrent workloads that may access the same rows. We tested this approach, and it can improve the performance by another 10%.

Finally, a byproduct of using psql in our script is that each job has to pay for the connection overhead on each run. This is by far the most expensive part of the script: This modified script uses both sharding and more importantly, a PL/pgSQL procedure to run the loop inside postgres, to spare the bash script from the reconnect overhead. As shown below, the total execution time drops from 7-8 seconds to just 1.5 seconds, and no serialization failures were raised:

./plpgsql-cross-partition-update.sh 8

job 1: done, iterations=126, errors_ignored=0
job 5: done, iterations=126, errors_ignored=0
job 4: done, iterations=126, errors_ignored=0
job 3: done, iterations=126, errors_ignored=0
job 7: done, iterations=126, errors_ignored=0
job 6: done, iterations=126, errors_ignored=0
job 2: done, iterations=126, errors_ignored=0
job 0: done, iterations=126, errors_ignored=0
----
all 8 jobs finished in 1.562369000s

5. Conclusion

Whether this specific optimization is worth the added complexity in Postgres's EPQ machinery is a genuinely open question. A 10% throughput improvement on this code path is not nothing, but teaching EPQ to prove "this query's condition on the partition key can never match the row's new partition" correctly, for every kind of query that can trigger this error, is a meaningfully harder and riskier change than the unconditional skip we measured. We don't have a strong opinion on whether it clears the bar for the Postgres project to take on.

What we're confident about is smaller and lower-risk: the documentation gap here is real, and cheap to fix. Postgres's docs already explain, correctly, that UPDATE and DELETE can get a serialization failure when they encounter a row that's been moved to another partition by a concurrent update. They just never say the same thing about SELECT ... FOR UPDATE, SELECT ... FOR SHARE, or MERGE — even though, as shown above, they hit the identical code path and the identical error. A short addition to the SELECT, MERGE, and Read Committed sections of the manual would have saved us the hour we spent figuring out why a "read-only-looking" locking query was throwing a serialization error in the one isolation level where nobody expects to need retry logic.