CAP Theorem & Consistency Models

-- PostgreSQL defaults to consistency
-- Under network partition, replicas may become unavailable

-- Synchronous replication: strong consistency
ALTER SYSTEM SET synchronous_commit = on;
-- Replicas lag by 0ms but primary blocks if no replica acknowledges

-- Asynchronous replication: availability
ALTER SYSTEM SET synchronous_commit = off;
-- Primary continues even if replicas are unreachable
-- Tradeoff: committed writes may be lost on primary failure

-- PostgreSQL: READ COMMITTED (eventual for concurrent transactions)
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- reads 1000
-- Another transaction updates to 800 and commits
SELECT balance FROM accounts WHERE id = 1;  -- reads 800 (new read)
COMMIT;

-- PostgreSQL: REPEATABLE READ (consistent snapshot)
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;  -- reads 1000
-- Another transaction updates to 800 and commits
SELECT balance FROM accounts WHERE id = 1;  -- still reads 1000
COMMIT;

-- PostgreSQL: SERIALIZABLE (strongest)
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- Detects read/write conflicts and rolls back one transaction
-- Prevents all anomalies but reduces concurrency
COMMIT;

# Choose CP (strong consistency) when:
cp_requirements:
  - Financial transactions (money cannot be duplicated)
  - Inventory management (overselling is unacceptable)
  - Configuration management (stale config causes errors)
  - Leader election (split-brain is catastrophic)

# Choose AP (high availability) when:
ap_requirements:
  - Social media feeds (slightly stale is acceptable)
  - Shopping cart (conflict resolution on merge)
  - IoT sensor data (latest reading matters, not history)
  - DNS (availability is more important than consistency)

# Choose CA (single node) when:
ca_requirements:
  - Small datasets (< 1TB)
  - Single-server deployment
  - No distribution requirements
  - Simple operations