Database Performance Optimization: PostgreSQL Production Tips

Database Performance Optimization: PostgreSQL Production Tips

Database slow query nedeniyle application performance %70 düşebilir. PostgreSQL optimization tricks production'da fark yaratır.

1. Index Strategy

❌ Slow Query:

SELECT * FROM users WHERE email = '[email protected]';
-- Seq Scan: 500ms

✅ Indexed:

CREATE INDEX idx_users_email ON users(email);
-- Index Scan: 2ms

Advanced: Partial Index

CREATE INDEX idx_active_users ON users(email) 
WHERE status = 'active';
-- Smaller index, faster

2. Query Optimization

EXPLAIN ANALYZE kullan:

EXPLAIN ANALYZE
SELECT u.*, o.total 
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > NOW() - INTERVAL '30 days';

Execution plan'a bak:

• Seq Scan → Index gerekli
• Nested Loop → JOIN strategy problem
• High cost → Optimize et

3. Connection Pooling

❌ Her request yeni connection:

// App connects directly
const client = new Client({
  host: 'db.example.com',
  port: 5432
});
// Max connections: 100, app crashed

✅ PgBouncer:

# pgbouncer.ini
[databases]
mydb = host=postgres port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 20

10,000 app connections → 20 DB connections. Connection reuse.

4. Vacuum & Analyze

PostgreSQL dead tuples birikir. Performance düşer.

-- Manual vacuum
VACUUM ANALYZE users;

-- Auto vacuum config
ALTER TABLE users SET (autovacuum_vacuum_scale_factor = 0.1);

-- Monitoring
SELECT schemaname, relname, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

5. Partitioning

Büyük tablolar (100M+ rows) partition et:

-- Range partitioning
CREATE TABLE orders (
  id BIGSERIAL,
  user_id INT,
  created_at TIMESTAMP
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2024_01 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE orders_2024_02 PARTITION OF orders
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

Query sadece ilgili partition scan eder. Faster.

6. Replication & Load Balancing

Primary-Replica Setup:

• Writes → Primary
• Reads → Replicas (load balanced)
• Async replication (low latency)

# postgresql.conf (primary)
wal_level = replica
max_wal_senders = 3

# recovery.conf (replica)
primary_conninfo = 'host=primary port=5432'
hot_standby = on

7. Monitoring Queries

Slow queries:

SELECT pid, now() - query_start as duration, query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '5 seconds'
ORDER BY duration DESC;

Lock waits:

SELECT blocked_locks.pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity 
  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
  ON blocking_locks.locktype = blocked_locks.locktype
WHERE NOT blocked_locks.granted;

8. Configuration Tuning

# postgresql.conf
shared_buffers = 4GB           # 25% of RAM
effective_cache_size = 12GB    # 75% of RAM
work_mem = 64MB
maintenance_work_mem = 512MB
max_connections = 200
random_page_cost = 1.1         # SSD
effective_io_concurrency = 200

Sonuç

Database optimization layered approach. Index, query, config, monitoring hepsi gerekli. Devups DB performance audit.