O DBMS do Postgres Pro Standard foi projetado para entregar nossos produtos aos usuários mais rapidamente do que podemos através do PostgreSQL. Esses recursos que ainda não estão incluídos no PostgreSQL, mas estão em um caminho sólido, incluem-se no Postgres Pro Standard. Além disso, o Postgres Pro Standard inclui algumas extensões exigidas por nossos clientes, mas não estão disponíveis na distribuição padrão do PostgreSQL.
Às vezes, há exceções quando no Postgres Pro Standard, a pedido dos usuários e para sua satisfação, são incluídos recursos menos triviais, que em um bom local apenas no Postgres Pro Enterprise. Em particular, é o PTRACK, sobre isso abaixo.
Nem todos, mas uma parte justa das extensões e utilitários adicionais incluídos no Standard, foram desenvolvidos pelo Postgres Professional. Todos os patches do Postgres Pro foram inventados e implementados por nossos próprios esforços. Vamos começar com as melhorias que exigiram intervenção no mecanismo de banco de dados.
O Postgres Pro Standard difere do PostgreSQL em dois níveis: o conjunto de extensões e utilitários que estão na montagem e o próprio kernel. Alguns patches úteis foram aplicados ao kernel que otimizam o desempenho (por exemplo, um detector de trava sem freio) e patches que aumentam a eficiência de utilitários e extensões (por exemplo, para fazer o pg_probackup funcionar com força total, o patch do PTRACK 2.0 é aplicado). As diferenças entre a versão principal do Standard e o PostgreSQL são minimizadas para a maior compatibilidade possível. Por exemplo, a extensão pg_pathman está incluída no Standard, mas pode ser baixada no github, criada e instalada no PostgreSQL, e não haverá problemas de compatibilidade.
Vamos começar com as mudanças no kernel.
Verificando versões da UTI
No PostgreSQL, por padrão, eles são usados para comparar cadeias, comparando-as usando a biblioteca padrão C. Mas também há a possibilidade de usar a biblioteca
ICU desenvolvida pela IBM para o mesmo objetivo. Essa biblioteca é valiosa para nós principalmente porque fornece classificação independente de plataforma. É por isso que, por exemplo, é usado em 1C, e as montagens PostgreSQL "for one-es" estão trabalhando com essa biblioteca há muito tempo.
Além disso, as comparações de cadeias de caracteres através da UTI são às vezes mais rápidas que através da libc, e o número de caracteres conhecidos por ela é maior. Em geral, uma biblioteca útil. O Postgres Pro Standard trabalha com ele desde a primeira versão (9.5). No PostgreSQL, o trabalho com a UTI é possível desde a versão 10.
A biblioteca é útil, mas você precisa ter em mente algumas situações de emergência. Suponha que um usuário do DBMS tenha decidido atualizar o sistema operacional. Juntamente com o sistema operacional, a biblioteca da ICU também pode ser atualizada e a ordem das palavras na classificação será alterada. Depois disso, imediatamente todos os índices se tornarão inutilizáveis: a pesquisa de índices fornecerá resultados incorretos. Nesses casos, a base informou que a versão da UTI havia mudado e parado.
Mas esta é uma decisão dolorosamente difícil. Após discussões e uma pesquisa com os clientes, foi decidido suavizar o comportamento. Agora apenas as versões de COLLATION (regras de classificação) são verificadas. Se as versões do COLLATION usadas no banco de dados foram alteradas, o banco de dados emitirá um aviso quando o DBMS for iniciado, mas não será interrompido. Ele também lembra o usuário no início de cada sessão.
Otimização de bloqueios, junções e GROUP BY
O mecanismo de detecção de deadlock pode prejudicar o desempenho. O padrão não pode mais: o patch do kernel permite que ele funcione sem frear. Após grandes melhorias no mecanismo de verificação, esses problemas aparecem apenas em um grande número de núcleos e conexões.
Melhor estimativa do número de resultados de junções na presença de índices adequados.
Agora você pode usar índices adequados para agrupar e classificar campos. Esse recurso foi incluído pela primeira vez no Padrão 11.1.1 e no Enterprise 11.2.1. Nosso padrão 12 também possui um.
Fedor Sigaev, CTO do Postgres Professional, ofereceu esses patches úteis à comunidade, eles estão sendo considerados e, esperamos, serão incluídos na versão PG 13.
Ilustramos a otimização da operação GROUP BY com exemplos: eles são claros e facilmente reproduzíveis.
O ponto desse patch é que o Postgres não otimizou a ordem dos campos listados em GROUP BY. E o tempo de execução depende da sequência do agrupamento (com o mesmo resultado da consulta). Há detalhes na
discussão na lista de discussão dos
hackers .
Se o valor na primeira coluna a ser processada for exclusivo, nada mais precisará ser comparado. Se você começar de outra coluna, precisará comparar.
Chegando ao teste:
DROP TABLE IF EXISTS btg; SELECT i AS id, i/2 AS p, format('%60s', i%2) AS v INTO btg FROM generate_series(1, 1000000) i;
No campo de texto v, são gerados 60 espaços, seguidos pelos números 0 ou 1. As entradas são assim:
SELECT * FROM btg ORDER BY id DESC LIMIT 3; id | p | v
VACUUM btg; ANALYSE btg; SET enable_hashagg=off; SET max_parallel_workers= 0; SET max_parallel_workers_per_gather = 0;
Agrupe os resultados:
VACUUM btg; EXPLAIN ANALYZE SELECT count(*) FROM btg GROUP BY p, v;
Plano do PostgreSQL:
QUERY PLAN
Agora na ordem inversa: ve somente então p:
EXPLAIN ANALYZE SELECT count(*) FROM btg GROUP BY v, p; QUERY PLAN
Acontece que o inverso é visivelmente mais lento. Isso ocorre porque o primeiro campo
v analisado com uma pequena dispersão de valores. Você precisa fazer muitas verificações nos campos restantes (aqui - o campo p).
Vamos ver como a mesma consulta funcionará com um patch que seleciona a ordem ideal para o processamento de colunas:
QUERY PLAN
E na ordem inversa:
QUERY PLAN
O plano diz que ali e ali a ordem de processamento é a mesma: Chave de classificação: p, v. Consequentemente, o tempo é aproximadamente o mesmo. Agora compare o que acontece quando o índice é usado.
CREATE INDEX ON btg(p, v); SET enable_seqscan=off; SET enable_bitmapscan=off; VACUUM btg; EXPLAIN ANALYZE SELECT count(*) FROM btg GROUP BY v, p ;
No PostgreSQL:
QUERY PLAN
E na ordem inversa:
QUERY PLAN
Agora no padrão:
QUERY PLAN
E na ordem inversa:
QUERY PLAN
O tempo é o mesmo novamente, o que é natural: de fato, as ações são as mesmas.
Substituindo um byte nulo na inicialização
O Postgres Pro não aceita zero bytes (0x00) nos dados; portanto, com COPY FROM eles devem ser substituídos,
caso contrário, haverá um erro . Esse é o problema real que o cliente encontrou ao importar dados de um arquivo CSV. Sua solução é substituir bytes nulos pelo caractere ASCII fornecido. Ele deve ser diferente dos caracteres QUOTE e DELIMITER usados ao executar COPY FROM; caso contrário, o resultado pode ser inesperado. Por padrão, o valor da variável nul_byte_replacement_on_import (string) '\ 0', ou seja, nenhuma substituição é executada.
WaitLSN
LSN é um
número seqüencial no log , ou seja, um ponteiro para uma posição no WAL (Log Sequence Number). O comando WAITLSN está aguardando para reproduzir o LSN especificado. Se o aplicativo funcionar com o mestre e a réplica, será necessário garantir que eles sejam síncronos de tempos em tempos. WAITLSN é um mecanismo de interprocesso no PostgrePro que controla a sincronização durante a
replicação síncrona . Por padrão, o tempo de espera é ilimitado. Você pode interromper a espera pressionando Ctrl + C ou parando o servidor postgres. Você também pode definir o tempo limite adicionando a dica TIMEOUT ou verificar o status do LSN de destino sem esperar usando a dica NOWAIT.
Suponha que um aplicativo execute uma determinada ação, receba o número LSN do DBMS no mestre e agora deseje garantir que as ações na réplica sejam sincronizadas com o mestre, ou seja, o aplicativo pode ter certeza de que o que foi gravado no assistente já chegou à réplica e está pronto para ser lido. Por padrão, isso geralmente não é garantido. WAITLSN permite controlar essa interação e selecionar um modo de suspensão de INFINITELY por padrão, para TIMEOUT e NOWAIT.
Relendo variáveis do antigo recovery.conf
Em um sinal SIGHUP, o PostgreSQL relê o postgresql.conf, mas não o recovery.conf. Um patch de kernel relativamente novo introduzido no Standard e Enterprise 10.4.1. forçado a reler e recovery.conf. Mas no Postgres 12 não há nenhum arquivo recovery.conf: todas as variáveis são transferidas para o postgresql.conf. No entanto, embora o arquivo inteiro seja relido, as variáveis de recovery.conf não foram redefinidas pelo SIGHUP, mas exigiram a reinicialização do Postgres. No padrão, isso não é necessário: tudo é lido e redefinido.
Suporte para PTRACK
O PTRACK 2.0 é um mecanismo PTRACK reprojetado para as versões Standard e Enterprise 11 e anteriores. No nível do DBMS, funcionou graças ao patch do kernel e agora a extensão ptrack foi adicionada ao
patch . O PTRACK 2.0 rastreia alterações na página de dados e fornece uma interface para recuperar essas informações. Ele pode ser usado tanto para fins de diagnóstico, por exemplo, para ter uma idéia de quão fortemente a instância "sofreu mutação" em relação a um determinado momento, definido como um número sequencial no log (LSN) e para criar backups incrementais.
A parte mais difícil e "cara" de um procedimento de backup incremental, como regra, é isolar um subconjunto de páginas alteradas de todo o conjunto de páginas em um cluster. Como o servidor pode executar essa tarefa e fornecer rapidamente informações sobre as páginas alteradas, o tempo de backups incrementais usando o PTRACK é reduzido significativamente.
O PTRACK 2.0 usa uma tabela de hash de um tamanho especificado na memória compartilhada, sincronizada periodicamente com o arquivo ptrack.map.
Devido a uma alteração fundamental do mecanismo interno de operação e uma interface de usuário incompatível com versões anteriores, a extensão ptrack está disponível apenas na 12ª versão do PostgresPro Standard e Enterprise, e estará disponível como patch e extensão no PostgreSQL 12.
Editando comandos no psql para Windows
O suporte avançado para editar comandos de entrada no psql para Windows é implementado usando o WinEditLine. Agora você pode exibir os caracteres de diferentes alfabetos simultaneamente (em particular, o cirílico é normalmente exibido no Windows não russo).
Estrutura do pacote unificado
A estrutura dos pacotes de pacotes binários para todas as distribuições Linux é unificada, a fim de simplificar a migração entre elas e permitir a instalação de vários produtos diferentes baseados no PostgreSQL juntos, sem conflitos. Isso pode ser encontrado no
capítulo 16 da documentação.
Agora sobre as extensões:
dump_stat
Apareceu tão cedo quanto 9,5. Ao transferir ou restaurar dados, as estatísticas acumuladas geralmente não são transferidas. Se você montá-lo novamente com o comando ANALYZE, ele será executado para todo o cluster e não para o banco de dados especificado. Isso pode exigir muito tempo extra para bancos de dados grandes.
A extensão dump_stat
fornece funções que permitem descarregar e restaurar o conteúdo da tabela pg_statistic. Ao executar o upload / recuperação de dados, você pode usar dump_stat para transferir estatísticas existentes para um novo servidor, sem precisar executar o comando ANALYZE para todo o cluster.
A função dump_statistic descarrega o conteúdo do catálogo do sistema pg_statistic. Ele produz um INSERT para cada tupla em pg_statistic, exceto aqueles que contêm estatísticas sobre tabelas nos esquemas information_schema e pg_catalog.
jsquery
Lembre-se de que
essa é uma extensão para trabalhar com JSON (B), não JS. Ele fornece um conjunto de funções para processar esses tipos de dados. Essa é uma linguagem de consulta especial para pesquisa eficiente, usando índices, em JSON (B). No
artigo no hub, você pode ver alguns exemplos de jsquery e métodos alternativos de trabalho com JSON (B), por exemplo, JSONPath (ambos do desenvolvimento de nossa empresa).
online_analyze
Esta extensão
fornece um conjunto de funções que atualizam imediatamente as estatísticas nas tabelas especificadas após as operações INSERT, UPDATE, DELETE ou SELECT INTO nelas. O autor da extensão é Fedor Sigaev.
Para usar o módulo online_analyze, você deve carregar a biblioteca compartilhada:
LOAD 'online_analyze';
As atualizações de estatísticas podem ser personalizadas. Por exemplo, defina uma porcentagem do tamanho da tabela ou o número mínimo (limite) de alterações de linha, após o qual as estatísticas serão coletadas imediatamente.
pg_pathman
A
extensão pg_pathman no Postgres Professional foi criada antes do kernel do PostgreSQL e implementou um conjunto completo de funções bastante completo para a criação de partições. Portanto, muitas operações com seções podem ser feitas com um e outro mecanismo. É aconselhável não misturar as seções criadas pelo particionamento declarativo e pg_pathman.
No entanto, muitas operações do pg_pathman ainda são mais rápidas e alguns recursos estão ausentes no PostgreSQL. Por exemplo, criação automática (corte) de seções. No PostgreSQL, você precisa definir os limites de cada seção. Se preenchermos dados sobre os quais não se sabe antecipadamente quantas seções podem e devem ser dispersas, é conveniente simplesmente definir o intervalo e deixar o software cortar as seções em si - o quanto for necessário. O pg_pathman sabe como o PostgreSQL não. Mas, começando na PG 11, há uma seção padrão (padrão), na qual você pode despejar todos os registros que não se enquadram nas seções com limites especificados.
Existe um acordo básico com os líderes da comunidade PostgreSQL de que, no futuro, os melhores, enquanto os recursos exclusivos do pg_pathman serão incluídos no ramo principal. Mas até esse momento, o pg_pathman pode facilitar a vida dos administradores de banco de dados e programadores de aplicativos.
Crie uma extensão:
CREATE EXTENSION pg_pathman;
O pg_pathman permite que você divida tabelas grandes em seções e fornece uma API conveniente - um conjunto de funções para criar seções e trabalhar com elas. Por exemplo, usando a função
create_range_partitions(relation REGCLASS, expression TEXT, start_value ANYELEMENT, p_interval INTERVAL, p_count INTEGER DEFAULT NULL, partition_data BOOLEAN DEFAULT TRUE);
nós podemos perguntar
SELECT create_range_partitions('log', 'dt', NULL::date, '1 month'::interval);
após o qual adicionamos seções:
SELECT add_range_partition('log', NULL, '2017-01-01'::date, 'log_archive', 'ts0'); SELECT add_range_partition('log', '2017-01-01'::date, '2017-02-01'::date, 'log_1'); SELECT add_range_partition('log', '2017-02-01'::date, '2017-03-01'::date', log_2');
O log de archive será criado no espaço de tabela ts0, o restante é por padrão. Mas você não pode especificar seções explicitamente, mas confie nesta operação do DBMS configurando o intervalo e criando seções em uma etapa:
SELECT create_range_partitions('log', 'dt', '2017-01-01'::date, '1 month'::interval);
Em uma tabela simples, será assim:
CREATE TABLE pg_pathmania(id serial, val float); INSERT INTO pg_pathmania(val) SELECT random() * 1000 FROM generate_series(1, 1000); SELECT create_range_partitions('pg_pathmania', 'id', 0, 50); test_parti=# \d+ pg_pathmania Table "public.pg_pathmania" Column | Type | Collation | Nullable | Default | Storage | S tats target | Description
No PostgreSQL, teríamos que criar cada seção com nossa própria equipe. Nesses casos, é claro, eles escrevem um script que gera o código DDL necessário automaticamente. Você não precisa escrever scripts no pg_pathman, tudo já está lá. Mas isso não é o mais interessante. Vamos inserir um registro que não só não identifica o código em nenhuma das seções existentes, mas também não se enquadra no mais próximo:
INSERT INTO pg_pathmania(id, val) VALUES (2000, 277.835794724524);
Mais uma vez, verifique o conteúdo da tabela com \ d + pg_pathmania:
Child tables: pg_pathmania_1, pg_pathmania_10, ... pg_pathmania_39, pg_pathmania_4, pg_pathmania_40, pg_pathmania_41,
Aqui está o que aconteceu: pg_pathman viu que o registro com id = 2000 não se enquadra nas seções já criadas, calculou quantas precisam ser criadas, sabendo o intervalo RANGE com o qual a tabela foi particionada antes e criou a seção em que o novo registro se encaixa e, é claro, todas as seções entre o limite superior das seções antigas e o limite inferior da nova seção. Isso é muito conveniente e, nos casos em que os valores do campo de divisão dos dados atualizados são pouco previstos, essa é uma séria vantagem do pg_pathman.
pg_query_state
Essa extensão que desenvolvemos nos
permite descobrir o estado atual das solicitações no processo de veiculação. Existe desde a versão 9.5 e é devido ao nascimento de inúmeras solicitações de administradores de clientes.
O fato é que EXPLAIN ANALYZE permite visualizar estatísticas de execução coletadas de cada nó da árvore do plano, mas essas estatísticas são coletadas somente após a conclusão da consulta. Mas na vida, infelizmente, há situações em que você precisa examinar o que a solicitação ainda não foi concluída e talvez não vá terminar. pg_query_state permite visualizar as estatísticas atuais de uma consulta em execução em um processo de serviço externo. Nesse caso, o formato da saída resultante é quase idêntico à saída do comando EXPLAIN ANALYZE usual.
Utilitários:
pgBouncer
Este é um
extrator de conexão tão
popular que seria estranho falar sobre isso aqui. É apenas parte do Standard e terá que ser instalado separadamente no PostgreSQL de baunilha.
pg_probackup
O pg_probackup é um dos nossos desenvolvimentos mais populares. Este é um gerenciador de backup e recuperação que está sendo desenvolvido e atualizado principalmente por Anastasia Lubennikova, Grigory Smolkin e pela comunidade de usuários.
Vantagens competitivas do pg_probackup: backup incremental com granularidade de bloco (8 KB), três modos de backup incremental (PAGE, DELTA, PTRACK), verificação de integridade de backup sob demanda, verificação de cluster do PostgreSQL, compactação de backup, recuperação parcial, etc.
O modo de cópia incremental do PTRACK, contando com
a extensão do mesmo nome como parte do mecanismo reprojetado - PTRACK 2.0 - tornou-se ainda mais rápido e agora é inequivocamente o mais rápido e mais barato dos modos pg_probackup.
pg_repack
O pg_repack é um utilitário popular, sua operação é semelhante ao VACUUM FULL ou
CLUSTER . Ele não apenas reempacota as tabelas, remove os vazios, mas também sabe como restaurar a ordem física dos índices em cluster. Ao contrário do CLUSTER e do VACUUM FULL, ele realiza essas operações "on the go", sem bloqueios exclusivos de tabela e geralmente trabalhando com eficiência. Não está incluído na versão baunilha.
pg_variables
Sobre esta extensão em um habr, há um
artigo interessante
de nosso funcionário Ivan Frolkov. O motivo da extensão é que trabalhar com resultados intermediários às vezes é inconveniente e caro. O artigo explora alternativas. O mais comum deles são tabelas temporárias.
Como um data warehouse temporário, a extensão pg_variables é muito mais produtiva que as tabelas temporárias (os testes pgbench estão no artigo) e é mais conveniente: o conjunto de dados é definido por um par "pacote - variável", que pode ser passado como parâmetros, retornado de uma função etc. Existem funções set / get para trabalhar com variáveis. Portanto, por exemplo, você pode armazenar muitas variáveis (pacote é o nome do pacote e a expressão após o ponto decimal são as variáveis deste pacote:
SELECT pgv_set_int('package','#'||n,n), n FROM generate_series(1,1000000) AS gs(n);
As variáveis têm uma propriedade interessante: não um bug ou vantagem, mas um recurso: os dados armazenados pelo meio de extensão existem fora das transações - eles são salvos no caso de corrigir uma transação e no caso de reversão; Além disso, mesmo ao executar um comando separado, dados parciais podem ser obtidos:
SELECT pgv_insert('package', 'errs', row(n)) FROM generate_series(1,5) AS gs(n) WHERE 1.0/(n-3)<>0; ERROR: there is a record in the variable "errs" with same key test_parti=# SELECT * FROM pgv_select('package','errs') AS r(i int); i
Por um lado, isso não é muito conveniente - em alguns casos é necessário prever a exclusão de dados inseridos incorretamente, mas em outros pode ser muito útil - por exemplo, salvando alguns dados mesmo no caso de uma reversão de transação. A
documentação possui detalhes.
Em conclusão, mais algumas extensões:
sr_plan, plantuner
sr_plan salva e restaura planos de consulta. Inclua-o assim:
SET sr_plan.write_mode = true;
Depois disso, os planos para todas as consultas subseqüentes serão armazenados na tabela sr_plans até que essa variável seja configurada como false. Os planos para todas as solicitações, incluindo repetidas, são salvos.
O plantuner suporta dicas para o planejador conectar ou desconectar índices especificados ao executar uma consulta. Existem apenas duas variáveis GUC: enable_index / desable_index:
SET plantuner.disable_index='id_idx2';
Extensões para pesquisa de texto completo: shared_ispell, pg_tsparser
A extensão shared_ispell, que permite colocar
dicionários na memória compartilhada, está no Standard e não no PostgreSQL. Nosso conjunto hunspell-dict possui dicionários para idiomas:
- hunspell_en_us,
- hunspell_fr,
- hunspell_nl_nl,
- hunspell_ru_ru
A
extensão pg_tsparser é
um analisador de pesquisa de texto
alternativo . Essa extensão altera a estratégia de análise de texto padrão para palavras que incluem sublinhados, além de números e letras separados por sublinhados. Além das partes individuais da palavra retornadas por padrão, pg_tsparser também retorna a palavra inteira. Isso é muito importante para documentação técnica ou artigos como este, em que o código do programa é encontrado e nele existem palavras como "pg_tsparser", "pg_probackup", "jsonb_build_object". Esse analisador percebe essas palavras não apenas como um conjunto de componentes, mas também como um único token e, assim, melhora a qualidade da pesquisa.
Extensões para 1C
- mchar é um tipo de dados opcional para compatibilidade com o Microsoft SQL Server;
- fulleq - fornece um operador de igualdade adicional para compatibilidade com o Microsoft SQL Server;
- fasttrun — - , pg_class.
, PostgresPro Standard PostgreSQL. , , , ,
.