database-advanced-optimization

Database Advanced Optimization

Level 1: Quick Reference (5 minutes)

Database Selection Guide

Use SQL (PostgreSQL) when:

• ACID compliance is critical
• Complex joins and transactions required
• Data has clear relational structure
• Strong consistency needed
• Rich query capabilities required

Use NoSQL (MongoDB) when:

• Flexible schema needed
• Horizontal scaling is priority
• Document-oriented data model fits
• High write throughput required
• Eventual consistency acceptable

Use In-Memory (Redis) when:

• Sub-millisecond latency required
• Caching layer needed
• Real-time features (pub/sub, streams)
• Session management
• Rate limiting or counters

Common Optimization Patterns

-- PostgreSQL: Create covering index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at)
INCLUDE (status, total_amount);

-- PostgreSQL: Analyze query plan
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 123;

// MongoDB: Create compound index
db.orders.createIndex({ userId: 1, createdAt: -1 }, { background: true });

// MongoDB: Use aggregation pipeline efficiently
db.orders.aggregate([
  { $match: { status: "pending" } },
  { $sort: { createdAt: -1 } },
  { $limit: 100 }
]);

# Redis: Implement cache-aside pattern
def get_user(user_id):
    cache_key = f"user:{user_id}"
    user = redis.get(cache_key)

    if user is None:
        user = db.query("SELECT * FROM users WHERE id = %s", user_id)
        redis.setex(cache_key, 3600, json.dumps(user))

    return json.loads(user)

Essential Optimization Checklist

PostgreSQL

• Create appropriate indexes (B-tree, GIN, GiST)
• Analyze query plans with EXPLAIN ANALYZE
• Configure autovacuum appropriately
• Set up connection pooling (PgBouncer)
• Monitor with pg_stat_statements
• Optimize shared_buffers and work_mem
• Configure appropriate WAL settings

MongoDB

• Design effective shard keys
• Create compound indexes for common queries
• Enable profiler for slow queries
• Configure replica sets for read scaling
• Optimize aggregation pipelines
• Set appropriate write concerns
• Monitor with MongoDB Compass/Atlas