PostgreSQL 17/18: Co nowego i ulepszenia wydajności
PostgreSQL pozostaje najbardziej zaawansowaną relacyjną bazą danych typu open source na świecie, a wersje 17 i 18 są konsolidowane
tę pozycję dzięki istotnym ulepszeniom, które bezpośrednio wpływają na wydajność produkcji.
PostgreSQL 17, wydany 26 września 2024 r., zapewnia przyrostową technologię VACUUM, SQL/JSON z JSON_TABLE()
i mądrzejszy planista. PostgreSQL 18, wydany we wrześniu 2025, dodaje natywne asynchroniczne operacje we/wy,
kolumny generowane wirtualnie i OAuth 2.0: skok pokoleniowy dla nowoczesnych aplikacji.
Czego się nauczysz
- Kluczowe funkcje PostgreSQL 17, które usprawniają zapytania i konserwację
- Wpływ asynchronicznych operacji we/wy PostgreSQL 18 na intensywne obciążenia
- Jak używać
JSON_TABLE()aby uprościć zapytania dotyczące danych częściowo ustrukturyzowanych - Prawdziwe testy porównawcze: jak bardzo wydajność poprawia się po aktualizacji?
- Praktyczny przewodnik po aktualizacji z PostgreSQL 16 do 17/18
PostgreSQL 17: najbardziej wpływowa wiadomość
Wersja 17 koncentruje się na trzech obszarach: zarządzaniu pamięcią i magazynowaniem, SQL/JSON i wydajności planisty. Przyjrzyjmy się innowacjom, które mają największy wpływ na produkcję.
PRÓŻNIA przyrostowa
Tradycyjny VACUUM skanuje cały stół. Z przyrostową próżnią PostgreSQL 17, proces działa w konfigurowalnych „fragmentach”, redukując rywalizację o blokady i szczytowe wejścia/wyjścia. Ma to kluczowe znaczenie w przypadku dużych stołów, gdzie tradycyjna PRÓŻNIA spowodowała degradację widoczne dla użytkowników.
-- 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 z JSON_TABLE()
JSON_TABLE() i najbardziej oczekiwana wiadomość SQL: przekształć JSON w wiersze relacyjne bez użycia
funkcje jsonb_to_recordset() lub złożone wyrażenia. Kompatybilny ze 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() to nie tylko czystsza składnia: planista może ją lepiej zoptymalizować,
generowanie bardziej wydajnych planów niż podejścia sprzed 17 lat oparte na bocznych połączeniach i funkcjach.
Ulepszenia Planera zapytań
PostgreSQL 17 zawiera znaczące ulepszenia w estymacji selektywności dla skanów zasięgu,
obniżenie szacunkowych kosztów za DISTINCT w kolumnach z indeksami i najlepsze wykorzystanie
indeksów B-Tree w obecności 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 do asynchronicznego wejścia/wyjścia
PostgreSQL 18 wprowadza natywne asynchroniczne operacje we/wy, jedną z najważniejszych zmian w architekturze
ostatnich lat. Historycznie rzecz biorąc, używany był PostgreSQL pread() synchroniczny: każda operacja
czytanie zablokowało proces aż do zakończenia. Dzięki asynchronicznym we/wy można wykonać więcej operacji
odczyt można rozpocząć jednocześnie, co pozwala lepiej wykorzystać nowoczesny sprzęt 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
kompatybilność io_uring
io_uring wymaga jądra Linuksa 5.1+. W systemach ze starszymi jądrami lub w systemie macOS/Windows
PostgreSQL 18 automatycznie powraca do io_method = worker, co jednak ulega poprawie
przepustowość w porównaniu do tradycyjnego synchronicznego.
Wirtualne kolumny generowane
PostgreSQL 17 miał przechowywane wygenerowane kolumny (wartość obliczona i zapisana na dysku). PostgreSQL 18 dodaje faktyczny wygenerowane kolumny: wartość wyliczana jest na miejscu czytania, bez zajmowania miejsca na dysku. Idealny do lekkich metamorfoz.
-- 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;
Uwierzytelnianie OAuth 2.0
PostgreSQL 18 obsługuje natywnie OAuth 2.0 jako metodę uwierzytelniania, eliminując tę potrzebę
zewnętrznych serwerów proxy lub opakowań w celu integracji z dostawcami tożsamości korporacyjnej (Azure AD, Okta, Google
Obszar roboczy). Konfiguracja odbywa się w 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"
Prawdziwe testy porównawcze: jak bardzo poprawia się wydajność?
Oficjalne testy porównawcze PostgreSQL i testy społeczności wykazują znaczną poprawę w realnych scenariuszach. Wyniki te uzyskano na typowym sprzęcie produkcyjnym: 8-rdzeniowy procesor, 32 GB RAM, dysk SSD NVMe.
| Scenariusz | PostgreSQL 16 | PostgreSQL 17 | PostgreSQL 18 |
|---|---|---|---|
| VACUUM na stole 50 GB z 30% martwych krotek | 480s | 310s (-35%) | 280 s (-42%) |
| Skanowanie sekwencyjne 100M linii (NVMe) | 28 s | 25 s (-11%) | 17 lat (-39%) |
| JSON_TABLE() w dokumentach JSON o wartości 1 mln | Nie dotyczy | 4,2 s | 3,1 s |
| pgbench TPS (skala=1000, klient=50) | 18 400 | 19 100 (+3,8%) | 21 800 (+18,5%) |
Przewodnik aktualizacji: z PostgreSQL 16 do 17/18
Aktualizację do PostgreSQL 17/18 można wykonać poprzez pg_upgrade (aktualizacja na miejscu, szybciej)
lub poprzez replikację logiczną (aktualizacja bez przestojów). Przyjrzyjmy się obu sposobom.
Uaktualnij za pomocą pg_upgrade (szybka 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
Aktualizacja bez przestojów poprzez replikację logiczną
-- 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');
Najlepsze praktyki dotyczące uaktualnień
- Zawsze wykonuj
pg_upgrade --checkprzed faktyczną aktualizacją w celu wykrycia niezgodności - Przed przełączeniem przetestuj aktualizację na klonie produkcyjnej bazy danych
- Con
--linkaktualizacja jest prawie natychmiastowa, ale nie cofaj się po pierwszym starcie PG17 - Zaktualizuj rozszerzenia po aktualizacji:
ALTER EXTENSION nome UPDATE - Monitoruj powolne zapytania przez tydzień po aktualizacji: planista może zmienić plany
Wiadomość dla programistów: Ulepszony SQL
Oprócz wydajności PostgreSQL 17 i 18 oferują kilka nowych funkcji upraszczających SQL codzienny rozwój.
POŁĄCZ Z POWROTEM (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 z 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;
Optymalna konfiguracja dla PostgreSQL 17/18
Każde uaktualnienie jest okazją do sprawdzenia konfiguracji. Te parametry są szczególnie odnoszące się do wiadomości z 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
Monitorowanie po aktualizacji
Po aktualizacji aktywnie monitoruj następujące wskaźniki przez co najmniej tydzień.
-- 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;
Wnioski
PostgreSQL 17 i 18 to obowiązkowe aktualizacje dla tych, którzy chcą w pełni wykorzystać swój sprzęt.
Przyrostowa próżnia rozwiązuje jeden z historycznych problemów na dużych stołach, JSON_TABLE()
upraszcza zapytania dotyczące danych częściowo ustrukturyzowanych, a asynchroniczne wejścia/wyjścia PG18 zapewniają ulepszenia
do 40% przepustowości w przypadku obciążeń związanych z we/wy dzięki NVMe.
Następny artykuł z tej serii opisuje, jak czytać i optymalizować plany zapytań
EXPLAIN ANALYZE: Najpotężniejsza technika identyfikowania powolnych zapytań w środowisku produkcyjnym.
Seria: Inżynieria i optymalizacja baz danych
- Artykuł 1 (ten): PostgreSQL 17/18 - Nowości i wydajność
- Artykuł 2: WYJAŚNIJ ANALIZĘ — czytaj i optymalizuj plany zapytań
- Artykuł 3: Indeksowanie zaawansowane – częściowe, obejmujące, BRIN, GIN
- Artykuł 4: Partycjonowanie tabeli — zarządzanie miliardami wierszy
- Artykuł 5: Pula połączeń – PgBouncer kontra Pgpool-II