Estratégias para implementar compactação colunar em grandes bancos de dados PostgreSQL

Dimensionar um banco de dados Postgres é um rito de passagem para aplicações em crescimento. À medida que você vê suas tabelas se expandirem com milhões ou até bilhões de linhas, suas consultas antes rápidas começam a ficar atrasadas e os crescentes custos de infraestrutura começam a lançar uma longa sombra em seus resultados financeiros. Você está preso em um enigma: você não quer se desfazer do seu amado PostgreSQL, mas parece que precisará de uma maneira mais eficaz de lidar com seus crescentes conjuntos de dados.

Neste artigo, contaremos a história de como construímos um mecanismo de compactação colunar flexível e de alto desempenho para PostgreSQL para melhorar sua escalabilidade. Ao combinar o armazenamento colunar com algoritmos de compressão especializados, conseguimos atingir taxas de compressão impressionantes, sem paralelo em qualquer outro banco de dados relacional (+95%).

Ao compactar seu conjunto de dados, você pode aumentar ainda mais seus bancos de dados PostgreSQL. Como veremos neste artigo, esse design de compactação altamente eficaz permite reduzir o tamanho de suas grandes tabelas PostgreSQL em até 10-20x. Você pode armazenar muito mais dados em discos menores (ou seja, economizar dinheiro) e, ao mesmo tempo, melhorar o desempenho da consulta. A compactação da escala de tempo também é totalmente mutável, facilitando o gerenciamento e as operações do banco de dados: você pode adicionar, alterar e eliminar colunas em tabelas compactadas e pode INSERT, UPDATE e DELETE dados diretamente.

Bem-vindo a um PostgreSQL mais escalável!

Visão geral do conteúdo

• Por que o PostgreSQL precisa de compactação de banco de dados
• Mas e o brinde?
• Por que a compactação não é nativa do PostgreSQL? Uma introdução aos bancos de dados orientados a linhas e colunas
• Bancos de dados orientados a linhas versus colunas: o que escolher?
• Construindo armazenamento colunar em um banco de dados orientado a linhas
• Transformando o PostgreSQL em um armazenamento híbrido linha-coluna
• Nos bastidores: de dados de linha a matrizes colunares compactadas
• Consultando dados compactados com eficiência
• Agrupando dados comumente consultados por coluna segmentby
• Definindo colunas segmentby
• Ajuste fino avançado via orderby
• A evolução da compressão da escala de tempo
• O resultado final: consultas mais rápidas, menos espaço de armazenamento para grandes bancos de dados PostgreSQL
• Compactação e armazenamento em camadas: ciclo de vida do armazenamento em escala de tempo
• Fique com o PostgreSQL

Por que o PostgreSQL precisa de compactação de banco de dados

Mas antes de entrar nos detalhes de como construímos a compactação, vamos passar alguns minutos respondendo a esta pergunta: por que é necessário adicionar um novo mecanismo de compactação de banco de dados ao PostgreSQL?

Vamos primeiro entender as necessidades dos aplicativos modernos e um pouco da história do software.

Adoramos o Postgres: acreditamos que ele é a melhor base para a construção de aplicativos, já que sua combinação de confiabilidade, flexibilidade e ecossistema rico é muito difícil de ser igualada por qualquer outro banco de dados. Mas o Postgres nasceu há décadas – essa robustez tem suas desvantagens.

Hoje, os desenvolvedores estão usando o PostgreSQL para muito mais do que o caso de uso tradicional de OLTP (OnLine Transaction Processing), pelo qual é mais conhecido. Muitos aplicativos exigentes e com uso intensivo de dados, executados 24 horas por dia, 7 dias por semana e lidando com volumes cada vez maiores de dados, são desenvolvidos com PostgreSQL:

• Os bancos de dados PostgreSQL estão sendo usados para ingerir grandes quantidades de fluxo de dados de sensores de sistemas de gerenciamento de tráfego, redes de serviços públicos e monitores de segurança pública.

  
  

• As empresas de energia estão usando o PostgreSQL para armazenar e analisar métricas de redes inteligentes e fontes de energia renováveis.

  
  

• No setor financeiro, o PostgreSQL está no centro dos sistemas de rastreamento de dados de mercado em tempo real.

• As plataformas de comércio eletrônico estão usando PostgreSQL para rastrear e analisar eventos gerados pelas interações do usuário.

  
  

• O Postgres está até sendo usado como banco de dados vetorial para impulsionar a nova onda de aplicações de IA.

  
  

Como resultado, as tabelas Postgres estão crescendo muito rapidamente, e tabelas que chegam a bilhões de linhas são o novo normal na produção.

Infelizmente, o PostgreSQL está nativamente mal equipado para lidar com esse volume de dados: o desempenho da consulta começa a ficar lento e o gerenciamento do banco de dados se torna complicado. Para resolver essas limitações, construímos Escala de tempoDB , uma extensão que dimensiona o desempenho do PostgreSQL para aplicativos exigentes por meio de particionamento automático, agregação contínua, melhorias no planejador de consultas e muitos outros recursos.

Construir um mecanismo de compactação de alto desempenho para PostgreSQL foi um desbloqueio igualmente importante. Esses conjuntos de dados cada vez maiores não são apenas um desafio para o bom desempenho, mas seu acúmulo de dados leva a discos cada vez maiores e a contas de armazenamento mais altas. O PostgreSQL precisava de uma solução.

Mas e o brinde?

Mas e o método TOAST existente do PostgreSQL? Apesar do nome incrível 🍞😋, TOAST não é eficaz na redução sistemática do tamanho de seus grandes bancos de dados PostgreSQL .

TOAST é o mecanismo automático que o PostgreSQL usa para armazenar e gerenciar valores grandes que não cabem nas páginas individuais do banco de dados. Embora o TOAST incorpore a compactação como uma de suas técnicas para conseguir isso, a função principal do TOAST não é otimizar o espaço de armazenamento em geral.

Por exemplo, se você tiver um banco de dados de 1 TB composto de pequenas tuplas, o TOAST não o ajudará a transformar sistematicamente esse 1 TB em 80 GB, não importa quantos ajustes você tente. O TOAST compactará automaticamente atributos superdimensionados em uma linha conforme eles excederem o limite de 2 KB, mas o TOAST não ajuda para valores pequenos (tuplas), nem você pode aplicar configurações mais avançadas configuráveis pelo usuário, como compactar todos os dados com mais de um mês em uma tabela específica. A compactação do TOAST é estritamente baseada no tamanho dos valores de colunas individuais, e não em tabelas mais amplas ou características do conjunto de dados.

O TOAST também pode introduzir sobrecarga significativa de E/S, especialmente para tabelas grandes com colunas superdimensionadas acessadas com frequência. Nesses casos, o PostgreSQL precisa recuperar os dados fora de linha da tabela TOAST, que é uma operação de E/S separada do acesso à tabela principal, pois o PostgreSQL deve seguir os ponteiros da tabela principal para a tabela TOAST para ler o dados completos. Isso normalmente leva a um pior desempenho.

Por último, a compressão do TOAST não foi projetada para fornecer taxas de compressão especialmente altas, pois utiliza um algoritmo padrão para todos os tipos de dados.

Por que a compactação não é nativa do PostgreSQL? Uma introdução aos bancos de dados orientados a linhas e colunas

Esta rápida menção ao TOAST também nos ajuda a entender as limitações do PostgreSQL na compactação eficaz de dados. Como acabamos de ver, a compactação do TOAST lida com os dados linha por linha, mas essa arquitetura orientada a linhas dispersa a homogeneidade em que os algoritmos de compactação prosperam, levando a um teto fundamental sobre o quão operacional uma compactação pode ser. Esta é uma razão fundamental pela qual os bancos de dados relacionais (como Postgres nativos) muitas vezes ficam aquém quando se trata de otimização de armazenamento.

Vamos analisar isso. Tradicionalmente, os bancos de dados se enquadram em uma de duas categorias:

• Os bancos de dados orientados a linhas organizam os dados por linhas, com cada linha contendo todos os dados de um registro específico. Eles são otimizados para processamento transacional onde inserções, atualizações e exclusões de registros são frequentes, e são eficientes para sistemas OLTP onde as operações envolvem registros individuais ou pequenos subconjuntos de dados (por exemplo, recuperação de todas as informações sobre um cliente específico).

  
  

• Os bancos de dados orientados a colunas (também conhecidos como “colunares”) , por outro lado, organizam os dados por colunas. Cada coluna armazena todos os dados de um atributo específico em vários registros. Eles normalmente são otimizados para sistemas OLAP (OnLine Analytical Processing), onde as consultas geralmente envolvem agregações e operações em muitos registros.

  
  

Vamos ilustrar isso com um exemplo. Digamos que temos uma tabela users com quatro colunas: user_id , name , logins e last_login . Se esta tabela armazenar os dados de um milhão de usuários, ela terá efetivamente um milhão de linhas e quatro colunas, armazenando fisicamente os dados de cada usuário (ou seja, cada linha) de forma contígua no disco.

Nesta configuração orientada a linhas, a linha inteira para user_id = 500.000 é armazenada de forma contígua, tornando a recuperação rápida. Como resultado, consultas superficiais e amplas serão mais rápidas em um armazenamento de linha (por exemplo, “buscar todos os dados do usuário X”):

 -- Create table CREATE TABLE users ( user_id SERIAL PRIMARY KEY, name VARCHAR(100), logins INT DEFAULT 0, last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Assume we have inserted 1M user records into the 'users' table -- Shallow-and-wide query example (faster in row store) SELECT * FROM users WHERE user_id = 500000;

Por outro lado, um armazenamento colunar armazenará todos os user_id s juntos, todos os nomes juntos, todos os valores de login juntos e assim por diante, para que os dados de cada coluna sejam armazenados de forma contígua no disco. Esta arquitetura de banco de dados favorece consultas profundas e restritas, por exemplo, “calcular o número médio de logins para todos os usuários”:

 -- Deep-and-narrow query example (faster in column store) SELECT AVG(logins) FROM users;

Os armazenamentos colunares funcionam particularmente bem com consultas restritas em dados amplos . Em um banco de dados colunar, apenas os dados da coluna de logins precisam ser lidos para calcular a média, o que pode ser feito sem a necessidade de carregar todo o conjunto de dados de cada usuário do disco.

Como você já deve ter adivinhado, o armazenamento de dados em linhas e colunas também influencia a qualidade da compactação dos dados. Em um banco de dados colunar, colunas individuais de dados são normalmente do mesmo tipo e geralmente extraídas de um domínio ou intervalo mais limitado.

Como consequência, os armazenamentos colunares normalmente compactam melhor do que os bancos de dados orientados a linhas. Por exemplo, nossa coluna logins anteriormente seria toda do tipo inteiro e provavelmente consistiria em apenas um pequeno intervalo de valores numéricos (e, portanto, teria uma entropia baixa, que é bem compactada). Compare isso com um formato orientado a linhas, onde uma linha inteira de dados compreende muitos tipos e intervalos de dados diferentes.

Mas mesmo que mostrem vantagens em consultas e compressibilidade no estilo OLAP, os armazenamentos colunares apresentam compensações:

• Consultas que recuperam linhas individuais têm muito menos desempenho (às vezes até impossíveis de executar).

  
  

• Sua arquitetura não é tão adequada para transações ACID tradicionais.

  
  

• Muitas vezes não é possível fazer atualizações em lojas colunares.

  
  

• É mais fácil para os armazéns baseados em linhas aproveitarem uma índice (por exemplo, árvore B) para encontrar rapidamente os registros apropriados.

  
  

• Com um armazenamento de linhas, é mais fácil normalizar seu conjunto de dados, de modo que você possa armazenar conjuntos de dados relacionados com mais eficiência em outras tabelas.

Bancos de dados orientados a linhas versus colunas: o que escolher?

Então, o que é melhor: orientado por linha ou colunar?

Tradicionalmente, você avaliaria as compensações entre ambos, dependendo da sua carga de trabalho. Se você estivesse executando um caso de uso típico de OLTP, provavelmente escolheria um banco de dados relacional orientado a linhas, como o PostgreSQL; se o seu caso de uso fosse claramente OLAP, você poderia optar por uma loja colunar como ClickHouse.

Mas e se a sua carga de trabalho for na verdade uma mistura de ambos?

• As consultas do seu aplicativo geralmente podem ser superficiais e amplas, com uma consulta individual acessando muitas colunas de dados, bem como dados em muitos dispositivos/servidores/itens diferentes. Por exemplo, você pode estar alimentando uma visualização voltada ao usuário que requer a exibição da última temperatura e umidade registradas para todos os sensores em uma fábrica específica. Tal consulta precisaria acessar múltiplas colunas em todas as linhas que correspondam aos critérios de construção, abrangendo potencialmente milhares ou milhões de registros.

  
  

• Mas algumas de suas consultas também podem ser profundas e restritas, com uma consulta individual selecionando um número menor de colunas para um sensor específico durante um período mais longo. Por exemplo, talvez você também precise analisar a tendência de temperatura de um dispositivo específico durante o último mês para inspecionar anomalias. Esse tipo de consulta se concentraria em uma única coluna (temperatura), mas precisaria recuperar essas informações de um grande número de linhas que correspondem a cada intervalo de tempo durante o período de destino.

  
  

• Seu aplicativo também pode consumir muitos dados e inserir (anexar) muitos. Como discutimos anteriormente, lidar com centenas de milhares de gravações por segundo é o novo normal. Seus conjuntos de dados provavelmente também são muito granulares, por exemplo, você pode coletar dados a cada segundo. Continuando com o exemplo anterior, seu banco de dados precisaria atender essas gravações pesadas junto com leituras constantes para potencializar sua visualização voltada ao usuário em tempo real.

  
  

• Seus dados são anexados principalmente , mas não necessariamente somente anexados . Pode ser necessário atualizar ocasionalmente registros antigos ou possivelmente registrar dados que chegam atrasados ou fora de ordem.

  
  

Essa carga de trabalho não é OLTP nem OLAP no sentido tradicional. Em vez disso, inclui elementos de ambos. Então o que fazer?

Vá híbrido!

Construindo armazenamento colunar em um banco de dados orientado a linhas

Para atender uma carga de trabalho como o exemplo anterior, um único banco de dados teria que incluir o seguinte:

• A capacidade de sustentar altas taxas de inserção, facilmente na casa das centenas de milhares de gravações por segundo

  
  

• Suporte para inserção de dados atrasados ou fora de ordem, bem como modificação de dados existentes

  
  

• Flexibilidade suficiente para processar com eficiência consultas superficiais e amplas e profundas e restritas em um grande conjunto de dados

  
  

• Um mecanismo de compactação capaz de reduzir consideravelmente o tamanho dos bancos de dados para melhorar a eficiência do armazenamento

  
  

Isso é o que pretendemos alcançar ao adicionar compactação colunar ao TimescaleDB (e, portanto, ao PostgreSQL).

Transformando o PostgreSQL em um armazenamento híbrido linha-coluna

Como mencionamos na seção anterior, construímos o TimescaleDB para expandir o PostgreSQL com mais desempenho e escalabilidade, tornando-o adequado para cargas de trabalho exigentes como dados de série temporal. O TimescaleDB é implementado como uma extensão do PostgreSQL: ao fazer isso, ele herda tudo o que há de bom no PostgreSQL, como SQL completo, consulta enorme e flexibilidade de modelo de dados, confiabilidade testada em batalha, uma base de desenvolvedores e usuários ardentes e um dos maiores ecossistemas de banco de dados em volta.

Em teoria, isso significa que o TimescaleDB também está bloqueado no formato de armazenamento orientado a linhas do PostgreSQL, com sua modesta compressibilidade. Na realidade, não há nada que um pouco de engenharia não consiga resolver.

Duas observações. Primeiro, a maioria das grandes cargas de trabalho do PostgreSQL tem uma estrutura semelhante a uma série temporal , ou seja, eles têm muitos acréscimos (em vez de muita atualização) com uma chave principal vagamente sequencial, como um carimbo de data/hora ou um ID de evento serial. Em segundo lugar, esses conjuntos de dados são consultados regularmente por meio de varreduras ou rollups, e não apenas de consultas pontuais. Com essas observações em mãos, arquitetamos um novo recurso de armazenamento colunar para TimescaleDB (que abordaremos em detalhes na próxima seção) que nos permite atingir níveis incomparáveis de compressibilidade.

Na verdade, esta transformação de linha para coluna não precisa ser aplicada a todo o seu banco de dados. Como usuário do Timescale, você pode transformar suas tabelas PostgreSQL em armazenamentos híbridos linha-coluna, selecionando exatamente quais dados compactar em formato colunar através da nossa API simples e se beneficiando de ambas as arquiteturas de armazenamento conforme exigido pelo seu aplicativo.

Vamos ilustrar como isso funciona na prática com um exemplo. Imagine um sistema de monitoramento de temperatura coletando leituras a cada segundo de vários dispositivos, armazenando dados como carimbo de data/hora, ID do dispositivo, código de status e temperatura.

Para acessar os dados de temperatura mais recentes de forma eficiente, especialmente para consultas operacionais onde você pode querer analisar as leituras mais recentes de diferentes dispositivos, você pode manter os dados mais recentes (por exemplo, a última semana) na tradicional estrutura PostgreSQL descompactada e orientada por linhas . Isso suporta altas taxas de ingestão e também é ótimo para consultas pontuais sobre dados recentes:

 -- Find the most recent data from a specific device SELECT * FROM temperature_data WHERE device_id = 'A' ORDER BY timestamp DESC LIMIT 1; -- Find all devices in the past hour that are above a temperature threshold SELECT DISTINCT device_id, MAX(temperature) FROM temperature WHERE timestamp > NOW() - INTERVAL '1 hour'   AND temperature > 40.0;

Mas quando esses dados têm alguns dias, consultas superficiais e amplas como a anterior não são mais executadas com frequência: em vez disso, consultas analíticas profundas e restritas são mais comuns. Portanto, para melhorar a eficiência do armazenamento e o desempenho das consultas para esse tipo de consulta, você pode optar automaticamente por transformar todos os dados com mais de uma semana em um formato colunar altamente compactado. Para fazer isso no Timescale, você definiria uma política de compactação como esta:

 -- Add a compression policy to compress temperature data older than 1 week SELECT add_compression_policy('temperature_data', INTERVAL '7 days');

Depois que seus dados forem compactados, a execução de consultas analíticas profundas e restritas nos dados de temperatura (seja em um dispositivo específico ou em vários dispositivos) mostraria o desempenho ideal da consulta.

 -- Find daily max temperature for a specific device across past year SELECT time_bucket('1 day', timestamp) AS day, MAX(temperature) FROM temperature_data WHERE timestamp > NOW() - INTERVAL '1 year'   AND device_id = 'A' ORDER BY day; -- Find monthly average temperatures across all devices SELECT device_id, time_bucket('1 month', timestamp) AS month, AVG(temperature) FROM temperature_data WHERE timestamp < NOW() - INTERVAL '2 weeks' GROUP BY device_id, month ORDER BY month;

Como representamos a “mudança” de um formato de linha para um formato de coluna? As hipertabelas do Timescale servem para particionar dados em “blocos” com base em uma chave de particionamento, como um carimbo de data/hora ou outra coluna de ID serial. Cada pedaço armazena os registros correspondentes a um determinado intervalo de carimbos de data/hora ou outros valores para essa chave de particionamento. No exemplo acima, os dados de temperatura seriam particionados por semana para que o último bloco permanecesse no formato de linha e todas as semanas anteriores fossem convertidas para o formato colunar.

Esse mecanismo de armazenamento híbrido linha-coluna é uma ferramenta incrivelmente poderosa para otimizar o desempenho de consultas em grandes bancos de dados PostgreSQL e, ao mesmo tempo, reduzir drasticamente o espaço ocupado pelo armazenamento. Como veremos mais adiante neste artigo, ao transformar dados em um formato colunar e aplicar mecanismos de compactação especializados, não apenas conseguimos acelerar suas consultas analíticas, mas também alcançamos taxas de compactação de até 98%. Imagine o que isso causa à sua conta de armazenamento!

Nos bastidores: de dados de linha a matrizes colunares compactadas

Antes de entrar em detalhes sobre o desempenho da consulta e a economia de armazenamento, vamos primeiro abordar como esse mecanismo funciona nos bastidores: como a transformação de linha em colunas é realmente executada e como a compactação é aplicada aos dados colunares.

Quando a política de compactação entra em ação, ela essencialmente transforma o que eram tradicionalmente numerosos registros individuais na hipertabela original do PostgreSQL – imagine 1.000 linhas densamente compactadas – em uma estrutura de linhas singular e mais compacta. Dentro deste formato compactado, cada atributo ou coluna não armazena mais entradas singulares de cada linha. Em vez disso, ele encapsula uma sequência ordenada e contínua de todos os valores correspondentes dessas 1.000 linhas. Vamos nos referir a essas 1.000 linhas como batch .

Para ilustrar, vamos imaginar uma tabela como esta:

 | Timestamp | Device ID | Status Code | Temperature | |-----------|-----------|-------------|-------------| | 12:00:01 | A | 0 | 70.11 | | 12:00:01 | B | 0 | 69.70 | | 12:00:02 | A | 0 | 70.12 | | 12:00:02 | B | 0 | 69.69 | | 12:00:03 | A | 0 | 70.14 | | 12:00:03 | B | 4 | 69.70 |

Para preparar esses dados para compactação, o Timescale primeiro transformaria esses dados tabulares em um armazenamento colunar. Dado um lote de dados (cerca de 1.000 linhas), os dados de cada coluna são agregados em uma matriz, com cada elemento da matriz correspondendo ao valor de uma das linhas originais. O processo resulta em uma única linha, com cada coluna armazenando uma matriz de valores desse lote.

 | Timestamp | Device ID | Status Code | Temperature | |------------------------------|--------------------|--------------------|-------------------------------| | [12:00:01, 12:00:01, 12...] | [A, B, A, B, A, B] | [0, 0, 0, 0, 0, 4] | [70.11, 69.70, 70.12, 69....] |

Mesmo antes de aplicar algoritmos de compactação, esse formato economiza armazenamento imediatamente, reduzindo bastante a sobrecarga interna por linha do Timescale. O PostgreSQL normalmente adiciona aproximadamente 27 bytes de sobrecarga por linha (por exemplo, para Controle de Simultaneidade Multi-Versão ou MVCC). Portanto, mesmo sem qualquer compactação, se nosso esquema acima tiver, digamos, 32 bytes, então as 1.000 linhas de dados de um lote que anteriormente ocupava [1.000 * (32 + 27)] ~= 59 kilobytes agora ocupa [1.000 * 32 + 27 ] ~= 32 kilobytes neste formato.

[ Além disso : essa noção de “agrupar” uma tabela maior em lotes menores e, em seguida, armazenar as colunas de cada lote de forma contígua (em vez das de toda a tabela) é na verdade uma abordagem semelhante aos “grupos de linhas” no formato de arquivo Apache Parquet. Embora só tenhamos percebido essa semelhança depois do fato!]

Mas a grande vantagem desta transformação é que agora, dado um formato onde dados semelhantes (carimbos de data e hora, IDS de dispositivos, leituras de temperatura, etc.) são armazenados de forma contígua, podemos empregar algoritmos de compressão específicos do tipo para que cada array seja compactado separadamente. . É assim que o Timescale atinge taxas de compactação impressionantes.

A escala de tempo emprega automaticamente os seguintes algoritmos de compactação. Todos esses algoritmos são “ sem perdas ”, para não desperdiçarmos a precisão ou introduzirmos imprecisões por meio de nossa compactação; qualquer descompressão resultante reconstrói perfeitamente os valores originais.

• Compressão Gorila para carros alegóricos

  
  

• Delta-de-delta + Simples-8b com codificação de comprimento de execução compactação para carimbos de data e hora e outros tipos semelhantes a números inteiros

  
  

• Compactação de dicionário de linha inteira para colunas com alguns valores repetidos (+ compactação LZ na parte superior)

  
  

• Compressão de array baseada em LZ para todos os outros tipos

  
  

Estendemos o Gorilla e o Simple-8b para lidar com a descompactação de dados na ordem inversa, o que nos permite acelerar consultas que usam varreduras reversas.

Descobrimos que essa compactação específica de tipo é bastante poderosa: além da maior compressibilidade, algumas técnicas como Gorilla e delta-of-delta podem ser até 40x mais rápidas do que a compactação baseada em LZ durante a decodificação, levando a um desempenho de consulta muito melhor. .

Ao descompactar dados, o Timescale pode operar nesses lotes compactados individuais, descompactando-os lote por lote e somente nas colunas solicitadas. Portanto, se o mecanismo de consulta puder determinar que apenas 20 lotes (correspondendo a 20.000 linhas originais de dados) precisam ser processados a partir de um bloco de tabela que originalmente incluía um milhão de linhas de dados, a consulta poderá ser executada muito mais rapidamente, pois está lendo e descompactando muito menos dados. Vamos ver como isso acontece.

Consultando dados compactados com eficiência

O formato anterior baseado em array apresenta um desafio: a saber, quais linhas o banco de dados deve buscar e descompactar para resolver uma consulta?

Vamos pegar nosso exemplo de dados de temperatura novamente. Vários tipos naturais de consultas surgem repetidamente: seleção e ordenação de dados por intervalos de tempo ou seleção de dados com base no ID do dispositivo (seja na cláusula WHERE ou por meio de um GROUP BY). Como podemos apoiar eficientemente essas questões?

Agora, se precisarmos de dados do último dia, a consulta deverá navegar pelos dados de carimbo de data/hora, que agora fazem parte de um array compactado. Portanto, o banco de dados deveria descompactar partes inteiras (ou mesmo toda a hipertabela) para localizar os dados do dia recente?

Ou mesmo se pudéssemos identificar os “lotes” individuais agrupados em uma matriz compactada (descrito acima), os dados de diferentes dispositivos estão intercalados, então precisamos descompactar toda a matriz para descobrir se ela inclui dados sobre um dispositivo específico? Embora esta abordagem mais simples ainda possa produzir uma boa compressibilidade, não seria tão eficiente do ponto de vista do desempenho da consulta.

Para resolver o desafio de localizar e descompactar dados de forma eficiente para consultas específicas em seu formato colunar, A escala de tempo introduz o conceito de colunas "segmentar por" e "ordenar por" .

Agrupando dados comumente consultados por coluna segmentby

Lembre-se de que os dados na escala de tempo são inicialmente convertidos em formato colunar compactado, bloco por bloco. Para aumentar a eficiência das consultas que filtram com base em uma coluna específica (por exemplo, consultas frequentes por device_id ), você tem a opção de definir esta coluna específica como “ compress_segmentby ”coluna. Essa abordagem é altamente benéfica para organizar os dados compactados.

Essas colunas segmentby são usadas para particionar logicamente os dados dentro de cada bloco compactado. Em vez de construir uma matriz compactada de valores arbitrários como mostrado acima, o mecanismo de compactação primeiro agrupa todos os valores que possuem a mesma chave segmentby .

Portanto, 1.000 linhas de dados sobre o device_id A são densamente apoiadas antes de serem armazenadas em uma única linha compactada, 1.000 linhas sobre o device_id B e assim por diante. Portanto, se device_id for escolhido como a coluna segmentby , cada linha compactada incluirá lotes de dados colunares compactados sobre um ID de dispositivo específico, que será armazenado descompactado nessa linha. Além disso, a escala de tempo cria um índice sobre esses valores segmentados dentro do bloco compactado.

 | Device ID | Timestamp | Status Code | Temperature | |-----------|--------------------------------|-------------|-----------------------| | A | [12:00:01, 12:00:02, 12:00:03] | [0, 0, 0] | [70.11, 70.12, 70.14] | | B | [12:00:01, 12:00:02, 12:00:03] | [0, 0, 4] | [69.70, 69.69, 69.70] |

Esse armazenamento contíguo de dados aumenta muito a eficiência das consultas filtradas pela coluna segmentby . Ao executar uma consulta filtrada por device_id onde device_id é a coluna segmentby , o Timescale pode selecionar rapidamente (por meio de um índice) todas as linhas compactadas no bloco que possuem os IDs de dispositivo especificados e ignora rapidamente os dados (e evita a descompactação ) dados não relacionados aos dispositivos solicitados.

Por exemplo, nesta consulta, o Timescale localizará e processará com eficiência apenas as linhas compactadas que contêm dados para o device_id A:

 SELECT AVG(temperature) FROM sensor_data WHERE device_id = 'A'   AND time >= '2023-01-01'   AND time < '2023-02-01';

Além disso, as hipertabelas de escala de tempo armazenam metadados associados a cada bloco, especificando o intervalo de valores que o bloco cobre. Portanto, se uma hipertabela for particionada com carimbo de data/hora por semana, quando o planejador de consultas executar a consulta acima, ele saberá processar apenas os 4 a 5 pedaços que cobrem o mês de janeiro, melhorando ainda mais o desempenho da consulta.

Definindo colunas segmentby

Você pode especificar quais colunas usar para segmentby ao ativar pela primeira vez a compactação de uma hipertabela. A escolha de qual coluna usar deve ser baseada em qual coluna ou colunas são frequentemente usadas em suas consultas. Na verdade, você pode usar várias colunas para segmentar por: por exemplo, em vez de agrupar lotes por device_id, você pode (digamos) agrupar os lotes que têm os mesmos tenant_id e device_id juntos.

Ainda assim, tome cuidado para não exagerar na seletividade: definir muitas colunas segmentadas diminuirá a eficiência da compactação, pois cada coluna segmentada adicional efetivamente divide os dados em lotes cada vez menores.

Se você não puder mais criar lotes de dados de 1.000 registros, mas tiver apenas cinco registros que tenham as chaves segmentadas especificadas em um bloco específico, ele não será compactado bem!

Mas depois de identificar por quais colunas você deseja segmentar, elas serão fáceis de configurar ao ativar a compactação em sua hipertabela:

 ALTER TABLE temperature_data SET (   timescaledb.compress,   timescaledb.compress_segmentby = 'device_id' );

Ajuste fino avançado via orderby

O TimescaleDB melhora o desempenho da consulta em dados compactados por meio da ordenação estratégica de dados dentro de cada bloco, ditada pelo parâmetro compress_orderby . Embora a configuração padrão de ordenação pelo carimbo de data/hora (a chave de particionamento típica em dados de série temporal) seja adequada para a maioria dos cenários, compreender essa otimização pode ser valioso. Continue lendo para uma perspectiva técnica ainda mais profunda.

Considere novamente o exemplo de blocos semanais e uma consulta que solicita dados apenas sobre um único dia. Em uma tabela regular com um índice de carimbo de data/hora, a consulta poderia percorrer esse índice com eficiência para encontrar os dados do dia.

No entanto, a situação é diferente com dados compactados: os carimbos de data/hora são compactados e não podem ser acessados sem descompactar lotes inteiros. Criar um índice em cada carimbo de data/hora individual seria contraproducente, pois poderia anular os benefícios da compactação ao se tornar excessivamente grande.

A escala de tempo resolve isso basicamente “classificando” os dados a serem agrupados em lote de acordo com seu carimbo de data/hora. Em seguida, ele registra metadados sobre os carimbos de data/hora mínimo e máximo de cada lote. Quando uma consulta é executada, esses metadados permitem que o mecanismo de consulta identifique rapidamente quais linhas compactadas (lotes) são relevantes para o intervalo de tempo da consulta, reduzindo assim a necessidade de descompactação completa.

Esta metodologia funciona bem com o uso de colunas segmentadas. Durante o processo de compactação, os dados são primeiro agrupados pela coluna segmentby, depois ordenados com base no parâmetro orderby e, finalmente, divididos em “minilotes” menores, ordenados por carimbo de data/hora, cada um contendo até 1.000 linhas.

A combinação da segmentação e ordenação do TimescaleDB melhora significativamente o desempenho de séries temporais comuns e consultas analíticas. Essa otimização no tempo (via orderby ) e no espaço (via segmentby ) garante que o TimescaleDB gerencie e consulte com eficácia dados de séries temporais em grande escala, oferecendo um equilíbrio otimizado entre compactação e acessibilidade.

A evolução da compressão da escala de tempo

A primeira versão do nosso design de compactação foi lançada em 2019 com TimescaleDB 1.5 . Muitos lançamentos depois, a compactação Timescale já percorreu um longo caminho.

Uma das principais limitações de nosso lançamento inicial foi que não permitimos quaisquer modificações adicionais nos dados – por exemplo, INSERTs, UPDATEs, DELETEs – uma vez que os dados foram compactados sem primeiro descompactar manualmente todo o pedaço da hipertabela em que residiam.

Dado que estávamos otimizando para casos de uso com uso intensivo de dados com base em dados analíticos e de séries temporais, que são principalmente pesados em inserções e não em atualizações, isso foi uma limitação muito menor do que seria em um caso de uso OLTP tradicional onde os dados são atualizados frequentemente (por exemplo, uma tabela de informações do cliente). No entanto, como discutimos neste artigo , há situações em que o preenchimento é necessário e isso complica significativamente os pipelines do desenvolvedor que usavam o TimescaleDB.

Outra limitação da nossa versão de compactação inicial foi que não permitimos modificações de esquema em tabelas, incluindo dados compactados. Isso significava que os desenvolvedores não poderiam evoluir sua estrutura de dados sem descompactar a tabela inteira, como adicionar novas colunas para ajustar novas métricas ou novos dispositivos .

Hoje, todas essas limitações foram removidas. A escala de tempo agora permite executar operações completas de linguagem de manipulação de dados (DML) e linguagem de definição de dados (DDL) em dados compactados:

• Você pode INSERIR dados em pedaços compactados (com ótimo desempenho).

• Você pode fazer UPDATEs, UPSERTs e DELETEs.

  
  

• Você pode adicionar colunas, inclusive com valores padrão.

  
  

• Você pode renomear e eliminar colunas.

  
  

Para automatizar a modificação de dados em dados compactados (tornando-os perfeitos para nossos usuários), mudamos nossa abordagem de compactação introduzindo uma “área de preparação” – essencialmente, um pedaço sobreposto que permanece descompactado e no qual realizamos as operações “sobre dados descompactados” em o capuz.

Como usuário, você não precisa fazer nada manualmente: você pode modificar seus dados diretamente enquanto nosso mecanismo cuida de tudo automaticamente nos bastidores. A capacidade de fazer alterações em dados compactados torna o mecanismo de armazenamento híbrido linha-coluna do Timescale muito mais flexível.

Esse design por meio da área de teste torna os INSERTs tão rápidos quanto inserir em pedaços não compactados, pois é isso que realmente está acontecendo (quando você insere em um pedaço compactado, agora você está gravando na área de teste). Também nos permitiu suportar UPDATEs, UPSERTs e DELETEs diretamente: quando um valor precisa ser alterado, o mecanismo move uma parte relevante dos dados compactados para a área de teste, descompacta-os, faz a alteração e (de forma assíncrona) move-os novamente para a tabela principal em sua forma compactada.

(Esta região de dados normalmente opera na escala dos “minilotes” compactados de até 1.000 valores que compõem uma “linha” no armazenamento subjacente do PostgreSQL para minimizar a quantidade de dados que precisam ser descompactados para suportar modificações.)

Essa “área de teste” ainda possui semântica transacional regular e suas consultas veem esses valores assim que são inseridos nela. Em outras palavras, o planejador de consultas é inteligente o suficiente para entender como consultar corretamente esses pedaços de “preparo” baseados em linhas e o armazenamento colunar regular.

O resultado final: consultas mais rápidas, menos espaço de armazenamento para grandes bancos de dados PostgreSQL

Neste ponto, a próxima pergunta lógica a fazer é: qual é o resultado final? Como a compactação afeta o desempenho da consulta e quanto tamanho do disco posso economizar usando-a?

Desempenho da consulta antes e depois da compactação

Como discutimos neste artigo, os armazenamentos colunares geralmente não funcionam muito bem para consultas que recuperam linhas individuais, mas tendem a ter um desempenho muito melhor para consultas analíticas que analisam valores agregados. Isso é exatamente o que vemos em Timescale: consultas profundas e restritas envolvendo médias apresentam melhorias significativas de desempenho ao usar a compactação.

Vamos ilustrar isso executando algumas consultas no Conjunto de dados de táxi de Nova York , um dos conjuntos de dados de exemplo que fornecemos em Timescale. Este conjunto de dados contém informações sobre viagens de táxi, incluindo horários de coleta e entrega, locais, distâncias, tarifas e muito mais.

Considere a seguinte consulta, solicitando o valor da tarifa mais alta de um subconjunto do conjunto de dados de táxi dentro de um período específico:

 SELECT max(fare_amount) FROM demo.yellow_compressed_ht WHERE   tpep_pickup_datetime >= '2019-09-01' AND   tpep_pickup_datetime <= '2019-12-01';

Quando executado no conjunto de dados não compactado, o tempo de execução da consulta é de 4,7 segundos. Estamos usando um serviço de teste pequeno e não otimizado e consultando muitos milhões de linhas, portanto esse desempenho não é o melhor. Mas depois de compactar os dados, o tempo de resposta cai para 77.074 milissegundos:

Vamos compartilhar outro exemplo. Esta consulta conta o número de viagens com um código de tarifa específico dentro de um determinado período:

 SELECT COUNT(*) FROM demo.yellow_compressed_ht WHERE   tpep_pickup_datetime >= '2019-09-01' AND   tpep_pickup_datetime <= '2019-12-01' AND   "RatecodeID" = 99;

Quando executada nos dados descompactados, essa consulta levaria 1,6 segundos para ser concluída. A mesma consulta executada em dados compactados termina em apenas 18.953 milissegundos. Mais uma vez, vemos uma melhoria imediata! Estes são apenas exemplos rápidos, mas ilustram como a compactação pode ser poderosa para acelerar suas consultas.

Como a compactação do Timescale reduz o tamanho do armazenamento do PostgreSQL: exemplos do mundo real

Não vamos esquecer o que nos trouxe até aqui: precisávamos de uma tática que nos permitisse reduzir o tamanho de nossos grandes bancos de dados PostgreSQL para que pudéssemos escalar ainda mais o PostgreSQL. Para mostrar o quão eficaz a compactação do Timescale pode ser para esta tarefa, a tabela abaixo inclui alguns exemplos reais de taxas de compactação observadas entre os clientes do Timescale .

Essas economias de armazenamento se traduzem diretamente em economia de dinheiro: A plataforma Timescale usa preços baseados no uso para armazenamento , portanto, se o seu armazenamento diminuir, sua conta também diminuirá proporcionalmente.

A taxa de compactação que você alcançará depende de vários fatores, incluindo o tipo de dados e os padrões de acesso. Mas como você pode ver, a compactação da escala de tempo pode ser extremamente eficiente— nós até o usamos internamente para potencializar nosso produto Insights voltado para o cliente com 96% de compactação .

Nossa equipe pode ajudá-lo a ajustar a compactação para economizar o máximo de dinheiro possível, então não hesite em entrar em contato .

“Com a compactação, observamos, em média, uma redução de 97% [no tamanho do disco].”

(Michael Gagliardo, Ndustrial)

“Descobrimos que a taxa de compressão do Timescale é absolutamente fenomenal! Atualmente estamos com uma taxa de compactação superior a 26, reduzindo drasticamente o espaço em disco necessário para armazenar todos os nossos dados.”

(Nicolas Quintin, Oitava)

“A compactação do Timescale foi tão boa quanto anunciada, o que nos proporcionou uma economia de espaço [em disco] de +90% em nossa hipertabela subjacente.”

(Paolo Bergantino, Grupo METER)

Compactação e armazenamento em camadas: ciclo de vida do armazenamento em escala de tempo

Por último, não poderíamos encerrar este artigo sem mencionar o Tiered Storage do Timescale, que acabamos de lançar em Disponibilidade Geral .

Além da compactação, agora você tem outra ferramenta para ajudá-lo a dimensionar ainda mais seus bancos de dados PostgreSQL na plataforma Timescale: você pode hierarquizar seus dados mais antigos e acessados com pouca frequência em um nível de armazenamento de objetos de baixo custo e ainda poder acessá-los via padrão SQL.

Esse nível de armazenamento de baixo custo tem um preço fixo de US$ 0,021 por GB/mês para dados (mais barato que o Amazon S3), permitindo que você mantenha muitos TB em seu banco de dados PostgreSQL por uma fração do custo.

É assim que nosso back-end de armazenamento em camadas funciona na plataforma Timescale e como a camada de baixo armazenamento funciona junto com a compactação:

• Seus dados mais recentes são gravados em um nível de armazenamento de alto desempenho otimizado para consultas rápidas e altas ingestões. Nesta camada, você pode habilitar a compactação colunar de escala de tempo para reduzir o tamanho do banco de dados e acelerar suas consultas analíticas, conforme discutimos neste artigo. Por exemplo, você pode definir uma política de compactação que comprima seus dados após 1 semana.

  
  

• Depois que seu aplicativo não acessar mais esses dados com frequência, você poderá colocá-los automaticamente em camadas para uma camada de armazenamento de objetos de custo mais baixo , configurando uma política de camadas. Os dados na camada de armazenamento de baixo custo permanecem totalmente consultáveis em seu banco de dados e não há limite para a quantidade de dados que você pode armazenar – até centenas de TB ou mais. Por exemplo, você pode definir uma política de camadas que mova todos os seus dados com mais de seis meses para a camada de armazenamento de baixo custo.

  
  

• Quando você não precisar mais manter esses dados em seu banco de dados, poderá descartá-los por meio de uma política de retenção. Por exemplo, você pode excluir todos os dados após cinco anos.

Fique com o PostgreSQL

Fornecemos ao Postgres um mecanismo eficaz de compactação de banco de dados, adicionando recursos de compactação colunar. Este é um recurso essencial para dimensionar bancos de dados PostgreSQL no mundo atual com uso intensivo de dados: a compactação permite enormes economias no uso do disco (armazenamento de mais dados por um custo mais barato) e melhorias de desempenho (execução de consultas analíticas em grandes volumes em milissegundos).

O design de compactação do Timescale atinge taxas de compactação impressionantes combinando os melhores algoritmos de compactação com um novo método para criar armazenamento híbrido de linha/coluna no PostgreSQL. Esse recurso torna o espaço de armazenamento do Timescale (e, portanto, do PostgreSQL) equivalente aos bancos de dados colunares personalizados e mais limitados.

Mas, diferentemente de muitos mecanismos colunares, o Timescale suporta semântica transacional ACID e suporte direto para modificações (INSERTs, UPDATEs, UPSERTs, DELETEs) em dados colunares compactados. Como o antigo modelo de “um banco de dados para cargas de trabalho transacionais, outro para análises” é obsoleto, muitos aplicativos modernos executam cargas de trabalho que atendem a ambos os padrões. Então, por que manter dois bancos de dados separados quando você pode fazer tudo no PostgreSQL?

A escala de tempo permite que você comece no PostgreSQL, dimensione com o PostgreSQL e permaneça com o PostgreSQL.

Criar uma conta gratuita e experimente o Timescale hoje mesmo - leva apenas alguns segundos, sem necessidade de cartão de crédito.

- Escrito por Carlota Soto e Mike Freedman .