-- ❌ Engineers shard when they see this
SELECT count(*) FROM orders;  -- 50M rows, queries feel slow

-- ✅ First check: is the query actually slow, or is an index missing?
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 12345;
-- If Seq Scan with 50M rows: add index
-- If Index Scan but still slow: look at buffer hits/misses in BUFFERS output

-- Then check connection count before sharding
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';
-- If this number is high: PgBouncer first, not sharding

-- Range partitioning: correct for time-series data, log tables, audit trails
CREATE TABLE orders (
  id         BIGSERIAL,
  created_at TIMESTAMPTZ NOT NULL,
  status     TEXT,
  PRIMARY KEY (id, created_at)  -- partition key must be in PK
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2024 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE orders_2025 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- ✅ Queries with a created_at filter only scan the matching partition
-- (partition pruning — Postgres eliminates irrelevant partitions from the plan)
EXPLAIN SELECT * FROM orders WHERE created_at >= '2025-06-01';
-- → only scans orders_2025

-- ✅ Old partitions can be detached and archived without locking the entire table
ALTER TABLE orders DETACH PARTITION orders_2024;

-- Hash partitioning: correct when you want even write distribution across partitions
-- (no natural range key, or avoid hot spots in a range partition)
CREATE TABLE sessions (
  id      UUID NOT NULL,
  user_id BIGINT,
  data    JSONB
) PARTITION BY HASH (id);

CREATE TABLE sessions_p0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_p1 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_p2 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_p3 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- ❌ These do NOT work across partitions:

-- 1. Unique constraints not including the partition key
CREATE UNIQUE INDEX ON orders(id);  -- ✗ Fails if id is not the partition key

-- 2. Foreign keys referencing partitioned tables from non-partitioned tables
-- (works FROM partitioned TO non-partitioned, not the reverse in older Postgres)

-- 3. Full-table exclusive locks (e.g., ALTER TABLE) are per-partition in Pg 12+
--    but CLUSTER and some operations still lock all partitions

-- ✅ Use partitioning for:
--   - Time-windowed data with range queries on the partition key
--   - Tables where you want to drop/archive whole time windows cheaply
--   - Reducing index size per partition (smaller B-Trees = faster scans)

Shard A: customer_id 1 – 1,000,000
Shard B: customer_id 1,000,001 – 2,000,000
Shard C: customer_id 2,000,001 – 3,000,000

shard = hash(customer_id) % num_shards

customer_id = 42:    hash(42) % 4 = 2  → Shard C
customer_id = 43:    hash(43) % 4 = 3  → Shard D
customer_id = 44:    hash(44) % 4 = 0  → Shard A

Shard routing table (maintained by a router service):
  customer_id 42   → Shard C
  customer_id 1001 → Shard A
  customer_id 8832 → Shard B
  ...

-- Router service maps each key to a shard dynamically
-- Adding a new shard: only remaps a fraction of keys (consistent hashing ring)

-- ❌ Before sharding: foreign keys, joins, transactions work normally
SELECT o.*, c.name, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';

-- ❌ After sharding on customer_id: order and customer may be on different shards
-- The JOIN above becomes:
-- 1. Query Shard A for pending orders: [order_id=1, customer_id=44]
-- 2. Query Shard C (where customer_id=44 lives) for customer name
-- 3. Application joins the two result sets in memory

-- ✅ Design for co-location: shard both orders AND customers on customer_id
-- Then: orders for customer 44 AND customer 44's profile both live on Shard C
-- The join can execute within a single shard

[Primary] ← writes only
  ├── WAL stream →  [Replica 1] ← reads
  ├── WAL stream →  [Replica 2] ← reads
  └── WAL stream →  [Replica 3] ← reads

-- ✅ Detecting replication lag on a standby
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

-- ✅ If lag > your SLA, route reads to primary for that session
-- (or use synchronous_commit = remote_write to bound maximum lag)
SET synchronous_commit = 'remote_write';

[Primary A] ← writes     [Primary B] ← writes
     │                          │
     └──── bidirectional ───────┘
           replication

-- ❌ Multi-master without conflict detection
-- Session on Primary A: UPDATE balance = 900 WHERE id = 42
-- Session on Primary B (concurrent): UPDATE balance = 800 WHERE id = 42
-- LWW resolution: the later timestamp wins → 800
-- A's update is gone. Lost update. No error surfaced to the application.

N = 5 nodes
W = 3 (write quorum: write must be acknowledged by 3 nodes before success)
R = 3 (read quorum: read must be confirmed by 3 nodes before returning result)

W + R = 6 > N = 5
∴ At least 1 node must overlap between write quorum and read quorum
∴ Every read will see at least one node that has the latest write

# Tuning W and R for different SLAs:

# High write availability (tolerate more stale reads):
W = 1  # Writes acknowledged after 1 node — fast, low durability
R = 5  # Reads from all nodes — expensive, always fresh
# W + R = 6 > 5 ✅ — but writes are fragile (1 node failure loses the write)

# High read availability (tolerate write latency):
W = 3  # Writes quorum — durable
R = 1  # Read from any node — fast, may be stale
# W + R = 4 < 5 ✗ — no overlap guarantee → stale reads possible

# Balanced (Cassandra QUORUM):
W = ceil(N/2) + 1 = 3  # Majority write
R = ceil(N/2) + 1 = 3  # Majority read
# W + R = 6 > 5 ✅ — strong consistency guarantee