How deeply does PostgreSQL Mistakes and How to Avoid Them cover serialization anomaly?

Works through a PostgreSQL standby failure, identifies a read-write conflict, and weighs synchronous replication against stale reads.

Serialization anomaly

As explained in PostgreSQL Mistakes and How to Avoid Them

A distributed serialization anomaly is an inconsistency in the apparent ordering or isolation of concurrent operations across database nodes.

A distributed serialization anomaly occurs when concurrent operations appear inconsistently ordered across database nodes, violating transaction isolation. It commonly arises when a long-running read uses a standby while the primary changes and vacuums the same data. section 5.6

A report fails on standby beta

A report on standby beta calculates a sum and average while users delete rows on primary alpha. After alpha vacuums those rows, beta cancels the report with `canceling statement due to conflict with recovery`: the query still needs row versions that recovery must remove. section 5.6

SELECT sum(balance) FROM test.accounts
UNION SELECT avg(balance)
FROM test.accounts;
ERROR:  canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.

Use the PostgreSQL error output to recognize recovery conflicts caused by a standby query requiring removed row versions. section 5.6

Treat the anomaly as a consistency-versus-latency decision:

Enforce synchronous replication when extreme correctness outweighs performance; network round trips can bottleneck database latency. section 5.6
Accept stale or out-of-date reads when low latency matters and the workload tolerates weaker consistency. section 5.6
Avoid spreading one application-side transaction across multiple nodes unless strong consistency and isolation are provided; multi-master systems require particular caution. section 5.6 section 5.7

How PostgreSQL Mistakes and How to Avoid Them covers it

Cover of PostgreSQL Mistakes and How to Avoid Them

liveBook manning.com

Read PostgreSQL Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Related concepts

sagaproduced by · A distributed transaction pattern that coordinates a sequence of local transactions and uses compensating actions to address failures.
write skewincludes · Write skew is a concurrency anomaly in which individually valid, concurrent updates based on overlapping observations jointly violate a consistency constraint.
lost update anomalyincludes · A lost update anomaly occurs when a concurrent modification to data is overwritten or omitted by another modification, leaving the resulting data without one of the changes.
anomalykind of · An anomaly is an observation, behavior, or state that deviates from an expected or typical pattern or condition.
synchronous replicationaddressed by · Synchronous replication is a replication method in which an operation is acknowledged or completed only after required replicas have confirmed the change.

This explainer was generated by Manning's knowledge graph from the text of PostgreSQL Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.