Database Performance Tuning and Scaling Questions

Situation: During peak traffic WAL shipping and apply lag grow on replicas, causing stale reads for analytics and downstream jobs.

Remediation plan

1. Triage (first 15–30 min)

• Identify whether bottleneck is network (transfer throughput, packet loss), replica IO (disk IOPS/latency), or CPU on apply worker. Check metrics: network bytes/sec, link errors, disk latency/queue, wal_sender/wal_receiver rates, apply lag, and OS-level drops.
• Determine SLA for read staleness: which consumers can tolerate X seconds/minutes delay.

2. Short-term mitigations (minutes→hours)

• Route latency-sensitive reads to primary or to replicas with low lag; tag consumers by freshness requirement.
• Reduce write burst: enable backpressure at ingestion (throttle producers, increase batching) to smooth WAL generation.
• Temporarily increase wal_sender buffer sizes and enable compression for WAL shipping (if supported) to cut bandwidth.
• Move noncritical analytic jobs to run during off-peak or against snapshots/dedicated read-replicas.
• If network is saturated, apply QoS or prioritize replication traffic on routers/switches.

3. Medium-term fixes (hours→days)

• Scale network: increase NIC speed, add parallel replication paths, or use faster VPN/peering between sites.
• Improve IO: switch replicas to higher IOPS storage (provisioned SSD), tune filesystem (noatime, writeback), and increase OS/file system cache for WAL apply.
• Tune replica apply workers: parallel apply, increase apply slots/worker threads, and tune checkpoint and wal_keep_segments to avoid churn.
• Consider logical replication for selective tables to reduce volume.

4. Long-term design changes (weeks→months)

• Add dedicated hot read-replicas for analytics with appropriate sizing and colocated storage/network.
• Introduce change-data-capture (CDC) stream to a message bus (Kafka) so consumers can read near-real-time data without stressing DB replication.
• Re-architect writes: sharding/partitioning to reduce WAL breadth per replica.
• Implement stronger monitoring + alerting (lag thresholds, SLOs) and automated remediation (auto-scale replicas, traffic routing).
• Review consistency model: provide clear docs and implement application-level read strategies (read-after-write from primary, bounded-staleness reads).

Trade-offs & acceptance

• Compression reduces bandwidth but increases CPU and latency; faster NICs and SSDs cost more.
• CDC adds complexity but decouples consumers and removes replication saturation risk.
• Decide acceptable staleness per consumer and enforce via routing or API flags.

Result: Immediate steps reduce lag and prevent stale critical reads; medium/long-term changes remove systemic bottlenecks and provide scalable, observable replication.

Start with profiling to find the root cause, then apply targeted fixes and a cost-control plan.

Profiling steps

• Run query-level byte scan report (cloud console / INFORMATION_SCHEMA / audit logs) to list top N queries by bytes scanned and cost.
• For a representative slow/expensive query, capture the query plan / explain output and I/O stats to see full-table scans, lack of partition/pruning, or missing predicate pushdown.
• Sample file size/format in the underlying storage (e.g., S3): count files, avg size, compression.

How to reduce scanned bytes (practical fixes)

• Predicate pushdown & partition pruning: ensure filters are on partition columns and data is partitioned appropriately (date, region). Example SQL:

-- Partition pruning: filter on partition key
SELECT id, metric FROM analytics.events WHERE event_date = '2025-11-01';

• Column selection: avoid SELECT *; project only needed columns. Use CTAS/materialized views for preselected wide tables.
• File format & compression: convert to columnar formats (Parquet/ORC) with efficient compression (ZSTD/SNAPPY). Example Spark:

df.write.mode('overwrite').parquet('s3://bucket/analytics/parquet/', compression='zstd')

• Small-file problem: compact many small files into larger ones (e.g., 128MB–1GB) to reduce metadata and read amplification (use Spark coalesce/repartition or cloud compaction jobs).
• Schema & data layout: place high-selectivity columns as columns accessed often; use column statistics and min/max to improve pruning.
• Ensure query engine settings allow pushdown (e.g., enable ORC/Parquet predicate pushdown, pushdown for JSON extraction).

Cost-control plan

• Short term: add alerts and dashboards (bytes scanned, top queries), set daily/weekly budgets, and apply query/role-level limits and cost quotas.
• Mid term: create a "cost runbook": mandatory pre-check for large queries (estimate bytes), require approval for queries > threshold, convert heavy landing tables to Parquet and partition/compact.
• Long term: enforce data lifecycle policies (hot/warm/cold storage tiers), automatic partition retention and archival, scheduled compaction jobs, adopt materialized views or aggregated tables for frequent analytics, and periodic audits to re-partition or reformat where patterns change.
• Governance: tagging, query templates, training for analysts on cost-efficient patterns, and automated rewrites or recommendations (using query optimization tools).

Follow-up: instrument a small A/B (before/after) on one heavy pipeline to measure bytes scanned and cost reduction, then rollout.

Detect skew with per-shard ingestion metrics: event rate, key cardinality, per-key frequency, CPU, write latency, and queue depth. Compute skew score (e.g., Gini coefficient or KS test on per-shard rates) and alert when top-k keys account for >X% of traffic or when variance exceeds threshold. Automated remediation strategies: 1) Hot-key splitting: route hot keys to a dedicated small shard (hash of key+salt) or use consistent-hash virtual nodes; 2) Dynamic re-sharding: create new partitions and progressively migrate key ranges; 3) Load-steering: introduce adaptive sampling or temporary throttling for heavy keys and route heavy-traffic producers to alternate paths. Safety checks: 1) rate-limit rebalancer concurrency, 2) simulate migration impact in a shadow environment, 3) use slow, staged migration with checkpoints and rollback points, 4) monitor end-to-end latency and error rates during rebalance; abort if SLA degrades. Implement orchestration: controller reads metrics (Prometheus), runs ML or rule-based detector, proposes actions to an operator or a safe-automated executor that uses small steps, verifies post-step telemetry, and proceeds. Include audit logs and canary migrations to ensure stability.

Start by running EXPLAIN ANALYZE <your query> to get the actual execution plan and timings (Postgres) or EXPLAIN FORMAT=JSON, ANALYZE (MySQL 8+). Look for three things: Seq Scan vs Index Scan, Join type (Hash Join / Nested Loop / Merge Join), and row estimates vs actuals.

1. Identify scan type

• In the plan you'll see "Seq Scan" or "Index Scan/Index Only Scan".
• Seq Scan on a large fact table indicates missing or unused indexes or planner choosing table scan due to poor stats or low selectivity.
  Remedies: create appropriate indexes (covering or composite), run ANALYZE to update stats, rewrite predicate for sargability, or use partitioning.

2. Identify join algorithm

• Hash Join: shows "Hash" + "Hash Join" (build probe). Good for large joins but needs memory; if build side is small (lookup), it's expected.
  Remedies: increase work_mem (Postgres) so hash fits memory; ensure build side is the smaller table; if hash spills to disk, increase memory or pre-aggregate lookup.
• Nested Loop: shows "Nested Loop". Works when outer is small; bad if outer is large — causes repeated index lookups.
  Remedies: add indexes on inner join keys, change join order (force/rewritten) or hint to prefer hash/merge; consider rewriting as EXISTS or using JOIN reordering.
• Merge Join: shows "Merge Join". Good for large sorted inputs. If not chosen but beneficial, ensure inputs are sorted (appropriate indexes) or increase planner costs.

3. Compare estimated vs actual rows

• Large discrepancies mean bad statistics — run ANALYZE, increase statistics_target for relevant columns, or add histogram/ndistinct (Postgres) or use ANALYZE SAMPLE.

4. Use buffers/timing

• In Postgres include BUFFERS to see I/O. High shared read indicates disk I/O — consider caching, VACUUM, or partitioning.

5. Experiments

• Toggle join types (enable_hashjoin=false) to test alternatives.
• Test adding/removing indexes or increasing work_mem and re-run EXPLAIN ANALYZE.

Quick example snippet (Postgres):

EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT ... FROM events e JOIN users u ON e.user_id = u.id WHERE ...;

Follow this loop: observe plan → hypothesize (index/memory/stats/partition) → change → re-run EXPLAIN ANALYZE until performance improves.

Definitions: A read replica is a copy of the primary database that serves read-only traffic; replication lag is the time difference between a write committed on primary and when that change is visible on the replica.

Two causes and mitigations:

1. Network or I/O saturation: large write volume or network spikes delay shipping/applying WAL. Mitigate by provisioning higher network I/O, using compression/parallel replication, or batching writes; monitor replica apply lag and disk throughput.
2. Replica overloaded (read queries or maintenance): heavy analytic reads or vacuum can block apply. Mitigate by offloading heavy reads to dedicated replicas, rate-limiting queries, or scheduling maintenance windows; use connection pooling and separate analytic replicas.

Operational note: alert on replication_lag > defined SLA (e.g., > 5s for near-real-time systems) and automate failover policies carefully.