🗄️ Database Tuning

Database Optimization Checklist

Query Level

• Slow queries identified and analyzed
• EXPLAIN plans reviewed
• Indexes added for WHERE/JOIN/ORDER BY
• N+1 queries eliminated
• SELECT * replaced with specific columns

Index Strategy

• Composite indexes for common patterns
• Index column order optimized
• Covering indexes for read-heavy queries
• Unused indexes removed
• Index bloat monitored

Configuration

• Memory settings tuned (shared_buffers, work_mem)
• Connection pooling configured
• Statement timeout set
• Logging configured for slow queries

Architecture

• Read replicas for read-heavy workloads
• Connection pooler (PgBouncer, ProxySQL)
• Partitioning for large tables
• Archival strategy for old data

PostgreSQL Tuning

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- Find missing indexes
SELECT 
    schemaname, tablename, 
    seq_scan, idx_scan,
    seq_tup_read, idx_tup_fetch
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
AND seq_tup_read > 10000
ORDER BY seq_tup_read DESC;

-- Find unused indexes
SELECT 
    schemaname, tablename, indexname,
    idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND NOT indisunique;

-- Find bloated tables
SELECT 
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
    n_dead_tup,
    last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Index Optimization Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- Before: 3+ seconds (no index, sequential scan)
EXPLAIN ANALYZE
SELECT * FROM orders 
WHERE dispensary_id = 123 
AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

-- Seq Scan on orders  (cost=0.00..45123.00 rows=50000)
-- Filter: ((dispensary_id = 123) AND (status = 'pending'))
-- Sort: created_at DESC
-- Execution Time: 3245.123 ms

-- After: Add composite index
CREATE INDEX CONCURRENTLY idx_orders_lookup
ON orders(dispensary_id, status, created_at DESC);

-- Re-run query
-- Index Scan using idx_orders_lookup (cost=0.42..123.45 rows=20)
-- Execution Time: 12.345 ms

-- 260x faster!

Read Replica Architecture

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

┌─────────────────────────────────────────┐
│              Application                 │
└─────────────────────────────────────────┘
                    │
    ┌───────────────┴───────────────┐
    │                               │
    ▼                               ▼
┌─────────────────┐       ┌─────────────────┐
│    Primary      │──────▶│    Replica      │
│   (Writes)      │  Sync │   (Reads)       │
└─────────────────┘       └─────────────────┘
        │
        │  Writes: INSERT, UPDATE, DELETE
        │  
┌───────▼──────────────────────────────────┐
│  Reads: Tracking, Reports, Analytics     │
│  (Route to replica)                      │
└──────────────────────────────────────────┘

Frequently Asked Questions

What is database performance tuning?

Database performance tuning optimizes query execution, indexing strategies, configuration settings, and hardware utilization to make databases faster. It includes identifying slow queries, analyzing execution plans, and implementing improvements.

How much does database tuning cost?

Database tuning typically costs $120-180 per hour. An initial assessment starts around $3,000-6,000, while thorough optimization projects range from $15,000-50,000+. The ROI is often immediate through reduced infrastructure costs and better user experience.

What databases do you tune?

I work with: PostgreSQL, MySQL, MongoDB, Redis, and Elasticsearch. Each has different tuning approaches, but the principles are similar: understand the workload, measure performance, identify bottlenecks, and implement targeted improvements.

How do you identify slow queries?

I use: database slow query logs, pg_stat_statements (PostgreSQL), explain analyze, APM tools, and monitoring dashboards. I focus on queries that run frequently or consume the most resources, not just the slowest individual queries.

Can you help reduce database costs?

Yes. Tuning often reduces costs by: eliminating unnecessary queries, enabling smaller instance sizes, reducing read replicas needed, and optimizing for cloud pricing. I’ve helped teams reduce database bills by 30-50% through optimization.

Related

Experience:

• Senior Engineer at Drop Delivery - 80% query improvement
• Engineer at Spiio - Time-series optimization
• AI Lead at Anaqua - Vector search tuning

Case Studies:

• Database Scaling for 354% Growth
• Time-Series Optimization

Related Technologies: PostgreSQL, MySQL, Redis, Performance Optimization