Após um terceiro beta bem-sucedido em Agosto de 2024, o grupo de desenvolvimento de PostgreSQL lançou a versão GA em 26 de Setembro. Recentemente, escrevi um blog sobre algumas das principais funcionalidades de replicação lógica que você verá em PostgreSQL 17. Neste blog, descreverei algumas novas funcionalidades de performance que você encontrará em Postgres 17, bem como outra importante funcionalidade de replicação lógica que não coveri em meu blog anterior desta série.
PostgreSQL tem crescido marcadamente ao longo dos anos, e com cada lançamento major, tornou-se um banco de dados mais robusto, confiável e responsivo para ambos os aplicativos de missão-crítica e não de missão-crítica de aplicações empresariais. A global e vibrante comunidade PostgreSQL está contribuindo para o sucesso de PostgreSQL, diligentemente garantindo que todas as mudanças são cuidadosamente examinadas e avaliadas antes de serem adicionadas ao código fonte do projeto. Também é encorajador ver nomes tecnológicos importantes como Microsoft, Google, Apple e outros investindo em Postgres desenvolvendo expertise interna e dando back para a comunidade open-source.
As melhorias na replicação lógica estão pavimentando o caminho para a adição do suporte de PostgreSQL distribuído à funcionalidade de núcleo. O PostgreSQL distribuído se refere à implementação de PostgreSQL em uma arquitetura distribuída, permitindo melhoria na escalabilidade, tolerância a falhas e melhoria na performance em vários nós.
Agora, sem adiar, vamos discutir algumas funcionalidades de performance de PostgreSQL 17.
Melhoria na Performance de Consultas com CTEs Materializadas
Expressões de Tabela Comum (CTEs) em PostgreSQL são conjuntos de resultados temporários que podem ser referenciados dentro de instruções SELECT, INSERT, UPDATE ou DELETE. Eles melhoram a leitura e a organização de consultas complexas e podem ser recursivos, tornando-os particularmente úteis para dados hierárquicos. A sintaxe básica de uma consulta CTE é a seguinte:
WITH cte_names AS
(– QUERY here )
Select * from cte_names;Inclua a palavra-chave WITH em uma consulta para criar a CTE; a consulta pai (que define o conjunto de resultados) segue a cláusula AS após o nome da CTE. Após definir a CTE, você pode referir-se à CTE por nome para referenciar o conjunto de resultados da CTE e executar operações adicionais no conjunto de resultados na mesma consulta.
A versão 17 do PostgreSQL continua a melhorar desempenho e funcionalidades em torno de CTEs, incluindo melhorias em planejamento de consultas e execução. Versões antigas do Postgres tratam CTEs como obstáculos de otimização, o que significa que o planejador não consegue aplicar predicados para dentro deles. No entanto, a partir da versão 12 do PostgreSQL, você pode definir planos de execução mais eficientes. Você deve sempre analisar suas consultas e considerar os planos de execução quando o desempenho é crítico.
Dica de performance: Se você vai referenciar o mesmo conjunto de resultados várias vezes, crie o CTE usando a palavra-chave MATERIALIZED. Quando você cria um CTE materializado, o Postgres calcula e armazena o resultado da consulta pai. Em consultas subsequentes, você não precisa realizar computações complexas várias vezes se você referenciar o CTE várias vezes.
Extraindo Estatísticas de Colunas de Referências de CTE, o Postgres 17 Melhora CTEs Materializadas
Um CTE materializado age basicamente como uma barreira de otimização, o que significa que a consulta externa não influirá no plano da sub-consulta assim que o plano for escolhido. A consulta externa tem visibilidade para o tamanho estimado e o número de linhas do conjunto de resultados do CTE, portanto é sensato para propagar as estatísticas das colunas da sub-consulta para o planejador para a consulta externa. A consulta externa pode usar qualquer informação disponível, permitindo que as informações estatísticas das colunas sejam propagadas para o plano externo mas não para o plano do CTE.
Este erro relatado à comunidade contém um caso de teste simples que pode demonstrar a melhoria e o efeito no planejador de consulta como resultado desta melhoria.
Exemplo: Comparando o Comportamento do Postgres 16 com o Postgres 17
Primeiro, criamos o nosso espaço de trabalho no Postgres 16 e executamos ANALYZE contra ele; duas tabelas e índices:
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)
Como podem ver no plano de consulta, as estatísticas de coluna de 200 linhas da sub-consulta estão erradas, o que está a afetar o plano geral.
-> 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
Então, testamos a mesma configuração e consulta contra o 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)Como podem ver no plano de consulta do Postgres 17, as estatísticas de coluna da sub-consulta estão corretamente propagando para o planejador superior da consulta externa. Isto ajuda o PostgreSQL a escolher um melhor plano que melhora o tempo de execução da consulta.
Esta é uma consulta simples, mas com consultas maiores e complexas, essa mudança pode resultar em uma diferença de performance significativa.
Propagação de Pathkeys de uma CTE para uma Consulta Externa
Outra melhoria interessante nas funcionalidades de CTE no Postgres 17 é a propagação de pathkeys da sub-consulta para a consulta externa. Em PostgreSQL, pathkeys são parte do processo de planejamento de execução de consultas usadas principalmente para ordenar e classificar linhas em consultas que exigem resultados ordenados, como consultas com a cláusula ORDER BY, ou quando a ordenação é necessária para outras operações, como junções de mesclagem.
Antes do Postgres 17, a ordem de classificação da sub-consulta materializada de CTE não era partilhada com a consulta externa, mesmo que a ordem de classificação fosse garantida por um nó de scanner de índice ou um nó de classificação. Não ter uma ordem de classificação garantida permite que o planejador do PostgreSQL escolha um plano menos otimizado, enquanto ter uma ordem de classificação garantida torna mais provável que escolha um plano otimizado.
Com o PostgreSQL 17, se uma CTE for materializada e tiver uma ordem de classificação específica, o planejador pode reutilizar essa informação na consulta externa, melhorando o desempenho evitando o ordenamento redundante ou permitindo métodos de junção mais eficientes. Como notado nos comentários de commit por Tom Lane:
“O código para elevar pathkeys para a consulta externa já existe para subqueries regulares
RTE_SUBQUERY, mas não estava sendo usado para CTEs, possivelmente por questões de manutenção de uma barreira de otimização entre a CTE e a consulta externa.”
Essa simples modificação no código fonte do Postgres deveria resultar em melhorias de desempenho para consultas envolvendo CTEs complexas, especialmente aquelas onde o ordenamento ou junções de mesclagem podem ser otimizadas com base na ordem inerente dos resultados da CTE.
Aqui está um exemplo usando os dados no PostgreSQL de regressão:
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
O plano de consulta do exemplo de código do Postgres 16 contém o seguinte:
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)
O plano de consulta do exemplo de código do Postgres 17 contém o seguinte:
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
------------------------------------------------------------------------------------------------------------------------------- Agregação ( custo=987,55..987,56 linhas=1 largura=8 ) (tempo real=8,777..8,778 linhas=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)Os planos de consulta em Postgres 16 e Postgres 17 são significativamente diferentes devido a este aprimoramento da versão 17. Este é um pequeno exemplo; você pode ver que o ganho de desempenho será significativo em consultas maiores. Observe que esta melhoria é eficaz apenas se a subconsulta do CTE tiver uma cláusula ORDER BY.
Scans de índice B-Tree rápido para vetores escalares
Em PostgreSQL, ScalarArrayOpExpr é um tipo de nó no plano de execução que gerencia consultas envolvendo operações como IN ou ANY com arrays ou listas de valores. Isso é particularmente útil para consultas onde você compara uma coluna contra um conjunto de valores, como: SELECT * FROM table WHERE column = ANY(ARRAY[1, 2, 3]);.
ScalarArrayOpExpr permite que o PostgreSQL optimize consultas que envolvem várias comparações que usam IN ou ANY. A versão 17 do PostgreSQL introduziu novos aprimoramentos de desempenho para tornar essas operações ainda mais rápidas.
Na versão 17 do PostgreSQL, melhorias significativas foram feitas emScans de índice B-Tree, que otimizam o desempenho, particularmente para consultas com grandes listas de IN ou condições de ANY. Essas melhorias reduzem o número de scans de índice realizados pelo sistema, diminuindo assim a contenção de CPU e páginas de buffer, resultando em execuções de consulta mais rápidas.
Uma das melhorias chave está no tratamento de Expressões de Operação de Array de escalar (SAOP), que permite uma travessia mais eficiente de índices de árvore B, particularmente para consultas multidimensionais. Por exemplo, quando você tem várias colunas de índice (cada uma com sua própria lista IN), o PostgreSQL 17 agora pode processar essas operações de forma mais eficiente em uma única leitura de índice, em vez de várias leituras, como nas versões anteriores. Isso pode resultar em ganhos de performance de 20-30% em cargas de trabalho comumente acopladas à CPU onde a acesso a página era anteriormente um ponto de travamento.
Adicionalmente, o PostgreSQL 17 introduce melhor gerenciamento de bloqueios internos, melhorando ainda mais a performance para cargas de trabalho de alta concorrência, especialmente quando fazendo escaneamentos em várias dimensões dentro de um índice de árvore B.
Podemos demonstrar isso com um exemplo simples. Usaremos a mesma tabela tenk1 e dados que usamos no exemplo anterior do conjunto de testes de regressão do Postgres.
Nosso exemplo, primeiro executado no 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)
Na consulta anterior, você pode ver que o hit no buffer compartilhado para a consulta IN foi 9 e que levou 3 leituras de índice para obter os resultados do escaneamento de índice. No PostgreSQL, o termo hit compartilhado se refere a um tipo específico de cache hit relacionado à gerenciamento de buffer. Um hit compartilhado ocorre quando o PostgreSQL acessa um bloco de dados ou página do pool de buffers compartilhados, em vez de do disco, melhorando a performance das consultas.
O mesmo exemplo, agora executado no 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)
Como você pode ver, com o Postgres 17, a taxa de acesso à cache compartilhada é reduzida para 5 e, mais importante, ela apenas realiza uma leitura de índice (ao contrário das 3 leituras no caso do Postgres 16). Com esta melhoria no Postgres 17, a performance das operações de arrays escalares é melhorada consideravelmente e o Postgres pode escolher planos de consulta melhor otimizados.
Manutenção de slots de replicação lógica e subscrições durante a atualização
A manutenção de slots de replicação lógica e a migração de dependências de subscrição durante o processo de atualização maior foram outras funcionalidades de replicação lógica adicionadas ao PostgreSQL 17. Observe que essa funcionalidade será útil apenas ao atualizar do PostgreSQL 17 para versões posteriores; essa funcionalidade não é suportada para atualizações anteriores ao Postgres 17. Os slots de replicação e origens de replicação são gerados quando é construído um ambiente de replicação lógica. No entanto, essa informação é específica para o nó para gravar o status de replicação, o status do aplicativo e o status de transmissão do WAL, portanto, eles não são atualizados como parte do processo de atualização. Assim que o nó publicado for atualizado, o usuário precisa construir esses objetos manualmente.
O processo pg_upgrade é melhorado no PostgreSQL 17 para referenciar e reconstruir esses objetos internos; essa funcionalidade permite que a replicação seja automaticamente retomada durante a atualização de um nó que tem replicação lógica. Anteriormente, ao realizar uma atualização de versão maior, os usuários tinham que descartar slots de replicação lógica, exigindo que eles re-sincronizassem dados com os subscritores após a atualização. Isto adicionava complexidade e aumentava o tempo de inatividade durante as atualizações.
Você precisa seguir os seguintes passos quando atualizando o cluster publicador:
- Certifique-se de que qualquer assinatura para o publicador esteja temporariamente desabilitada executando
ALTER SUBSCRIPTION….DISABLE. Essas são habilitadas depois que o processo de atualização for concluído. - Defina o
wal_leveldo novo cluster paralogical. - O
max_replication_slotsno novo cluster deve ser definido para um valor maior ou igual ao número de slots de replicação no cluster antigo. - Os plugins de saída usados pelos slots devem estar instalados no novo cluster.
- Todas as mudanças do cluster antigo já foram replicadas para o cluster de destino antes da atualização.
- Todos os slots no cluster antigo devem ser utilizáveis; você pode garantir isso verificando as colunas conflitantes na view pg_replication_slots. O conflito deve ser falso para todos os slots no cluster antigo.
- Nenhum slot no novo cluster deve ter um valor de
falsena colunaTemporaryda viewpg_replication_slots. Não deve haver slots de replicação lógica permanentes no novo cluster.
O processo pg_upgrade de atualização de slots de replicação resultará em um erro se algum dos pré-requisitos acima não for atendido.
Conclusão
Com o PostgreSQL 17, o foco da comunidade continua sendo tornar o PostgreSQL mais performático, escalável, seguro e pronto para empresas. O Postgres 17 também melhora a experiência do desenvolvedor, adicionando novas funcionalidades para compatibilidade e tornando as funcionalidades existentes mais poderosas e robustas.
Além da versão 17, o PostgreSQL continuará crescendo, melhorando e tornando-se mais performático para atender a aplicações empresariais que exigem bancos de dados mais escaláveis. A escalabilidade (tanto horizontal quanto vertical) melhorou ao longo dos anos, mas certamente há espaço para melhorar a capacidade horizontal adicionando capacidades de divisão ao PostgreSQL. Vamos ver melhorias na replicação lógica, com mais funcionalidades em área de replicação de DDL ou replicação de objetos faltantes (como sequências) e melhor gerenciamento de nós. A comunidade também reconhece a necessidade de tornar o PostgreSQL maiscompatível, portanto as melhorias no comando MERGE no Postgres 17, e planos para mais funcionalidades de compatibilidade além do Postgres 17.
Source:
https://dzone.com/articles/postgresql-17-a-major-step-forward-in-performance