Confirmation levels v6.5.0

A confirmation level sets how far a node has processed a transaction before it confirms the transaction to the origin node.

You set the level as part of a commit scope rule. Each rule is made up of two or three parts: the commit scope group, an optional confirmation level, and the commit scope kind. Setting a level is optional. If you leave it out, the default level, visible, applies.

The levels are inspired by the PostgreSQL synchronous_commit settings, but PGD behaves differently from a PostgreSQL warm standby.

commit_scope_group [ ON confirmation_level ] commit_scope_kind

For example, to ask a majority of nodes in the origin group to confirm after they flush the transaction to disk, add ON durable to the rule:

SELECT bdr.create_commit_scope(
    commit_scope_name := 'example_scope',
    origin_node_group := 'example_bdr_group',
    rule := 'MAJORITY ORIGIN GROUP ON durable SYNCHRONOUS COMMIT',
    wait_for_ready := true
);

The full syntax diagram is in the commit scope syntax reference.

Confirmation level types

PGD has four confirmation levels: received, replicated, durable, and visible. Each level is a later point in a node's processing of the transaction, and the node confirms once it reaches that point.

received

A peer confirms as soon as it receives the transaction, before it updates its tuple store or writes anything to storage. The origin node only waits for the transaction to arrive, so with large transactions, received can improve transactions per second.

If a Postgres server or operating system crash hits the peer after it confirms but before it writes the transaction to persistent storage, the peer loses the transaction and it has to be sent again.

Because a peer confirms before it applies the transaction, reads from that peer can return stale data.

replicated

For commit scope kinds that support replicated, it's the same as received. Synchronous Commit and CAMO don't distinguish the two levels, and Quorum Commit supports neither.

durable

A peer confirms after it receives the transaction, applies it to the tuple store, and flushes the changes to storage. The origin node waits for the flush. With some commit scope kinds, such as Quorum Commit, the peer hasn't yet made the changes available to other sessions. The level is similar to the PostgreSQL on setting.

The flush protects against data loss after a crash and recovery of the peer, which recovers without retransmission.

If a session commits a transaction with the durable level, then disconnects and reconnects, the transaction's changes aren't guaranteed to be visible to the reconnected session. The level doesn't guarantee consistency during failover.

visible

A peer confirms after it receives and applies the transaction. The changes are also visible to concurrent transactions. With Synchronous Commit and CAMO, visible behaves the same as durable, because PGD applies a transaction before it flushes it. With Quorum Commit, peers confirm after applying the transaction, without waiting for a flush. The visible level is the default and is similar to the PostgreSQL remote_apply setting.

Confirmation of visibility means that the transaction was fully applied remotely. If a session commits a transaction with an ON visible rule before disconnecting and reconnecting, the transaction's changes are guaranteed to be visible to the reconnected session.

Comparing levels by commit scope kind

The origin confirms a transaction when its results become visible to the client or application. A peer applies a transaction when it becomes visible on that peer, even though the transaction might not be on disk yet, because peers flush replicated transactions asynchronously.