MVCC & Distributed Tx — Series 2

Preview — 3 of 10 questions

A read replica is running a long SELECT. Meanwhile, VACUUM on the primary removes a row version that this replica query still needs to produce a consistent result. What happens?

javascript

ERROR: canceling statement due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.

APostgreSQL cancels the conflicting query on the replica with an error like "canceling statement due to conflict with recovery" — the incoming WAL replay (reflecting the VACUUM's cleanup) takes priority over the long-running read, since the replica must apply WAL to stay in sync

BThe replica silently returns incorrect/incomplete results, since VACUUM cannot be stopped by anything happening on a replica

CThe replica automatically pauses WAL replay indefinitely until the query finishes, with no configuration needed

DThis scenario is impossible because VACUUM never removes row versions that any query anywhere might need

Setting hot_standby_feedback = on on a replica prevents the query-cancellation problem from replication conflicts. What's the cost on the primary?

javascript

Replica: continuously sends its oldest active transaction's XID back to the primary
Primary: VACUUM treats that XID as a lower bound — it won't clean up
         row versions that are still potentially needed by that replica query

AThere is no cost — hot_standby_feedback is a pure win with no trade-offs

BThe replica informs the primary of its oldest still-needed transaction ID, and the primary's VACUUM defers cleaning up rows that the replica might still need — this can cause bloat (dead tuples accumulating) on the primary if the replica has long-running queries or falls behind

Chot_standby_feedback disables replication entirely, converting the replica into an independent primary

DIt only affects logical replication, never physical streaming replication

An ORM wraps every individual database operation in its own SAVEPOINT, resulting in transactions with hundreds of nested savepoints. What performance concern does this raise?

javascript

BEGIN;
SAVEPOINT sp1;  -- subtransaction 1
-- ... operation ...
SAVEPOINT sp2;  -- subtransaction 2
-- ... operation ...
-- ... hundreds more ...

ASAVEPOINT has zero performance cost no matter how many are created in a single transaction

BPostgreSQL silently limits transactions to a maximum of 10 savepoints and ignores any beyond that

CEach SAVEPOINT creates a subtransaction with its own XID, tracked in pg_subtrans; a very large number of subtransactions within one transaction can slow down visibility checks (each check may need to walk the subtransaction chain) and increase overhead in ways that are a documented real-world performance anti-pattern, not just a theoretical concern

DSubtransactions are stored entirely in the WAL and have no effect on runtime visibility checking

Answer all 10 questions (7 more), see explanations for every answer, and track your score.