PostgreSQL performance tuning — shared_buffers, EXPLAIN i pg_stat_statements
Opublikowano: 10 kwietnia 2026 · Kategoria: VPS / Bazy danych
Domyślna konfiguracja PostgreSQL jest skrojona pod minimalny serwer z 128 MB RAM —
shared_buffers = 128MB, work_mem = 4MB. Na serwerze produkcyjnym z
8-32 GB RAM te wartości to przepis na powolne zapytania i przeciążony dysk. Ten przewodnik
pokazuje jak znaleźć wąskie gardła, dostosować parametry i zrozumieć plany zapytań.
pg_tune — kalkulator parametrów
Zamiast zgadywać wartości, użyj kalkulatora pgtune.leopard.in.ua — wpisujesz RAM, CPU, typ dysku (HDD/SSD/NVMe) i profil pracy (Web, OLTP, Data Warehouse, Desktop, Mixed) i dostajesz gotowy blok konfiguracyjny. Poniżej typowy output dla serwera 8 GB RAM / 4 CPU / SSD / profil Web:
# /etc/postgresql/16/main/postgresql.conf # pg_tune output dla: 8 GB RAM, 4 CPU, SSD, profil Web # Pamięć shared_buffers = 2GB # 25% RAM effective_cache_size = 6GB # szacunek dostępnego OS cache (ok. 75% RAM) work_mem = 10MB # per-operacja (sort, hash join) — uważaj na wiele połączeń! maintenance_work_mem = 512MB # VACUUM, CREATE INDEX, ALTER TABLE # Planer zapytań random_page_cost = 1.1 # SSD: 1.1, HDD: 4.0 (domyślnie) effective_io_concurrency = 200 # SSD: 200, HDD: 2 seq_page_cost = 1.0 # Checkpoint i WAL wal_buffers = 16MB checkpoint_completion_target = 0.9 min_wal_size = 1GB max_wal_size = 4GB # Połączenia max_connections = 100 # dla pgBouncer zmniejsz do 20-30 shared_preload_libraries = 'pg_stat_statements' # do profilowania # Logowanie wolnych zapytań log_min_duration_statement = 1000 # loguj zapytania > 1s log_checkpoints = on log_connections = off log_lock_waits = on
# Zastosuj zmiany
sudo systemctl reload postgresql
# Sprawdź bieżące wartości
psql -U postgres -c "SHOW shared_buffers;"
psql -U postgres -c "SHOW work_mem;"
psql -U postgres -c "SELECT name, setting, unit FROM pg_settings WHERE name IN ('shared_buffers','work_mem','effective_cache_size');" EXPLAIN ANALYZE — analiza planów zapytań
EXPLAIN ANALYZE wykonuje zapytanie i pokazuje rzeczywisty plan, czasy i liczby wierszy.
Dodaj BUFFERS żeby zobaczyć trafienia cache bufora — kluczowe dla diagnostyki I/O:
-- Podstawowe EXPLAIN EXPLAIN SELECT * FROM orders WHERE user_id = 123; -- EXPLAIN ANALYZE — rzeczywiste wykonanie (uwaga: modyfikuje dane!) EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123; -- Pełna diagnostyka: BUFFERS + FORMAT JSON (do pgExplain.dalibo.com) EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT o.id, o.total, u.email FROM orders o JOIN users u ON u.id = o.user_id WHERE o.created_at > NOW() - INTERVAL '30 days' ORDER BY o.created_at DESC LIMIT 100; -- Interpretacja kluczowych węzłów: -- Seq Scan → pełny skan tabeli (brak indeksu lub zbyt mało selektywny) -- Index Scan → skan przez indeks (dobry wynik) -- Index Only Scan → dane wyłącznie z indeksu (najszybszy) -- Hash Join, Merge Join → join dużych tabel (sprawdź work_mem) -- Nested Loop → join małych tabel (OK) lub duże tabele bez indeksu (problem) -- Sprawdź "rows estimated vs actual": -- Duże rozbieżności = nieaktualne statystyki → ANALYZE tabela ANALYZE orders;
pg_stat_statements — znajdź najwolniejsze zapytania
-- Włącz rozszerzenie (wymagane shared_preload_libraries = 'pg_stat_statements') CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- Top 10 najwolniejszych zapytań (całkowity czas) SELECT LEFT(query, 80) AS query_snippet, calls, ROUND(total_exec_time::numeric, 2) AS total_ms, ROUND((total_exec_time / calls)::numeric, 2) AS avg_ms, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; -- Top 10 najczęściej wywoływanych SELECT LEFT(query, 80) AS query_snippet, calls, ROUND((total_exec_time / calls)::numeric, 2) AS avg_ms FROM pg_stat_statements ORDER BY calls DESC LIMIT 10; -- Zapytania z największą ilością odczytów z dysku (shared_blks_read) SELECT LEFT(query, 80) AS query_snippet, shared_blks_hit, shared_blks_read, ROUND(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 2) AS hit_ratio FROM pg_stat_statements WHERE shared_blks_hit + shared_blks_read > 0 ORDER BY shared_blks_read DESC LIMIT 10; -- Reset statystyk (po dostosowaniu konfiguracji) SELECT pg_stat_statements_reset();
Indeksy — BRIN, GiST i partial
Poza standardowym B-tree PostgreSQL oferuje wyspecjalizowane typy indeksów. Poniższa tabela pomaga wybrać właściwy:
| Typ indeksu | Kiedy używać | Zalety | Wady |
|---|---|---|---|
| B-tree (domyślny) | Większość kolumn, =, <, >, BETWEEN, LIKE 'abc%' | Wszechstronny, szybki | Duży rozmiar, overhead na INSERT |
| BRIN | Duże tabele z korelacją fizyczną (timestamp, serial) | Minimalny rozmiar (kilka MB), fast INSERT | Tylko przybliżone dopasowanie, powolny dla losowych zapytań |
| GiST | Dane geometryczne, full-text search, range types | PostGIS, tsvector, inet, tsrange | Wolniejszy od B-tree dla prostych typów |
| GIN | JSONB, arrays, full-text search (tsvector) | Szybkie wyszukiwanie w kolekcjach | Duży rozmiar, wolny UPDATE |
| Partial index | Podzbiór wierszy z warunkiem WHERE | Mały, szybki dla filtrowanych zapytań | Działa tylko gdy WHERE pasuje do definicji indeksu |
| Covering index (INCLUDE) | Index Only Scan dla wybranych kolumn | Eliminuje heap access, szybkie SELECT | Większy rozmiar niż zwykły indeks |
-- BRIN dla tabeli logów (miliony wierszy, chronologicznie wstawianych)
CREATE INDEX CONCURRENTLY idx_logs_created_brin
ON access_logs USING BRIN (created_at)
WITH (pages_per_range = 128);
-- GiST dla PostGIS (geolokalizacja)
CREATE INDEX idx_locations_geom
ON locations USING GIST (coordinates);
-- Partial index — tylko aktywne zamówienia (nie archiwum)
CREATE INDEX idx_orders_active_user
ON orders (user_id, created_at)
WHERE status NOT IN ('completed', 'cancelled', 'refunded');
-- Covering index — Index Only Scan dla typowego zapytania
CREATE INDEX idx_products_covering
ON products (category_id, is_active)
INCLUDE (name, price, slug);
-- Sprawdź czy indeks jest używany
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC; -- indeksy z idx_scan=0 to kandydaci do usunięcia
-- Znajdź brakujące indeksy (tabele z dużą liczbą seq_scan)
SELECT schemaname, relname, seq_scan, idx_scan,
n_live_tup,
ROUND(seq_scan::numeric / NULLIF(seq_scan + idx_scan, 0) * 100, 1) AS seq_scan_pct
FROM pg_stat_user_tables
WHERE seq_scan > 100
ORDER BY seq_scan DESC; Autovacuum — tuning dla szybkich tabel
-- Globalne parametry autovacuum w postgresql.conf autovacuum = on # nigdy nie wyłączaj! autovacuum_max_workers = 3 autovacuum_naptime = 1min # co ile sprawdzać tabele autovacuum_vacuum_scale_factor = 0.05 # uruchom VACUUM po 5% dead tuples (domyślnie 20%) autovacuum_analyze_scale_factor = 0.02 # uruchom ANALYZE po 2% zmian (domyślnie 10%) autovacuum_cost_delay = 2ms # SSD: 2ms (domyślnie 20ms) autovacuum_cost_limit = 400 # SSD: 400 (domyślnie 200) -- Per-tabela override dla tabeli z intensywnym ruchem ALTER TABLE orders SET ( autovacuum_vacuum_scale_factor = 0.01, autovacuum_analyze_scale_factor = 0.005, autovacuum_vacuum_cost_delay = 0 ); -- Sprawdź stan autovacuum SELECT relname, n_dead_tup, last_vacuum, last_autovacuum, last_analyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10; -- Sprawdź bloat tabel (puchnięcie) SELECT tablename, pg_size_pretty(pg_total_relation_size(quote_ident(tablename))) AS total_size, pg_size_pretty(pg_relation_size(quote_ident(tablename))) AS table_size FROM pg_tables WHERE schemaname = 'public' ORDER BY pg_total_relation_size(quote_ident(tablename)) DESC;