Komprese dat pomocí sloupcových tabulek ve službě Azure Cosmos DB for PostgreSQL

  • Článek

PLATÍ PRO: Azure Cosmos DB for PostgreSQL (využívá rozšíření databáze Citus pro PostgreSQL)

Azure Cosmos DB for PostgreSQL podporuje úložiště sloupcových tabulek jen s připojením pro analytické úlohy a úlohy datových skladů. Když jsou sloupce (spíše než řádky) uloženy souvisle na disku, data se stanou komprimovatelnějšími a dotazy můžou rychleji vyžadovat podmnožinu sloupců.

Vytvoření tabulky

Pokud chcete použít sloupcové úložiště, při vytváření tabulky zadejte USING columnar :

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

Azure Cosmos DB for PostgreSQL během vkládání převádí řádky na sloupcové úložiště v "pruhech". Každý pruh obsahuje data jedné transakce, neboli 150 000 řádků, podle toho, která hodnota je menší. (Velikost pruhu a další parametry sloupcové tabulky je možné změnit pomocí funkce alter_columnar_table_set .)

Například následující příkaz vloží všech pět řádků do stejného pruhu, protože všechny hodnoty jsou vloženy do jedné transakce:

-- 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}');

Pokud je to možné, je nejlepší vytvářet velké pruhy, protože Azure Cosmos DB for PostgreSQL komprimuje sloupcová data pro jednotlivé pruhy samostatně. Pomocí můžeme zobrazit fakta o naší sloupcové tabulce, jako je míra komprese, počet pruhů a průměrný počet řádků na pruh VACUUM VERBOSE:

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

Výstup ukazuje, že služba Azure Cosmos DB for PostgreSQL použila algoritmus komprese zstd k získání 1,31x komprese dat. Míra komprese porovnává a) velikost vložených dat, jak byla rozfázována v paměti, oproti b) velikosti dat komprimovaných v jejich konečném proužku.

Vzhledem k tomu, jak se měří, míra komprese může nebo nemusí odpovídat rozdílu velikosti mezi řádkovým a sloupcovým úložištěm tabulky. Jediný způsob, jak skutečně najít tento rozdíl, je vytvořit řádkovou a sloupcovou tabulku, které obsahují stejná data, a porovnat.

Měření komprese

Pojďme vytvořit nový příklad s více daty, abychom mohli porovnat úspory komprese.

-- 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;

Vyplňte obě tabulky stejnou velkou datovou sadou:

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;

U těchto dat vidíte ve sloupcové tabulce lepší komprimační poměr než 8X.

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

Příklad

Sloupcové úložiště funguje dobře s dělením tabulek. Příklad najdete v dokumentaci komunity Citus Engine k archivaci pomocí sloupcového úložiště.

Gotchas

  • Komprese sloupcových úložišť na proužek Pruhy jsou vytvořeny pro každou transakci, takže vložení jednoho řádku na transakci umístí jednotlivé řádky do jejich vlastních pruhů. Komprese a výkon pruhů s jedním řádkem budou horší než tabulka řádků. Vždy vkládejte hromadně do sloupcové tabulky.
  • Když to zkazíte a vysypoute pár drobných pruhů, zaseknete se. Jedinou opravou je vytvořit novou sloupcovou tabulku a zkopírovat data z původní tabulky v jedné transakci:
    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;
  • Zásadně nekomprimovatelná data mohou být problémem, i když sloupcové úložiště je stále užitečné při výběru konkrétních sloupců. Ostatní sloupce nemusí načítat do paměti.
  • V dělené tabulce s kombinací oddílů řádků a sloupců musí být aktualizace pečlivě zacílené. Filtrujte je tak, aby se chytly jenom oddíly řádků.
    • Pokud je operace cílená na konkrétní oddíl řádku (například UPDATE p2 SET i = i + 1), bude úspěšná. Pokud cílíte na zadaný sloupcový oddíl (například UPDATE p1 SET i = i + 1), selže.
    • Pokud je operace zaměřena na dělenou tabulku a obsahuje klauzuli WHERE, která vylučuje všechny sloupcové oddíly (například UPDATE parent SET i = i + 1 WHERE timestamp = '2020-03-15'), bude úspěšná.
    • Pokud je operace zaměřena na tabulku s oddíly, ale nefiltruje sloupce klíče oddílu, selže. I když existují klauzule WHERE, které odpovídají řádkům pouze ve sloupcových oddílech, nestačí – klíč oddílu musí být také filtrovaný.

Omezení

Tato funkce má stále významná omezení:

  • Komprese je na disku, ne v paměti.
  • Pouze připojení (bez podpory UPDATE/DELETE)
  • Žádné uvolnění místa (například transakce s vrácením zpět můžou stále zabírat místo na disku)
  • Žádná podpora indexů, prohledávání indexů ani prohledávání rastrového indexu
  • Žádné tidscans
  • Žádné ukázkové kontroly
  • Žádná podpora informačních nástů (velké hodnoty se podporují v textu)
  • Žádná podpora příkazů ON CONFLICT (s výjimkou akcí DO NOTHING bez zadaného cíle).
  • Žádná podpora zámků řazené kolekce členů (SELECT ... U MOŽNOSTI SDÍLET VYBERTE ... PRO AKTUALIZACI)
  • Žádná podpora serializovatelné úrovně izolace
  • Podpora pouze pro server PostgreSQL verze 12 nebo novější
  • Žádná podpora cizích klíčů, jedinečných omezení nebo omezení vyloučení
  • Žádná podpora logického dekódování
  • Žádná podpora paralelních kontrol v rámci uzlu
  • Bez podpory pro AFTER ... AKTIVAČNÍ UDÁLOSTI PRO KAŽDÝ ŘÁDEK
  • Žádné nezařazené sloupcové tabulky
  • Žádné dočasné sloupcové tabulky

Další kroky

  • Podívejte se na příklad sloupcového úložiště v kurzu časové řady Citus (externí odkaz).