Em todo o mundo em 4 segundos no Columnstore (parte 1)

Neste artigo, vou considerar aumentar a velocidade dos relatórios. Por um relatório, quero dizer qualquer consulta a um banco de dados que use funções agregadas. Além disso, abordarei questões relacionadas aos recursos gastos na produção e suporte de relatórios, humanos e máquinas.

Nos exemplos, usarei um conjunto de dados contendo 52.608.000 registros.

Usando o exemplo de reservas analíticas não difíceis, demonstrarei que mesmo um computador fraco pode ser transformado em uma boa ferramenta para analisar uma quantidade "decente" de dados sem muito esforço.

Depois de configurar experimentos não complicados, veremos que uma tabela regular não é uma fonte adequada para consultas analíticas.

Se o leitor puder decifrar facilmente as abreviações OLTP e OLAP, pode fazer sentido ir diretamente para a seção Columnstore

Duas abordagens para trabalhar com dados

Aqui vou ser breve, porque Há informações mais do que suficientes sobre esse tópico na Internet.

Portanto, no nível mais alto, existem apenas duas abordagens para trabalhar com dados: OLTP e OLAP.

OLTP - pode ser traduzido como processamento instantâneo de transação. De fato, estamos falando sobre o processamento on-line de transações curtas que funcionam com uma pequena quantidade de dados. Por exemplo, gravando, atualizando ou excluindo um pedido. Na grande maioria dos casos, um pedido é uma quantidade extremamente pequena de dados, durante o processamento dos quais você não pode ter medo dos longos bloqueios impostos pelo RDBMS moderno.

OLAP - pode ser traduzido como processamento analítico de um grande número de transações por vez. Qualquer relatório usa essa abordagem específica, porque, na grande maioria dos casos, produz relatórios resumidos e agregados para determinadas seções.

Cada abordagem tem sua própria tecnologia. Por exemplo, para OLTP, é PostgreSQL e, para OLAP, é Microsoft SQL Server Analysis Services. Enquanto o PostgresSQL usa um formato conhecido para armazenar dados em tabelas, vários formatos diferentes foram inventados para o OLAP. Estas são tabelas multidimensionais, balde cheio de pares de valores-chave e meu columnstore favorito. Sobre o último em mais detalhes abaixo.

Por que são necessárias duas abordagens?

Observou-se que qualquer data warehouse, mais cedo ou mais tarde, enfrenta dois tipos de carga: leitura frequente (gravação e atualização, é claro, também) de quantidades extremamente pequenas de dados e leitura rara, mas quantidades muito grandes. De fato, essa é uma atividade, por exemplo, da bilheteria e da cabeça. O balcão de caixa, trabalhando o dia todo, preenche o armazenamento com pequenos pedaços de dados, enquanto no final do dia o volume acumulado, se o negócio está indo bem, atinge tamanho impressionante. Por sua vez, o gerente no final do dia quer saber quanto dinheiro a bilheteria ganha por dia.

Portanto, no OLTP, temos tabelas e índices. Essas duas ferramentas são ótimas para registrar as atividades de bilheteria com todos os detalhes. Os índices fornecem uma pesquisa rápida para um pedido gravado anteriormente, portanto, é fácil alterar um pedido. Mas, para satisfazer as necessidades do líder, precisamos considerar toda a quantidade de dados acumulados por dia. Além disso, como regra, o gerente não precisa de todos os detalhes de todos os pedidos. O que ele realmente precisa saber é quanto dinheiro a bilheteria ganhou em geral. Não importa onde fica a bilheteria, quando houve uma pausa para o almoço, quem trabalhou para ela etc. O OLAP existe então, para que, em um curto período de tempo, o sistema possa responder à pergunta - quanto a empresa ganhou como um todo sem leitura sequencial de cada pedido e todos os seus detalhes. O OLAP pode usar as mesmas tabelas e índices que o OLTP? A resposta é não, pelo menos não deveria. Em primeiro lugar, porque o OLAP simplesmente não precisa de todos os detalhes registrados nas tabelas. Esse problema é resolvido armazenando dados em outros formatos que não sejam tabelas bidimensionais. Em segundo lugar, as informações analisadas geralmente estão espalhadas por diferentes tabelas, o que implica várias associações, incluindo associações do tipo de auto-junção. Para resolver esse problema, eles geralmente desenvolvem um esquema especial de banco de dados. Esse esquema é otimizado para carga OLAP, bem como o esquema normalizado normal para carga OLTP.

O que acontece quando o OLAP usa um esquema OLTP

De fato, apresentei esta seção para que este artigo atenda claramente aos meus próprios requisitos para o formato desse material, ou seja, problema, solução, conclusão.

Listamos várias desvantagens do uso de esquemas OLTP para análise de dados.

• Muitos índices.
  
  Muitas vezes, você precisa criar índices especiais para dar suporte a relatórios. Esses índices implementam um esquema de armazenamento de dados OLAP. Eles não são usados ​​pela parte OLTP do aplicativo, enquanto exercem uma carga nele, exigindo suporte constante e ocupando espaço em disco.
• A quantidade de dados lidos excede o necessário.
• Falta de um esquema de dados claro.
  
  O fato é que muitas vezes as informações enviadas pelos relatórios em um único formulário estão espalhadas em tabelas diferentes. Essa informação requer constante transformação em tempo real. O exemplo mais simples é o valor da receita, que consiste em dinheiro e não dinheiro. Outro exemplo impressionante são as hierarquias de dados. Porque Como o desenvolvimento de aplicativos é progressivo e nem sempre se sabe o que será necessário no futuro, a mesma hierarquia de significado pode ser armazenada em tabelas diferentes. E enquanto a aquisição on-the-fly é usada ativamente no OLAP, essas são coisas ligeiramente diferentes.
• Complexidade excessiva de consultas.
  
  Porque Um esquema OLTP difere de um OLAP É necessária uma camada de software fortemente relacionada que traga o esquema de dados OLTP para a forma correta.
• Complexidade de suporte, depuração e desenvolvimento.
  
  Em geral, podemos dizer que quanto mais complexa a base de código, mais difícil é mantê-la em um estado íntegro. Este é um axioma.
• A complexidade da cobertura do teste.
  
  Muitas cópias foram quebradas devido a discussões sobre como obter um banco de dados cheio de todos os scripts de teste, mas é melhor dizer que, com um esquema de dados mais simples, a tarefa de cobrir os testes é simplificada muitas vezes.
• Depuração de desempenho sem fim.
  
  Há uma alta probabilidade de o usuário solicitar um relatório "pesado" para o servidor de banco de dados. Essa probabilidade aumenta com o tempo. Deve-se observar que o OLAP também é propenso a esse problema, mas, diferentemente do OLTP, o recurso OLAP nesse assunto é muito maior.

Columnstore

Este artigo focará no formato de armazenamento columnstore, mas sem detalhes de baixo nível. Outros formatos mencionados acima também merecem atenção, mas este é um tópico para outro artigo.

Na verdade, o formato columnstore é conhecido há 30 anos, mas não foi implementado no RDBMS até recentemente. A essência do columnstore é que os dados são armazenados não em linhas, mas em colunas. I.e. em uma página (todos conhecidos 8 Kb), o servidor registra dados de apenas um campo. E assim, com cada campo da tabela, por sua vez. Isso é necessário para que você não precise ler informações extras. Vamos imaginar uma tabela com 10 campos e uma consulta que tenha apenas um campo especificado na instrução SELECT. Se fosse uma tabela regular salva em um formato baseado em linhas, o servidor seria forçado a ler todos os 10 campos, mas ao mesmo tempo retornaria apenas um. Acontece que o servidor leu 9 vezes mais informações do que o necessário. O columnstore resolve completamente esse problema, porque O formato de armazenamento permite que você leia apenas um campo solicitado. Tudo isso acontece porque a unidade de armazenamento em um RDBMS é uma página. I.e. o servidor sempre grava e lê pelo menos uma página. A única questão é quantos campos estão presentes nele.

Como o Columnstore pode realmente ajudar

Para responder, é preciso ter números exatos. Vamos pegá-los. Mas que números podem dar uma imagem precisa?

1. A quantidade de espaço em disco.
2. Desempenho da consulta.
3. Tolerância a falhas.
4. Facilidade de implementação.
5. Quais novas habilidades um desenvolvedor deve ter para trabalhar com novas estruturas.

Espaço em disco

Vamos criar uma tabela simples, preenchê-la com dados e verificar quanto espaço é necessário.

create foreign table cstore_table ( trd date, org int, op int, it int, wh int, m1 numeric(32, 2), m2 numeric(32, 2), m3 numeric(32, 2), m4 numeric(32, 2), m5 numeric(32, 2) ) server cstore_server options(compression 'pglz'); 

Como você notou, eu criei uma tabela externa. O fato é que o PostgreSQL não possui suporte a columnstore embutido. Mas o PostgreSQL possui um sistema poderoso para extensões. Um deles torna possível criar tabelas columnstore. Links no final do artigo.

• pglz - informa a extensão que os dados devem ser compactados usando o algoritmo embutido no PostgreSQL;
• trd - tempo de transação;
• op, it, wh - seções ou medições analíticas;
• m1, m2, m3, m4, m5 - indicadores ou medidas numéricos;

Vamos inserir uma quantidade "decente" de dados e ver quanto espaço é necessário no disco. Ao mesmo tempo, verificamos o desempenho da inserção. Porque Coloquei minhas experiências em um laptop doméstico, sou um pouco orgânico na quantidade de dados. Além disso, o que é ainda bom, usarei o HDD executando o sistema operacional convidado Fedora 30. Host do sistema operacional - Windows 10 Home Edition. Processador Intel Core 7. O SO convidado recebeu 4 GB de RAM. Versão do PostgreSQL - PostgreSQL 10.10 no x86_64-pc-linux-gnu, compilado pelo gcc (GCC) 9.1.1 20190503 (Red Hat 9.1.1-1), 64 bits. Vou experimentar um conjunto de dados com o número de registros 52 608 000.

 explain (analyze) insert into cstore_table select '2010-01-01'::date + make_interval(days => d) as trd , op , org , wh , it , 100 as m1 , 100 as m2 , 100 as m3 , 100 as m4 , 100 as m5 from generate_series(0, 1) as op cross join generate_series(1, 2) as org cross join generate_series(1, 3) as wh cross join generate_series(1, 4000) as it cross join generate_series(0, 1095) as d; 

O plano de implementação será o seguinte

Inserir na tabela cstore_table (custo = 0,01..24902714242540.01 linhas = 1000000000000000 largura = 150) (tempo real = 119560.456..119560.456 linhas = 0 loops = 1)
----> Loop aninhado (custo = 0,01..24902714242540.01 linhas = 1000000000000000 largura = 150) (tempo real = 1,823..22339,976 linhas = 52608000 loops = 1)
----------> Varredura de funções em generate_series d (custo = 0.00..10.00 linhas = 1000 largura = 4) (tempo real = 0.151..2.198 linhas = 1096 loops = 1)
----------> Materializar (custo = 0,01..27284555030,01 linhas = 1000000000000 largura = 16) (tempo real = 0,002..3,196 linhas = 48000 loops = 1096)
----------------> Loop aninhado (custo = 0.01..17401742530.01 linhas = 1000000000000 largura = 16) (tempo real = 1.461..15.072 linhas = 48000 loops = 1)
----------------------> Varredura de funções em generate_series (custo = 0.00..10.00 linhas = 1000 largura = 4) (tempo real = 1.159..2,007 linhas = 4000 loops = 1)
----------------------> Materializar (custo = 0,01..26312333,01 linhas = 1.000.000.000 de largura = 12) (tempo real = 0,000..0,001 linhas = 12 loops = 4000)
----------------------------> Loop aninhado (custo = 0,01..16429520,01 linhas = 1.000.000.000 de largura = 12) (tempo real = 0,257 ..0.485 linhas = 12 loops = 1)
----------------------------------> Varredura de funções em generate_series wh (custo = 0.00..10.00 linhas = 1000 width = 4) (tempo real = 0,046..0,049 linhas = 3 loops = 1)
----------------------------------> Materializar (custo = 0,01..28917,01 linhas = 1.000.000 de largura = 8) (tempo real = 0,070..0,139 linhas = 4 loops = 3)
---------------------------------------> Loop aninhado (custo = 0,01..20010,01 linhas = 1000000 de largura = 8) (tempo real = 0,173..0,366 linhas = 4 loops = 1)
-------------------------------------------> Função Scan on generate_series op ( custo = 0,00..10,00 linhas = 1000 largura = 4) (tempo real = 0,076..0,079 linhas = 2 loops = 1)
---------------------------------------------> Função Digitalizar em generate_series org (custo = 0,00..10,00 linhas = 1000 largura = 4) (tempo real = 0,043..0,047 linhas = 2 loops = 2)
Tempo de planejamento: 0,439 ms
Tempo de execução: 119692,051 ms

Tempo total de entrega - 1.994867517 minutos

Hora de criação do conjunto de dados - 22.339976 segundos

Tempo de inserção - 1.620341333 minutos

Não consegui avaliar o espaço em disco ocupado usando as funções do PostgreSQL. Não sei por que, mas mostra 0. Talvez esse seja o comportamento padrão para tabelas externas. Usado para este gerenciador de arquivos. Portanto, o volume de espaço em disco ocupado é 226,2 Mb. Para avaliar muito ou pouco, vamos compará-lo com uma tabela regular.

 explain (analyze) create table rbstore_table as select '2010-01-01'::date + make_interval(days => d) as trd , op , org , wh , it , 100 as m1 , 100 as m2 , 100 as m3 , 100 as m4 , 100 as m5 from generate_series(0, 1) as op cross join generate_series(1, 2) as org cross join generate_series(1, 3) as wh cross join generate_series(1, 4000) as it cross join generate_series(0, 1095) as d; 

O plano de implementação será o seguinte

Loop aninhado (custo = 0,01..22402714242540,01 linhas = 1000000000000000 largura = 44) (tempo real = 0,585..23781,942 linhas = 52608000 loops = 1)
---> Função Varredura em generate_series d (custo = 0,00..10,00 linhas = 1000 largura = 4) (tempo real = 0,091..2,130 linhas = 1096 loops = 1)
---> Materializar (custo = 0,01..27284555030,01 linhas = 1000000000000 largura = 16) (tempo real = 0,001..3,574 linhas = 48000 loops = 1096)
----------> Loop aninhado (custo = 0,01..17401742530,01 linhas = 1000000000000 largura = 16) (tempo real = 0,489..14,044 linhas = 48000 loops = 1)
----------------> Varredura de funções em generate_series it (custo = 0.00..10.00 linhas = 1000 largura = 4) (tempo real = 0.477..1.352 linhas = 4000 loops = 1 )
----------------> Materializar (custo = 0,01..26312333,01 linhas = 1000000000 largura = 12) (tempo real = 0,000..0,001 linhas = 12 loops = 4000)
----------------------> Loop aninhado (custo = 0,01..16429520,01 linhas = 1.000.000.000 de largura = 12) (tempo real = 0,010..0,019 linhas = 12 loops = 1)
----------------------------> Função Digitalizar em generate_series wh (custo = 0.00..10.00 linhas = 1000 largura = 4) (real time = 0.003..0.003 linhas = 3 loops = 1)
----------------------------> Materializar (custo = 0,01..28917,01 linhas = 1.000.000 de largura = 8) (tempo real = 0,002. .0.004 linhas = 4 loops = 3)
----------------------------------> Loop aninhado (custo = 0,01..20010,01 linhas = 1.000.000 de largura = 8 ) (tempo real = 0,006..0,009 linhas = 4 loops = 1)
----------------------------------------> Varredura de funções em generate_series op (cost = 0.00 ..10,00 linhas = 1000 largura = 4) (tempo real = 0,002..0,002 linhas = 2 loops = 1)
----------------------------------------> Verificação de funções na organização generate_series (custo = 0,00 ..10,00 linhas = 1000 largura = 4) (tempo real = 0,001..0,001 linhas = 2 loops = 2)
Tempo de planejamento: 0,569 ms
Tempo de execução: 378883.989 ms

O tempo gasto na implementação deste plano não nos interessa, porque na vida real, essas inserções não são supostas. Estamos interessados ​​em quanto espaço em disco esta tabela ocupa. Tendo cumprido o pedido de funções do sistema, recebi 3,75 GB.

Portanto, cstore_table - 226 MB, rbstore_table - 3,75 GB. A diferença de 16,99 vezes é impressionante, mas é improvável que a mesma diferença possa ser obtida na produção, principalmente devido à distribuição de dados. Como regra, essa diferença será menor e será cerca de 5 vezes.

Mas espere, ninguém usa dados brutos em um formato baseado em linha para fins de análise. Por exemplo, eles tentam usar dados indexados para gerar relatórios. E porque Os dados "brutos" sempre serão, você precisará comparar os tamanhos com os tamanhos dos índices. Vamos criar pelo menos um índice. Seja um índice no campo de data e tipo de operação - trd + op.

Portanto, indexei apenas dois campos e o índice ocupou 1583 MB, o que é muito mais que a tabela cstore_. Mas, como regra, é necessário mais de um índice para carregar OLAP. É apropriado observar aqui que a tabela cstore_table não precisa de indexação adicional. Esta tabela atua como um índice que cobre todas as consultas.

De todas as opções acima, uma conclusão simples pode ser feita - usando as tabelas columnstore, você pode reduzir a quantidade de espaço em disco usado.

Desempenho da consulta

Para avaliar o desempenho, vamos executar uma consulta que retorne dados resumidos de um mês específico para um tipo específico de operação.

 explain (analyze, costs, buffers) select sum(m1) from cstore_table where trd = '2011-01-01' and op = 1; 

O plano de implementação será o seguinte

Agregado (custo = 793602.69..793602.70 linhas = 1 largura = 32) (tempo real = 79.708..79.708 linhas = 1 loops = 1)
--Buffers: ocorrência compartilhada = 44226
---> Verificação estrangeira na tabela cstore (custo = 0.00..793544.70 linhas = 23197 largura = 5) (tempo real = 23.209..76.628 linhas = 24000 loops = 1)
-------- Filtro: ((trd = '2011-01-01' :: data) AND (op = 1))
-------- Linhas removidas pelo filtro: 26000
-------- Arquivo CStore: / var / lib / pgsql / 10 / data / cstore_fdw / 14028/16417
Tamanho do arquivo CStore: 120818897
-------- Buffers: ocorrência compartilhada = 44226
Tempo de planejamento: 0,165 ms
Tempo de execução: 79,887 ms

E

 explain (analyze, costs, buffers) select sum(m1) from rbstore_table where trd = '2011-01-01' and op = 1; 

O plano de implementação será o seguinte

Agregado (custo = 40053.80..40053.81 linhas = 1 largura = 8) (tempo real = 389.183..389.183 linhas = 1 loops = 1)
--Buffers: leitura compartilhada = 545
---> Varredura de índice usando trd_op_ix em rbstore_table (custo = 0,56..39996.70 linhas = 22841 largura = 4) (tempo real = 55.955..385.283 linhas = 24000 loops = 1)
-------- Índice Cond: ((trd = '2011-01-01 00:00:00' :: carimbo de data / hora sem fuso horário) AND (op = 1))
-------- Buffers: leitura compartilhada = 545
Tempo de planejamento: 112,175 ms
Tempo de execução: 389,219 ms

389,219 ms vs 79,887 ms. Aqui vemos que, mesmo em uma quantidade relativamente pequena de dados do columnstore, uma tabela é significativamente mais rápida que um índice em uma tabela baseada em linhas.

Vamos alterar a solicitação e tentar obter a unidade para todo o ano de 2011.

 explain (analyze, costs, buffers) select sum(m1) from cstore_table where trd between '2011-01-01' and '2011-12-31' and op = 1; 

O plano de implementação será o seguinte

Agregado (custo = 946625.58..946625.59 linhas = 1 largura = 32) (tempo real = 3123.604..3123.604 linhas = 1 loops = 1)
--Buffers: ocorrência compartilhada = 44226
---> Verificação estrangeira na tabela cstore_table (custo = 0.00..925064.70 linhas = 8624349 largura = 5) (tempo real = 21.728..2100.665 linhas = 8760000 loops = 1)
-------- Filtro: ((trd> = '2011-01-01' :: data) AND (trd <= '2011-12-31' :: date) AND (op = 1))
-------- Linhas removidas pelo filtro: 8760000
-------- Arquivo CStore: / var / lib / pgsql / 10 / data / cstore_fdw / 14028/16411
Tamanho do arquivo CStore: 120818897
-------- Buffers: ocorrência compartilhada = 44226
Tempo de planejamento: 0,212 ms
Tempo de execução: 3123.960 ms

E

 explain (analyze, costs, buffers) select sum(m1) from rbstore_table where trd between '2011-01-01' and '2011-12-31' and op = 1; 

O plano de implementação será o seguinte

Finalizar agregado (custo = 885214.33..885214.34 linhas = 1 largura = 8) (tempo real = 98512.560..98512.560 linhas = 1 loops = 1)
--Buffers: hit compartilhado = 2565 lido = 489099
---> Reunir (custo = 885214.12..885214.33 linhas = 2 largura = 8) (tempo real = 98427.034..98523.194 linhas = 3 loops = 1)
-------- Trabalhadores planejados: 2
-------- Trabalhadores Lançados: 2
-------- Buffers: hit compartilhado = 2565 read = 489099
---------> Agregado parcial (custo = 884214.12..884214.13 linhas = 1 largura = 8) (tempo real = 97907.608..97907.608 linhas = 1 loops = 3)
-------------- Buffers: hit compartilhado = 2565 read = 489099
---------------> Varredura Seq paralela em rbstore_table (custo = 0,00..875264,00 linhas = 3580047 largura = 4) (tempo real = 40820,004..97405,250 linhas = 2920000 loops = 3)
--------------------- Filtro: ((trd> = '2011-01-01 00:00:00' :: carimbo de data / hora sem fuso horário) AND (trd <= '2011-12-31 00:00:00' :: carimbo de data / hora sem fuso horário) AND (op = 1))
-------------------- Linhas removidas pelo filtro: 14616000
-------------------- Buffers: hit compartilhado = 2565 read = 489099
Tempo de planejamento: 7.899 ms
Tempo de execução: 98523.278 ms

98523.278 ms vs 3123.960 ms. Talvez um índice parcial nos ajude, mas é melhor não arriscar e criar uma estrutura baseada em linha adequada na qual os valores prontos serão armazenados.

Agregados manuais

Uma estrutura adequada para agregações manuais pode ser uma tabela regular baseada em row_ contendo valores pré-computados. Por exemplo, ele pode conter um registro relacionado a 2011 com o tipo de operação igual a 1, enquanto nos campos m1, m2, m3, m4 e m5 o valor agregado será armazenado precisamente para essas seções analíticas. Assim, tendo um conjunto suficiente de agregados e índices, as consultas analíticas adquirem um desempenho sem precedentes. Curiosamente, o Microsoft SQL Server Analysis Services possui um assistente especial que permite configurar o número e a profundidade dos valores pré-calculados.

Esta solução tem as seguintes vantagens:

• Análise em tempo real.
  
  Por favor, não confunda o termo "análise em tempo real". Aqui estamos falando sobre o fato de que o incremento da unidade ocorre durante um período de tempo aceitável na grande maioria dos casos.
  
  De fato, esse plus é controverso, mas não vamos falar sobre isso. O fato permanece. A arquitetura da solução é tal que as unidades permanecem “novas” quase sempre.
• Independência completa do volume de dados.
  
  Esta é uma vantagem muito séria. Não importa quantos dados sejam processados, mais cedo ou mais tarde serão processados ​​e agregados recebidos.
• Complexidade relativa.
  
  Para obter análises em tempo real e independência do volume de dados, a solução deve usar tecnologias avançadas, como multithreading e gerenciamento de bloqueio manual no nível do DBMS.
• Teste de dificuldade.
  
  Aqui estamos falando sobre testes de unidade e testes manuais. Acho que o leitor não deve explicar que identificar erros de multithreading não é uma tarefa fácil.
• Aumento dos requisitos de espaço em disco.
  
  

O uso real do columnstore

Aqui devemos novamente mergulhar na teoria e analisar a questão do que são dados analíticos com mais detalhes.

Assuma o chefe médio da empresa. Como regra, ele / ela está preocupado com duas questões globais: "Como estão as coisas no momento?" e "O que mudou ultimamente?".

Para responder à pergunta "Como estão as coisas no momento", absolutamente não precisamos de dados históricos. I.e. não importa como foram as coisas há um mês.

Para acompanhar o pulso, a pergunta é frequentemente feita. Esse tipo de análise de dados é chamado operacional.

Para responder à pergunta "O que mudou ultimamente", precisamos de dados precisamente históricos. Além disso, como regra, a análise é realizada nos mesmos intervalos de tempo. Por exemplo, um mês é comparado a um mês, ano a ano etc. Obviamente, o sistema não deve limitar o usuário da capacidade de comparar períodos arbitrários, mas esse caso deve ser reconhecido como raro, porque comparar um ano fechado com o meio não fechado faz pouco sentido. Uma característica distintiva da análise comparativa é que ela não é necessária com a mesma frequência operacional. Vamos chamar esse tipo de análise de histórico.

Obviamente, a análise operacional deve ocorrer rapidamente. Consequentemente, exige muito desempenho. Embora para análise histórica, esses requisitos não possam ser apresentados. Embora o desempenho da análise histórica deva permanecer em um nível muito alto. Pelo menos para que o próprio sistema de análise permaneça competitivo.

Portanto, de acordo com dois tipos de análise, podemos distinguir dois tipos de dados analíticos: dados operacionais e históricos. Do lado do usuário, não deve ser notado com quais dados específicos ele está trabalhando no momento.

É a partir dessas considerações que em servidores de banco de dados surgiu a possibilidade de dividir tabelas em seções separadas.

No que diz respeito ao columnstore, é possível misturar seções nos formatos baseado em linha e columnstore. Sabe-se que os dados da análise operacional estão sujeitos a alterações frequentes, o que impede seu armazenamento no formato columnstore. E, como os dados operacionais não acontecem muito, eles podem ser armazenados no formato baseado em linhas.

Os dados históricos não são alterados. Existem muitos desses dados e, portanto, o formato columnstore se adapta melhor a eles. Lembre-se de que o desempenho de consultas em negrito em uma fonte columnstore é maior que em uma fonte baseada em linha.

Vejamos um exemplo de todos os itens acima.

Abaixo, crio a tabela principal do armazém e anexo as seções de análise operacional e histórica a ela.

 create table warehouse ( trd date, org int, op int, it int, wh int, m1 numeric(32, 2), m2 numeric(32, 2), m3 numeric(32, 2), m4 numeric(32, 2), m5 numeric(32, 2) ) partition by range(trd); create foreign table historycal_data ( trd date, org int, op int, it int, wh int, m1 numeric(32, 2), m2 numeric(32, 2), m3 numeric(32, 2), m4 numeric(32, 2), m5 numeric(32, 2) ) server cstore_server options(compression 'pglz'); insert into historycal_data select '2010-01-01'::date + make_interval(days => d) as trd , op , org , wh , it , 100 as m1 , 100 as m2 , 100 as m3 , 100 as m4 , 100 as m5 from generate_series(0, 1) as op cross join generate_series(1, 2) as org cross join generate_series(1, 3) as wh cross join generate_series(1, 4000) as it cross join generate_series(0, (1095 - 31)) as d; analyze historycal_data; create table operational_data as select ('2012-12-01'::date + make_interval(days => d))::date as trd , op , org , wh , it , 100::numeric(32, 2) as m1 , 100::numeric(32, 2) as m2 , 100::numeric(32, 2) as m3 , 100::numeric(32, 2) as m4 , 100::numeric(32, 2) as m5 from generate_series(0, 1) as op cross join generate_series(1, 2) as org cross join generate_series(1, 3) as wh cross join generate_series(1, 4000) as it cross join generate_series(0, 30) as d; create index trd_op_ix on operational_data (trd, op); analyze operational_data; alter table warehouse attach partition operational_data for values from ('2012-12-01') to ('2112-01-01'); alter table warehouse attach partition historycal_data for values from ('2010-01-01') to ('2012-12-01'); 

Está tudo pronto. Vamos tentar solicitar alguns relatórios. Vamos começar solicitando dados para um dia do mês atual.

 explain (analyze, costs, buffers) select sum(m1) from warehouse where trd = '2012-12-01' and op = 1; 

Agregado (custo = 15203,37..15203,38 linhas = 1 largura = 32) (tempo real = 17,320..17,320 linhas = 1 loops = 1)
--Buffers: ocorrência compartilhada = 3 lidos = 515
---> Anexar (custo = 532,59..15140,89 linhas = 24991 largura = 5) (tempo real = 1,924..13,838 linhas = 24000 loops = 1)
------- Buffers: hit compartilhado = 3 lido = 515
---------> Varredura de Heap de Bitmap em dados_operacionais (custo = 532.59..15140.89 linhas = 24991 largura = 5) (tempo real = 1.924..11.992 linhas = 24000 loops = 1)
--------------- Verifique novamente Cond: ((trd = '2012-12-01' :: data) AND (op = 1))
--------------- Blocos de heap: exato = 449
--------------- Buffers: hit compartilhado = 3 lido = 515
----------------> Análise de Índice de Bitmap em trd_op_ix (custo = 0.00..526.34 linhas = 24991 largura = 0) (tempo real = 1.877..1.877 linhas = 24000 loops = 1 )
--------------------- Índice Cond: ((trd = '2012-12-01' :: data) AND (op = 1))
--------------------- Buffers: hit compartilhado = 2 lido = 67
Tempo de planejamento: 0,388 ms
Tempo de execução: 100,941 ms

Agora, solicitaremos dados para todo o ano de 2012, nos quais o número de transações é de 8.784.000.

 explain (analyze, costs, buffers) select sum(m1) from warehouse where trd between '2012-01-01' and '2012-12-31' and op = 1; 

Agregado (custo = 960685.82..960685.83 linhas = 1 largura = 32) (tempo real = 4124.681..4124.681 linhas = 1 loops = 1)
--Buffers: hit compartilhado = 45591 read = 11282
---> Anexar (custo = 0,00..938846,60 linhas = 8735687 largura = 5) (tempo real = 66,581..3036,394 linhas = 8784000 loops = 1)
--------- Buffers: hit compartilhado = 45591 read = 11282
----------> Varredura estrangeira no históricocal_data (custo = 0.00..898899.60 linhas = 7994117 largura = 5) (tempo real = 66.579..2193.801 linhas = 8040000 loops = 1)
--------------- Filtro: ((trd> = '2012-01-01' :: data) AND (trd <= '2012-12-31' :: data) AND (op = 1))
--------------- Linhas removidas pelo filtro: 8040000
--------------- Arquivo do CStore: / var / lib / pgsql / 10 / data / cstore_fdw / 14028/16448
--------------- Tamanho do arquivo CStore: 117401470
--------------- Buffers: ocorrência compartilhada = 42966
----------> Varredura Seq em operating_data (cost = 0.00..39947.00 linhas = 741570 width = 5) (tempo real = 0.019..284.824 linhas = 744000 loops = 1)
--------------- Filtro: ((trd> = '2012-01-01' :: data) AND (trd <= '2012-12-31' :: data) AND (op = 1))
--------------- Linhas removidas pelo filtro: 744000
--------------- Buffers: hit compartilhado = 2625 read = 11282
Tempo de planejamento: 0,256 ms
Tempo de execução: 4125,239 ms

No final, vamos ver o que acontece se o usuário quiser, por exemplo, sem intenção maliciosa, solicitar um relatório sobre todas as transações no sistema, das quais existem 52 608 000.

 explain (analyze, costs, buffers) select sum(m1) from warehouse 

Agregado (custo = 672940.20..672940,21 linhas = 1 largura = 32) (tempo real = 15907,886..15907,886 linhas = 1 loops = 1)
--Buffers: hit compartilhado = 17075 read = 11154
---> Anexar (custo = 0,00..541420,20 linhas = 52608000 largura = 5) (tempo real = 0,192..9115,144 linhas = 52608000 loops = 1)
--------- Buffers: hit compartilhado = 17075 read = 11154
----------> Varredura estrangeira no históricocal_data (custo = 0.00..512633.20 linhas = 51120000 largura = 5) (tempo real = 0.191..5376.449 linhas = 51120000 loops = 1)
--------------- Arquivo do CStore: / var / lib / pgsql / 10 / data / cstore_fdw / 14028/16448
--------------- Tamanho do arquivo CStore: 117401470
--------------- Buffers: ocorrência compartilhada = 14322
----------> Varredura Seq em dados_operacionais (custo = 0,00..28787,00 linhas = 1488000 largura = 5) (tempo real = 0,032..246,978 linhas = 1488000 loops = 1)
--------------- Buffers: hit compartilhado = 2753 read = 11154
Tempo de planejamento: 0,157 ms
Tempo de execução: 15908.096 ms

Observe que ainda estou escrevendo meu artigo, como se nada tivesse acontecido. Nem precisei reiniciar meu laptop não tão poderoso com HDD e 4 GB de RAM. Embora a questão do consumo de recursos exija um estudo mais cuidadoso.

Tolerância a falhas

Em parte, a tolerância a falhas foi testada no momento da redação deste documento. Meu laptop está ativo e, em geral, não notei lentidão no trabalho, além dos habituais.

Permita que o leitor me perdoe pelo fato de não ter tido tempo de resolver a questão da tolerância a falhas em detalhes, mas posso dizer que a extensão em questão tem tolerância a falhas - o backup é possível.

Facilidade de implementação

Como se viu, ao criar uma tabela que armazena dados em um formato columnstore, não há outras opções além de um algoritmo de compactação. A compressão em si é absolutamente necessária.

O formato em si tem uma certa estrutura. Ao definir os parâmetros apropriados, você pode obter uma certa aceleração de consultas analíticas ou ajustar o grau de compactação das informações.

Como demonstrado acima, a criação de uma tabela columnstore não é um aborrecimento. A extensão pode funcionar com 40 tipos de dados PostgreSQL. Os seminários on-line falaram sobre todos os tipos suportados pelo PostgreSQL.

Quais novas habilidades um desenvolvedor deve ter para trabalhar com novas estruturas

O desenvolvedor do SQL não precisa de habilidades especiais para gravar consultas em tabelas columnstore. Essa tabela é visível em todas as consultas, como uma tabela regular baseada em linhas. Embora isso não exclua a necessidade de otimização de consulta.

Conclusão

Neste artigo, mostrei como uma tabela com um formato de armazenamento columnstore pode ser útil. Isso economiza espaço em disco e consultas analíticas de alto desempenho. A facilidade de trabalhar com a tabela reduz automaticamente o custo de criação de um data warehouse analítico completo, porque seu uso não requer o desenvolvimento de algoritmos complexos e difíceis de depurar. O teste é simplificado.

Apesar do fato de que os experimentos expostos acima inspiram otimismo, muitas questões não foram resolvidas. Por exemplo, qual plano de consulta será gerado quando a tabela columnstore ingressar em outras tabelas. Espero continuar esse trabalho na próxima parte. Quantas partes dependerão de como o cstore_fdw se comporta em dados mais ou menos reais.

Links para materiais adicionais

Revisão curta cstore_fdw

cstore_fdw no github

Roteiro cstore_fdw