PostgreSQL 15 - Nuove funzionalita'

La versione PostgreSQL 15 introduce nuove funzionalita' all'RDBMS Open Source piu' avanzato.
E' passato un anno dall'uscita della precedente release e, addirittura in anticipo, e' stata rilasciata la versione di produzione 15.0 [NdA 2022-10-13]: ecco quindi pubblicato anche questo documento!

In generale la versione 15 e' un'evoluzione della versione precedente con nuove funzionalita' (eg. MERGE, FOR ALL in logical replication) ma anche con migliori prestazioni ed una costante attenzione al miglioramento della sicurezza dei dati.

In questo documento sono riportati in dettaglio i principali nuovi elementi introdotti nella versione 15 riportando esempi pratici di utilizzo:

• MERGE
• Logical replication: ALL TABLES IN SCHEMA, filters
• Ulteriori regexp
• security_invoker in views
• jsonlog logging

Ma le novita' non sono solo queste... continuate a leggere!

MERGE

Il nuovo comando di MERGE consente, con un unico comando SQL, di eseguire istruzioni di INSERT, UPDATE, DELETE su una tabella di destinazione partendo dai dati di una tabella sorgente.

Si tratta di una tipica funzionalita' richiesta da applicazioni batch o eseguita da ETL che in precedenza non poteva essere eseguita con un comando unico.

MERGE INTO forno f
  USING ordini o
  ON o.tipo_pizza = f.tipo_pizza
  WHEN MATCHED AND o.quantita+f.numero>0
    THEN UPDATE SET f.numero = f.numero + o.quantita
  WHEN MATCHED AND o.quantita+f.numero<=0
    THEN DELETE
  WHEN NOT MATCHED 
    THEN INSERT (tipo_pizza, numero)
         VALUES (o.tipo_pizza, o.quantita);

E' utile ricordare che la funzionalita' di UPSERT era gia' stata introdotta in PG 9.5 con la clausola ON CONFLICT della INSERT, ma lo statement di MERGE prevede piu' possibilita'.

Logical Replication

Sono molto importanti le nuove funzionalita' introdotte in PG15 per la logical replication.

ALL TABLES IN SCHEMA introduce la possibilita' di pubblicare tutte le tabelle di uno schema. Questo risulta terribilmente comodo quando si utilizza la replica logica per la migrazione di uno schema perche' in precedenza era necessario dichiarare ogni singola tabella. Altro caso di utilizzo e' quello di utilizzare su uno schema con tutte le tabelle dell'anagrafica "aziendale": dopo la creazione una nuova tabella sara' subito disponibile a tutti i sottoscrittori. L'istruzione infatti vale anche per il futuro ovvero anche per le tabelle inserite nello schema dopo la creazione della pubblicazione.

Altra importante novita' della versione 15 e' quella di filtrare le colonne e/o le righe da pubblicare. In questi casi i vantaggi sono sia prestazionali che di sicurezza/visibilita' dei dati.

CREATE PUBLICATION p1
   FOR TABLE customer(customer_id, first_name, last_name, email_list)
 WHERE (active = 1);

Nell'esempio la pubblicazione filtra sia le colonne che verranno distribuite che pone una condizione sulle righe trattate.

Nuove regular expressions

Con la versione 15 sono state introdotte nuove funzioni per il controllo delle regular expression: regexp_count(), regexp_instr(), regexp_like(), regexp_substr() inoltre e' stata e' stata estesa la regexp_replace().

Un esempio lo farei volentieri... se sapessi usare le regular expressions!

select regexp_count(emails_list, '[^@, ]+@[^@, ]+\.[^@, \.]{2,}')
  from customer;

Con questa select viene calcolato il numero di email valide contenute nel campo emails_list... almeno spero!

security_invoker in views

E' stata aggiunta in PG15 la possibilita' di indicare che i privilegi di accesso quando viene applicata la vista non siano quelli dell'utente proprietario della vista ma quelli dell'utente che la esegue.
La sintassi e' molto semplice:

CREATE VIEW customer_v
    WITH ( security_invoker=true )
    AS SELECT *
         FROM customer
        WHERE valid=1
    WITH CHECK OPTION:

Questo aggiunge un'ulteriore possibilita' di controllo dei privilegi degli utenti nell'utilizzo delle viste.

I controlli di sicurezza di PostgreSQL sono molto sofisticati... per le viste gia' erano presenti le opzioni security_barrier [NdA 9.2] e check_option [NdA 9.4]. Mentre per le funzioni e' possibile indicare l'esecuzione come SECURITY ONWER oppure SECURITY DEFINER [NdA 8.1].
Le differenze tra queste opzioni sono sottili ma importanti...

Logging JSON

Nel parametro log_destination e' ora possibile indicare il formato jsonlog [NdA oltre ai classici formati stderr, csvlog e syslog]. Il formato JSON e' utile per analizzare i log da programma; nelle versioni precedenti era necessario utilizzare il formato csvlog quando il log doveva essere trattato.
La configurazione nel file postgresql.conf e' molto semplice:

log_destination = 'jsonlog' 

Non sono le uniche modifiche al logging presenti in PG15. Sono stati cambiati anche alcuni default: ad esempio log_autovacuum_min_duration era impostato per default a -1 (nessun logging delle attivita' di autovacuum) ed ora e' impostato a 10m, una configurazione utile per sapere se le attivita' di autovacuum cominciano ad essere significative; e' stato cambiato il default del parametro log_checkpoints che ora e' impostato a true.

Queste variazioni rendono piu' semplice diagnosticare problemi ed eseguire il tuning del database analizzando i log di PostgreSQL [NdA come fanno sempre piu' spesso le AI che vogliono sostituire noi vecchi DBA].

Varie ed eventuali

Non sono solo quelle riportate fino ad ora le uniche variazioni importanti della versione 15 di PostgreSQL. Altre novita' interessanti sono:

• Optimized sort
  Gli algoritmi di sort in memoria/disco sono stati ulteriormente ottimizzati. Questo porta a migliori prestazioni del database perche' i sort sono utilizzati in moltissime elaborazioni con l'SQL.
• PUBLIC
  Da sempre in Postgres qualsiasi utente puo' creare oggetti nello schema PUBLIC... Da sempre e' consigliato revocare questo diritto in una configurazione di produzione...
  Con la versione 15 questo privilegio e' stato tolto ed ora solo l'owner del database inizialmente avra' il diritto di creare oggetti su PUBLIC. Naturalmente i privilegi poi possono essere concessi ad altri utenti con i normali GRANT.
• hash_mem_multiplier
  Il parametro hash_mem_multiplier era gia' stato introdotto nella versione 13. Ora e' stato variato il default che passa da 1.0 a 2.0. E' un parametro utile per il tuning SQL. Infatti l'impostazione della work_mem generalmente e' lasciata bassa a livello di sistema per evitare problemi di memoria ma questo porta spesso l'ottimizzatore ad escludere l'efficiente HASH JOIN. Con questa impostazione di default si alzano i limiti per poter eseguire l'HASH JOIN.
• ICU collations for clusters/databases
  ICU e' l' International Components for Unicode: una libreria che fornisce le funzionalita' di internationalization (I18N) e localization (L10N). Nella versione 15 e' stata inserita la possibilita' di definire come default le ICU collations a livello di cluster o di database. In precedenza era possibile utilizzare solo le collation libc che sono molto efficienti ma molto piu' limitate.
  A cosa serve una collation? E' semplice. In ogni lingua vengono utilizzati caratteri diversi e l'ordine alfabetico risulta cosi' differente da quello numerico della rappresentazione dei caratteri. Inoltre spesso sono presenti lettere accentate o alterate che pero' vorremmo ordinare come se fossero lettere semplici... Una collation serve a definire l'ordine corretto dei caratteri per ogni lingua o dialetto o variazione standardizzati ed e' quindi fondamentale per tutti gli ordinamenti di stringhe [NdA compresi gli indici, quindi non e' cosi' banale come puo' sembrare a prima vista].
• Server statistics in shared memory
  Le statistiche del DB ora vengono mantenute nella shared memory ed aggiornate da tutti i processi. Non e' quindi piu' necessario il processo stats collector.
• Server side compression for pg_basebackup
  Il comando pg_basebackup e' utilissimo sia per eseguire i backup che nella fase iniziale della creazione di una replica di PostgreSQL. Tra le molteplici opzioni presenti e' stata aggiunta la possibilita' di eseguire la compressione sul lato server riducendo quindi la quantita' di dati che deve essere trasmessa in rete quando si esegue il comando da remoto.
  Ad esempio con l'opzione -Z server-lz4:1 si sceglie una compressione LZ4 sul server con livello 1 (fast).
• Deprecated
  Ad ogni nuova versione alcune funzionalita' sono deprecate e quindi e' consigliabile non utilizzarle piu'... anche se ancora funzionano. Tra queste la piu' importante, a mio modesto parere, e' il limitato supporto alle versioni 9.2 e precedenti sia nei comandi che con il pg_upgrade. Il supporto e' limitato nel senso che e' stato solo rimosso il codice specifico della 9.2 che continua quindi a funzionare per tutti i comandi che non richiedono un trattamento diverso da quello delle versioni successive [NdA la versione 9.2 e' del 2012 e non e' piu' supportata dal 2017].
• Removed
  Alcune funzionalita' deprecate da tempo sono state definitivamente rimosse nella versione 15. Tra queste sono significativi i seguenti casi: exclusive backup mode [NdA era deprecato gia' dalla versione 9.6], python 2. Per rendere chiara la differenza di comportamento sono state anche rinominate le funzioni utilizzate per richiamare i backup. Fino alla 14 era presente la pg_start_backup() con tre possibili parametri: label, fast, exclusive; dalla 15 e' presente la funzione pg_backup_start() con due possibili parametri: label, fast.

Avro' dimenticato qualcosa? Certamente si!
Ecco il contenuto della matrice delle nuove funzionalita' di PostgreSQL 15:
"jsonlog" logging format, Server statistics in shared memory, UNIQUE NULLS NOT DISTINCT, MERGE, range_agg range type aggregation function, regexp_count, regexp_instr, regexp_like, ALTER TABLE ... SET ACCESS METHOD, Improved performance for sorts exceeding working memory, Improved window function performance, Parallel "SELECT DISTINCT", SECURITY INVOKER views, ALTER SUBSCRIPTION ... SKIP, Logical replication column lists, Logical replication publish all tables in schema, Logical replication row filtering, Logical replication subscriber can disable on error, Archive modules, lz4 and Zstandard (zstd) compression for WAL full page writes, pg_basebackup client decompression, pg_basebackup server-side compression, Pre-fetch WAL during recovery, postgres_fdw parallel commit, Default ICU collations for clusters/databases, psql \dconfig, pg_walinspect.
Non c'e' solo questo: l'elenco completo riportato dalla documentazione ufficiale e l'ottimo prospetto riassuntivo di pgPedia.

In realta' manca ancora qualcosa, perche' non c'e' proprio nella versione 15... l'annunciato full SQL/JSON (presente al freeze della release e praticamente fino all'ultimo momento [NdA 2022-09-01]) e' stato rimosso dai sorgenti.
Speriamo di rivederlo nella 16. PostgreSQL ha gia' un notevole supporto del JSON: il datatype JSON e' stato introdotto nella 9.2 (2012-09) ed ogni versione ha aggiunto nuove funzionalita'. Ma il full SQL/JSON sarebbe stato comodo per i vecchi pigri come me che vogliono utilizzare solo l'SQL [NdE in Oracle il datatype JSON e' presente solo dalla versione 21 (2020-12) che non e' una versione LTS].

PostgreSQL e' in costante evoluzione!
Per il passato... un documento completo con le versioni di tutti prodotti SW che ritengo piu' significativi, ed ovviamente anche di PostgreSQL, e' il tuo server puzza!
Per il futuro... in realta' il futuro di PostgreSQL 16 e' gia' adesso perche' gli sviluppi per la versione 16 sono gia' formalmente iniziati [NdA 2022-07] ed ad aprile 2023 si arrivera' al Feature Freeze in cui vengono fissate le nuove funzionalita' che verranno aggiunte nella versione. Arriveranno quindi le prime Beta e quindi una o piu' RC (Release Candidate) a seconda delle necessita'. Probabilmente entro il 2023-11 la nuova versione PG16 sara' disponibile come produzione [NdA spesso in corrispondenza al secondo giovedi di Novembre, giorno di rilascio delle minor releases, ma sono possibili leggeri anticipi o ritardi per le nuova release].

Titolo: PostgreSQL 15 - Nuove funzionalita'
Livello: Avanzato (3/5)
Data: 13 Ottobre 2022
Versione: 1.0.1 - 31 Ottobre 2022
Autore: mail [AT] meo.bogliolo.name