Linha vs coluna: como bancos de dados armazenam dados

Um armazenamento orientado a linhas (row store), como o PostgreSQL, guarda todos os valores de um registro lado a lado no disco. Um armazenamento orientado a colunas (column store), como BigQuery, Snowflake, Redshift, ClickHouse ou um arquivo Parquet, guarda lado a lado todos os valores de uma coluna. Essa única escolha decide quantos bytes uma consulta precisa ler, quão bem os dados se comprimem e quão barato é alterar um único registro.

Este guia trata desse layout físico, a ordem dos bytes no disco. Se você chegou aqui procurando sobre planilhas: uma linha vai da esquerda para a direita, uma coluna vai de cima para baixo, e o resto desta página é sobre bancos de dados. Para deixar as diferenças concretas, montamos a mesma tabela de pedidos com 3.000.000 de linhas de três formas (uma tabela heap no PostgreSQL, um arquivo CSV, ou seja, texto separado por vírgulas, e um arquivo Parquet), rodamos as mesmas consultas nas três e colocamos os números nas tabelas abaixo.

O que significa "linha vs coluna" no armazenamento de bancos de dados?

O armazenamento orientado a linhas grava os valores de um registro um após o outro, depois o próximo registro. O armazenamento orientado a colunas grava todos os valores da primeira coluna, depois todos os valores da segunda coluna, e assim por diante. A documentação do SQL Server, da Microsoft, define da mesma forma: um rowstore guarda os dados linha a linha, um columnstore guarda coluna a coluna.

Aqui está uma pequena tabela de pedidos nos dois layouts. A tabela lógica é a mesma. Só muda a ordem dos bytes no disco.

ROW STORE (one page holds whole rows)
[101 | 7 | paid | 20.00] [102 | 7 | paid | 35.00] [103 | 9 | refunded | 40.00]

COLUMN STORE (one row group holds one chunk per column)
order_id: [101, 102, 103]
customer_id: [7, 7, 9]
status: [paid, paid, refunded]
total: [20.00, 35.00, 40.00]

Uma consulta como a de baixo precisa de duas das quatro colunas. No layout em colunas, ela lê o bloco de customer_id e o bloco de total e pula o resto. No layout em linhas, cada linha lida carrega order_id e status junto, de carona.

SELECT customer_id, SUM(total)
FROM orders
GROUP BY customer_id;

As engines reais adicionam cabeçalhos de página, bitmaps de nulos, metadados, índices e compressão por cima desse desenho. O resto do artigo olha esses detalhes com arquivos reais.

Como row stores e column stores organizam os bytes no disco?

O PostgreSQL guarda uma tabela como um array de páginas de tamanho fixo, normalmente de 8 kB, e cada item de uma página é uma linha. O Parquet guarda uma tabela como row groups (grupos de linhas), e cada row group contém um bloco contínuo por coluna.

No lado do PostgreSQL, toda tabela é um array de páginas de 8 kB. Uma página começa com um cabeçalho de 24 bytes, depois um ponteiro de 4 bytes por item, e então as próprias linhas. Cada linha tem seu próprio cabeçalho de 23 bytes antes dos valores das colunas. Para ler a coluna amount da linha 2.000.000, o PostgreSQL lê a página inteira de 8 kB em que essa linha está.

No lado do Parquet, o formato de arquivo divide a tabela em row groups, e cada row group contém um column chunk (bloco de coluna) por coluna. Os leitores primeiro leem os metadados no rodapé para encontrar os blocos de que precisam e depois buscam só esses. A especificação do Parquet recomenda row groups de 512 MB a 1 GB e páginas de dados de cerca de 8 KB.

É assim que um leitor de Parquet responde a uma consulta sobre três das nossas sete colunas:

Nossa tabela de pedidos com 3.000.000 de linhas (sete colunas: order_id, customer_id, order_date, status, amount, category e uma string notes de 40 caracteres) ficou assim:

O arquivo Parquet é 3,4 vezes menor que a tabela do PostgreSQL para as mesmas linhas (316,21 / 91,85 = 3,44). A razão está nas duas próximas seções: um column store lê menos bytes e comprime cada coluna separadamente.

Por que um column store lê menos dados em uma consulta analítica?

Um column store lê só as colunas que a consulta cita, enquanto um row store lê páginas inteiras que carregam todas as colunas. No nosso teste, o PostgreSQL leu todas as 40.475 páginas (316 MB) para responder a uma agregação de três colunas, enquanto essas mesmas três colunas ocupam 20,7 MB do arquivo Parquet.

A consulta que cronometramos soma um mês de pedidos por cliente:

SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE order_date >= DATE '2024-06-01'
AND order_date < DATE '2024-07-01'
GROUP BY customer_id
ORDER BY total DESC
LIMIT 5;

Ela usa três das sete colunas. O plano do PostgreSQL mostra o que ele precisou ler para responder:

Parallel Seq Scan on orders
Filter: ((order_date >= '2024-06-01'::date) AND (order_date < '2024-07-01'::date))
Rows Removed by Filter: 918042
Buffers: shared hit=3233 read=37242
Execution Time: 250.521 ms

Shared hit mais read dá 3.233 + 37.242 = 40.475 buffers. São todas as páginas da tabela, porque a coluna notes e as outras colunas que a consulta ignora dividem as páginas com as que ela precisa. No arquivo Parquet, os três column chunks necessários somam 20.678.569 bytes de 91.850.195 (20.678.569 / 91.850.195 = 22,5%). O resto do arquivo, principalmente a coluna notes, nunca é lido.

Estes são os tempos medianos para a mesma consulta na mesma máquina (64 CPUs, 247 GiB de RAM). O DuckDB 1.5.5 rodou as consultas no CSV e no Parquet; o PostgreSQL 15.19 rodou a tabela heap com dois workers paralelos. Os resultados bateram nos três.

O count no Parquet leva menos de um milissegundo porque o número de linhas está nos metadados do arquivo. O PostgreSQL precisa varrer a tabela para contar as linhas ativas. São duas engines diferentes, então a diferença mistura o layout com o design da engine, mas os números de buffers acima mostram a parte do layout: um lado leu 316 MB, o outro leu cerca de 21 MB.

Por que dados em colunas comprimem melhor?

Dados em colunas comprimem melhor porque os valores vizinhos no disco vêm da mesma coluna, então compartilham o tipo e muitas vezes se repetem. Uma coluna de status com quatro valores possíveis encolhe muito mais do que uma página que mistura ids, datas, texto e valores monetários.

Os números por coluna do nosso arquivo Parquet deixam isso visível. O tamanho "codificado" é depois da etapa de codificação do próprio Parquet (codificações de dicionário e do tipo run-length) e antes da compressão ZSTD; a especificação do Parquet trata codificação e compressão como etapas separadas e permite escolher a compressão por coluna.

Duas coisas chamam atenção. Primeiro, a coluna status já estava em 1,1 MB antes da compressão, porque a codificação trocou 3.000.000 de strings por pequenos códigos de dicionário. Segundo, a coluna notes, aleatória, quase não encolhe e acaba sendo a maior parte do arquivo (68.016.030 / 91.850.195 = 74%). O armazenamento em colunas ajuda mais em colunas com repetições ou padrões, e menos em texto aleatório.

Os warehouses usam as mesmas ideias. O formato Capacitor do BigQuery usa run-length encoding, codificação de dicionário, técnicas de bit-vector e frame-of-reference, e coleta estatísticas de cada coluna enquanto codifica. A Citus reportou uma taxa de compressão de 8,02x para sua tabela colunar no PostgreSQL, em um dataset de teste próprio, e a Microsoft cita até 7x de compressão para índices columnstore do SQL Server em relação ao rowstore. São números dos fornecedores em datasets dos fornecedores, então o número com os seus dados pode cair em qualquer ponto dessa faixa.

Por que leituras e atualizações de uma única linha são baratas em um row store e caras em um column store?

Um row store encontra um registro por meio de um índice e lê uma página que já tem todas as colunas desse registro. Um column store precisa acessar um bloco por coluna para remontar o mesmo registro, e guarda os dados em unidades comprimidas e ordenadas que não podem ser editadas no lugar.

Nosso teste mostra o lado da leitura. Buscando um pedido pela chave primária:

SELECT * FROM orders WHERE order_id = 1234567;

O plano do PostgreSQL para essa consulta lê quatro páginas de 8 kB no total: três páginas do índice e uma da tabela. Uma atualização de uma única linha na mesma tabela levou 1,06 ms no nosso teste.

Os column stores lidam com escritas colocando-as em uma área intermediária e depois reescrevendo as unidades em segundo plano:

• O ClickHouse transforma cada insert em uma data part ordenada e imutável. Merges em segundo plano descomprimem as parts, juntam os arquivos de coluna em arquivos maiores, constroem um novo índice e comprimem de novo. As parts antigas são apagadas depois de 8 minutos, por padrão.
• O SQL Server manda pequenas escritas para um deltastore baseado em linhas e só move as linhas para rowgroups columnstore comprimidos quando 1.048.576 delas se acumulam.
• O reclustering do Snowflake apaga e reinsere os registros afetados em novas micro-partitions, e as antigas ficam guardadas para Time Travel e Fail-safe.
• O BigQuery limita DML (data manipulation language, ou seja, inserts, updates e deletes) a 2 comandos de alteração simultâneos por tabela e 25 comandos DML a cada 10 segundos (cotas no momento em que este texto foi escrito). A documentação diz que o BigQuery não tem bom desempenho quando usado como banco de dados relacional e recomenda trabalhar em lotes em vez de inserções frequentes de uma linha.

Por isso faz sentido manter a aplicação em um row store e os relatórios em um column store, com algo movendo os dados entre eles em lotes.

Como particionamento e clustering reduzem a varredura em um warehouse?

O particionamento divide uma tabela em grandes blocos por uma coluna (normalmente uma data), e um filtro nessa coluna permite que o warehouse pule partições inteiras. O clustering ordena os dados dentro de cada partição por até algumas colunas a mais, para que o warehouse também pule blocos cujos valores mínimo e máximo ficam fora do filtro.

No BigQuery, uma tabela particionada pode ser dividida por horário de ingestão, por uma coluna de data ou timestamp, ou por um intervalo de inteiros, e uma tabela só pode ter uma coluna de particionamento. Cada tabela pode ter até 10.000 partições. Quando uma consulta filtra pela coluna de particionamento, o BigQuery lê só as partições correspondentes.

Uma tabela clusterizada ordena os blocos de armazenamento dentro de cada partição por até quatro colunas, e a ordem em que você as lista importa. A documentação dá este exemplo, com uma tabela de vendas particionada por dia e clusterizada por customer_id:

SELECT SUM(totalSale)
FROM `mydataset.ClusteredSalesData`
WHERE customer_id BETWEEN 20000 AND 23000
AND DATE(timestamp) = "2016-05-01";

O BigQuery descarta o bloco B3 porque a data dele não corresponde, descarta o bloco B1 porque a faixa de customer_id dele não se sobrepõe e varre só as colunas timestamp, customer_id e totalSale nos blocos B2 e B4. Dois níveis de descarte acontecem antes de qualquer column chunk ser lido:

Snowflake e Redshift fazem a mesma coisa, com outros nomes e outras unidades:

Particionar por dia e clusterizar pela coluna mais usada em filtros é a configuração que a documentação do BigQuery sugere quando uma tabela recebe atualizações frequentes ao longo de um período amplo. O custo do clustering aparece no lado da escrita: a documentação do Snowflake diz que reclusterizar uma tabela com muito DML consome mais créditos, porque cada reclustering reescreve micro-partitions.

Um mesmo banco pode ser orientado a linhas e a colunas ao mesmo tempo?

Sim. Várias engines mantêm um layout em linhas para transações e um layout em colunas para analytics ao mesmo tempo, seja como uma segunda cópia dos dados, seja como uma escolha por tabela.

O padrão da Citus mostra bem como isso é usado: a partição do mês atual continua como tabela heap para que a aplicação possa atualizá-la, e as partições mais antigas são convertidas para colunar, com armazenamento barato e varreduras rápidas.

Onde ELT e CDC entram entre o PostgreSQL ou MySQL e um warehouse colunar?

Uma ferramenta de ELT (extract, load e depois transform dentro do warehouse) lê as mudanças do write-ahead log do row store, junta essas mudanças em arquivos e carrega esses arquivos no warehouse em lotes. Isso combina com o caminho de escrita do column store: poucas cargas grandes em vez de muitas escritas de uma linha.

Change data capture (CDC) significa ler o próprio log de mudanças do banco em vez de consultar as tabelas. No PostgreSQL, a Erathos lê o WAL (write-ahead log), o que exige wal_level definido como logical e REPLICA IDENTITY FULL nas tabelas em que você quer o estado completo de antes e depois de updates e deletes. No MySQL, ela lê o binlog em formato ROW com imagens FULL das linhas. Os dois conectores oferecem dois modos de snapshot: initial (copia a tabela uma vez e depois transmite as mudanças) e no_data (transmite só as mudanças).

O lado da carga funciona em lotes por design. A Erathos extrai os dados, grava em arquivos temporários em um bucket na nuvem, carrega o warehouse com um comando COPY ou o equivalente do destino e apaga os arquivos temporários quando o job termina. Um COPY de um arquivo em lote é o tipo de escrita para o qual BigQuery e Redshift foram feitos, e fica longe das cotas de DML por comando citadas acima.

Este é o caminho de uma mudança em uma única linha até uma carga no warehouse:

No destino, a Erathos carrega em BigQuery e Redshift, com pipelines incrementais por padrão e um tipo de atualização por tabela: lote, incremental por cursor ou CDC. Há um guia passo a passo de MySQL CDC para BigQuery se você quiser ver a configuração de ponta a ponta.

O que é mais rápido: armazenamento em linhas ou em colunas?

Nenhum dos dois é mais rápido em geral; depende do formato da consulta. O armazenamento em linhas vence quando a consulta precisa de um registro ou de uma faixa pequena, com todas as colunas. O armazenamento em colunas vence quando a consulta varre muitas linhas e precisa de poucas colunas.

Nossos números, em uma única máquina, resumem isso:

A orientação da Microsoft para o SQL Server traça a mesma linha: rowstore para buscas pontuais e faixas pequenas em cargas transacionais, columnstore para grandes varreduras em cargas de data warehouse. Para um conjunto maior de números entre engines, o ClickBench publica tempos de consultas analíticas para PostgreSQL, DuckDB, ClickHouse, BigQuery, Snowflake, Redshift e configurações com Parquet. Compare apenas linhas com o mesmo tipo de máquina, porque o ranking mistura hardware e datas.

Perguntas frequentes

O PostgreSQL é orientado a linhas ou a colunas?

A linhas. As tabelas heap padrão do PostgreSQL guardam as linhas como itens em páginas de tamanho fixo, normalmente de 8 kB. Extensões como Citus Columnar e pg_duckdb adicionam uma opção colunar, mas a tabela padrão continua orientada a linhas.

O BigQuery é colunar?

Sim. O BigQuery converte os dados carregados para seu próprio formato colunar, o Capacitor, que codifica cada coluna separadamente e guarda níveis de definição e repetição, para conseguir remontar registros aninhados apenas com as colunas que a consulta pede.

O Parquet é orientado a linhas ou a colunas?

A colunas. A especificação descreve o Parquet como um formato de arquivo de dados orientado a colunas: um arquivo contém row groups, um row group contém exatamente um bloco por coluna e um bloco contém páginas. No nosso arquivo de 3.000.000 de linhas, uma consulta sobre três de sete colunas precisou de 20,7 MB dos 91,9 MB do arquivo.

O CSV é orientado a linhas?

Sim, no sentido simples de que um arquivo CSV tem uma linha de texto por registro, com todos os campos em ordem. Ele guarda tudo como texto puro, sem os blocos por coluna, os metadados de rodapé ou a compressão do Parquet. No nosso teste, as mesmas linhas ocuparam 265,64 MB em CSV e 91,85 MB em Parquet, e um count(*) levou 254,80 ms no CSV contra 0,49 ms no Parquet.

SQL é um banco de dados colunar?

Não. SQL é uma linguagem de consulta. O mesmo SQL roda em row stores (PostgreSQL, MySQL) e em column stores (BigQuery, Snowflake, Redshift, ClickHouse). O layout de armazenamento é uma escolha da engine, e não dá para identificá-lo pela consulta.

O que é mais rápido, um banco orientado a linhas ou um banco colunar?

Depende da consulta. No nosso teste, uma busca pela chave primária foi 144 vezes mais rápida na tabela heap do PostgreSQL (0,06 ms contra 8,64 ms), e uma agregação de um mês sobre três colunas foi 6,4 vezes mais rápida no Parquet (31,82 ms contra 204,01 ms).

Um banco pode ser orientado a linhas e a colunas ao mesmo tempo?

Sim. O SQL Server pode adicionar um índice columnstore a uma tabela rowstore, o Oracle Database In-Memory mantém os dois formatos ao mesmo tempo e o PostgreSQL ganha uma opção colunar com Citus Columnar ou pg_duckdb.

Leve os dados do seu row store para um column store

Se a sua aplicação roda em PostgreSQL ou MySQL e seus relatórios rodam em BigQuery ou Redshift, o trabalho no meio é capturar as mudanças linha a linha e carregá-las em lotes. Teste a Erathos grátis por 14 dias e configure um pipeline de CDC do seu banco para o seu warehouse.