Database Mastery
1. Database Selection Decision Tree
What data do you have? ─┬─ Structured, relational ──→ PostgreSQL (complex queries) or MySQL/MariaDB (simplicity)
├─ Key-value, caching ──→ Redis (in-memory) or DynamoDB (managed)
├─ Document/semi-structured ──→ MongoDB or PostgreSQL JSONB
├─ Time-series ──→ TimescaleDB, InfluxDB, ClickHouse
├─ Graph (relationships) ──→ Neo4j, ArangoDB
└─ Full-text search ──=> Elasticsearch, Meilisearch, PostgreSQL tsvector
2. MySQL/MariaDB Deep-Dive
InnoDB Tuning (my.cnf)
[mysqld]
Buffer Pool — 70-80% of RAM on dedicated DB server
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8
Log settings
innodb_log_file_size = 1G
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 1 # 1=safe, 2=perf tradeoff
sync_binlog = 1 # Always sync binlog
Connection settings
max_connections = 500
thread_cache_size = 100
connect_timeout = 10
wait_timeout = 600
Query cache (DISABLED in MySQL 8.0+, use app-level cache instead)
query_cache_type = 0
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
Slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
MariaDB Galera Cluster Setup
# my.cnf (each node)
[mysqld]
binlog-format=ROW
default-storage-engine=InnoDB
innodb-auto-increment-offset=1 # Node 1=1, Node 2=2, Node 3=3
wsrep-provider=/usr/lib/galera/libgalera_smm.so
wsrep-cluster-address=gcomm://10.0.1.1,10.0.1.2,10.0.1.3
wsrep-cluster-name=my_cluster
wsrep-node-address=10.0.1.1
wsrep-node-name=node1
wsrep-sst-method=rsync
wsrep-sync-wait=7 # Read consistency (1+2+4 = all)
Backup with XtraBackup
# Full backup
xtrabackup --backup --target-dir=/backup/full --user=root --password=$PASS
Prepare backup (apply transaction log)
xtrabackup --prepare --target-dir=/backup/full
Restore
systemctl stop mysql
xtrabackup --copy-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql
Incremental backup
xtrabackup --backup --target-dir=/backup/inc1 \
--incremental-basedir=/backup/full --user=root --password=$PASS
xtrabackup --prepare --apply-log-only --target-dir=/backup/full
xtrabackup --prepare --target-dir=/backup/full --incremental-dir=/backup/inc1
3. PostgreSQL Deep-Dive
PostgreSQL Config (postgresql.conf)
# Memory (for 32GB RAM server)
shared_buffers = 8GB # 25% of RAM
effective_cache_size = 24GB # 75% of RAM
work_mem = 64MB # Per-query sort/hash
maintenance_work_mem = 512MB # For VACUUM, CREATE INDEX
wal_buffers = 64MB
WAL & Replication
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1GB
hot_standby = on
Query planning
random_page_cost = 1.1 # SSD (default 4.0 for HDD)
effective_io_concurrency = 200 # SSD concurrent IO
jit = on
Logging
log_min_duration_statement = 500 # Log queries > 500ms
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
Autovacuum
autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = 30s
PostgreSQL Streaming Replication
# Primary (postgresql.conf)
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1GB
Primary (pg_hba.conf) — allow replica connections
host replication replicator 10.0.2.0/24 md5
Replica setup (on standby)
pg_basebackup -h primary -U replicator -D /var/lib/postgresql/16/main -Fp -Xs -P -R
Replica (postgresql.conf) — promoted from recovery.conf
hot_standby = on
primary_conninfo = 'host=10.0.1.1 port=5432 user=replicator password=secret'
PgBouncer Connection Pooling
; pgbouncer.ini
[databases]
app_db = host=10.0.1.1 port=5432 dbname=app_db
[pgbouncer]
pool_mode = transaction # transaction pooling (most efficient)
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 300
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pg_stat_statements — Find Slow Queries
-- Enable extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Top 10 queries by total execution time
SELECT query, calls, total_exec_time, mean_exec_time, rows,
round(total_exec_time::numeric / sum(total_exec_time) OVER () * 100, 2) as pct_total
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
-- Queries with highest I/O
SELECT query, shared_blks_hit, shared_blks_read,
round(shared_blks_hit::numeric / nullif(shared_blks_hit + shared_blks_read, 0) * 100, 1) as hit_pct
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;
4. Redis Patterns
Cache-Aside with Redis
import redis
import json
r = redis.Redis(host='redis', port=6379, db=0, decode_responses=True)
def get_user(user_id: str) -> dict:
key = f"user:{user_id}"
# Try cache
cached = r.get(key)
if cached:
return json.loads(cached)
# Cache miss → DB
user = db.get_user(user_id)
# Populate cache with jittered TTL (avoid thundering herd)
ttl = 3600 + random.randint(0, 300) # 1h ± 5min jitter
r.setex(key, ttl, json.dumps(user))
return user
def update_user(user_id: str, data: dict):
db.update_user(user_id, data)
# Invalidate cache
r.delete(f"user:{user_id}")
# Also invalidate related caches
r.delete(f"user_posts:{user_id}")
r.delete(f"user_stats:{user_id}")
Redis Eviction Policies
Policy? ─┬─ Volatile-LRU ──→ Evict least recently used with TTL set (default for cache)
├─ Allkeys-LRU ──→ Evict LRU regardless of TTL (pure cache)
├─ Volatile-LFU ──→ Evict least frequently used with TTL
├─ Allkeys-LFU ──→ Evict LFU regardless of TTL
├─ Volatile-TTL ──→ Evict shortest TTL first
└─ Noeviction ──→ Return errors on memory full (queue/session data)
Redis Persistence
# RDB snapshots (point-in-time, faster recovery)
save 900 1 # Save after 900s IF at least 1 key changed
save 300 10 # Save after 300s IF at least 10 keys changed
save 60 10000 # Save after 60s IF at least 10000 keys changed
AOF append-only (every write logged, more durable)
appendonly yes
appendfsync everysec # always | everysec | no
auto-aof-rewrite-percentage 100
auto-aof-rewrite-min-size 64mb
Hybrid (best of both)
RDB for disaster recovery + AOF for durability
5. Query Optimization
EXPLAIN ANALYZE Deep-Dive
-- PostgreSQL EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2025-01-01'
GROUP BY u.name
ORDER BY COUNT(o.id) DESC
LIMIT 20;
-- Key things to check in output:
-- Seq Scan → Add index
-- Nested Loop (high rows) → Add index on join key
-- Sort → Add ORDER BY column to index
-- Hash Aggregate → Usually fine, but check memory
-- Bitmap Heap Scan → Good (index used)
-- Filter with high rows removed → Add selective index
-- MySQL EXPLAIN
EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE user_id = 42 AND status = 'active'\G
-- Check: type=ref (good), type=ALL (bad), Extra=Using filesort (bad)
Covering Index Strategy
-- Before: query reads from table (heap fetch)
SELECT user_id, status, created_at FROM orders WHERE user_id = 42;
-- After: covering index (all columns in index, no heap fetch)
CREATE INDEX idx_orders_user_status_date ON orders (user_id, status, created_at);
-- Composite index column order (most selective first):
-- Equality columns → Range columns → Sort columns → Include columns
CREATE INDEX idx_magic ON orders (status, user_id, created_at) INCLUDE (total_amount);
Slow Query Log Analysis
# MySQL: Enable and analyze slow queries
mysqldumpslow -s t /var/log/mysql/slow.log | head -20
pt-query-digest /var/log/mysql/slow.log --limit 10
PostgreSQL: pg_stat_statements top queries
psql -c "SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;"
6. Replication Strategies
Replication Comparison
| Strategy | Latency | Durability | Use Case |
|----------|---------|------------|----------|
| Async | Low | Risk of data loss | Read replicas, analytics |
| Semi-sync | Medium | Confirmed on 1 replica | Production OLTP |
| Fully sync | High | Zero data loss | Financial transactions |
| GTID-based | Low | Trackable | Easy failover |
Orchestrator (MySQL HA)
# Install Orchestrator for MySQL HA failover
docker run -d --name orchestrator \
-p 3000:3000 \
-v /etc/orchestrator:/etc/orchestrator \
openoracle/orchestrator:latest
orchestrator -c discover -i master1.example.com:3306
orchestrator -c discover -i replica1.example.com:3306
orchestrator -c discover -i replica2.example.com:3306
Graceful master takeover
orchestrator -c graceful-master-takeover -i master1.example.com:3306
7. Backup & Recovery
Backup Strategy Decision Tree
Need backup strategy? ─┬─ <10GB DB, simple ──→ mysqldump/pg_dump daily + S3
├─ 10-500GB, online ──→ XtraBackup/pg_basebackup + S3
├─ >500GB, minimal downtime ──→ Incremental + PITR
└─ Compliance/audit ──→ Snapshot + WAL/binlog archiving
Automated Backup Script
#!/bin/bash
PostgreSQL backup with S3 upload
set -euo pipefail
DB_NAME="app_db"
BACKUP_DIR="/backup"
S3_BUCKET="s3://my-backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=30
Create backup
pg_dump -Fc -f "${BACKUP_DIR}/${DB_NAME}_${DATE}.dump" ${DB_NAME}
Compress
gzip "${BACKUP_DIR}/${DB_NAME}_${DATE}.dump"
Upload to S3
aws s3 cp "${BACKUP_DIR}/${DB_NAME}_${DATE}.dump.gz" "${S3_BUCKET}/${DATE}/"
Verify upload
aws s3 ls "${S3_BUCKET}/${DATE}/${DB_NAME}_${DATE}.dump.gz"
Cleanup old backups
find ${BACKUP_DIR} -name "*.dump.gz" -mtime +${RETENTION_DAYS} -delete
Slack notification
curl -X POST "$SLACK_WEBHOOK" \
-H 'Content-Type: application/json' \
-d "{\"text\": \"✅ DB backup completed: ${DB_NAME}_${DATE}.dump.gz\"}"
8. Migration Patterns
Zero-Downtime Schema Change (pt-online-schema-change)
# Add column without locking table
pt-online-schema-change \
--alter "ADD COLUMN email VARCHAR(255) NOT NULL DEFAULT ''" \
--host=localhost \
--user=admin \
--password=$PASS \
D=app_db,t=users \
--execute
Add index without locking
pt-online-schema-change \
--alter "ADD INDEX idx_users_email (email)" \
--host=localhost \
D=app_db,t=users \
--execute \
--max-load=Threads_running=50 \ # Throttle if load high
--chunk-size=5000
# Run on replica first, then switch
gh-ost \
--user=admin \
--password=$PASS \
--host=replica1 \
--database=app_db \
--table=orders \
--alter="ADD COLUMN priority TINYINT DEFAULT 0" \
--allow-on-master \
--execute
9. Monitoring & Alerting
PostgreSQL Prometheus Exporter
# postgres_exporter config
DATA_SOURCE_NAME: "postgresql://exporter:password@db:5432/app_db?sslmode=disable"
Key metrics to watch
pg_database_size_bytes # Database size
pg_stat_activity_count # Active connections
pg_stat_database_deadlocks # Deadlock count
pg_replication_lag_seconds # Replication lag
pg_stat_statements_mean_time_seconds # Avg query time
Grafana Dashboard Key Panels
| Panel | Metric | Alert Threshold |
|-------|--------|----------------|
| Active Connections | pg_stat_activity | >80% max_connections |
| Replication Lag | pg_replication_lag | >30s |
| Deadlocks | pg_stat_database_deadlocks | >0 in 5min |
| Slow Queries | pg_stat_statements >1s | >10/min |
| Cache Hit Ratio | pg_stat_database_hit_pct | <95% |
| Table Bloat | pgstattuple dead_tuple_pct | >20% |
| TPS | pg_stat_database_xact_commit | track baseline |
| DB Size Growth | pg_database_size_bytes | >10GB/day |
10. Flowcharts & Decision Diagrams
Slow query? ─┬─ Seq Scan → Add index on WHERE/JOIN columns
├─ Low cache hit (<95%) → Increase shared_buffers / innodb_buffer_pool
├─ High rows examined → Tighten WHERE, add covering index
├─ Filesort → Add composite index matching ORDER BY
├─ Temp table on disk → Increase work_mem / sort_buffer_size
├─ Lock waits → Check long-running transactions, add innodb_lock_wait_timeout
└─ Network latency → Check connection pooling, colocate app+DB
Replication Problem Decision Tree
Replication broken? ─┬─ Behind master → Check network, increase parallel workers
├─ SQL thread stopped → Check error log, fix conflicting data
├─ IO thread stopped → Check master connectivity, binlog position
├─ Lag growing → Add parallel replication, tune relay_log_recovery
└─ Corrupt data → Re-provision from backup or fresh pg_basebackup
Backup Decision Tree
What backup? ─┬─ Point-in-time recovery needed? ──Yes──→ WAL/binlog archiving
├─ Full cluster backup? ──Yes──→ pg_basebackup / XtraBackup
├─ Single database? ──Yes──→ pg_dump / mysqldump
└─ Schema only? ──Yes──→ pg_dump --schema-only
11. Testing Strategy
Load Testing with sysbench
# Install sysbench
apt install sysbench
Prepare OLTP test (1M rows, 10 tables)
sysbench oltp_read_write \
--db-driver=pgsql \
--pgsql-host=db \
--pgsql-port=5432 \
--pgsql-user=benchmark \
--pgsql-password=secret \
--pgsql-db=app_db \
--tables=10 \
--table-size=1000000 \
--threads=32 \
--time=300 \
--report-interval=10 \
prepare
Run benchmark
sysbench oltp_read_write \
--threads=64 \
--time=300 \
--report-interval=10 \
run
Cleanup
sysbench oltp_read_write cleanup
pgbench (PostgreSQL native)
# Initialize benchmark database
pgbench -i -s 100 app_db # scale factor 100 = 10M rows
Run benchmark (64 clients, 5 minutes)
pgbench -c 64 -j 8 -T 300 -r app_db
Custom script for realistic workload
pgbench -c 32 -T 300 -f custom_workload.sql app_db
Connection Pool Sizing
# Formula for pool size
pool_size = (core_count * 2) + effective_disk_spindles
Example: 8-core CPU, 4 disks = 8*2 + 4 = 20 connections
More connections than this → context switching overhead > benefit
Table Partitioning (PostgreSQL)
-- Range partitioning by date
CREATE TABLE orders (
id BIGSERIAL,
user_id INTEGER,
total DECIMAL(10,2),
created_at TIMESTAMP,
status TEXT
) PARTITION BY RANGE (created_at);
-- Create monthly partitions
CREATE TABLE orders_2025_01 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE orders_2025_02 PARTITION OF orders
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
-- Auto-create future partitions (pg_partman)
SELECT partman.create_parent(
p_parent_table := 'public.orders',
p_control := 'created_at',
p_type := 'range',
p_interval := '1 month',
p_premake := 3
);
13. Best Practices
Always use connection pooling — never open/close per request
Index for queries, not columns — add indexes matching your WHERE + ORDER BY
Monitor replication lag continuously — alert on >30 seconds
Test backups by restoring — untested backups aren't backups
Use covering indexes — avoid heap fetches with INCLUDE columns
Set vacuum/autovacuum aggressively — prevent bloat before performance degrades
Use pt-online-schema-change or gh-ost — never ALTER TABLE on large tables directly
Encrypt at rest and in transit — TLS for connections, encryption for storage
Separate read and write connections — route reads to replicas
Automate failover — Orchestrator (MySQL) or repmgr (PostgreSQL)
14. Common Pitfalls
| # | Pitfall | Impact | Fix |
|---|---------|--------|-----|
| 1 | Missing connection pool | DB connection storms | PgBouncer/ProxySQL with min=5,max=20 |
| 2 | No covering index | Slow queries, heap fetches | Include all SELECT columns in index |
| 3 | Autovacuum too conservative | Table bloat, degraded performance | Tune autovacuum_vacuum_scale_factor=0.05 |
| 4 | Shared_buffers too small | Poor cache hit ratio | Set to 25% RAM on dedicated DB server |
| 5 | No query timeouts | Long-running queries block others | Set statement_timeout=30s, wait_timeout=30 |
| 6 | Replication lag unmonitored | Stale reads on replicas | Alert on lag >30s, use synchronous for critical |
| 7 | Not testing backups | Can't restore when needed | Weekly restore test to staging |
| 8 | Direct ALTER TABLE on production | Locks table for hours | Use pt-OSC/gh-ost for live schema changes |
| 9 | No slow query log | Unknown performance regress | Enable slow_query_log, long_query_time=1 |
| 10 | Over-indexing | Slow writes, wasted space | Remove unused indexes: SELECT * FROM pg_unused_indexes |
| 11 | Hot replica promotions | Data loss if lagging | Use Orchestrator/repmgr with pre-flight checks |
| 12 | String dates in WHERE | Full table scan | Use DATE type + index, not string comparisons |
15. Quick Reference
```bash
MySQL: status, process, replication
mysqladmin -u root -p status
mysql -e "SHOW PROCESSLIST;"
mysql -e "SHOW SLAVE STATUS\G"
PostgreSQL: connections, locks, bloat
psql -c "SELECT COUNT(*) FROM pg_stat_activity;"
psql -c "SELECT * FROM pg_locks WHERE NOT granted;"
psql -c "SELECT schemaname, relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;"
Redis: memory, connections, slow log
redis-cli INFO memory
redis-cli INFO clients
redis-cli SLOWLOG GET 10
redis-cli CLIENT LIST
Backup verification
pg_restore --list backup.dump | head # List contents
mysqlcheck --all-databases --check # MySQL table check