01 - PostgreSQL 17/18: Novita e Miglioramenti Performance

PostgreSQL 17/18: Novita e Miglioramenti Performance

PostgreSQL rimane il database relazionale open-source piu avanzato del mondo, e le versioni 17 e 18 consolidano questa posizione con miglioramenti sostanziali che impattano direttamente le performance di produzione. PostgreSQL 17, rilasciato il 26 settembre 2024, porta incremental VACUUM, SQL/JSON con JSON_TABLE() e un planner piu intelligente. PostgreSQL 18, rilasciato a settembre 2025, aggiunge I/O asincrono nativo, virtual generated columns e OAuth 2.0: un salto generazionale per le applicazioni moderne.

PostgreSQL 17: Le Novita piu Impattanti

La versione 17 si focalizza su tre aree: gestione della memoria e dello storage, SQL/JSON e performance del planner. Vediamo le novita con maggiore impatto in produzione.

Incremental VACUUM

Il VACUUM tradizionale scansiona l'intera tabella. Con l'incremental VACUUM di PostgreSQL 17, il processo lavora in "chunk" configurabili, riducendo il lock contention e il picco di I/O. Questo e cruciale per tabelle di grandi dimensioni dove il VACUUM tradizionale causava degrado visibile per gli utenti.

-- 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 con JSON_TABLE()

JSON_TABLE() e la novita SQL piu attesa: trasforma JSON in righe relazionali senza usare funzioni jsonb_to_recordset() o espressioni complesse. Compatibile con lo standard 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() non e solo sintassi piu pulita: il planner puo ottimizzarla meglio, generando piani piu efficienti rispetto agli approcci pre-17 basati su lateral join e funzioni.

Miglioramenti al Query Planner

PostgreSQL 17 include miglioramenti significativi alla stima della selettivita per i range scan, riduzione del costo stimato per DISTINCT su colonne con indici, e miglior utilizzo degli indici B-Tree in presenza di 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: Il Salto dell'I/O Asincrono

PostgreSQL 18 introduce l'I/O asincrono nativo, uno dei cambiamenti architetturali piu profondi degli ultimi anni. Storicamente, PostgreSQL usava pread() sincrono: ogni operazione di lettura bloccava il processo fino al completamento. Con l'I/O asincrono, piu operazioni di lettura possono essere avviate simultaneamente, sfruttando meglio l'hardware NVMe moderno.

-- 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

Virtual Generated Columns

PostgreSQL 17 aveva le stored generated columns (valore calcolato e salvato su disco). PostgreSQL 18 aggiunge le virtual generated columns: il valore viene calcolato al momento della lettura, senza occupare spazio su disco. Ideale per trasformazioni leggere.

-- 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 Authentication

PostgreSQL 18 supporta OAuth 2.0 nativamente come metodo di autenticazione, eliminando la necessita di proxy o wrapper esterni per integrare con provider di identita aziendali (Azure AD, Okta, Google Workspace). La configurazione avviene 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"

Benchmark Reali: Quanto Migliorano le Performance?

I benchmark ufficiali di PostgreSQL e i test della community mostrano miglioramenti significativi in scenari reali. Questi risultati sono stati ottenuti su hardware tipico di produzione: 8-core CPU, 32GB RAM, NVMe SSD.

Guida all'Upgrade: Da PostgreSQL 16 a 17/18

L'upgrade a PostgreSQL 17/18 puo avvenire tramite pg_upgrade (upgrade in-place, piu veloce) o tramite logical replication (upgrade con zero downtime). Vediamo entrambi gli approcci.

Upgrade con pg_upgrade (Metodo Veloce)

#!/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 con Zero Downtime tramite Logical Replication

-- 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');

Novita per gli Sviluppatori: SQL Migliorato

Oltre alle performance, PostgreSQL 17 e 18 portano diverse novita a livello di SQL che semplificano lo sviluppo quotidiano.

MERGE con 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 con 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;

Configurazione Ottimale per PostgreSQL 17/18

Ogni upgrade e un'occasione per rivedere la configurazione. Questi parametri sono particolarmente rilevanti per le novita di 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

Monitoraggio Post-Upgrade

Dopo l'upgrade, monitorare attivamente i seguenti indicatori per almeno una settimana.

-- 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;

Conclusioni

PostgreSQL 17 e 18 sono upgrade obbligatori per chi vuole estrarre il massimo dal proprio hardware. L'incremental VACUUM risolve uno dei problemi storici su tabelle grandi, JSON_TABLE() semplifica le query su dati semi-strutturati, e l'I/O asincrono di PG18 porta miglioramenti di throughput fino al 40% su workload I/O-bound con NVMe.

Il prossimo articolo della serie approfondisce come leggere e ottimizzare i query plan con EXPLAIN ANALYZE: la tecnica piu potente per identificare le query lente in produzione.