🕰️ 📱 🐂 Após a reunião "Novos recursos do PostgreSQL 11" ⛷️ 🛳️ 🤷🏼

Hoje falaremos sobre os recursos mais importantes do PostgreSQL 11. Por que apenas sobre eles - porque nem todo mundo precisa de alguns recursos, então escolhemos os mais populares.

Conteúdo

Jit compilação

O PostgreSQL finalmente introduziu a compilação JIT, ou seja, compilar consultas em código binário. Para fazer isso, compile o PostgreSQL com suporte à compilação JIT (Compile time 1 (--with-llvm)) . Ao mesmo tempo, a máquina deve ter a versão LLVM não inferior a 3.9.

O que pode acelerar o JIT?

Consultas com a cláusula WHERE, ou seja, tudo o que vem após esta palavra-chave. Isso nem sempre é necessário, mas a oportunidade é útil.
Cálculo da lista de alvos: na terminologia do PostgreSQL, é tudo o que há entre select e from.
Agregados.
Converta registros de uma visualização para outra (Projeção). Por exemplo, quando você aplica junção a duas tabelas, o resultado é uma nova tupla contendo campos de ambas as tabelas.
Deformação da tupla. Um dos problemas de qualquer banco de dados, pelo menos em letras minúsculas, relacionais, é como obter um campo de um registro no disco. Afinal, pode haver nulo, eles têm registros diferentes e, em geral, essa não é a operação mais barata.

Compile time 2 significa que o JIT não é usado. No PostgreSQL, há um momento de planejamento de consultas, quando o sistema decide o que vale JIT e o que não vale. Nesse ponto, ele faz JITs e, em seguida, o executor é executado como está.

O JIT é tornado plugável. Por padrão, ele funciona com o LLVM, mas você pode conectar qualquer outro JIT.

Se você compilou o PostgreSQL sem o suporte ao JIT, a primeira configuração não funcionará. Opções implementadas para desenvolvedores, existem configurações para funções JIT individuais.

O próximo ponto sutil está relacionado a jit_above_cost. O JIT em si não é gratuito. Portanto, o PostgreSQL assume como padrão a otimização de JIT se o custo de uma consulta exceder 100 mil papagaios condicionais, nos quais são explicados, explicados, analisados e assim por diante. Esse valor é escolhido aleatoriamente, portanto, preste atenção nele.

Mas nem sempre depois de ativar o JIT, tudo funciona imediatamente. Geralmente, todo mundo começa a experimentar o JIT usando a tabela select * from, na qual id = 600 consulta e eles falham. Provavelmente, é necessário de alguma forma complicar a solicitação e, em seguida, todos geram um banco de dados gigante e compõem a solicitação. Como resultado, o PostgreSQL repousa sobre as capacidades do disco; não possui a capacidade de buffers e caches compartilhados.

Aqui está um exemplo completamente abstrato. Existem 9 campos nulos com frequências diferentes, para que você possa observar o efeito da deformação da tupla.

select i as x1,
case when i % 2 = 0 then i else null end as x2,
case when i % 3 = 0 then i else null end as x3,
case when i % 4 = 0 then i else null end as x4,
case when i % 5 = 0 then i else null end as x5,
case when i % 6 = 0 then i else null end as x6,
case when i % 7 = 0 then i else null end as x7,
case when i % 8 = 0 then i else null end as x8,
case when i % 9 = 0 then i else null end as x9
into t
from generate_series(0, 10000000) i;

vacuum t;
analyze t;

O PostgreSQL tem muitas possibilidades e, para ver as vantagens do JIT, desative as duas primeiras linhas para não interferir e redefina os limites.

set max_parallel_workers=0;
set max_parallel_workers_per_gather=0;
set jit_above_cost=0;
set jit_inline_above_cost=0;
set jit_optimize_above_cost=0;

Aqui está o próprio pedido:

set jit=off;
explain analyze
select count(*) from t where
sqrt(pow(x9, 2) + pow(x8,2)) < 10000;

set jit=on;
explain analyze
select count(*) from t where
sqrt(pow(x9, 2) + pow(x8,2)) < 10000;

E aqui está o resultado:

Planning Time: 0.71 ms
Execution Time: 1986.323 ms

VS

Planning Time: 0.060 ms
JIT:
Functions: 4
Generation Time: 0.911 ms
Inlining: true
Inlining Time: 23.876 ms
Optimization: true
Optimization Time: 41.399 ms
Emission Time: 21.856 ms
Execution Time: 949.112 ms

O JIT ajudou a acelerar a solicitação pela metade. O tempo de planejamento é praticamente o mesmo, mas esse é provavelmente o resultado do cache do PostgreSQL, então ignore-o.

Se resumir, foram necessários cerca de 80 ms para a compilação do JIT. Por que o JIT não é gratuito? Antes de executar a solicitação, você precisa compilá-la e isso também leva tempo. E três ordens de magnitude a mais que o planejamento. Não é um prazer caro, mas vale a pena devido ao tempo de execução.

Dessa forma, você pode usar o JIT, embora nem sempre seja benéfico.

Particionamento

Se você prestou atenção ao particionamento no PostgreSQL, provavelmente percebeu que ele foi feito para exibição. A situação melhorou um pouco na versão 10, quando uma declaração declarativa de partições (seções) apareceu. Por outro lado, tudo permaneceu o mesmo por dentro e funcionou aproximadamente o mesmo que nas versões anteriores, ou seja, ruim.
De muitas maneiras, esse problema foi resolvido pelo módulo pg_pathman, que permitiu trabalhar com seções e cortá-las no tempo ideal ideal no tempo de execução.

Na versão 11, o particionamento foi bastante aprimorado:

Primeiro, a tabela de partições pode ter uma chave primária, que deve incluir a chave de partição. De fato, essa é uma chave semi-primária ou uma semi-chave primária. Infelizmente, você não pode fazer uma chave estrangeira nela. Espero que isso seja corrigido no futuro.
Agora também é possível particionar não apenas por intervalo, mas também por lista e hash. O hash é bastante primitivo, o restante da expressão é usado para isso.
Ao atualizar, a linha se move entre as seções. Anteriormente, era necessário escrever um gatilho, mas agora é feito automaticamente.

A grande questão é: quantas seções posso ter? Honestamente, com um grande número de seções (milhares e dezenas de milhares), o recurso não funciona bem. Pg_pathman faz melhor.

Também fez seções por padrão. Novamente, em pg_pathman, você pode fazer a criação automática de seções, o que é mais conveniente. Aqui, tudo o que não pôde ser empurrado em algum lugar cai na seção. Se em um sistema real para fazer isso por padrão, depois de algum tempo você fica com essa bagunça, que atormenta.

O PostgreSQL 11 agora pode otimizar o particionamento se duas tabelas forem unidas por uma chave de partição e os esquemas de particionamento corresponderem. Isso é controlado por um parâmetro especial, que é desativado por padrão.

Você pode calcular agregados para cada seção separadamente e adicionar. Por fim, você pode criar um índice na tabela particionada pai e, em seguida, serão criados índices locais em todas as tabelas conectadas a ela.

Na seção "O que há de novo", uma coisa maravilhosa é mencionada - a capacidade de descartar seções ao executar uma solicitação. Vamos verificar como funciona. O resultado é uma tabela:

Fazemos um tipo e uma tabela de duas colunas com uma chave primária, com uma coluna bigserial, inserindo os dados. Criamos a segunda tabela, que será particionada e será uma cópia da primeira. Adicione a chave primária à tabela particionada.

A tabela consistirá em dois tipos de entradas: “babás do sexo feminino” e “motoristas do sexo masculino”. E haverá uma motorista do sexo feminino. Fazemos duas seções, dividimos por lista, adicionamos a chave primária e inserimos todos os dados da tabela na qual tudo isso é gerado. O resultado foi completamente desinteressante:

Preste atenção ao pedido. Selecionamos tudo de uma tabela não particionada, conectamos a uma tabela particionada. Pegamos um pedaço pequeno e escolhemos apenas um tipo, eles passam por um. Indicamos que a coluna oss deve ter um valor. Acontece uma seleção de drivers sólidos.

Na execução, desabilitamos especificamente a paralelização, porque o PostgreSQL 11, por padrão, paraleliza muito ativamente consultas mais ou menos complexas. Se olharmos para o plano de execução (explique analisar), pode-se ver que o sistema adicionou os dados nas duas seções: na babá e nos motoristas, embora as babás não estivessem lá. Não houve chamadas para o buffer. Tempo gasto, condição usada, embora o PostgreSQL possa descobrir tudo. Ou seja, a declaração de eliminação da partição não funciona imediatamente. Talvez nas próximas versões isso seja corrigido. Nesse caso, o módulo pg_pathman nesse caso funciona sem problemas.

Índices

Otimização de lances de maneira monótona, ou seja, árvore b. Todo mundo sabe que, quando você insere dados em crescimento monótono, eles não são muito rápidos. Agora o PostgreSQL é capaz de armazenar em cache a página final de uma maneira especial e não percorrer todo o caminho desde a raiz até a inserção. Isso acelera significativamente o trabalho.
O PostgreSQL 10 tornou possível o uso de um índice de hash, porque começou a usar o WAL (write write log). Anteriormente, obtivemos o valor, desbloqueamos a página, retornamos o valor. Para o próximo valor, você teve que bloquear a página novamente, retornar, desbloquear e assim por diante. Agora o hash se tornou muito mais rápido. Ele permite bloquear uma página de cada vez para recuperar um registro de um índice de hash, retornar todos os valores de lá e desbloqueá-lo. Agora está implementado para HASH, GiST e GIN. No futuro, isso provavelmente será implementado para o SP-GiST. Mas para o BRIN, com sua lógica min / max, isso não pode ser feito em princípio.
Se você costumava criar índices funcionais, a atualização HOT (Heap Only Tuple) era efetivamente desabilitada. Quando um registro é atualizado no PostgreSQL, uma nova cópia é realmente criada, e isso requer colar em todos os índices que estão na tabela para que o novo valor aponte para a nova tupla. Essa otimização é implementada há muito tempo: se a atualização não alterar os campos que não estão incluídos nos índices e houver espaço livre na mesma página, os índices não serão atualizados e, na versão antiga da tupla, será colocado um ponteiro para a nova versão. Isso permite reduzir um pouco a gravidade do problema com as atualizações. No entanto, essa otimização não funcionaria se você tivesse índices funcionais. No PostgreSQL 11, ele começou a funcionar. Se você criou um índice funcional e atualizou uma tupla que não altera de que depende o índice funcional, a atualização HOT funcionará.

Índices de Cobertura

Essa funcionalidade foi implementada pelo PostgresPro há três anos e, durante todo esse tempo, o PostgreSQL tentou adicioná-lo. Os índices de cobertura significam que você pode adicionar colunas extras ao índice exclusivo, diretamente na tupla do índice.

Porque Todo mundo adora a varredura apenas de índice para o seu trabalho rápido. Para isso, são construídos índices condicionais de "cobertura":

Mas, ao mesmo tempo, você precisa manter a exclusividade. Portanto, dois índices estão sendo criados, estreitos e amplos.
A desvantagem é que, ao aplicar vácuo, inserir ou atualizar uma tabela, você deve atualizar os dois índices. Portanto, a inserção em um índice é uma operação lenta. E o índice de cobertura permitirá gerenciar apenas um índice.

É verdade que ele tem algumas limitações. Mais precisamente, os benefícios que podem não ser imediatamente entendidos. As colunas c e d no primeiro índice de criação não precisam ser tipos escalares para os quais um índice de árvore b é definido. Ou seja, eles não têm necessariamente uma comparação mais-menos. Pode ser pontos ou polígonos. A única coisa é que a tupla deve ser menor que 2,7 Kb, porque não há brindes no índice, mas você pode se encaixar naquilo que não pode ser comparado.

No entanto, dentro do índice com essas colunas cobertas garantidas, nenhum cálculo é feito durante a pesquisa. Isso deve ser feito por um filtro que esteja acima do índice. Por um lado, por que não calculá-lo dentro do índice, por outro lado, é uma chamada de função extra. Mas nem tudo é tão assustador quanto parece.

Além disso, você pode adicionar essas colunas cobertas à chave primária.

SP GiST

Poucas pessoas usam esse índice porque é bastante específico. No entanto, tornou-se possível armazenar nele não exatamente o que foi inserido. Refere-se ao índice com perdas, compactação. Tome polígonos como exemplo. Em vez disso, uma caixa delimitadora é colocada no índice, ou seja, o retângulo mínimo que contém o polígono desejado. Nesse caso, representamos o retângulo como um ponto no espaço quadridimensional e, em seguida, trabalhamos com o quad3 clássico, no espaço quadridimensional.

Também para o SP-GiST introduziu a operação "prefix search". Retorna true se uma linha é um prefixo de outra. Eles o introduziram não apenas assim, mas por uma solicitação desse tipo, com suporte ao SP-GiST.

SELECT * FROM table WHERE c ^@ „abc“

Na árvore b, há um limite de 2,7 Kb por linha, mas o SP-GiST não. É verdade que o PostgreSQL tem uma limitação: um único valor não pode exceder 1 GB.

Desempenho

A verificação apenas do índice de bitmap foi exibida . Funciona da mesma forma que a varredura de índice clássico, exceto que não pode garantir nenhum pedido. Portanto, é aplicável apenas a algumas agregações, como count (*), porque o bitmap não pode transferir campos do índice para o executor. Ele só pode relatar o fato de um registro que satisfaça as condições.
A próxima inovação é a atualização do Mapa do Espaço Livre durante a aplicação do vácuo . Infelizmente, nenhum dos desenvolvedores de sistemas que trabalham com o PostgreSQL pensa que é necessário excluir no final da tabela, caso contrário, furos e espaço não alocado serão exibidos. Para acompanhar isso, implementamos o FSM, o que nos permite não ampliar a tabela, mas inserir a tupla nos vazios. Anteriormente, isso era feito com vácuo, mas no final. E agora o vácuo é capaz de fazer isso no processo e, em sistemas com muita carga, ajuda a manter o tamanho da mesa sob controle.
Possibilidade de pular a verificação de índice durante a execução a vácuo . O fato é que todos os índices do PostgreSQL, de acordo com a teoria do banco de dados, são chamados de secundários. Isso significa que os índices são armazenados longe da tabela; os ponteiros levam a eles a partir deles. A varredura de índice apenas permite que você não faça esse salto nos ponteiros, mas retire diretamente do índice. Mas o vácuo, que exclui registros, não pode examiná-los no índice e decidir se deve excluí-los ou não, simplesmente porque não existem esses dados no índice. Portanto, o vácuo é sempre realizado em duas passagens. Primeiro, ele passa pela mesa e descobre o que precisa excluir. Em seguida, ele vai para os índices anexados a esta tabela, exclui os registros que se referem aos encontrados, retorna à tabela e exclui para o que estava indo. E a etapa de ir para os índices nem sempre é necessária.

Se desde o último vácuo não houve exclusão ou atualização, você não possui registros mortos, não é necessário excluí-los. Nesse caso, você não pode ir para o índice. Existem sutilezas adicionais, o b-tree não exclui suas páginas imediatamente, mas em duas passagens. Portanto, se você excluiu muitos dados da tabela, precisará fazer o vácuo. Mas se você quiser liberar espaço nos índices, aspire duas vezes.

Alguém ficará surpreso. Qual é a tabela em que não houve exclusão ou atualização? De fato, muitos lidam com isso, simplesmente não pensam. Essas são apenas tabelas anexadas, onde, por exemplo, os logs são adicionados. Neles, a remoção é extremamente rara. E isso economiza bastante a duração do vácuo / vácuo automático, reduz a carga no disco, o uso de caches e assim por diante.
Confirmar transações competitivas simultâneas . Isto não é uma inovação, mas uma melhoria. Agora o PostgreSQL detecta que irá confirmar agora e atrasa a confirmação da transação atual, aguardando o restante das confirmações. Observe que esse recurso tem pouco efeito se você tiver um servidor pequeno com 2 a 4 núcleos.
postgres_fdw (invólucros de dados estrangeiros) . O FDW é uma maneira de conectar uma fonte de dados externa para que pareça um verdadeiro pós-Congresso. O postgres_fdw permite conectar uma tabela de uma instância vizinha à sua instância, e ela parecerá quase como uma tabela real. Agora, uma das restrições para atualização e exclusão foi removida. O PostgreSQL costuma adivinhar que você precisa enviar dados brutos. A maneira de executar a solicitação de junção é bastante simples: executamos em nossa máquina, retiramos a tabela da instância usando o FDW, descobrimos a chave primária de identificação que precisamos excluir e aplicamos atualização e / ou exclusão, ou seja, os dados que vamos e voltamos . Agora é possível fazer. Obviamente, se as tabelas estiverem em máquinas diferentes, isso não é tão fácil, mas o FDW permite que você faça a máquina remota executar operações, e nós apenas esperamos.
toast_tuple_target . Existem situações em que os dados vão além dos limites após os quais é necessário brindar, mas ao mesmo tempo brindar esses valores nem sempre é agradável. Suponha que você tenha um limite de 90 bytes e precise ajustar 100. Você precisa iniciar o brinde por 10 bytes, adicioná-los separadamente e, quando selecionar esse campo, precisará acessar o índice do brinde, descobrir onde estão os dados necessários, acessar a tabela do brinde, coletar e dar.

Agora, com a ajuda do ajuste fino, você pode alterar esse comportamento para todo o banco de dados ou uma tabela separada, para que saídas tão pequenas não exijam o uso de brinde. Mas você deve entender o que está fazendo, sem isso, nada funcionará.

WAL

WAL (Write Forward Log) é um registro de gravação antecipada. O tamanho do segmento WAL agora está definido no initdb. Graças a Deus, não ao compilar.
A lógica também mudou. Anteriormente, o conjunto de segmentos WAL era salvo a partir do momento do penúltimo ponto de verificação e agora a partir do último. Isso pode reduzir significativamente a quantidade de dados armazenados. Mas se você tiver um banco de dados de 1 TB e TPS = 1, ou seja, uma solicitação por segundo, não verá a diferença.

Backup e replicação

Truncar apareceu na replicação lógica . Foi a última das operações DML que não foi refletida na replicação lógica. Agora refletido.
Uma mensagem sobre preparação apareceu na replicação lógica . Agora você pode pegar a transação de preparação, uma confirmação de duas fases na replicação lógica. Isso é implementado para a construção de clusters - heterogêneo, homogêneo, fragmentado e não sombreado, multimaster e assim por diante.
Exceção das tabelas temporárias e não registradas de pg_basebackup . Muitos reclamaram que pg_basebackup inclui as tabelas listadas. E excluindo-os, reduzimos o tamanho do backup. Porém, contanto que você use tabelas temporárias e não registradas, caso contrário, essa opção será inútil para você.
Controle de soma de verificação na replicação de streaming (para tabelas) . Isso permite que você entenda o que aconteceu com sua réplica. Até o momento, a função é implementada apenas para tabelas.
Houve uma promoção de posições do slot de replicação . Como sempre, você pode apenas avançar, voltar apenas se houver um WAL. Além disso, você precisa entender muito bem o que está fazendo com isso e por quê. Na minha opinião, essa é mais uma opção de desenvolvimento, mas quem usa replicação lógica para alguns aplicativos exóticos pode aproveitar.

Para dba

Altere a tabela, adicione a coluna, não o padrão nulo X , escreva a tabela inteira. Há uma pequena taxa para isso: o valor padrão é armazenado separadamente. Se você escolher a tupla e precisar desta coluna, o PostgreSQL é forçado a seguir um caminho de codificação adicional para obter um valor temporário, substituí-lo na tupla e fornecê-lo a você. No entanto, pode-se viver com isso.
Vácuo / análise . Anteriormente, você só podia aplicar vácuo ou analisar um banco de dados inteiro ou uma única tabela. Agora é possível fazer isso em várias tabelas, com um comando.

Execução paralela

Construção paralela de índices b-tree . Na versão 11, tornou-se possível incorporar índices b-tree em vários trabalhadores. Se você tem uma máquina realmente boa, muitos discos e muitos núcleos, pode criar índices em paralelo, isso promete um aumento notável no desempenho.
Hash conexão paralela usando uma tabela de hash compartilhada para executores . , -. , . - , . .
, union, create table as, select create materialized view!
- (limit) . .

alter table usr reset (parallel_workers)
create index on usr(lower((so).occ)) — 2
alter table usr set (parallel_workers=2)
create index on usr(upper((so).occ)) — 1.8

parallel worker. . 16 4 ( ) 2 ., — 1,8 . , , . , .

:

explain analyze
select u1.* from usr u, usr1 u1 where
u.id=u1.id+0

, . , user — , . . , , .

, PostgreSQL 11 .

1425 , 1,5 . 1,4 . 2 . , 9.6 : 1 — 1 ., 2 1 . , 10 tuple. 11 . : user, batch, x-scan append .

:

. 211 , 702 . , 510 1473. , 2 .

parallel hash join. . — 4. , .

parallel index scan . batch . ? hash join, . user . , parallel hash, .

1 . , OLAP-, OLTP . OLTP , .

. , . , «» «», index scan, . (highly skewed data), , . . , , .
«», .

Window-

SQL:2011, .

, , . , , , , , .

websearch, . , . , .

# select websearch_to_tsquery('dog or cat');
----------------------
'dor' | 'cat'
# select websearch_to_tsquery('dog -cat');
----------------------
'dor' & !'cat'
# select websearch_to_tsquery('or cat');
----------------------
'cat'

— dog or cat — . Websearch . | , . “or cat”. , . websearch “or” . , -, .

Websearch — . : , . , .

Json(b)

10- , 11- . json json(b), tsvector. ( json(b)) - . , , , bull, numeric, string, . .

# select jsonb_to_tsvector
('{"a":"texts", "b":12}', '"string"');
-------------------
'text':1
# select jsonb_to_tsvector
('{"a":"texts", "b":12}', '["string", "numeric"]');
-------------------
'12':3 'text':1

json(b), . , , , .

PL/*

CREATE PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
FOR i IN 0..9 LOOP
INSERT INTO test1 (a) VALUES (i);
IF i % 2 = 0 THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
END LOOP;
END
$$;
CALL transaction_test1();

call, , . . . select, insert .

, , PostgreSQL . Perl, Python, TL PL/pgSQL. Perl sp begin, .

PL/pgSQL : , .

pgbench

pgbench ICSB bench — , , . if, , . case, - . --init-steps , , .

random-seed. zipfian- . / — , . - , , - , .

, , - .

PSQL

, PSQL, . exit quit.

— copy, 2 ³² . copy : 2 ³² - . , 2 ³¹ 2 ³² copy . 64- , 2 ⁶⁴ .
POSIX : NaN ⁰ = 1 1 ^NaN = 1.

Após a reunião "Novos recursos do PostgreSQL 11"