Comprimere i dati con tabelle a colonne in Azure Cosmos DB for PostgreSQL

Articolo
03/25/2024

SI APPLICA A: Azure Cosmos DB for PostgreSQL (basato su estensione database Citus per PostgreSQL)

Azure Cosmos DB for PostgreSQL supporta l'archiviazione tabelle a colonne solo accodamento per carichi di lavoro analitici e di data warehousing. Quando le colonne (anziché le righe) vengono archiviate in modo contiguo su disco, i dati diventano più comprimibili e le query possono inviare la richiesta di un subset di colonne in modo più rapido.

Crea una tabella

Per usare l'archiviazione di dati in colonna, specificare USING columnar quando si crea una tabella:

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

Durante l’inserimento, Azure Cosmos DB for PostgreSQL converte le righe per un archivio a colonne in "strisce". Ogni striscia contiene dati equivalenti a una transazione o 150.000 righe, a seconda di quale sia il valore minore (le dimensioni di della striscia e altri parametri di una tabella a colonne possono essere modificati con la funzione alter_columnar_table_set).

Ad esempio, l'istruzione seguente inserisce tutte le cinque righe nella stessa striscia, perché tutti i valori vengono inseriti in una singola transazione:

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

Quando possibile, è consigliabile creare strisce di grandi dimensioni. Azure Cosmos DB for PostgreSQL, infatti, comprime i dati a colonne separatamente per striscia. È possibile visualizzare i fatti sulla tabella a colonne, ad esempio il tasso di compressione, il numero di strisce e le righe medie per striscia usando 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

L'output mostra che per ottenere la compressione dei dati 1.31x, Azure Cosmos DB for PostgreSQL ha usato l'algoritmo di compressione zstd. Il tasso di compressione confronta a) le dimensioni dei dati inseriti con gestione temporanea in memoria rispetto a b) le dimensioni dei dati compressi nella relativa striscia finale.

Data la modalità di misurazione, il tasso di compressione può corrispondere o meno alla differenza di dimensione tra l'archiviazione a righe e a colonne per una tabella. L'unico modo per determinare veramente questa differenza consiste nel costruire una tabella a righe e a colonne contenenti gli stessi dati, e quindi metterle a confronto.

Misurazione della compressione

Si creerà un nuovo esempio con più dati per ottenere il benchmark dei risparmi di compressione.

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

Riempire entrambe le tabelle con lo stesso set di dati di grandi dimensioni:

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;

Per questi dati, è possibile visualizzare un rapporto di compressione superiore a 8X nella tabella a colonne.

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

Esempio

L'archiviazione a colonne funziona bene con il partizionamento delle tabelle. Per un esempio, vedere la documentazione della community di Citus Engine: archiviare con l'archiviazione a colonne.

Gotcha

L'archiviazione a colonne esegue la compressione per striscia. Le strisce vengono create per transazione, quindi l'inserimento di una riga per transazione inserirà le singole righe nelle proprie strisce. La compressione e le prestazioni delle strisce a riga singola saranno peggiori rispetto a una tabella di righe. Eseguire l’inserimento sempre in blocco in una tabella a colonne.
Se si crea disordine e si creano colonne per un gruppo di piccole strisce, si crea un blocco. L'unica correzione consiste nel creare una nuova tabella a colonne e copiare i dati dall'originale in una transazione:
```
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;
```
I dati fondamentalmente non comprimibili possono essere un problema, anche se l'archiviazione a colonne è comunque utile quando si selezionano colonne specifiche. Non occorre caricare le altre colonne in memoria.
In una tabella partizionata con una combinazione di partizioni di righe e colonne, occorre fare attenzione con la destinazione degli aggiornamenti. Eseguire il filtro per raggiungere solo le partizioni di riga.
- Se l'operazione è destinata a una partizione di riga specifica (ad esempio UPDATE p2 SET i = i + 1) avrà esito positivo; se è destinata a una partizione a colonne specificata ( ad esempio UPDATE p1 SET i = i + 1) avrà esito negativo.
- Se l'operazione è destinata alla tabella partizionata e ha una clausola WHERE che esclude tutte le partizioni a colonne ( ad esempio UPDATE parent SET i = i + 1 WHERE timestamp = '2020-03-15'), avrà esito positivo.
- Se l'operazione è destinata alla tabella partizionata, ma non esegue il filtro per le colonne della chiave di partizione, l'operazione avrà esito negativo. Anche se sono presenti clausole WHERE corrispondenti alle righe nelle sole partizioni a colonne, questo non è sufficiente: è necessario eseguire il filtro anche per la chiave di partizione.

Limiti

Questa funzionalità presenta ancora limitazioni significative:

La compressione è su disco, non in memoria
Solo accodamento (nessun supporto UPDATE/DELETE)
Nessun recupero dello spazio (ad esempio, le transazioni di cui è stato eseguito il rollback potrebbero continuare a utilizzare lo spazio su disco)
Nessun supporto di indici, analisi degli indici o analisi dell'indice bitmap
Nessuna analisi tid
Nessuna analisi campione
Nessun supporto TOAST (valori di grandi dimensioni supportati inline)
Nessun supporto per le istruzioni ON CONFLICT (ad eccezione delle azioni DO NOTHING senza destinazione specificata).
Nessun supporto per i blocchi tupla (SELECT ... FOR SHARE, SELECT ... FOR UPDATE)
Nessun supporto per il livello di isolamento serializzabile
Supporto solo per server PostgreSQL versione 12+
Nessun supporto per chiavi esterne, vincoli univoci o vincoli di esclusione
Nessun supporto per la decodifica logica
Nessun supporto per le analisi parallele intra-nodo
Nessun supporto per AFTER ... Trigger FOR EACH ROW
Nessuna tabella a colonne UNLOGGED
Nessuna tabella a colonne TEMPORARY

Passaggi successivi

Vedere un esempio di archiviazione a colonne in un'esercitazione sulla serie temporale Citus (collegamento esterno).

Condividi tramite