Armazenamento de mesas colunar (pré-visualização)

Importante

O armazenamento da mesa colunar em Hyperscale (Citus) está atualmente em pré-visualização. Esta versão de pré-visualização é disponibiliza sem um contrato de nível de serviço e não é recomendada para cargas de trabalho de produção. Algumas funcionalidades poderão não ser suportadas ou poderão ter capacidades limitadas.

Pode ver uma lista completa de outras novidades em funcionalidades de pré-visualização para Hyperscale (Citus).

A Base de Dados Azure para PostgreSQL - Hiperescala (Citus) suporta o armazenamento de mesas de coluna apenas para cargas de trabalho de armazenamento de dados e analíticos e de armazenamento de dados. Quando as colunas (em vez de linhas) são armazenadas contíguas no disco, os dados tornam-se mais compressíveis, e as consultas podem solicitar um subconjunto de colunas mais rapidamente.

Utilização

Para utilizar o armazenamento de colunas, especifique USING columnar ao criar uma tabela:

CREATE TABLE contestant (
    handle TEXT,
    birthdate DATE,
    rating INT,
    percentile FLOAT,
    country CHAR(3),
    achievements TEXT[]
) USING columnar;

A hiperescala (Citus) converte linhas para armazenamento colunar em "listras" durante a inserção. Cada risca contém dados de uma transação, ou 150000 linhas, o que for menor. (O tamanho das listras e outros parâmetros de uma tabela colunar podem ser alterados com a função alter_columnar_table_set.)

Por exemplo, a seguinte declaração coloca todas as cinco linhas na mesma faixa, porque todos os valores são inseridos numa única transação:

-- insert these values into a single columnar stripe

INSERT INTO contestant VALUES
  ('a','1990-01-10',2090,97.1,'XA','{a}'),
  ('b','1990-11-01',2203,98.1,'XA','{a,b}'),
  ('c','1988-11-01',2907,99.4,'XB','{w,y}'),
  ('d','1985-05-05',2314,98.3,'XB','{}'),
  ('e','1995-05-05',2236,98.2,'XC','{a}');

É melhor fazer listras grandes quando possível, porque a Hyperscale (Citus) comprime dados colunares separadamente por listra. Podemos ver fatos sobre a nossa tabela colunar como taxa de compressão, número de listras e filas médias por listra VACUUM VERBOSE usando:

VACUUM VERBOSE contestant;
INFO:  statistics for "contestant":
storage id: 10000000000
total file size: 24576, total data size: 248
compression rate: 1.31x
total row count: 5, stripe count: 1, average rows per stripe: 5
chunk count: 6, containing data for dropped columns: 0, zstd compressed: 6

A saída mostra que a Hyperscale (Citus) usou o algoritmo de compressão zstd para obter uma compressão de dados de 1,31x. A taxa de compressão compara a) o tamanho dos dados inseridos tal como foi encenado na memória contra b) o tamanho desses dados comprimidos na sua eventual risca.

Devido à forma como é medido, a taxa de compressão pode ou não corresponder à diferença de tamanho entre a linha e o armazenamento colunar para uma mesa. A única maneira de realmente encontrar essa diferença é construir uma tabela de linha e colunar que contenha os mesmos dados, e comparar.

Compressão de medição

Vamos criar um novo exemplo com mais dados para comparar as economias de compressão.

-- first a wide table using row storage
CREATE TABLE perf_row(
  c00 int8, c01 int8, c02 int8, c03 int8, c04 int8, c05 int8, c06 int8, c07 int8, c08 int8, c09 int8,
  c10 int8, c11 int8, c12 int8, c13 int8, c14 int8, c15 int8, c16 int8, c17 int8, c18 int8, c19 int8,
  c20 int8, c21 int8, c22 int8, c23 int8, c24 int8, c25 int8, c26 int8, c27 int8, c28 int8, c29 int8,
  c30 int8, c31 int8, c32 int8, c33 int8, c34 int8, c35 int8, c36 int8, c37 int8, c38 int8, c39 int8,
  c40 int8, c41 int8, c42 int8, c43 int8, c44 int8, c45 int8, c46 int8, c47 int8, c48 int8, c49 int8,
  c50 int8, c51 int8, c52 int8, c53 int8, c54 int8, c55 int8, c56 int8, c57 int8, c58 int8, c59 int8,
  c60 int8, c61 int8, c62 int8, c63 int8, c64 int8, c65 int8, c66 int8, c67 int8, c68 int8, c69 int8,
  c70 int8, c71 int8, c72 int8, c73 int8, c74 int8, c75 int8, c76 int8, c77 int8, c78 int8, c79 int8,
  c80 int8, c81 int8, c82 int8, c83 int8, c84 int8, c85 int8, c86 int8, c87 int8, c88 int8, c89 int8,
  c90 int8, c91 int8, c92 int8, c93 int8, c94 int8, c95 int8, c96 int8, c97 int8, c98 int8, c99 int8
);

-- next a table with identical columns using columnar storage
CREATE TABLE perf_columnar(LIKE perf_row) USING COLUMNAR;

Preencha ambas as tabelas com o mesmo conjunto de dados grande:

INSERT INTO perf_row
  SELECT
    g % 00500, g % 01000, g % 01500, g % 02000, g % 02500, g % 03000, g % 03500, g % 04000, g % 04500, g % 05000,
    g % 05500, g % 06000, g % 06500, g % 07000, g % 07500, g % 08000, g % 08500, g % 09000, g % 09500, g % 10000,
    g % 10500, g % 11000, g % 11500, g % 12000, g % 12500, g % 13000, g % 13500, g % 14000, g % 14500, g % 15000,
    g % 15500, g % 16000, g % 16500, g % 17000, g % 17500, g % 18000, g % 18500, g % 19000, g % 19500, g % 20000,
    g % 20500, g % 21000, g % 21500, g % 22000, g % 22500, g % 23000, g % 23500, g % 24000, g % 24500, g % 25000,
    g % 25500, g % 26000, g % 26500, g % 27000, g % 27500, g % 28000, g % 28500, g % 29000, g % 29500, g % 30000,
    g % 30500, g % 31000, g % 31500, g % 32000, g % 32500, g % 33000, g % 33500, g % 34000, g % 34500, g % 35000,
    g % 35500, g % 36000, g % 36500, g % 37000, g % 37500, g % 38000, g % 38500, g % 39000, g % 39500, g % 40000,
    g % 40500, g % 41000, g % 41500, g % 42000, g % 42500, g % 43000, g % 43500, g % 44000, g % 44500, g % 45000,
    g % 45500, g % 46000, g % 46500, g % 47000, g % 47500, g % 48000, g % 48500, g % 49000, g % 49500, g % 50000
  FROM generate_series(1,50000000) g;

INSERT INTO perf_columnar
  SELECT
    g % 00500, g % 01000, g % 01500, g % 02000, g % 02500, g % 03000, g % 03500, g % 04000, g % 04500, g % 05000,
    g % 05500, g % 06000, g % 06500, g % 07000, g % 07500, g % 08000, g % 08500, g % 09000, g % 09500, g % 10000,
    g % 10500, g % 11000, g % 11500, g % 12000, g % 12500, g % 13000, g % 13500, g % 14000, g % 14500, g % 15000,
    g % 15500, g % 16000, g % 16500, g % 17000, g % 17500, g % 18000, g % 18500, g % 19000, g % 19500, g % 20000,
    g % 20500, g % 21000, g % 21500, g % 22000, g % 22500, g % 23000, g % 23500, g % 24000, g % 24500, g % 25000,
    g % 25500, g % 26000, g % 26500, g % 27000, g % 27500, g % 28000, g % 28500, g % 29000, g % 29500, g % 30000,
    g % 30500, g % 31000, g % 31500, g % 32000, g % 32500, g % 33000, g % 33500, g % 34000, g % 34500, g % 35000,
    g % 35500, g % 36000, g % 36500, g % 37000, g % 37500, g % 38000, g % 38500, g % 39000, g % 39500, g % 40000,
    g % 40500, g % 41000, g % 41500, g % 42000, g % 42500, g % 43000, g % 43500, g % 44000, g % 44500, g % 45000,
    g % 45500, g % 46000, g % 46500, g % 47000, g % 47500, g % 48000, g % 48500, g % 49000, g % 49500, g % 50000
  FROM generate_series(1,50000000) g;

VACUUM (FREEZE, ANALYZE) perf_row;
VACUUM (FREEZE, ANALYZE) perf_columnar;

Para estes dados, pode ver uma relação de compressão superior a 8X na tabela colunaar.

SELECT pg_total_relation_size('perf_row')::numeric/
       pg_total_relation_size('perf_columnar') AS compression_ratio;
 compression_ratio
--------------------
 8.0196135873627944
(1 row)

Exemplo

O armazenamento colunar funciona bem com a divisória de mesa. Por exemplo, consulte a documentação comunitária do Citus Engine, arquivando com armazenamento de colunas.

Gotchas

  • Comprimem compressas de armazenamento colunar por listra. As riscas são criadas por transação, por isso a inserção de uma linha por transação colocará linhas únicas nas suas próprias riscas. Compressão e desempenho de riscas de linha única serão piores do que uma mesa de linha. Insira sempre a granel numa mesa de colunar.
  • Se fizeres asneira e forres um monte de riscas minúsculas, ficas preso. A única correção é criar uma nova tabela colunar e copiar dados do original numa transação:
    BEGIN;
    CREATE TABLE foo_compacted (LIKE foo) USING columnar;
    INSERT INTO foo_compacted SELECT * FROM foo;
    DROP TABLE foo;
    ALTER TABLE foo_compacted RENAME TO foo;
    COMMIT;
    
  • Os dados fundamentalmente não compressíveis podem ser um problema, embora o armazenamento de colunares ainda seja útil na seleção de colunas específicas. Não precisa de colocar as outras colunas na memória.
  • Numa mesa dividida com uma mistura de divisórias de linha e colunas, as atualizações devem ser cuidadosamente direcionadas. Filtre-os para atingir apenas as divisórias de linha.
    • Se a operação for direcionada a uma partição de linha específica (por UPDATE p2 SET i = i + 1 exemplo, ), terá sucesso; se for direcionada a uma partição colunar especificada (por UPDATE p1 SET i = i + 1 exemplo, ), falhará.
    • Se a operação for direcionada para a mesa dividida e tiver uma cláusula WHERE que exclui todas as divisórias colunares (por UPDATE parent SET i = i + 1 WHERE timestamp = '2020-03-15' exemplo), terá sucesso.
    • Se a operação for direcionada para a mesa partida, mas não filtrar as colunas das chaves de partição, falhará. Mesmo que existam cláusulas WHERE que correspondem a linhas apenas em divisórias colunares, não é suficiente-- a chave de partição também deve ser filtrada.

Limitações

Esta funcionalidade ainda tem limitações significativas. Ver limites e limitações de Hiperescala (Citus).

Passos seguintes

  • Veja um exemplo de armazenamento colunar num tutorial da série de tempos Citus (ligação externa).