Dopo un terzo beta riuscito agosto 2024, il gruppo di sviluppo di PostgreSQL ha rilasciato la versione GA il 26 settembre. Recentemente, ho scritto un blog su alcune delle principali funzionalità di replica logica che si vedranno in PostgreSQL 17. In questo blog, descriverò due nuove funzionalità di prestazioni che troverete in Postgres 17 così come un’altra importante funzionalità di replica logica che non ho coverto nel mio blog precedente di questa serie.
PostgreSQL ha notevolmente cresciuto negli anni, e con ogni rilascio principale, è diventato un database più robusto, affidabile e reattivo per sia applicazioni aziendali critiche che non critiche. La comunità globale e vivace di PostgreSQL contribuisce al successo di PostgreSQL, attraverso la diligenza nell’esaminare e revisionare tutte le modifiche prima di aggiungerle al codice sorgente del progetto. È anche incoraggiante vedere nomi tecnologici importanti come Microsoft, Google, Apple e altri che investiscono in Postgres sviluppando esperienza in-house e restituendo alla comunità open-source.
Le migliorie alle repliche logiche stanno facendo strada per l’aggiunta della supportazione distribuita di PostgreSQL alle funzionalità di base. PostgreSQL distribuito si riferisce all’implementazione di PostgreSQL in un’architettura distribuita, consentendo una migliore scalabilità, tolleranza ai guasti e prestazioni migliorate su più nodi.
Ora, senza ulteriori indugi, parliamo delle funzionalità di prestazioni di PostgreSQL 17.
Prestazioni query migliorate con CTE materializzate.
Le Common Table Expressions (CTEs) in PostgreSQL sono risultati temporanei che possono essere richiamati all’interno di una istruzione SELECT, INSERT, UPDATE o DELETE. Sviluppano la leggibilità e l’organizzazione di query complesse e possono essere ricorsive, rendendole particolarmente utili per i dati gerarchici. La sintassi di base di una query CTE è la seguente:
WITH cte_names AS
(– QUERY here )
Select * from cte_names;Includere il keyword WITH in una query per creare la CTE; la query padre (che definisce il risultato) segue la clausola AS dopo il nome della CTE. Dopo aver definito la CTE, è possibile richiamare il nome della CTE per riferirsi al risultato della CTE e eseguire ulteriori operazioni sul risultato nella stessa query.
PostgreSQL 17 continua a migliorare le prestazioni e le capacità relative alle CTEs, inclusi i miglioramenti nel pianificamento e nell’esecuzione delle query. Le versioni precedenti di Postgres trattavano le CTEs come barriere all’ottimizzazione, ovvero il pianificatore non poteva spingere giù i predicati dentro di esse. Tuttavia, a partire da PostgreSQL 12, è possibile definire piani di esecuzione più efficienti. È sempre necessario analizzare le query e considerare i piani di esecuzione quando la performance è critica.
Consiglio per il rendimento: Se avrai bisogno di fare riferimento allo stesso insieme di risultati più volte, crea il CTE utilizzando la parola chiave MATERIALIZED. Quando crei un CTE materializzato, Postgres calcola e memorizza il risultato dell’query genitore. Successivamente, le query successive non devono eseguire calcoli complessi più di una volta se fai riferimento all’CTE più di una volta.
Estrazione delle statistiche delle colonne da riferimenti CTE, Postgres 17 migliora i CTE materializzati
Un CTE materializzato agisce come una barriera di ottimizzazione, il che significa che l’query esterno non influirà sul piano della sotto-query una volta che questo piano sia scelto. L’query esterno ha una visione sugli stimati spessori e conteggi di righe dell’insieme di risultati dell’CTE, quindi ha senso propagare le statistiche delle colonne dalla sotto-query al pianista per l’query esterno. L’query esterno può usare qualsiasi informazione sia disponibile, permettendo alle informazioni statistiche delle colonne di propagarsi verso l’esterno nello schema esterno ma non verso il piano dell’CTE.
Questo bug segnalato alla comunità contiene un semplice caso di test che può dimostrare l’improvements e l’effetto sul pianista delle query come risultato di questa miglioria.
Esempio: Confrontare il comportamento di Postgres 16 con Postgres 17
Prima di tutto, creiamo il nostro spazio di lavoro in Postgres 16 e eseguiamo ANALYZE contro di esso; due tabelle e gli indici:
postgres=# create table t1(a int);
CREATE TABLE
postgres=# create table t2(b int);
CREATE TABLE
postgres=# create index my_index on t1 using btree (a);
CREATE INDEX
postgres=# insert into t1 select generate_series(1, 100000) from generate_series(1, 3);
INSERT 0 300000
postgres=# insert into t2 select generate_series(1, 100) from generate_series(1, 10);
INSERT 0 1000
postgres=# analyze t1;
ANALYZE
postgres=# analyze t2;
ANALYZE
Then, we create our materialized CTE:
postgres=# explain analyze with my_cte as materialized (select b from t2) select *
from t1 where t1.a in (select b from my_cte);
The query plan from our Postgres 16 code sample contains:
QUERY PLAN
----------------------------------------------------------------------
Nested Loop (cost=37.92..856.50 rows=2966 width=4) (actual time=0.574..0.722 rows=300 loops=1)
CTE my_cte
-> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=4) (actual time=0.038..0.161 rows=1000 loops=1)
-> HashAggregate (cost=22.50..24.50 rows=200 width=4) (actual time=0.449..0.461 rows=100 loops=1)
Group Key: my_cte.b
Batches: 1 Memory Usage: 40kB
-> CTE Scan on my_cte (cost=0.00..20.00 rows=1000 width=4) (actual time=0.046..0.322 rows=1000 loops=1)
-> Index Only Scan using my_index on t1 (cost=0.42..4.06 rows=3 width=4) (actual time=0.002..0.002 rows=3 loops=1
00)
Index Cond: (a = my_cte.b)
Heap Fetches: 0
Planning Time: 1.242 ms
Execution Time: 1.051 ms
(12 rows)
Come potete vedere nella pianificazione della query, le statistiche di colonna per le 200 righe della sottoserie sono sbagliate, il che influenza il piano complessivo.
-> HashAggregate (cost=22.50..24.50 rows=200 width=4) (actual time=0.449..0.461 rows=100 loops=1)
Group Key: my_cte.b
Successivamente, abbiamo testato la stessa configurazione e query contro PostgreSQL 17:
postgres=# explain analyze with my_cte as materialized (select b from t2) select *
from t1 where t1.a in (select b from my_cte);
QUERY PLAN
-------------------------------------------------------------------------------------------------
---------------------------------
Merge Join (cost=42.25..54.29 rows=302 width=4) (actual time=0.627..0.712 rows=300 loops=1)
Merge Cond: (t1.a = my_cte.b)
CTE my_cte
-> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=4) (actual time=0.031..0.134 rows=1000
loops=1)
-> Index Only Scan using my_index on t1 (cost=0.42..7800.42 rows=300000 width=4) (actual tim
e=0.027..0.049 rows=301 loops=1)
Heap Fetches: 0
-> Sort (cost=26.82..27.07 rows=100 width=4) (actual time=0.598..0.604 rows=100 loops=1)
Sort Key: my_cte.b
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=22.50..23.50 rows=100 width=4) (actual time=0.484..0.494 rows=1
00 loops=1)
Group Key: my_cte.b
Batches: 1 Memory Usage: 24kB
-> CTE Scan on my_cte (cost=0.00..20.00 rows=1000 width=4) (actual time=0.033..0
.324 rows=1000 loops=1)
Planning Time: 1.066 ms
Execution Time: 0.946 ms
(15 rows)Come potete vedere nella pianificazione della query per Postgres 17, le statistiche di colonna dalla sottoserie sono correttamente propagate all’allocatore di piano esterno. Questo aiuta PostgreSQL a scegliere un piano migliore che migliora il tempo di esecuzione della query.
Questa è una query semplice, ma con query più grandi e complesse questo cambiamento può portare a una differenza di prestazioni significativa.
Propagazione delle Pathkeys da una CTE a una query esterna
Un’altra interessante miglioria alle funzionalità di CTE in Postgres 17 è la propagazione delle pathkeys dalla sottoserie alla query esterna. In PostgreSQL, le pathkeys fanno parte del processo di pianificazione dell’esecuzione delle query usate principalmente per ordinare e sistemare le righe nelle query che richiedono risultati ordinati, come ad esempio le query con la clausola ORDER BY, o quando è necessario ordinare per altre operazioni come le join di merge.
Prima di Postgres 17, l’ordine di sortimento della sottoserie materializzata della CTE non era condiviso con la query esterna, anche se l’ordine di sortimento era garantito da uno scanner di indice o da un nodo di ordinamento. Non avere un ordine di sortimento garantito consente all’allocatore di PostgreSQL di scegliere un piano meno ottimizzato, mentre avere un ordine di sortimento garantito rende più probabile scegliere un piano ottimizzato.
Con PostgreSQL 17, se una CTE (Common Table Expression) è materializzata ed ha un ordinamento specifico, il pianificatore può riutilizzare quelle informazioni nella query esterna, migliorando le prestazioni evitando l’ordinamento ridondante o consentendo metodi di join più efficienti. Come notato dai commenti del commit di Tom Lane:
“Il codice per sollevare pathkeys nella query esterna esiste già per le sottquery regolari
RTE_SUBQUERY, ma non veniva utilizzato per le CTE, forse a causa della preoccupazione per la manutenzione di un recinto di ottimizzazione tra la CTE e la query esterna.”
Questa semplice modifica al codice sorgente di Postgres dovrebbe portare a miglioramenti delle prestazioni per le query che coinvolgono CTE complesse, specialmente quelle in cui l’ordinamento o il join di fusione possono essere ottimizzati sulla base dell’ordine naturale dei risultati della CTE.
Ecco un esempio utilizzando i dati nel regression test di PostgreSQL:
postgres=# CREATE TABLE tenk1 (
postgres(# unique1 int4,
postgres(# unique2 int4,
postgres(# two int4,
postgres(# four int4,
postgres(# ten int4,
postgres(# twenty int4,
postgres(# hundred int4,
postgres(# thousand int4,
postgres(# twothousand int4,
postgres(# fivethous int4,
postgres(# tenthous int4,
postgres(# odd int4,
postgres(# even int4,
postgres(# stringu1 name,
postgres(# stringu2 name,
postgres(# string4 name
postgres(# );
CREATE TABLE
postgres=# CREATE INDEX tenk1_unique1 ON tenk1 USING btree(unique1 int4_ops);
CREATE INDEX
postgres=# \copy tenk1 FROM '~/projects/postgres/src/test/regress/data/tenk.data';
COPY 10000
postgres=# VACUUM ANALYZE tenk1;
VACUUM
Il piano di query dal nostro esempio di codice di Postgres 16 contiene il seguente:
postgres=# explain analyze with x as materialized (select unique1 from tenk1 b order by unique1)
select count(*) from tenk1 a
where unique1 in (select * from x);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
---
Aggregate (cost=764.29..764.30 rows=1 width=8) (actual time=21.592..21.593 rows=1 loops=1)
CTE x
-> Index Only Scan using tenk1_unique1 on tenk1 b (cost=0.29..306.29 rows=10000 width=4) (actual time=0.046..1.415 rows=10000 loops=
1)
Heap Fetches: 0
-> Nested Loop (cost=225.28..445.50 rows=5000 width=0) (actual time=7.545..20.911 rows=10000 loops=1)
-> HashAggregate (cost=225.00..227.00 rows=200 width=4) (actual time=7.535..9.051 rows=10000 loops=1)
Group Key: x.unique1
Batches: 1 Memory Usage: 929kB
-> CTE Scan on x (cost=0.00..200.00 rows=10000 width=4) (actual time=0.070..3.933 rows=10000 loops=1)
-> Index Only Scan using tenk1_unique1 on tenk1 a (cost=0.29..1.08 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=10000)
Index Cond: (unique1 = x.unique1)
Heap Fetches: 0
Planning Time: 0.806 ms
Execution Time: 21.890 ms
(14 rows)
Il piano di query dal nostro esempio di codice di Postgres 17 contiene il seguente:
postgres=# explain analyze with x as materialized (select unique1 from tenk1 b order by unique1)
select count(*) from tenk1 a
where unique1 in (select * from x);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=987.55..987.56 rows=1 width=8) (actual time=8.777..8.778 rows=1 loops=1)
CTE x
-> Index Only Scan using tenk1_unique1 on tenk1 b (cost=0.29..306.29 rows=10000 width=4) (actual time=0.010..1.095 rows=100
00 loops=1)
Heap Fetches: 0
-> Merge Semi Join (cost=0.31..656.26 rows=10000 width=0) (actual time=0.037..8.024 rows=10000 loops=1)
Merge Cond: (a.unique1 = x.unique1)
-> Index Only Scan using tenk1_unique1 on tenk1 a (cost=0.29..306.29 rows=10000 width=4) (actual time=0.013..1.262 rows
=10000 loops=1)
Heap Fetches: 0
-> CTE Scan on x (cost=0.00..200.00 rows=10000 width=4) (actual time=0.016..3.678 rows=10000 loops=1)
Planning Time: 0.800 ms
Execution Time: 8.899 ms
(11 rows)I piani di query in Postgres 16 e Postgres 17 sono significativamente differenti a causa di questa migliorazione della versione 17. Questo è solo un piccolo esempio; potete vedere che la guadagno di prestazioni sarà significativa in query più grandi. Notate che questa miglioria è efficace solo se la sottostoria CTE ha una clausola ORDER BY.
Scansioni B-Tree veloci per Array Scalare
In PostgreSQL, ScalarArrayOpExpr è un tipo di nodo dell’esecuzione del piano che gestisce le query che coinvolgono operazioni come IN o ANY con array o liste di valori. È particolarmente utile per le query in cui si confronta una colonna con un insieme di valori, come: SELECT * FROM table WHERE column = ANY(ARRAY[1, 2, 3]);.
ScalarArrayOpExpr consente a PostgreSQL di ottimizzare le query che coinvolgono molteplici confronti che usano IN o ANY. PostgreSQL 17 ha introdotto nuove migliorie di prestazioni per renderle ancora più veloci.
In PostgreSQL 17, sono state fatte significative migliorie alle scansioni B-Tree, che ottimizzano il rendimento, soprattutto per le query con lunghi elenchi IN o condizioni ANY. Queste migliorie riducono il numero di scansioni dell’indice effettuate dal sistema, diminuendo la contesa CPU e pagine del buffer, portando a una esecuzione più veloce delle query.
Uno degli sviluppi chiave riguarda il trattamento delle espressioni di operazione sugli array scalari (SAOP), che consente una scansione più efficiente degli indici B-tree, soprattutto per query multidimensionali. Per esempio, quando si hanno molti colonne di indice ( ciascuna con la sua lista IN), PostgreSQL 17 può ora processare queste operazioni in maniera più efficiente in una singola scansione di indice invece di diverse scansioni, come in versioni precedenti. Questo può portare a guadagni di performance di circa il 20-30% nei carichi di lavoro limitati dalla CPU in cui le accessioni a pagina erano in precedenza un bottleneck.
Inoltre, PostgreSQL 17 introduce un migliore management delle lock interne, ulteriormente incrementando la performance per carichi di lavoro a alta concorrenza, specialmente durante la scansione di molte dimensioni all’interno di un indice B-tree.
possiamo dimostrare questo con un semplice esempio. Usiamo la stessa tabella tenk1 e i dati utilizzati negli esempi precedenti del set di regressione di Postgres.
Il nostro esempio, eseguito per la prima volta su Postgres 16:
CREATE TABLE tenk1 (
postgres(# unique1 int4,
postgres(# unique2 int4,
postgres(# two int4,
postgres(# four int4,
postgres(# ten int4,
postgres(# twenty int4,
postgres(# hundred int4,
postgres(# thousand int4,
postgres(# twothousand int4,
postgres(# fivethous int4,
postgres(# tenthous int4,
postgres(# odd int4,
postgres(# even int4,
postgres(# stringu1 name,
postgres(# stringu2 name,
postgres(# string4 name
postgres(# );
CREATE TABLE
postgres=# CREATE INDEX tenk1_unique1 ON tenk1 USING btree(unique1 int4_ops);
CREATE INDEX
postgres=# \copy tenk1 FROM '~/projects/postgres/src/test/regress/data/tenk.data';
COPY 10000
postgres=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 IN (1, 2, 3);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=14.20..330.12 rows=176 width=244) (actual time=0.138..0.153 rows=3 loops=1)
Recheck Cond: (unique1 = ANY ('{1,2,3}'::integer[]))
Heap Blocks: exact=3
Buffers: shared hit=9
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..14.16 rows=176 width=0) (actual time=0.102..0.102 rows=3 loops=1)
Index Cond: (unique1 = ANY ('{1,2,3}'::integer[]))
Buffers: shared hit=6
Planning:
Buffers: shared hit=2
Planning Time: 0.900 ms
Execution Time: 0.242 ms
(11 rows)
postgres=# SELECT idx_scan, idx_tup_fetch FROM pg_stat_user_tables WHERE relname = 'tenk1';
idx_scan | idx_tup_fetch
----------+---------------
3 | 3
(1 row)
Nella query precedente, potete vedere che il numero di hit sui buffer condivisi per la query IN era 9 e che servirono 3 scansioni di indice per ottenere i risultati dalla scansione dell’indice. In PostgreSQL, il termine shared hit si riferisce a un particolare tipo di cache hit relativo alla gestione dei buffer. Un hit condiviso avviene quando PostgreSQL accede a un blocco di dati o pagina dalla pool di buffer condiviso invece che da disco, migliorando le performance delle query.
Lo stesso esempio, questa volta eseguito su Postgres 17:
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 IN (1, 2, 3);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=12.88..24.08 rows=3 width=244) (actual time=0.043..0.054 rows=3 loops=1)
Recheck Cond: (unique1 = ANY ('{1,2,3}'::integer[]))
Heap Blocks: exact=3
Buffers: shared hit=5
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..12.88 rows=3 width=0) (actual time=0.026..0.026 rows=3 loops=1)
Index Cond: (unique1 = ANY ('{1,2,3}'::integer[]))
Buffers: shared hit=2
Planning:
Buffers: shared hit=59
Planning Time: 0.479 ms
Execution Time: 0.116 ms
(11 rows)
postgres=# SELECT idx_scan, idx_tup_fetch FROM pg_stat_user_tables WHERE relname = 'tenk1';
idx_scan | idx_tup_fetch
----------+---------------
1 | 3
(1 row)
Come potete vedere, con Postgres 17 la percentuale di hit sul buffer condiviso viene ridotta a 5, e soprattutto viene eseguito solo un scansione dell’indice (invece di tre scansioni nel caso di Postgres 16). Con questa miglioria in Postgres 17, le prestazioni delle operazioni array scalari subiscono un grande miglioramento, e Postgres può scegliere piani di query ottimizzati miglioremente.
Retention of Logical Replication Slots and Subscriptions During Upgrade
The retention of logical replication slots and migration of subscription dependencies during the major upgrade process is another logical replication feature added to PostgreSQL 17. Please note that this feature will only be useful in upgrading from PostgreSQL 17 to later versions, this is not supported for upgrade prior to Postgres 17. The replication slots and replication origins are generated when building a logical replication environment. However, this information is specific to the node in order to record replication status, application status, and WAL transmission status so they aren’t upgraded as part of the upgrade process. Once the published node is upgraded the user needs to manually construct these objects.
The pg_upgrade process is improved in PostgreSQL 17 to reference and rebuild these internal objects; this functionality enables replication to automatically resume when upgrading a node that has logical replication. Previously, when performing a major version upgrade, users had to drop logical replication slots, requiring them to re-synchronize data with the subscribers after the upgrade. This added complexity and increased downtime during upgrades.
Nel momento in cui si aggiorna il cluster del publisher, segui i seguenti passaggi:
- Assicurarsi che tutti i sottoscrizioni al publisher siano disattivate temporaneamente eseguendo un
ALTER SUBSCRIPTION….DISABLE. Queste vengono reattivate dopo aver completato il processo di aggiornamento. - Impostare il
wal_leveldel nuovo cluster alogical. - Il
max_replication_slotsdel nuovo cluster deve essere impostato su un valore maggiore o uguale a quello dei slots di replica dell’vecchio cluster. - Installare nei nuovi cluster i plugin di output utilizzati dai slot.
- Tutti i cambiamenti dal vecchio cluster sono già replicati nel cluster di destinazione prima dell’aggiornamento.
- Tutti i slot dell’vecchio cluster devono essere utilizzabili; puoi assicurarti questo controllando le colonne in conflitto nella vista pg_replication_slots. Il valore “conflitto” deve essere falso per tutti i slot dell’vecchio cluster.
- Nessun slot del nuovo cluster deve avere un valore di
falsenella colonnaTemporarydella vistapg_replication_slots. Non deve esistere alcun slot logico di replica permanente nel nuovo cluster.
Il processo pg_upgrade per l’aggiornamento dei slot di replica genera un errore se i prerequisiti elencati precedentemente non sono soddisfatti.
Conclusione
Con PostgreSQL 17, l’attenzione della comunità continua a concentrarsi sul rendere PostgreSQL più performante, scalabile, sicuro e pronto per l’uso aziendale. Postgres 17 migliora anche l’esperienza degli sviluppatori aggiungendo nuove funzionalità per la compatibilità e rendendo le funzionalità esistenti più potenti e robuste.
Oltre la versione 17, PostgreSQL continuerà a crescere, migliorare e diventare più performante per soddisfare le applicazioni aziendali che richiedono database più scalabili. La scalabilità (sia orizzontale che verticale) è migliorata nel corso degli anni, ma c’è sicuramente spazio per migliorare la capacità orizzontale aggiungendo funzionalità di sharding a PostgreSQL. Vedremo ulteriori miglioramenti nella replicazione logica, con altri progressi nell’area della replicazione DDL o nella replicazione di oggetti mancanti (come le sequenze) e una migliore gestione dei nodi. La comunità riconosce anche la necessità di rendere PostgreSQL più compatibile, da qui i miglioramenti del comando MERGE in Postgres 17 e i piani per ulteriori funzionalità di compatibilità oltre Postgres 17.
Source:
https://dzone.com/articles/postgresql-17-a-major-step-forward-in-performance