Week 4 — SQL at Scale

Read Replicas

PostgreSQL streaming replication sends WAL (Write-Ahead Log) changes to replicas in near-real time.

Primary (all writes) ──WAL──→ Replica 1 (reads)
                      ──WAL──→ Replica 2 (reads)

Trade-offs:

• Asynchronous: replicas lag by milliseconds to seconds. If primary crashes before WAL is sent → data loss on replica
• Synchronous: zero data loss, but write latency increases (must wait for replica ACK)

Don’t use replicas for read-after-write. If user writes then immediately reads, route that read to primary. Otherwise they see stale data.

Sharding Strategies

Split data across multiple DB instances when a single instance can’t handle the load.

Strategy	How	Hot spot risk	Range query
Range-based	user_id 1–1M → shard 1	Yes — new IDs always hit last shard	Easy
Hash-based	hash(user_id) % N → shard	No	Hard (must query all shards)
Directory-based	Lookup table: user → shard	No	Depends

Odoo’s approach: Database-per-tenant avoids cross-shard joins entirely. Each tenant is isolated.

Connection Pooling

PostgreSQL connections are expensive (each is a process). PgBouncer multiplexes many app connections onto fewer DB connections.

100 Odoo workers → PgBouncer → 20 PostgreSQL connections

Modes:

• Session mode: one DB connection per app session. Preserves prepared statements.
• Transaction mode: connection returned to pool after each transaction. Best for most cases.
• Statement mode: returned after each statement. Breaks multi-statement transactions.

Sizing rule of thumb: (num_cores × 2) + num_disks connections is the PostgreSQL sweet spot.

Index Deep Dive

B-tree Index (default)

Fast for equality (=) and range queries (>, <, BETWEEN). Useless for LIKE '%term'.

CREATE INDEX idx_orders_date ON sale_order(date_order);
-- Fast: WHERE date_order > '2025-01-01'
-- Slow: WHERE date_order::text LIKE '%2025%'

Composite Index — Column Order Matters

CREATE INDEX idx_move_company_date ON account_move(company_id, date);
-- Uses index: WHERE company_id = 1 AND date > '2025-01-01'
-- Uses index: WHERE company_id = 1
-- Does NOT use: WHERE date > '2025-01-01'  ← leftmost column missing

Always put the highest-selectivity column first, or the column used in equality filters first.

Covering Index

Include all columns needed by the query — eliminates the heap fetch:

CREATE INDEX idx_covering ON sale_order(partner_id) INCLUDE (amount_total, state);
-- Query can be answered from index alone, no table access

When Indexes Hurt

• High-write tables: every write must update all indexes
• Low-selectivity columns (e.g. boolean, status with 3 values): full scan often faster
• Too many indexes on one table → slow INSERT/UPDATE/DELETE

EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT * FROM account_move WHERE company_id = 1 AND date > '2025-01-01';

Look for: Seq Scan (bad on large tables) vs Index Scan (good). Check actual rows vs estimated rows — large mismatch means stale statistics, run ANALYZE.

Practice

Design the indexing strategy for an Odoo database with:

• 10M sale orders, filtered by company, date, and state frequently
• 50M account move lines, queried by account, date, partner
• 100 concurrent users running reports

Self-check

• Why is async replication risky for financial data?
• What breaks if you shard by hash and then need to run GROUP BY across all orders?
• Why does PgBouncer in transaction mode break some Odoo features?