Tuning de performanță PostgreSQL pentru workload-uri de producție

Configurația implicită a PostgreSQL este gândită pentru compatibilitate, nu pentru performanță. Presupune 128MB de RAM, câteva conexiuni concurente și workload-uri ușoare. Dacă rulezi trafic de producție pe setările implicite, lași 80% din hardware nefolosit.

Administrăm clustere Postgres care duc de toate, de la backend-uri SaaS până la date de tip time-series. Iată cum le tunăm.

Configurarea memoriei

Cele două setări cu cel mai mare impact în PostgreSQL:

shared_buffers

Cache-ul intern al PostgreSQL. Valoarea implicită este 128MB. Este absurd pentru un server de producție.

-- Default: 128MB
-- Recommendation: 25% of total RAM
-- For a 64GB server:
shared_buffers = '16GB'

De ce 25% și nu mai mult? PostgreSQL se bazează și pe page cache-ul sistemului de operare. Dacă setezi shared_buffers prea sus (>40% din RAM), performanța poate chiar să scadă, pentru că faci cache dublu și lași mai puțin loc pentru OS.

effective_cache_size

Îi spune planificatorului de query-uri cât cache total este disponibil (shared_buffers + cache-ul OS-ului). Nu alocă memorie — doar îl ajută pe planner să ia decizii mai bune:

-- Recommendation: 75% of total RAM
-- For a 64GB server:
effective_cache_size = '48GB'

work_mem

Memoria alocată per operație pentru sortări, hash join-uri și operații similare. Atenție — este per operație, nu per conexiune. Un query complex cu 5 hash join-uri folosește 5× work_mem.

-- Default: 4MB
-- Recommendation: depends on (RAM / max_connections / expected_operations)
-- For 64GB RAM, 200 connections:
work_mem = '64MB'

-- For analytical queries (batch jobs, reporting):
-- Set per-session: SET work_mem = '512MB';

maintenance_work_mem

Memoria pentru operațiile de mentenanță: VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY.

-- Default: 64MB
-- Recommendation: 1-2GB (these run infrequently but benefit from more memory)
maintenance_work_mem = '2GB'

Gestionarea conexiunilor

Problema conexiunilor

Fiecare conexiune PostgreSQL este un proces (nu un thread). Fiecare proces consumă ~5-10MB de RAM. La 500 de conexiuni, înseamnă 2.5-5GB doar pentru overhead-ul conexiunilor. La 1000, ai o problemă.

-- Don't set this higher than necessary
max_connections = 200

Dar dacă ai nevoie de 1000 de conexiuni concurente? Folosește un connection pooler.

PgBouncer

PgBouncer stă între aplicația ta și PostgreSQL. 1000 de conexiuni din aplicație se mapează pe 50 de conexiuni reale la baza de date:

; pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
pool_mode = transaction    ; release connection after each transaction
default_pool_size = 50     ; 50 actual Postgres connections
max_client_conn = 1000     ; accept up to 1000 app connections

Transaction pooling (pool_mode = transaction) este cel mai eficient mod. Conexiunea este returnată în pool după fiecare tranzacție, nu după ce clientul se deconectează.

Atenție: transaction pooling strică comenzile SET, LISTEN/NOTIFY și prepared statements care se întind pe mai multe tranzacții. Dacă ai nevoie de ele, folosește modul session pentru pool-urile respective.

Vacuum și autovacuum

MVCC-ul din PostgreSQL (Multi-Version Concurrency Control) înseamnă că UPDATE și DELETE nu șterg efectiv versiunile vechi ale rândurilor. VACUUM este cel care le curăță.

Dacă autovacuum rămâne în urmă, tabela se umflă, query-urile încetinesc și, în cele din urmă, riști transaction ID wraparound — cel mai înfricoșător mod de eșec din PostgreSQL.

-- More aggressive autovacuum for high-write tables
autovacuum_vacuum_scale_factor = 0.05     -- default 0.2 (vacuum at 5% dead tuples vs 20%)
autovacuum_analyze_scale_factor = 0.025   -- default 0.1
autovacuum_vacuum_cost_delay = 2          -- default 2ms (how much vacuum sleeps)
autovacuum_max_workers = 6                -- default 3 (more workers for more tables)
autovacuum_naptime = 15                   -- default 60s (check more frequently)

Pentru tabelele mari, cu multe update-uri, setează override-uri per tabelă:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 1000
);

Monitorizează starea vacuum-ului:

SELECT schemaname, relname, 
       n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / greatest(n_live_tup, 1) * 100, 1) as dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables 
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

Strategia de indexare

Problema indexului lipsă

Problema de performanță nr. 1 pe care o găsim în audituri: indecși lipsă. PostgreSQL îți spune despre asta — trebuie doar să te uiți:

-- Find sequential scans on large tables (missing indexes)
SELECT schemaname, relname, seq_scan, seq_tup_read,
       idx_scan, idx_tup_fetch,
       pg_size_pretty(pg_relation_size(relid)) as size
FROM pg_stat_user_tables
WHERE seq_scan > 100 
  AND pg_relation_size(relid) > 10485760  -- > 10MB
ORDER BY seq_tup_read DESC;

Indecși parțiali

Nu indexa tot. Dacă 95% din query-urile tale filtrează după status = 'active', folosește un index parțial:

-- Instead of indexing all 10M rows:
CREATE INDEX idx_orders_status ON orders(created_at);

-- Index only the rows that matter (probably 500K):
CREATE INDEX idx_orders_active ON orders(created_at)
WHERE status = 'active';

Index mai mic → încape în memorie → lookup-uri mai rapide.

Indecși de acoperire (INCLUDE)

Dacă un query are nevoie doar de date care există deja în index, PostgreSQL poate sări complet peste citirea tabelei:

-- Query: SELECT email, name FROM users WHERE email = 'user@example.com';

-- Regular index: index lookup → table lookup (2 reads)
CREATE INDEX idx_users_email ON users(email);

-- Covering index: index lookup only (1 read)
CREATE INDEX idx_users_email ON users(email) INCLUDE (name);

Mentenanța indecșilor

Indecșii se degradează în timp, mai ales pe workload-uri cu multe UPDATE/DELETE. Reindexează periodic:

-- Check index bloat
SELECT indexrelname, 
       pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
       idx_scan as times_used
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND pg_relation_size(indexrelid) > 1048576
ORDER BY pg_relation_size(indexrelid) DESC;

-- Reindex concurrently (no lock, but takes longer)
REINDEX INDEX CONCURRENTLY idx_orders_active;

Optimizarea query-urilor

EXPLAIN ANALYZE este cel mai bun prieten al tău

Nu ghici niciodată. Măsoară întotdeauna:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
  AND o.created_at > now() - interval '7 days';

La ce să te uiți:

• Seq Scan pe tabele mari → index lipsă
• Nested Loop cu număr mare de rânduri → ia în calcul un hash join (crește work_mem)
• Buffers: shared read (mare) → datele nu sunt în cache, ai nevoie de mai mult shared_buffers sau query-ul atinge prea multe date
• Sort Method: external merge → work_mem prea mic pentru acest query

pg_stat_statements

Cea mai valoroasă extensie. Urmărește performanța fiecărui query:

CREATE EXTENSION pg_stat_statements;

-- Top 10 queries by total time
SELECT query, calls, 
       round(total_exec_time::numeric, 2) as total_ms,
       round(mean_exec_time::numeric, 2) as avg_ms,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Trecem prin asta săptămânal pentru fiecare bază de date de producție pe care o administrăm. Primele 5 query-uri după timp total sunt întotdeauna locul în care se ascund cele mai mari câștiguri.

WAL și checkpoint-uri

Setările Write-Ahead Log (WAL) afectează performanța la scriere și timpul de recuperare după crash:

-- Larger WAL buffers for write-heavy workloads
wal_buffers = '64MB'

-- Spread checkpoints over time (reduce I/O spikes)
checkpoint_completion_target = 0.9

-- Larger checkpoint distance (fewer, larger checkpoints)
max_wal_size = '4GB'
min_wal_size = '1GB'

Baseline-ul nostru de producție

Pentru un server cu 64GB RAM, 16 core-uri și stocare NVMe:

-- Memory
shared_buffers = '16GB'
effective_cache_size = '48GB'
work_mem = '64MB'
maintenance_work_mem = '2GB'

-- Connections (use PgBouncer)
max_connections = 200

-- WAL
wal_buffers = '64MB'
max_wal_size = '4GB'
checkpoint_completion_target = 0.9

-- Query planner
random_page_cost = 1.1        -- NVMe: nearly same as seq read
effective_io_concurrency = 200 -- NVMe: high parallelism

-- Autovacuum
autovacuum_max_workers = 6
autovacuum_vacuum_scale_factor = 0.05
autovacuum_naptime = 15

-- Logging
log_min_duration_statement = 500  -- Log queries > 500ms
log_checkpoints = on
log_lock_waits = on

Nu ghici, măsoară

Fiecare recomandare de aici este un punct de plecare. Workload-ul tău este unic. Folosește pg_stat_statements, EXPLAIN ANALYZE și pg_stat_user_tables ca să iei decizii de tuning bazate pe date.

Rulezi Postgres pe infrastructura noastră? Tuning-ul de performanță este inclus în oferta noastră de baze de date administrate. Îl rulezi în altă parte? Facem și audituri.