Database / Performance

PostgreSQL optymalizacja

EXPLAIN ANALYZE, B-tree i GIN indeksy, RANGE partitioning, PgBouncer connection pooling i Patroni HA — jak wycisnąć maksimum z PostgreSQL w produkcji.

EXPLAIN ANALYZE

Query analysis

Index Only Scan

Pokrywający indeks

PgBouncer

Connection pooler

Patroni + etcd

Auto failover

6 kluczowych technik optymalizacji

Od analizy zapytań przez tunowanie indeksów po replikację i wysoką dostępność.

Technika	Obszar	Kiedy stosować	Narzędzia	Efekt
EXPLAIN ANALYZE	Query Analysis	Zawsze przy slow query	pg_stat_statements, auto_explain	Identyfikacja bottleneck
Index Tuning	Read Performance	Seq Scan na dużych tabelach	B-tree, GIN, GiST, Partial, Covering	10-1000x szybsze czytanie
Partitioning	Skalowanie dużych tabel	Tabele > kilkaset GB	RANGE/HASH/LIST + pg_partman	Query pruning, szybki DROP
Connection Pooling	Concurrency	Wiele połączeń aplikacji	PgBouncer (transaction mode)	Mniej pamięci, wyższy throughput
Vacuum / Autovacuum	Bloat i Freeze	Duże operacje UPDATE/DELETE	VACUUM ANALYZE, autovacuum tuning	Brak bloat, aktualne statystyki
Streaming Replication	High Availability	Produkcja (obowiązkowe)	Patroni, pg_auto_failover	Zero downtime failover

Często zadawane pytania

Jak optymalizować zapytania w PostgreSQL — EXPLAIN ANALYZE i query planner?

EXPLAIN ANALYZE: narzędzie do analizy planu wykonania zapytania. EXPLAIN — pokazuje plan bez wykonania. EXPLAIN ANALYZE — wykonuje i pokazuje rzeczywisty czas. EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ... — pełna analiza z informacją o buffer cache. Kluczowe węzły planu: Seq Scan — skanowanie całej tabeli (pełny scan, brak indeksu lub mały filtr). Index Scan — użycie indeksu do znalezienia wierszy. Index Only Scan — wszystkie dane z indeksu (najszybszy). Bitmap Index Scan — efektywny dla wielu wierszy z indeksu. Hash Join — łączenie przez hash table (duże zestawy). Nested Loop — zagnieżdżona pętla (małe zestawy, indeks na inner). Merge Join — sortowanie i scalanie. Koszty: first_value (koszt startu) .. second_value (koszt totalu). Niższy = lepszy. Aktuals: rzeczywisty czas (ms). actual rows vs estimated rows — duża różnica = nieaktualne statystyki. VACUUM ANALYZE: aktualizuje statystyki dla query plannera. Automatyczny autovacuum (domyślnie włączony). pg_stat_statements: rozszerzenie zbierające statystyki per zapytanie. Najdroższe zapytania: SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 20. pgBadger: analiza logów PostgreSQL. Slow query log (log_min_duration_statement = 1000ms). Raporty top queries. auto_explain: automatycznie loguj EXPLAIN dla wolnych zapytań.

PostgreSQL indeksy — B-tree, GIN, GiST, BRIN, partial, covering?

B-tree (domyślny): idealny dla equality (=) i range queries (<, >, BETWEEN). Operatory: =, <, >, <=, >=, BETWEEN, LIKE 'prefix%' (tylko prefix!). Composite index: INDEX ON orders(user_id, created_at) — kolejność pól ma znaczenie. Zapytanie WHERE user_id = X AND created_at > Y — użyje indeksu. WHERE created_at > Y — nie użyje (user_id musi być pierwsze lub indeks na created_at). Hash Index: tylko equality (=). Szybszy niż B-tree dla equality. Nie wspiera range. GIN (Generalized Inverted Index): dla typów złożonych. Full-text search (tsvector). JSONB (zawieranie '@>', klucze '?'). Arrays. GiST (Generalized Search Tree): geometric types (PostGIS). Full-text search (alternatywa do GIN). Exclusion constraints. BRIN (Block Range Index): bardzo małe (kilka KB). Dla tabel z naturalnym porządkiem (timestamp, auto-increment ID). Nie dla random access. Partial Index: CREATE INDEX ON orders(user_id) WHERE status = 'pending'. Indeks tylko dla aktywnych zamówień. Mniejszy, szybszy. Covering Index (INCLUDE): CREATE INDEX ON orders(user_id) INCLUDE (total_amount). Index Only Scan możliwy dla total_amount bez heap access. Concurrent Index: CREATE INDEX CONCURRENTLY — bez blokowania tabeli. Dłużej buduje ale nie blokuje. Expression Index: CREATE INDEX ON users(LOWER(email)) — dla case-insensitive search. Monitoring indeksów: pg_stat_user_indexes — użycie indeksów. Wykryj nieużywane indeksy.

PostgreSQL partitioning — jak podzielić dużą tabelę?

Partitioning: podział dużej tabeli na mniejsze fizyczne części (partycje) z zachowaniem jednego logicznego interfejsu. Typy partycjonowania: RANGE Partitioning: CREATE TABLE orders (id, created_at, ...) PARTITION BY RANGE (created_at). Partycja per miesiąc lub rok. Stara partycja może być przenoszona do cold storage. HASH Partitioning: CREATE TABLE users PARTITION BY HASH (user_id). Równomierny podział danych między N partycji. Dobre dla równomiernego rozkładu. LIST Partitioning: CREATE TABLE events PARTITION BY LIST (region). Każda partycja = lista wartości (EU, US, APAC). Zalety partitioning: query pruning — PostgreSQL skanuje tylko partycje które mogą zawierać wyniki. Szybsze DROP TABLE dla starych danych (zamiast DELETE). Parallel query per partycja. Zarządzanie archiwizacją. Wady: joins między partycjonowanymi tabelami mogą być wolne. Złożoność administracyjna. Constraint exclusion: planista wyklucza partycje na podstawie WHERE clause. partition_pruning = on (domyślnie). pg_partman: rozszerzenie do automatycznego zarządzania partycjami. Automatyczne tworzenie nowych partycji. Automatyczne usuwanie starych. Tablespace per partycja: stare partycje na tańszym storage (HDD). Nowe na SSD. Partition pruning sprawdzenie: EXPLAIN pokazuje które partycje są skanowane.

Connection pooling — PgBouncer, pgpool-II i connection limits?

Problem bez connection pooling: każde połączenie do PostgreSQL = osobny proces (fork). 1000 jednoczesnych połączeń = 1000 procesów. Duże zużycie RAM (~5-10MB per połączenie). Limit max_connections domyślnie 100. PgBouncer: lekki connection pooler. Tryby: Session mode — jedno połączenie per klient session (jak bez pooler, ale z recyclingiem). Transaction mode — połączenie przydzielane na czas transakcji. Statement mode — połączenie przydzielane na jedno statement. Transaction mode: najlepszy dla bezstanowych aplikacji. Nie działa z: SET statements (per-session). Named prepared statements (z workaroundem pgbouncer_prepared_statements). LISTEN/NOTIFY. Architektura: Aplikacja -> PgBouncer (lokalnie lub centralnie) -> PostgreSQL. PgBouncer maintains pool of X connections do PostgreSQL. Setki klientów na X połączeń. pgpool-II: więcej funkcji: load balancing read między replikami. Connection pooling. Query caching (rzadko używane). Replication. Cięższy niż PgBouncer. Supabase Pooler (Supavisor): nowoczesny pooler napisany w Elixir. Multi-tenant. Connection limits PostgreSQL: max_connections — limit połączeń. superuser_reserved_connections — dla adminów. Zalecenia: max_connections = (RAM_GB * 100) / 10 (zgrubnie). Z PgBouncer: PgBouncer pool = 20-30 połączeń do PostgreSQL. Aplikacja -> 1000 połączeń -> PgBouncer -> 25 połączeń -> PostgreSQL.

PostgreSQL replication i high availability — streaming replication, Patroni?

Streaming Replication (WAL): PostgreSQL wysyła WAL (Write-Ahead Log) do repliki w czasie rzeczywistym. Synchronous replication: primary czeka na ACK od repliki przed COMMIT. Gwarancja zero data loss. Wada: wyższa latencja write. Asynchronous replication: primary nie czeka. Niższa latencja. Ryzyko: kilka sekund data loss przy failover. Replica lag: monitoring replication lag (SELECT pg_replication_lag()). Alert gdy lag > threshold. Czytanie z repliki: aplikacja może wysyłać READ do repliki. Redukuje load na primary. PgBouncer lub HAProxy do routing read/write. Patroni: high availability framework dla PostgreSQL. Zarządza failover automatycznie. Używa DCS (Distributed Configuration Store): etcd, Consul, ZooKeeper. Primary election: Patroni wybiera nową primary po awarii. Automatyczny failover w 30-60 sekund. pg_auto_failover (Citus): prostszy niż Patroni. Citus: extension dla distributed PostgreSQL. Sharding. Równoległe zapytania. Timescaledb: PostgreSQL extension dla time-series. Automatyczne partitioning po czasie. Compression. Continuous aggregates. pg_logical: logical replication (selektywna replikacja tabel). Pglogical dla cross-version replication. Read replicas w cloud: AWS RDS Read Replica. Supabase replicas. Neon (serverless PostgreSQL) — automatic branching.

Czytaj dalej