Read Scaling

Read scaling techniques address the most common performance bottleneck in web applications: read-heavy workloads that overwhelm a single database primary. Read replicas distribute SELECT queries across multiple copies of the database, scaling read throughput linearly with replica count. CQRS (Command Query Responsibility Segregation) takes this further, using separate data models optimized for reads (denormalized, indexed projections) and writes (normalized). Materialized views and denormalization trade storage and update complexity for read performance, reducing multi-table JOINs to single-row lookups.

Key Points

• Read replicas: synchronous or asynchronous replication from primary — AWS Aurora supports 15 replicas with <10ms replication lag; route all SELECT statements through replica endpoint.
• CQRS: separate command (write) model from query (read) model — read model is a pre-computed projection updated by events from the write model, optimized for specific queries.
• Materialized views: pre-computed query results stored as a table (PostgreSQL `MATERIALIZED VIEW`, Oracle) — refresh on a schedule or on write via triggers.
• Denormalization: intentionally store redundant data to eliminate JOINs — store username alongside each comment instead of joining to users table on every read.
• Elasticsearch as read replica: sync relational data to Elasticsearch for full-text search and complex aggregations that would kill a relational DB.
• Query result caching: cache expensive aggregate query results in Redis — invalidate on write, or use TTL if slight staleness is acceptable.
• Database connection routing: ProxySQL and PgBouncer can route SELECT to replicas and writes to primary automatically based on query type.
• Replication lag monitoring: monitor `Seconds_Behind_Master` (MySQL) or `pg_replication_slots` lag (PostgreSQL) — alert if lag exceeds your acceptable staleness window.