Database performance tuning: 5 kroków do 50% szybszych zapytań

Wprowadzenie

Optymalizacja bazy danych to jedna z najskuteczniejszych metod poprawy wydajności aplikacji. W tym przewodniku pokażemy 5 konkretnych kroków, które przyniosły 50% poprawę wydajności w realnym projekcie.

Krok 1: Audit istniejących zapytań

Identyfikacja slow queries

-- Włączenie logowania slow queries
ALTER SYSTEM SET log_min_duration_statement = '1000';
SELECT pg_reload_conf();

-- Analiza najwolniejszych zapytań
SELECT 
  query,
  calls,
  total_time,
  mean_time,
  rows
FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;

Typowe problemy:

• Brak indeksów na kolumnach WHERE
• N+1 queries w aplikacji
• Niepotrzebne JOINy
• Skanowanie całych tabel

Krok 2: Optymalizacja indeksów

Przed optymalizacją:

-- Wolne zapytanie (1.2s)
SELECT * FROM orders 
WHERE customer_id = 123 
AND status = 'pending' 
AND created_at > '2024-01-01';

Po dodaniu kompozytowego indeksu:

-- Szybkie zapytanie (45ms)
CREATE INDEX idx_orders_customer_status_date 
ON orders (customer_id, status, created_at);

Wyniki:

• Query time: 1200ms → 45ms (-96%)
• Index size: 15MB
• Maintenance overhead: Minimalny

Krok 3: Connection pooling

Problem

Aplikacja tworzyła nowe połączenie dla każdego requestu:

// Przed - złe podejście
app.get('/api/users', async (req, res) => {
  const client = new Client(dbConfig);
  await client.connect();
  // query
  await client.end();
});

Rozwiązanie - PgBouncer

# pgbouncer.ini
[databases]
myapp = host=localhost port=5432 dbname=myapp

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
server_reset_query = DISCARD ALL

Wyniki:

• Connection time: 50ms → 1ms
• Concurrent users: 100 → 1000
• Memory usage: -60%

Krok 4: Query optimization

Przykład optymalizacji JOIN

Przed:

-- Wolne zapytanie z niepotrzebnym JOIN
SELECT o.*, u.name, u.email, p.name as product_name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.status = 'completed'
AND o.created_at > '2024-01-01';

Po:

-- Szybsze zapytanie z EXISTS
SELECT o.*, 
       (SELECT name FROM users WHERE id = o.user_id) as user_name,
       (SELECT email FROM users WHERE id = o.user_id) as user_email
FROM orders o
WHERE o.status = 'completed'
AND o.created_at > '2024-01-01'
AND EXISTS (
  SELECT 1 FROM order_items oi 
  WHERE oi.order_id = o.id
);

Krok 5: PostgreSQL configuration tuning

Kluczowe parametry:

# postgresql.conf optimizations
shared_buffers = '4GB'              # 25% of RAM
effective_cache_size = '12GB'       # 75% of RAM
work_mem = '256MB'                  # For complex queries
maintenance_work_mem = '1GB'        # For VACUUM, CREATE INDEX
checkpoint_completion_target = 0.9
wal_buffers = '64MB'
random_page_cost = 1.1              # For SSD storage

Wyniki optymalizacji:

| Metryka | Przed | Po | Poprawa | |---------|-------|----|---------:| | Avg query time | 250ms | 125ms | -50% | | 95th percentile | 2.1s | 800ms | -62% | | Throughput | 1,000 req/s | 2,200 req/s | +120% | | CPU usage | 75% | 45% | -40% |

Monitoring i alerting

Kluczowe metryki do monitorowania:

-- Connection monitoring
SELECT state, count(*) 
FROM pg_stat_activity 
GROUP BY state;

-- Lock monitoring  
SELECT mode, count(*) 
FROM pg_locks 
GROUP BY mode;

-- Cache hit ratio
SELECT 
  schemaname,
  tablename,
  heap_blks_hit/(heap_blks_hit+heap_blks_read) as cache_hit_ratio
FROM pg_statio_user_tables
WHERE heap_blks_read > 0;

Wnioski

1. Measurement first: Nie optymalizuj na ślepo
2. Index strategy: Kompozytowe indeksy > pojedyncze
3. Connection pooling: Must-have dla production
4. Regular maintenance: VACUUM, ANALYZE, REINDEX
5. Monitor continuously: Performance degrades over time

---

Potrzebujesz pomocy z optymalizacją bazy danych? Skontaktuj się z nami.