1. Designing Query Optimization Strategy
| Step | Action |
|---|
| EXPLAIN ANALYZE | Inspect plan + actual times |
| Identify scans | Convert seq scans to index scans |
| Reduce rows early | Push WHERE before joins |
| Avoid SELECT * | Project needed columns only |
| Statistics | ANALYZE; tune autovacuum |
| Rewrite | Prefer EXISTS over IN; CTE inlining |
2. Designing Database Indexing Strategy
| Index Type | Best For |
|---|
| B-tree | Equality, range, sort |
| Hash | Equality only |
| GIN | JSONB, arrays, full-text |
| GiST / SP-GiST | Geometric, geo |
| BRIN | Very large, naturally ordered (time series) |
| Composite | Match leftmost-prefix queries |
| Partial | WHERE filter on subset (e.g., active=true) |
| Covering (INCLUDE) | Index-only scans |
Warning: Each index slows writes. Drop unused indexes (pg_stat_user_indexes idx_scan = 0).
3. Designing Database Query Caching
| Layer | Tool | Notes |
|---|
| Result cache | App-level Redis | Cache by query hash + params |
| Plan cache | Prepared statements | Avoid repeated parsing |
| DB cache | InnoDB/Postgres buffer pool | Tune to working set |
| Materialized view | Precomputed result | REFRESH on schedule or trigger |
4. Designing N+1 Query Solutions
| Solution | Tool |
|---|
| Eager loading | JPA fetch join, Hibernate @EntityGraph |
| Batch fetching | Hibernate @BatchSize, Sequelize include |
| DataLoader | GraphQL batched/cached fetch per request |
| Single SQL | JOIN + group by |
5. Designing Batch Operations Strategy
| Op | Pattern |
|---|
| Bulk insert | COPY (Postgres), LOAD DATA (MySQL), batched VALUES |
| Bulk update | UPDATE ... FROM (VALUES ...) AS v |
| Upsert | INSERT ... ON CONFLICT DO UPDATE |
| Batch size | 500–5000 rows; balance lock time |
6. Designing Database Materialized Views
| Strategy | Refresh |
|---|
| Full refresh | REFRESH MATERIALIZED VIEW |
| Concurrent refresh | No write lock; needs unique index |
| Incremental | pg_ivm, triggers, CDC + custom |
| Use cases | Dashboards, leaderboards, search |
7. Designing Lock and Deadlock Management
| Issue | Mitigation |
|---|
| Lock contention | Shorter txns, smaller scope |
| Deadlocks | Consistent lock order, retry on detection |
| Long-running txn | Statement timeout, idle_in_transaction_session_timeout |
| Optimistic locking | Version column + WHERE version=? |
| Pessimistic | SELECT ... FOR UPDATE [SKIP LOCKED] |
8. Designing Read Replica Strategy
| Decision | Recommendation |
|---|
| Number of replicas | Based on read RPS / replica capacity |
| Routing | RW endpoint = primary; RO endpoint = LB over replicas |
| Lag handling | Read-after-write to primary; bounded staleness |
| Workload isolation | Dedicated replica for analytics |
9. Designing Database Schema Optimization
| Technique | Benefit |
|---|
| Right datatypes | SMALLINT vs INT, TIMESTAMPTZ vs TEXT |
| Normalize for writes | Avoid duplication / anomalies |
| Denormalize for reads | Materialized columns/tables |
| Constraints | Enforce in DB; planner uses them |
| Partitioning | Manage large tables |
10. Designing Connection Pool Tuning
| Param | Guideline |
|---|
| maxPoolSize | ≈ ((cpu × 2) + spindles) × replica count |
| minIdle | 0 in cloud; small in on-prem |
| connectionTimeout | 200–500ms |
| idleTimeout | 5–10 min |
| maxLifetime | 30 min (rotates conns) |
| External pooler | PgBouncer for >1000 client conns |
11. Designing Slow Query Detection
| Tool | What it Provides |
|---|
| log_min_duration_statement (PG) | Logs queries above threshold |
| slow_query_log (MySQL) | Same |
| pg_stat_statements | Aggregated stats per query |
| APM | Datadog, New Relic, Honeycomb |
| auto_explain | Plan capture for slow queries |
12. Designing Query Plan Analysis
| Plan Operator | Meaning | Action |
|---|
| Seq Scan | Full table read | Add index if selective |
| Index Scan | B-tree lookup | OK for small result |
| Bitmap Heap Scan | Multi-row index lookup | OK for medium results |
| Nested Loop | O(N×M) | Bad for large sets; need hash join |
| Hash Join | O(N+M) | Good when one side fits memory |
| Sort | External sort if > work_mem | Add index or raise work_mem |