Fine-tuning with custom scopes v6.4.0

Use custom scopes to give your applications precise durability and consistency guarantees across complex, multi-group environments. Each clause defines its own confirmation requirements independently, covering which nodes must respond, how many, and what kind of confirmation applies. Combining clauses lets you enforce different durability requirements across different node groups within a single commit.

Constructing a custom scope

Every custom scope definition answers three questions:

Each clause pairs a "who confirms?" with a "how?". Combine as many clauses as you need using AND to enforce different durability requirements across node groups in the same commit. PGD evaluates each clause independently, and all must be satisfied before the commit returns.

When you create a custom commit scope, you define:

• A unique name via commit_scope_name, used to reference and assign the scope to a node group or individual transaction
• Which group the scope applies to, via origin_node_group. Only transactions originating from nodes in this group use the scope
• A rule string specifying who confirms and how:
  • The confirming nodes are expressed as a commit scope group specifier, which defines how many must confirm and which group they come from.
  • The type of confirmation is the commit kind, whether that means durability, quorum agreement, bounded lag, or single-commit enforcement.
  • Optionally, DEGRADE ON sets a timeout after which the clause falls back to asynchronous if the required nodes don't respond in time.

Optionally, specify wait_for_ready to block the call until the scope is active across all nodes before returning.

For example, define a scope that requires a majority of local nodes to confirm synchronously before the commit returns.

In this example, the rule requires a majority of nodes in the group my_group to confirm (MAJORITY ORIGIN GROUP), using synchronous commit as the commit kind (SYNCHRONOUS COMMIT).

Choosing a commit kind

Pick a commit kind to define what confirmation means for your workload. The right choice depends on how much latency you can tolerate, whether conflicts need to be prevented at commit time, and how your application handles retries:

• Synchronous Commit requires acknowledgment from the designated peers before the commit returns. Pair with DEGRADE ON to fall back to asynchronous after a timeout if the required peers don't respond.
• Quorum Commit requires all participating nodes to agree on the transaction outcome before any node commits, preventing conflicting changes from being committed independently across nodes.
• CAMO (Commit At Most Once) enforces single-commit semantics across nodes, preventing a transaction from being applied more than once when a client retries after a connection failure. You must implement a verification step in your application to check transaction status before retrying.
• Lag Control provides async semantics gated by a maximum lag threshold. As the cluster approaches the threshold, PGD throttles application traffic to prevent runaway lag. The commit returns after local durability, but writes slow down before the threshold is breached.

Applying a commit scope

You can apply a scope at three levels of granularity. A transaction-level setting overrides a session-level one, which in turn overrides the group default.

• Per group: Set a default scope on the node group when you want a consistent default for all writes originating from that group without changing application code:

  → WrapCopy

  SELECT bdr.alter_node_group_option(
      node_group_name := 'my_group',
      config_key := 'default_commit_scope',
      config_value := 'my_scope'
  );

• Per session: Set the scope as a configuration parameter when running a batch of operations that all need the same scope, or when testing a scope before committing to a group-level default. The scope applies to all transactions in the session until changed or the session ends:

  → WrapCopy

  SET bdr.commit_scope = 'my_scope';

• Per transaction: Set the scope inside the transaction block when you need a different durability guarantee for a specific high-value operation without affecting everything else. You must set the scope before issuing any writes:

  → WrapCopy

  BEGIN;
  SET LOCAL bdr.commit_scope = 'my_scope';
  -- application writes
  COMMIT;

Example: Combining local majority with cross-region confirmation

When a geo-distributed workload needs strong local durability and cross-region confirmation, but can't afford to block if the remote region is unavailable, pair a local majority clause with a cross-region clause and a degrade timeout. The example uses the two data groups, active-active topology, with two active data groups (Region A and Region B, each with three data nodes) and a single witness node in Region C.

SELECT bdr.create_commit_scope(
    commit_scope_name := 'regional_ha',
    origin_node_group := 'region_a',
    rule := 'MAJORITY ORIGIN GROUP SYNCHRONOUS COMMIT
             AND ANY 1 NOT ORIGIN GROUP SYNCHRONOUS COMMIT
                 DEGRADE ON (timeout = 10s, require_write_lead = true) TO ASYNCHRONOUS COMMIT',
    wait_for_ready := true
);

The scope uses two clauses joined by AND. The red box shows the local clause, where A2 (write leader) and A3 satisfy MAJORITY ORIGIN GROUP. The orange box shows the cross-region clause, where A2 as the origin node participates in both and B2 in Region B satisfies ANY 1 NOT ORIGIN GROUP. Each clause is evaluated independently and both must be satisfied before the commit returns. DEGRADE ON applies only to the cross-region clause. If B2 can't confirm within 10 seconds, the cross-region clause falls back to async while the local majority requirement stays enforced.

Depending on your workload, you can also apply this scope at the session or transaction level, for example to enforce stricter durability only for specific high-value writes rather than all traffic from the group.

Example: Requiring all local nodes and two cross-region nodes

When partial confirmation isn't acceptable, for example in regulated industries or financial systems requiring complete consensus, require every local node and two remote nodes to confirm with no fallback. Built on the same two data groups, active-active topology, ALL ORIGIN GROUP requires every node in Region A to confirm rather than a majority, and ANY 2 NOT ORIGIN GROUP requires two Region B nodes to confirm rather than one.

SELECT bdr.create_commit_scope(
    commit_scope_name := 'strict_ha',
    origin_node_group := 'region_a',
    rule := 'ALL ORIGIN GROUP SYNCHRONOUS COMMIT
             AND ANY 2 NOT ORIGIN GROUP SYNCHRONOUS COMMIT',
    wait_for_ready := true
);

The red box covers A1, A2, and A3 because ALL ORIGIN GROUP selects every node in the origin group, including A1, which plays no role in the previous example. The orange selection covers A2, B2, and B3, and the two arrows show that both B nodes must confirm. There's no DEGRADE ON clause, so if any of the required nodes can't confirm, the transaction waits.

Example: Handling multiple durability requirements in a payments workload

Consider a payments application that processes wire transfers, point-of-sale transactions, and batch settlement jobs in the same cluster. Different transaction types carry different risks, and applying the same commit kind to all of them either over-protects low-criticality paths or under-protects high-value ones.

In this scenario, the application faces two distinct durability problems:

• Conflicting commits: two nodes can each approve a wire transfer against the same account without knowing about the other, resulting in an overdraft.
• Duplicate commits: when a client loses its connection during a commit, it can't tell whether the transaction succeeded, so retrying risks processing the payment twice.

To address these two problems, use different commit scope kinds:

• Use Quorum Commit for wire transfers. It requires a majority of nodes to agree before any node finalizes the transaction. If they can't agree, the transaction rolls back everywhere.
• Use CAMO for payment gateway transactions. It lets the application query a partner node for the definitive outcome before deciding whether to retry.

To apply both in the same cluster, define a scope for each commit kind, set Quorum Commit as the group default, and override to CAMO per transaction for the payment gateway path:

1. Define the two scopes:

   → WrapCopy

   -- Quorum Commit scope for wire transfers
   SELECT bdr.create_commit_scope(
       commit_scope_name := 'quorum_commit',
       origin_node_group := 'payments_group',
       rule := 'MAJORITY ORIGIN GROUP QUORUM COMMIT',
       wait_for_ready := true
   );
   
   -- CAMO scope for the payment gateway
   SELECT bdr.create_commit_scope(
       commit_scope_name := 'payment_gateway_camo_scope',
       origin_node_group := 'payment_gateway_camo',
       rule := 'ALL (payment_gateway_camo) CAMO DEGRADE ON (timeout=500ms) TO ASYNC',
       wait_for_ready := true
   );

2. Set Quorum Commit as the default for the payments group:

   → WrapCopy

   SELECT bdr.alter_node_group_option(
       node_group_name := 'payments_group',
       config_key := 'default_commit_scope',
       config_value := 'quorum_commit'
   );

3. Apply CAMO per transaction for payment gateway operations that need exactly-once semantics:

   → WrapCopy

   BEGIN;
   SET LOCAL bdr.commit_scope = 'payment_gateway_camo_scope';
   -- payment gateway writes
   COMMIT;