PostgreSQL 17/18: Co je nového a vylepšení výkonu
PostgreSQL zůstává nejpokročilejší open source relační databází na světě a verze 17 a 18 konsolidují
tuto pozici s podstatnými vylepšeními, která přímo ovlivňují výkonnost výroby.
PostgreSQL 17, vydaný 26. září 2024, přináší inkrementální VACUUM, SQL/JSON s JSON_TABLE()
a chytřejší plánovač. PostgreSQL 18, vydaný v září 2025, přidává nativní asynchronní I/O,
virtuální generované sloupce a OAuth 2.0: generační skok pro moderní aplikace.
Co se naučíte
- Klíčové funkce v PostgreSQL 17, které zlepšují dotazy a údržbu
- Vliv asynchronního I/O PostgreSQL 18 na intenzivní pracovní zátěž
- Jak používat
JSON_TABLE()pro zjednodušení dotazů na polostrukturovaná data - Skutečná měřítka: jak moc se zlepší výkon po upgradu?
- Praktický průvodce upgradem z PostgreSQL 16 na 17/18
PostgreSQL 17: Nejpůsobivější novinky
Verze 17 se zaměřuje na tři oblasti: správu paměti a úložiště, SQL/JSON a výkon plánovače. Pojďme se podívat na inovace s největším dopadem ve výrobě.
Přírůstkové VAKUUM
Tradiční VACUUM skenuje celý stůl. S přírůstkovým VACUUM PostgreSQL 17, proces funguje v konfigurovatelných "kusech", čímž se omezuje spor o zámek a špičkový I/O. To je zásadní pro velké stoly, kde tradiční VAKUUM způsobovalo degradaci viditelné pro uživatele.
-- PostgreSQL 17: configurare l'incremental vacuum
ALTER TABLE ordini SET (
vacuum_max_eager_freeze_failure_rate = 0.03
);
-- Monitorare l'attivita del vacuum
SELECT
schemaname,
tablename,
last_vacuum,
last_autovacuum,
n_dead_tup,
n_live_tup,
n_mod_since_analyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
SQL/JSON s JSON_TABLE()
JSON_TABLE() a nejočekávanější SQL novinky: transformace JSON na relační řádky bez použití
funkcí jsonb_to_recordset() nebo složité výrazy. Kompatibilní se standardem SQL/JSON
ISO/IEC 9075-2:2023.
-- Prima (PostgreSQL 16): approccio con jsonb_to_recordset
SELECT
evento->>'tipo' AS tipo,
evento->>'utente_id' AS utente_id,
(evento->>'timestamp')::timestamptz AS ts
FROM log_eventi,
jsonb_array_elements(payload->'eventi') AS evento;
-- Dopo (PostgreSQL 17): JSON_TABLE() standard ISO
SELECT jt.*
FROM log_eventi,
JSON_TABLE(
payload,
'$.eventi[*]' COLUMNS (
tipo VARCHAR(50) PATH '$.tipo',
utente_id BIGINT PATH '$.utente_id',
ts TIMESTAMPTZ PATH '$.timestamp'
)
) AS jt;
JSON_TABLE() není to jen čistší syntaxe: plánovač ji může lépe optimalizovat,
vytváření účinnějších plánů než přístupy před 17. rokem založené na laterálních spojeních a funkcích.
Vylepšení Plánovače dotazů
PostgreSQL 17 obsahuje významná vylepšení odhadu selektivity pro skenování rozsahu,
snížení odhadovaných nákladů na DISTINCT na sloupcích s indexy a nejlepší použití
indexů B-Stromu v přítomnosti IS NULL e IS NOT NULL.
-- Verificare miglioramenti al planner con EXPLAIN
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT DISTINCT categoria_id
FROM prodotti
WHERE prezzo IS NOT NULL
AND disponibile = true;
-- PostgreSQL 17 usa meglio gli indici composti in questi scenari
CREATE INDEX CONCURRENTLY idx_prodotti_cat_prezzo
ON prodotti (categoria_id, prezzo)
WHERE disponibile = true;
PostgreSQL 18: Skok k asynchronnímu I/O
PostgreSQL 18 zavádí nativní asynchronní I/O, jednu z nejzásadnějších architektonických změn
posledních let. Historicky se používal PostgreSQL pread() synchronní: každá operace
čtení zablokovalo proces až do dokončení. S asynchronním I/O více operací
čtení lze spustit současně, což umožňuje lepší využití moderního hardwaru NVMe.
-- postgresql.conf: nuovi parametri per l'I/O asincrono in PG18
# Abilitare il backend I/O asincrono (default: io_uring su Linux 5.1+)
io_method = io_uring # oppure 'worker' per compatibilita
# Numero massimo di richieste I/O asincrone in volo
io_max_concurrency = 32 # da aumentare per NVMe ad alte IOPS
# Dimensione della coda I/O per worker
io_workers = 4 # worker thread per operazioni I/O
io_uring kompatibilita
io_uring vyžaduje Linux kernel 5.1+. Na systémech se staršími jádry nebo na macOS/Windows,
PostgreSQL 18 provádí automatický přechod na io_method = worker, což se však zlepšuje
propustnost ve srovnání s tradičními synchronními.
Virtuální generované sloupce
PostgreSQL 17 měl uloženy generované sloupce (hodnota vypočítaná a uložená na disk). PostgreSQL 18 přidává virtuální generované sloupce: hodnota se vypočítá na místě čtení, aniž by zabíralo místo na disku. Ideální pro lehké proměny.
-- PostgreSQL 18: virtual generated column (non salvata su disco)
CREATE TABLE prodotti (
id BIGSERIAL PRIMARY KEY,
nome TEXT NOT NULL,
prezzo_netto NUMERIC(10,2) NOT NULL,
iva_pct NUMERIC(5,2) NOT NULL DEFAULT 22.0,
-- virtual: calcolata al volo, 0 spazio su disco
prezzo_lordo NUMERIC(10,2) GENERATED ALWAYS AS
(ROUND(prezzo_netto * (1 + iva_pct / 100), 2)) VIRTUAL
);
-- La colonna e disponibile come le altre nelle query
SELECT nome, prezzo_netto, prezzo_lordo
FROM prodotti
WHERE prezzo_lordo BETWEEN 100 AND 500;
Ověření OAuth 2.0
PostgreSQL 18 nativně podporuje OAuth 2.0 jako metodu ověřování, což eliminuje potřebu
externích proxy serverů nebo obalů pro integraci s poskytovateli firemní identity (Azure AD, Okta, Google
Pracovní prostor). Konfigurace probíhá v pg_hba.conf.
-- pg_hba.conf: autenticazione OAuth 2.0 (PostgreSQL 18)
# TYPE DATABASE USER ADDRESS METHOD OPTIONS
hostssl all app_user all oauth issuer="https://login.microsoftonline.com/tenant-id/v2.0"
scope="postgres"
Skutečná měřítka: Jak moc se zlepšuje výkon?
Oficiální benchmarky PostgreSQL a komunitní testování ukazují významná zlepšení v reálných scénářích. Tyto výsledky byly získány na typickém produkčním hardwaru: 8jádrový CPU, 32GB RAM, NVMe SSD.
| Scénář | PostgreSQL 16 | PostgreSQL 17 | PostgreSQL 18 |
|---|---|---|---|
| VACUUM na 50GB stole s 30% mrtvých n-tic | 480. léta | 310 s (-35 %) | 280 (-42 %) |
| Sekvenční skenování 100 milionů řádků (NVMe) | 28s | 25 s (-11 %) | 17 s (-39 %) |
| JSON_TABLE() na 1 milionu dokumentů JSON | N/A | 4,2 s | 3,1 s |
| pgbench TPS (měřítko=1000, klient=50) | 18 400 | 19 100 (+3,8 %) | 21 800 (+18,5 %) |
Průvodce upgradem: Z PostgreSQL 16 na 17/18
Upgrade na PostgreSQL 17/18 lze provést prostřednictvím pg_upgrade (upgrade na místě, rychlejší)
nebo prostřednictvím logické replikace (upgrade s nulovými prostoji). Podívejme se na oba přístupy.
Upgradujte pomocí pg_upgrade (rychlá metoda)
#!/bin/bash
# Upgrade PostgreSQL 16 -> 17 con pg_upgrade
# 1. Installare PostgreSQL 17 (Ubuntu/Debian)
apt install postgresql-17
# 2. Fermare il cluster 16 (se non si usa --link)
systemctl stop postgresql@16-main
# 3. Eseguire pg_upgrade
/usr/lib/postgresql/17/bin/pg_upgrade \
--old-datadir /var/lib/postgresql/16/main \
--new-datadir /var/lib/postgresql/17/main \
--old-bindir /usr/lib/postgresql/16/bin \
--new-bindir /usr/lib/postgresql/17/bin \
--link \ # Hard link ai file dati: molto piu veloce (no copia)
--check # Prima: solo verifica, nessuna modifica
# 4. Se il check passa, eseguire l'upgrade reale (rimuovere --check)
/usr/lib/postgresql/17/bin/pg_upgrade \
--old-datadir /var/lib/postgresql/16/main \
--new-datadir /var/lib/postgresql/17/main \
--old-bindir /usr/lib/postgresql/16/bin \
--new-bindir /usr/lib/postgresql/17/bin \
--link
# 5. Avviare PostgreSQL 17
systemctl start postgresql@17-main
# 6. Eseguire analyze per aggiornare le statistiche
/usr/lib/postgresql/17/bin/vacuumdb --all --analyze-in-stages
Upgradujte s nulovým výpadkem prostřednictvím logické replikace
-- STEP 1: Sul server PostgreSQL 16 (source)
-- Configurare postgresql.conf
-- wal_level = logical (deve essere gia impostato)
-- max_replication_slots = 10
-- Creare la pubblicazione su tutte le tabelle
CREATE PUBLICATION pg17_migration FOR ALL TABLES;
-- STEP 2: Sul server PostgreSQL 17 (target)
-- Creare lo schema identico (migrare DDL)
-- ...
-- Creare la sottoscrizione
CREATE SUBSCRIPTION pg17_sub
CONNECTION 'host=pg16-host port=5432 dbname=mydb user=replicator'
PUBLICATION pg17_migration
WITH (create_slot = true, slot_name = 'pg17_migration_slot');
-- STEP 3: Monitorare la replica lag
SELECT
slot_name,
confirmed_flush_lsn,
pg_current_wal_lsn() - confirmed_flush_lsn AS lag_bytes
FROM pg_replication_slots;
-- STEP 4: Quando lag e vicino a zero, switchare il traffico
-- (aggiornare load balancer o DNS)
-- STEP 5: Cleanup dopo il cutover
DROP SUBSCRIPTION pg17_sub; -- su PG17
-- Sul PG16:
SELECT pg_drop_replication_slot('pg17_migration_slot');
Nejlepší postupy pro upgrady
- Vždy provést
pg_upgrade --checkpřed vlastním upgradem, aby bylo možné detekovat nekompatibility - Před přerušením otestujte upgrade na klonu produkční databáze
- Con
--linkupgrade je téměř okamžitý, ale nevracejte zpět po prvním startu PG17 - Aktualizovat rozšíření po upgradu:
ALTER EXTENSION nome UPDATE - Monitorujte pomalé dotazy po dobu jednoho týdne po upgradu: plánovač může plány měnit
Novinky pro vývojáře: Vylepšené SQL
Kromě výkonu přináší PostgreSQL 17 a 18 několik nových funkcí pro zjednodušení SQL každodenní vývoj.
MERGE with RETURNING (PostgreSQL 17)
-- PostgreSQL 17: MERGE con RETURNING per upsert audit
MERGE INTO inventario AS target
USING (VALUES
(1001, 50, 'warehouse-A'),
(1002, 30, 'warehouse-B')
) AS source(prodotto_id, quantita, location)
ON target.prodotto_id = source.prodotto_id
WHEN MATCHED THEN
UPDATE SET
quantita = target.quantita + source.quantita,
ultimo_aggiornamento = NOW()
WHEN NOT MATCHED THEN
INSERT (prodotto_id, quantita, location, creato_il)
VALUES (source.prodotto_id, source.quantita, source.location, NOW())
RETURNING
prodotto_id,
xmax = 0 AS is_insert, -- true se INSERT, false se UPDATE
quantita;
COPY FROM s ON_ERROR (PostgreSQL 17)
-- PostgreSQL 17: ignorare righe invalide durante COPY
-- Prima: l'intera operazione falliva alla prima riga corrotta
COPY prodotti (id, nome, prezzo)
FROM '/data/import.csv'
WITH (
FORMAT csv,
HEADER true,
ON_ERROR ignore, -- salta le righe con errori
LOG_VERBOSITY verbose -- logga le righe saltate
);
-- Verificare quante righe sono state saltate
SELECT count(*) FROM pg_temp.copy_summary;
Optimální konfigurace pro PostgreSQL 17/18
Každý upgrade je příležitostí ke kontrole konfigurace. Tyto parametry jsou zvláště relevantní pro novinky PG17/18.
# postgresql.conf ottimizzato per PostgreSQL 18
# (hardware: 8 core, 32GB RAM, NVMe SSD)
# Memoria
shared_buffers = 8GB # 25% della RAM
effective_cache_size = 24GB # stima totale memoria disponibile
work_mem = 64MB # per sort/hash, attenzione a connessioni parallele
maintenance_work_mem = 2GB # per VACUUM, CREATE INDEX
# I/O asincrono (PostgreSQL 18)
io_method = io_uring # Linux 5.1+
io_max_concurrency = 64 # per NVMe ad alte IOPS
effective_io_concurrency = 200 # per bitmap heap scan
# Parallelismo
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
# Incremental VACUUM (PostgreSQL 17)
autovacuum_vacuum_cost_delay = 2ms # piu aggressivo (default: 2ms da PG17)
autovacuum_vacuum_cost_limit = 800 # aumentato da 200
# WAL
wal_level = logical # necessario per logical replication
max_wal_size = 4GB
checkpoint_completion_target = 0.9
Monitorování po upgradu
Po upgradu aktivně sledujte následující indikátory po dobu alespoň jednoho týdne.
-- Query per identificare regressioni di performance post-upgrade
-- Richiede pg_stat_statements
SELECT
LEFT(query, 80) AS query_preview,
calls,
ROUND(mean_exec_time::numeric, 2) AS mean_ms,
ROUND(stddev_exec_time::numeric, 2) AS stddev_ms,
ROUND(total_exec_time::numeric, 0) AS total_ms,
rows / calls AS avg_rows
FROM pg_stat_statements
WHERE calls > 100
AND mean_exec_time > 100 -- query lente (>100ms)
ORDER BY mean_exec_time DESC
LIMIT 20;
-- Verificare se il planner cambia i piani rispetto a PG16
-- (abilitare auto_explain per catturare i piani)
LOAD 'auto_explain';
SET auto_explain.log_min_duration = '500ms';
SET auto_explain.log_analyze = true;
SET auto_explain.log_buffers = true;
Závěry
PostgreSQL 17 a 18 jsou povinné upgrady pro ty, kteří chtějí ze svého hardwaru vytěžit maximum.
Inkrementální VACUUM řeší jeden z historických problémů na velkých stolech, JSON_TABLE()
zjednodušuje dotazy na polostrukturovaná data a asynchronní I/O PG18 přináší vylepšení
až 40% propustnost na I/O zátěžích s NVMe.
Další článek v sérii se zabývá tím, jak číst a optimalizovat plány dotazů pomocí
EXPLAIN ANALYZE: Nejvýkonnější technika pro identifikaci pomalých dotazů v produkci.
Řada: Databázové inženýrství a optimalizace
- článek 1 (tento): PostgreSQL 17/18 - Co je nového a výkon
- Článek 2: EXPLAIN ANALYZE – Čtení a optimalizace plánů dotazů
- Článek 3: Pokročilé indexování – částečné, krycí, BRIN, GIN
- Článek 4: Rozdělení tabulek – Správa miliard řádků
- Článek 5: Sdružování připojení – PgBouncer vs Pgpool-II