Database Performance Optimization 🗄️
Optimizing database performance is a critical task in system design. As data grows, the way we store, retrieve, and manage it determines the overall responsiveness and cost of the application.
This content is adapted from Mastering System Design from Basics to Cracking Interviews (Udemy). It has been curated and organized for educational purposes on this portfolio. No copyright infringement is intended.
🚀 Foundational Strategies (Recap)
Before diving into advanced techniques, it's important to understand the core structural choices:
1. Database Replication
Copying data across multiple servers.
- Master-Slave: One primary for writes, multiple replicas for reads. Improves read throughput.
- Master-Master: Multiple nodes for both reads and writes. High availability and redundancy.
2. Sharding & Partitioning
- Sharding: Distributing data across multiple physical machines (Horizontal Scaling).
- Partitioning: Dividing data within a single instance (Vertical or Horizontal Partitioning) to improve query efficiency.
3. CAP Theorem (Performance Focus)
In highly distributed systems, you often choose Availability + Partition Tolerance (AP) over strong Consistency to improve performance and throughput. Eventual consistency is a common trade-off for speed.
🛠️ Performance Optimization Techniques
1. Indexing: Types & Use Cases
Indexes are data structures that improve query speed by reducing the amount of data the engine must scan.
- B-Tree Indexes: Standard for range and exact match queries.
- Hash Indexes: Ultra-fast for equality checks (
=), but don't support ranges. - Full-Text Indexes: Used for keyword searches in large text blocks.
- Bitmap Indexes: Ideal for low-cardinality columns (e.g., "Gender" or "Boolean flags").
2. Normalization vs. Denormalization
- Normalization: Reducing redundancy to save space and maintain integrity. Great for writes (OLTP).
- Denormalization: Intentionally adding redundancy (combined tables) to avoid expensive JOINs. Essential for high-performance reads (OLAP).
3. Connection Pooling
Reusing pre-established database connections instead of creating a new one for every request. This eliminates the significant "handshake" overhead of opening connections.
4. Query & Data Patterns
- Query Optimization: Using execution plans to avoid N+1 problems and full table scans.
- Materialized Views: Precomputed, physical versions of complex queries that are periodically refreshed.
- Batching: Sending multiple writes/updates in a single transaction to reduce network and disk overhead.
- Pagination: Breaking large result sets into smaller chunks to prevent memory exhaustion and UI lag.
Interview Questions - DB Performance Optimization 💡
1. Difference between Sharding and Partitioning?
Answer: Sharding distributes data across multiple servers for horizontal scaling. Partitioning divides data within a single database instance to narrow search scope.
2. How does connection pooling improve throughput?
Answer: It reuses existing connections, avoiding the expensive time and resource overhead of the TCP/TLS handshake and database authentication for every new request.
3. When should you use Denormalization?
Answer: Use it in read-heavy reporting systems (OLAP) where high-speed retrieval of combined data is more important than write performance or storage efficiency.
4. How do you optimize a slow SQL query?
Answer: Use EXPLAIN to analyze the execution plan, verify correct indexing on WHERE and JOIN columns, avoid SELECT * by retrieving only needed fields, and implement pagination for large results.
5. Why use Materialized Views?
Answer: To store precomputed query results for expensive aggregation or JOIN-heavy queries where real-time accuracy can be slightly sacrificed for sub-millisecond read speed.
Next up? Detecting these bottlenecks in production — Performance Testing & Monitoring