PostgreSQL gedraagt zich anders dan SQL Server: MVCC met VACUUM, een andere optimizer, andere wait-events. Een freelance PostgreSQL DBA die uit de SQL Server-wereld komt zonder PostgreSQL-praktijk komt soms slecht uit. Wat een echte PostgreSQL-expert aanpakt.
EXPLAIN ANALYZE als eerste reflex
EXPLAIN (ANALYZE, BUFFERS) op een trage query toont werkelijke kosten, buffer hits versus reads, en parallellisatie. Anders dan SQL Server toont PostgreSQL geen geactualiseerde plan-cache; elke query gaat door de planner tenzij u prepared statements gebruikt.
Indexering en partial indexes
B-tree blijft de standaard. GIN voor full-text en arrays. BRIN voor zeer grote, datum-geordende tabellen (TimescaleDB-stijl). Partial indexes (WHERE-clausule in de index-definitie) voor selectieve filter-patronen. Hash indexes pas vanaf PostgreSQL 10 weer crash-safe.
work_mem, shared_buffers en effective_cache_size
shared_buffers ongeveer 25 procent van het geheugen, effective_cache_size ongeveer 75 procent. work_mem niet te hoog: het wordt per sort-operatie en per node toegekend. Een query met vier hash joins en work_mem 256MB kan 1GB allokeren. Wij tunen op basis van pg_stat_statements.
Parallel query en max_worker_processes
Sinds PostgreSQL 10 is parallel query default. max_parallel_workers_per_gather op 4 of 8 op moderne hardware. Te hoog en u verbrandt CPU op overhead, te laag en grote scans blijven seriael. Per workload-profiel anders.
Connection pooling met PgBouncer
PostgreSQL is process-per-connection. Boven 200 actieve connections op een 32-core machine gaat het pijn doen. PgBouncer in transaction mode voor OLTP, session mode voor session-state. Connection pooling is geen luxe maar een productie-vereiste.
Verwant: Freelance SQL DBA inhuren, PostgreSQL VACUUM tuning.