PostgreSQL 17/18: Wat is er nieuw en prestatieverbeteringen
PostgreSQL blijft de meest geavanceerde open-source relationele database ter wereld, en versies 17 en 18 worden geconsolideerd
deze positie met substantiële verbeteringen die een directe impact hebben op de productieprestaties.
PostgreSQL 17, uitgebracht op 26 september 2024, brengt incrementeel VACUUM, SQL/JSON met JSON_TABLE()
en een slimmere planner. PostgreSQL 18, uitgebracht in september 2025, voegt native asynchrone I/O toe,
virtueel gegenereerde kolommen en OAuth 2.0: een generatiesprong voor moderne toepassingen.
Wat je gaat leren
- Belangrijkste functies in PostgreSQL 17 die query's en onderhoud verbeteren
- De impact van PostgreSQL 18 asynchrone I/O op intensieve werklasten
- Hoe te gebruiken
JSON_TABLE()om zoekopdrachten op semi-gestructureerde gegevens te vereenvoudigen - Echte benchmarks: hoeveel verbeteren de prestaties na de upgrade?
- Praktische gids voor het upgraden van PostgreSQL 16 naar 17/18
PostgreSQL 17: het meest impactvolle nieuws
Versie 17 richt zich op drie gebieden: geheugen- en opslagbeheer, SQL/JSON en plannerprestaties. Laten we eens kijken naar de innovaties met de grootste impact in de productie.
Incrementeel VACUÜM
De traditionele VACUUM scant de hele tafel. Met PostgreSQL 17 incrementeel VACUUM, het proces werkt in configureerbare "chunks", waardoor lock-conflicten en piek-I/O worden verminderd. Dit is cruciaal voor grote tafels waar traditioneel VACUUM degradatie veroorzaakte zichtbaar voor gebruikers.
-- 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 met JSON_TABLE()
JSON_TABLE() en het langverwachte SQL-nieuws: transformeer JSON in relationele rijen zonder te gebruiken
functies jsonb_to_recordset() of complexe uitdrukkingen. Compatibel met de SQL/JSON-standaard
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() het is niet alleen een schonere syntaxis: de planner kan deze beter optimaliseren,
het genereren van efficiëntere plannen dan benaderingen van vóór de 17e eeuw, gebaseerd op laterale verbindingen en functies.
Verbeteringen in de Queryplanner
PostgreSQL 17 bevat aanzienlijke verbeteringen in de selectiviteitsschatting voor bereikscans,
verlaging van de geschatte kosten voor DISTINCT op kolommen met indexen en het beste gebruik
van B-Tree-indices in aanwezigheid van 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: de sprong naar asynchrone I/O
PostgreSQL 18 introduceert native asynchrone I/O, een van de meest diepgaande architectonische veranderingen
van de afgelopen jaren. Historisch gezien gebruikte PostgreSQL pread() synchroon: elke bewerking
lezen blokkeerde het proces tot het voltooid was. Met asynchrone I/O, meer bewerkingen
het lezen kan tegelijkertijd worden gestart, waardoor beter gebruik wordt gemaakt van moderne NVMe-hardware.
-- 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-compatibiliteit
io_uring vereist Linux-kernel 5.1+. Op systemen met oudere kernels of op macOS/Windows,
PostgreSQL 18 doet automatisch terugval naar io_method = worker, wat echter verbetert
doorvoer vergeleken met traditioneel synchroon.
Virtueel gegenereerde kolommen
PostgreSQL 17 had de opgeslagen gegenereerde kolommen (waarde berekend en opgeslagen op schijf). PostgreSQL 18 voegt de virtueel gegenereerde kolommen: de waarde wordt ter plekke berekend lezen, zonder schijfruimte in beslag te nemen. Ideaal voor lichte transformaties.
-- 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;
OAuth 2.0-verificatie
PostgreSQL 18 ondersteunt OAuth 2.0 native als authenticatiemethode, waardoor de noodzaak wordt geëlimineerd
van externe proxy's of wrappers om te integreren met huisstijlproviders (Azure AD, Okta, Google
Werkruimte). Configuratie vindt plaats in 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"
Echte benchmarks: hoeveel verbeteren de prestaties?
Officiële PostgreSQL-benchmarks en communitytests laten aanzienlijke verbeteringen zien in echte scenario's. Deze resultaten zijn verkregen op typische productiehardware: 8-core CPU, 32 GB RAM, NVMe SSD.
| Scenario | PostgreSQL 16 | PostgreSQL 17 | PostgreSQL 18 |
|---|---|---|---|
| VACUÜM op een tafel van 50 GB met 30% dode tupels | 480s | 310s (-35%) | 280s (-42%) |
| Sequentiële scan 100 miljoen lijnen (NVMe) | 28s | 25s (-11%) | 17s (-39%) |
| JSON_TABLE() op 1 miljoen JSON-documenten | N.v.t | 4,2 s | 3,1 s |
| pgbench TPS (schaal=1000, klant=50) | 18.400 | 19.100 (+3,8%) | 21.800 (+18,5%) |
Upgradehandleiding: van PostgreSQL 16 naar 17/18
De upgrade naar PostgreSQL 17/18 kan worden gedaan via pg_upgrade (in-place upgrade, sneller)
of via logische replicatie (upgrade zonder downtime). Laten we beide benaderingen bekijken.
Upgrade met pg_upgrade (snelle methode)
#!/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
Upgrade zonder downtime via logische replicatie
-- 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');
Beste praktijken voor upgrades
- Altijd uitvoeren
pg_upgrade --checkvóór de daadwerkelijke upgrade om incompatibiliteiten op te sporen - Test de upgrade op een kloon van de productiedatabase vóór de overgang
- Met
--linkde upgrade is vrijwel onmiddellijk, maar niet terugdraaien na de eerste start van PG17 - Extensies bijwerken na het upgraden:
ALTER EXTENSION nome UPDATE - Houd langzame vragen tot een week na de upgrade in de gaten: de planner kan plannen wijzigen
Nieuws voor ontwikkelaars: Verbeterde SQL
Naast de prestaties bieden PostgreSQL 17 en 18 verschillende nieuwe functies om SQL te vereenvoudigen dagelijkse ontwikkeling.
SAMENVOEGEN met 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;
KOPIEER VAN met 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;
Optimale configuratie voor PostgreSQL 17/18
Elke upgrade is een gelegenheid om de configuratie te herzien. Deze parameters zijn bijzonder relevant voor het nieuws van 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
Controle na upgrade
Houd na de upgrade de volgende indicatoren minimaal een week actief in de gaten.
-- 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;
Conclusies
PostgreSQL 17 en 18 zijn verplichte upgrades voor degenen die het maximale uit hun hardware willen halen.
Incrementeel VACUUM lost een van de historische problemen op grote tafels op, JSON_TABLE()
vereenvoudigt zoekopdrachten op semi-gestructureerde gegevens, en de asynchrone I/O van de PG18 zorgt voor verbeteringen
tot 40% doorvoer op I/O-gebonden workloads met NVMe.
In het volgende artikel in de serie wordt dieper ingegaan op het lezen en optimaliseren van queryplannen
EXPLAIN ANALYZE: De krachtigste techniek voor het identificeren van langzame query's in productie.
Serie: Database-engineering en -optimalisatie
- Artikel 1 (dit): PostgreSQL 17/18 - Wat is er nieuw en prestaties
- Artikel 2: UITLEG ANALYSE - Queryplannen lezen en optimaliseren
- Artikel 3: Geavanceerde Indexering - Gedeeltelijk, Dekkend, BRIN, GIN
- Artikel 4: Tabelpartitionering - Miljarden rijen beheren
- Artikel 5: Verbindingspooling - PgBouncer versus Pgpool-II