-- ❌ 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)

+-----------------------------------------------------------------------------+
|                   THE 5-PILLAR DATABASE CAPACITY FRAMEWORK                  |
+-----------------------------------------------------------------------------+
| 1. MEMORY PILLAR     : Working Set vs shared_buffers (Cache Hit Ratio)      |
| 2. CONCURRENCY PILLAR: Little's Law (Connection Pool vs Query Latency)      |
| 3. I/O PILLAR        : Storage IOPS vs Write Amplification Factor           |
| 4. CPU PILLAR        : vCPU Processing Time per Query (Burst Credits)       |
| 5. CONTENTION PILLAR : Row-level locks and DDL Exclusive Locks              |
+-----------------------------------------------------------------------------+

RAM_required = RAM_OS + shared_buffers + (N_connections * (work_mem + conn_overhead))

Component                                         Size
-------------------------------------------------+--------
RAM_OS  (Linux kernel + RDS monitoring agents)   :  ~350 MB
shared_buffers (PostgreSQL in-memory page cache) :  ~256 MB
conn_overhead (RAM per open Postgres process)    :    ~4 MB
work_mem (RAM per sort / hash operation)         :    ~4 MB

Max Safe Connections = (1024 - (350 + 256)) / (4 + 4)
                     =  418 / 8
                     = ~52 connections

Max Throughput (QPS) = N_active_connections / Average_Query_Latency (seconds)

Avg query latency (indexed insert / conditional upsert) : 3 ms = 0.003 s
Max Throughput = 10 connections / 0.003 s = 3,333 QPS

Write_Ceiling (QPS) = Storage_IOPS_Baseline / Write_Amplification_Factor (WAF)

+-------------------------------------+-----------------------------------------+
| gp3 Baseline Provisioned IOPS       | 3,000 IOPS                              |
| Write Amplification Factor          | ~6 IOPS per insert                      |
|   (WAL + 4 local B-Tree indexes +   |   (table block + WAF)                   |
|    data block flush)                |                                         |
| Max Continuous Write Capacity       | 3000 / 6 = ~500 Writes/sec              |
| Typical burst (ferry dock event)    | ~60 Writes/sec                          |
| IOPS Utilization at Peak            | 12% of baseline                         |
+-------------------------------------+-----------------------------------------+

Max Operations/sec = (vCPUs * 1000 ms) / CPU_time_per_query (ms)

CPU time per indexed B-Tree insert : ~0.25 ms
Max Operations = (2 * 1000) / 0.25 = 8,000 operations/sec

-- ❌ Hot-row contention: a shared counter updated by every insert
UPDATE daily_summary SET scan_count = scan_count + 1 WHERE day = today;
-- Every concurrent insert blocks waiting for this row lock.

-- ✅ Append-only design: each insert is independent, no shared row
INSERT INTO all_log (badge_id, scanned_at, turnstile_id) VALUES (...);
-- Zero row-level contention — each row is new, no locks compete.

-- ❌ Standard DETACH blocks live traffic
ALTER TABLE all_log DETACH PARTITION all_log_2024_q1;

-- ✅ CONCURRENTLY variant allows reads and inserts to proceed uninterrupted
ALTER TABLE all_log DETACH PARTITION all_log_2024_q1 CONCURRENTLY;

+------------------+----------------------+----------------------+------------+
| Dimension        | Saturation Limit     | Actual Peak Usage    | Headroom   |
+------------------+----------------------+----------------------+------------+
| Memory (RAM)     | 50 Connections       | 10 Pooled Conns      | 5.0x Safe  |
| Throughput (QPS) | ~3,300 QPS           | 60 QPS Burst         | 55x  Safe  |
| Disk I/O         | 500 Writes/sec       | 60 Writes/sec        | 8.3x Safe  |
| Active Hot Data  | 100 MB in Buffers    | ~75 MB (3 months)    | 1.3x Safe  |
| Storage Disk     | 20,000 MB (20 GB)    | ~350 MB (1 Year)     | 57x  Safe  |
+------------------+----------------------+----------------------+------------+

1. Active concurrent pooled connections  <=  15
2. Peak sustained write velocity         <= 250 writes/sec
3. Active operational working set in RAM <= 100 MB
4. All multi-row queries use indexes and partition pruning (no sequential 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