Use SQL (PostgreSQL) when:
Use SQL (PostgreSQL) when:
Use NoSQL (MongoDB) when:
Use In-Memory (Redis) when:
-- 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)
Immediate Impact:
Performance Monitoring:
# PostgreSQL query stats
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC LIMIT 10;
# MongoDB profiler
db.setProfilingLevel(1, { slowms: 100 });
db.system.profile.find().sort({ ts: -1 }).limit(10);
# Redis stats
redis-cli INFO stats | grep -E 'keyspace_hits|keyspace_misses'
📚 Full Examples: See REFERENCE.md for complete code samples, detailed configurations, and production-ready implementations.
Implementation Guide (45 minutes)
PostgreSQL supports multiple index types, each optimized for specific use cases:
B-tree Indexes (Default)
See REFERENCE.md for complete implementation.
GIN Indexes (Full-text and Array Search)
See REFERENCE.md for complete implementation.
GiST Indexes (Geometric and Range Data)
See REFERENCE.md for complete implementation.
See REFERENCE.md for complete implementation.
Understanding EXPLAIN Output:
See REFERENCE.md for complete implementation.
Optimization Decisions:
See REFERENCE.md for complete implementation.
See REFERENCE.md for complete implementation.
Connection Pooling Best Practices:
transaction mode for most applicationsdefault_pool_size = (CPU cores × 2) + effective_spindle_countSHOW POOLS; in PgBouncer consolepostgresql://user:pass@pgbouncer:6432/myappSee REFERENCE.md for complete implementation.
Shard Key Selection:
See REFERENCE.md for complete implementation.
Shard Key Patterns:
// Good for time-series data
sh.shardCollection("analytics.events", { "date": 1, "userId": 1 })
// Queries benefit from targeted routing
db.events.find({ date: ISODate("2025-01-17"), userId: 123 })
// Good for even distribution, poor for range queries
sh.shardCollection("users.profiles", { "_id": "hashed" })
// All shards queried for range
db.profiles.find({ _id: { $gt: 1000, $lt: 2000 } })
See REFERENCE.md for complete implementation.
See REFERENCE.md for complete implementation.
Index Analysis:
See REFERENCE.md for complete implementation.
See REFERENCE.md for complete implementation.
See REFERENCE.md for complete implementation.
1. Cache-Aside (Lazy Loading)
See REFERENCE.md for complete implementation.
2. Write-Through Caching
See REFERENCE.md for complete implementation.
3. Write-Behind (Write-Back) Caching
See REFERENCE.md for complete implementation.
4. Read-Through Caching
See REFERENCE.md for complete implementation.
See REFERENCE.md for complete implementation.
Pub/Sub Pattern:
See REFERENCE.md for complete implementation.
Redis Streams (Preferred for Reliable Messaging):
See REFERENCE.md for complete implementation.
See REFERENCE.md for complete implementation.
Redis Configuration (redis.conf):
See REFERENCE.md for complete implementation.
Bad: N+1 Queries
See REFERENCE.md for complete implementation.
Good: Join or Batch Loading
See REFERENCE.md for complete implementation.
1. Vertical Scaling (Scale Up)
2. Horizontal Scaling (Scale Out)
Read Replicas:
See REFERENCE.md for complete implementation.
Sharding:
See REFERENCE.md for complete implementation.
HikariCP (Java):
See REFERENCE.md for complete implementation.
Python (SQLAlchemy):
See REFERENCE.md for complete implementation.
Node.js (pg-pool):
See REFERENCE.md for complete implementation.
pg_stat_statements Extension:
See REFERENCE.md for complete implementation.
Key Metrics to Monitor:
See REFERENCE.md for complete implementation.
Enable Profiler:
See REFERENCE.md for complete implementation.
Analyze Slow Queries:
See REFERENCE.md for complete implementation.
Server Status Metrics:
See REFERENCE.md for complete implementation.
INFO Command:
See REFERENCE.md for complete implementation.
Key Metrics:
See REFERENCE.md for complete implementation.
Slow Log:
See REFERENCE.md for complete implementation.
Physical Backup (pg_basebackup):
# Full backup
pg_basebackup -h localhost -U postgres -D /backup/pg_data -Fp -Xs -P
# Compressed backup
pg_basebackup -h localhost -U postgres -D /backup/pg_data -Ft -z -P
Logical Backup (pg_dump):
See REFERENCE.md for complete implementation.
Restore:
See REFERENCE.md for complete implementation.
Point-in-Time Recovery (PITR):
See REFERENCE.md for complete implementation.
mongodump:
See REFERENCE.md for complete implementation.
mongorestore:
See REFERENCE.md for complete implementation.
Filesystem Snapshots (Replica Set):
See REFERENCE.md for complete implementation.
RDB (Snapshot):
See REFERENCE.md for complete implementation.
AOF (Append-Only File):
# Enable AOF (redis.conf)
appendonly yes
appendfsync everysec
# Rewrite AOF
redis-cli BGREWRITEAOF
# Backup AOF file
cp /var/lib/redis/appendonly.aof /backup/appendonly-$(date +%Y%m%d).aof
Restore:
# Stop Redis
systemctl stop redis
# Restore RDB or AOF
cp /backup/dump.rdb /var/lib/redis/
# or
cp /backup/appendonly.aof /var/lib/redis/
# Start Redis
systemctl start redis
PostgreSQL:
MongoDB:
Redis:
PostgreSQL:
MongoDB:
Redis:
// TODO: Add basic example for advanced-optimization
// This example demonstrates core functionality
// TODO: Add advanced example for advanced-optimization
// This example shows production-ready patterns
// TODO: Add integration example showing how advanced-optimization
// works with other systems and services
See examples/advanced-optimization/ for complete working examples.
This skill integrates with:
Problem: Not testing edge cases and error conditions leads to production bugs
Solution: Implement comprehensive test coverage including:
Prevention: Enforce minimum code coverage (80%+) in CI/CD pipeline
Problem: Hardcoding values makes applications inflexible and environment-dependent
Solution: Use environment variables and configuration management:
Prevention: Use tools like dotenv, config validators, and secret scanners
Problem: Security vulnerabilities from not following established security patterns
Solution: Follow security guidelines:
Prevention: Use security linters, SAST tools, and regular dependency updates
Best Practices:
This skill includes 6 production-ready templates and scripts:
All resources are located in the skills/database/advanced-optimization/ directory.