Databases

Enterprise Database Optimization Guide

Master enterprise database optimisation with PostgreSQL, MySQL, Redis, connection pooling, query optimisation, monitoring, backup strategies, and high availability for production environments.

What you'll learn

Tune PostgreSQL and MySQL configuration for production
Design optimal indexing strategies
Analyse and optimise slow queries with EXPLAIN ANALYZE
Set up connection pooling with PgBouncer
Implement read replicas for horizontal scaling
Configure Redis caching to reduce DB load
Set up automated backups and point-in-time recovery

PostgreSQL Tuning

INI

1# postgresql.conf — key parameters for a 16 GB RAM server
2max_connections         = 200
3shared_buffers          = 4GB        # 25% of RAM
4effective_cache_size    = 12GB       # 75% of RAM
5maintenance_work_mem    = 1GB
6work_mem                = 20MB       # shared_buffers / max_connections
7wal_buffers             = 64MB
8checkpoint_completion_target = 0.9
9random_page_cost        = 1.1        # SSD — reduce from default 4
10effective_io_concurrency = 200       # SSD
11max_wal_size            = 4GB
12min_wal_size            = 1GB
13default_statistics_target = 100
14
15# Apply settings (no restart needed for most)
16sudo systemctl reload postgresql

Indexing Strategies

SQL

1-- Composite index for common query pattern
2CREATE INDEX CONCURRENTLY idx_orders_user_status
3  ON orders (user_id, status)
4  WHERE status != 'cancelled';   -- partial index
5
6-- Full-text search index
7CREATE INDEX idx_courses_fts
8  ON courses USING GIN (to_tsvector('english', title || ' ' || description));
9
10-- BRIN index for time-series data (huge tables, sequential writes)
11CREATE INDEX idx_events_created_at
12  ON events USING BRIN (created_at);
13
14-- Find missing indexes
15SELECT schemaname, tablename, attname, n_distinct, correlation
16FROM pg_stats
17WHERE tablename = 'orders' ORDER BY n_distinct;
18
19-- Find unused indexes (run after 1+ weeks in production)
20SELECT indexname, idx_scan
21FROM pg_stat_user_indexes
22WHERE schemaname = 'public' AND idx_scan = 0;

Query Optimisation

SQL

1-- Analyse a slow query
2EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
3SELECT o.*, u.name
4FROM orders o
5JOIN users u ON u.id = o.user_id
6WHERE o.status = 'pending'
7ORDER BY o.created_at DESC
8LIMIT 20;
9
10-- Find slow queries (requires pg_stat_statements)
11SELECT query, calls, mean_exec_time, total_exec_time
12FROM pg_stat_statements
13ORDER BY mean_exec_time DESC
14LIMIT 10;
15
16-- Log slow queries
17log_min_duration_statement = 500   # ms — in postgresql.conf

Connection Pooling

Bash

1# Install PgBouncer
2sudo apt install -y pgbouncer
3
4# /etc/pgbouncer/pgbouncer.ini
5[databases]
6mydb = host=127.0.0.1 port=5432 dbname=mydb
7
8[pgbouncer]
9listen_addr     = 127.0.0.1
10listen_port     = 6432
11pool_mode       = transaction      # best for web apps
12max_client_conn = 1000
13default_pool_size = 25
14auth_type       = scram-sha-256
15
16sudo systemctl enable --now pgbouncer
17# App now connects to :6432 instead of :5432

Replication & HA

Bash

1# Primary — postgresql.conf
2wal_level             = replica
3max_wal_senders       = 3
4wal_keep_size         = 1GB
5
6# Create replication user
7CREATE USER replicator REPLICATION LOGIN PASSWORD 'securepass';
8
9# Replica — clone from primary
10pg_basebackup -h PRIMARY_IP -U replicator -D /var/lib/postgresql/16/main   -P -Xs -R
11
12# The -R flag writes standby.signal and primary_conninfo automatically
13sudo systemctl start postgresql    # on replica

Redis Caching

TypeScript

1# Cache-aside pattern (Node.js example)
2async function getCourse(id: string): Promise<Course> {
3  const cacheKey = `course:${id}`;
4  const cached = await redis.get(cacheKey);
5  if (cached) return JSON.parse(cached) as Course;
6
7  const course = await db.query('SELECT * FROM courses WHERE id = $1', [id]);
8  await redis.setex(cacheKey, 300, JSON.stringify(course));  // 5 min TTL
9  return course;
10}
11
12// Invalidate on write
13async function updateCourse(id: string, data: Partial<Course>) {
14  await db.query('UPDATE courses SET ... WHERE id = $1', [id]);
15  await redis.del(`course:${id}`);
16}

Backup & Recovery

Bash

1# Logical backup with pg_dump
2pg_dump -Fc -U postgres mydb > /backups/mydb_$(date +%Y%m%d).dump
3
4# Restore
5pg_restore -U postgres -d mydb_new /backups/mydb_20250918.dump
6
7# Continuous WAL archiving (point-in-time recovery)
8# postgresql.conf
9archive_mode = on
10archive_command = 'cp %p /archive/%f'
11restore_command = 'cp /archive/%f %p'

Monitoring

SQL

1# Key metrics to alert on
2-- Active connections vs max_connections
3SELECT count(*) FROM pg_stat_activity WHERE state = 'active';
4
5-- Table bloat (vacuum health)
6SELECT schemaname, tablename, n_dead_tup, n_live_tup,
7  round(100 * n_dead_tup / (n_live_tup + n_dead_tup + 1), 2) AS dead_pct
8FROM pg_stat_user_tables
9ORDER BY dead_pct DESC;
10
11-- Replication lag (on primary)
12SELECT client_addr, state,
13  pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS lag
14FROM pg_stat_replication;