Distribuire e modificare tabelle in Azure Cosmos DB per PostgreSQL

SI APPLICA A: Azure Cosmos DB per PostgreSQL (basato sull'estensione del database Citus in PostgreSQL)

Distribuzione di tabelle

Per creare una tabella distribuita, è necessario innanzitutto definire lo schema della tabella. A tale scopo, è possibile definire una tabella usando l'istruzione CREATE TABLE nello stesso modo in cui si farebbe con una normale tabella PostgreSQL.

CREATE TABLE github_events
(
    event_id bigint,
    event_type text,
    event_public boolean,
    repo_id bigint,
    payload jsonb,
    repo jsonb,
    actor jsonb,
    org jsonb,
    created_at timestamp
);

È quindi possibile usare la funzione create_distributed_table() per specificare la colonna di distribuzione della tabella e creare le partizioni del ruolo di lavoro.

SELECT create_distributed_table('github_events', 'repo_id');

La chiamata di funzione informa Azure Cosmos DB per PostgreSQL che la tabella github_events deve essere distribuita nella colonna repo_id (hashing del valore della colonna).

Crea un totale di 32 partizioni per impostazione predefinita, in cui ogni partizione possiede una parte di uno spazio hash e viene replicata in base al valore di configurazione citus.shard_replication_factor predefinito. Le repliche di partizione create nel ruolo di lavoro hanno le stesse definizioni di schema, indice e vincolo della tabella nel coordinatore. Dopo aver creato le repliche, la funzione salva tutti i metadati distribuiti nel coordinatore.

A ogni partizione creata viene assegnato un ID partizione univoco e tutte le relative repliche hanno lo stesso ID partizione. Le partizioni vengono rappresentate nel nodo di lavoro come normali tabelle PostgreSQL denominate "tablename_shardid", dove tablename è il nome della tabella distribuita e l'ID partizione è l'ID univoco assegnato. È possibile connettersi alle istanze di Postgres di lavoro per visualizzare o eseguire comandi in singole partizioni.

A questo momento si è pronti per inserire i dati nella tabella distribuita ed eseguire query su di esso. È anche possibile ottenere altre informazioni sulla funzione definita dall'utente usata in questa sezione nella tabella e nel riferimento DDL di partizione .

Tabelle di riferimento

Il metodo precedente distribuisce le tabelle in più partizioni orizzontali. Un'altra possibilità consiste nel distribuire le tabelle in una singola partizione e replicare la partizione in ogni nodo di lavoro. Le tabelle distribuite in questo modo vengono denominate tabelle di riferimento. Vengono usati per archiviare i dati a cui è necessario accedere di frequente da più nodi in un cluster.

I candidati comuni per le tabelle di riferimento includono:

  • Tabelle più piccole che devono essere unite a tabelle distribuite di dimensioni maggiori.
  • Tabelle nelle app multi-tenant che non dispongono di una colonna ID tenant o che non sono associate a un tenant. In alternativa, durante la migrazione, anche per alcune tabelle associate a un tenant.
  • Le tabelle che richiedono vincoli univoci tra più colonne e sono sufficienti.

Si supponga, ad esempio, che un sito di e-commerce multi-tenant debba calcolare l'imposta sulle vendite per le transazioni in uno dei suoi negozi. Le informazioni fiscali non sono specifiche di alcun tenant. È opportuno inserirlo in una tabella condivisa. Una tabella di riferimento incentrata sugli Stati Uniti potrebbe essere simile alla seguente:

-- a reference table

CREATE TABLE states (
  code char(2) PRIMARY KEY,
  full_name text NOT NULL,
  general_sales_tax numeric(4,3)
);

-- distribute it to all workers

SELECT create_reference_table('states');

Ora le query, ad esempio un calcolo dell'imposta per un carrello acquisti, possono essere aggiunte alla states tabella senza sovraccarico di rete e possono aggiungere una chiave esterna al codice di stato per una migliore convalida.

Oltre a distribuire una tabella come singola partizione replicata, la funzione definita dall'utente create_reference_table lo contrassegna come tabella di riferimento nelle tabelle di metadati di Azure Cosmos DB per PostgreSQL. Azure Cosmos DB per PostgreSQL esegue automaticamente commit in due fasi (2PC) per le modifiche alle tabelle contrassegnate in questo modo, che offre garanzie di coerenza assoluta.

Per un altro esempio dell'uso di tabelle di riferimento, vedere l'esercitazione sul database multi-tenant.

Distribuzione dei dati coordinatori

Se un database PostgreSQL esistente viene convertito nel nodo coordinatore per un cluster, i dati nelle tabelle possono essere distribuiti in modo efficiente e con un'interruzione minima in un'applicazione.

La create_distributed_table funzione descritta in precedenza funziona in tabelle vuote e non vuote e, per quest'ultima, distribuisce automaticamente le righe della tabella in tutto il cluster. Si saprà se copia i dati in base alla presenza del messaggio "AVVISO: Copia dei dati dalla tabella locale..." Per esempio:

CREATE TABLE series AS SELECT i FROM generate_series(1,1000000) i;
SELECT create_distributed_table('series', 'i');
NOTICE:  Copying data from local table...
 create_distributed_table
 --------------------------

 (1 row)

Le scritture nella tabella vengono bloccate durante la migrazione dei dati e le scritture in sospeso vengono gestite come query distribuite dopo il commit della funzione. Se la funzione non riesce, le query diventano nuovamente locali. Le letture possono continuare come di consueto e diventeranno query distribuite dopo il commit della funzione.

Quando si distribuiscono le tabelle A e B, dove A ha una chiave esterna a B, distribuire prima la tabella di destinazione della chiave B. L'operazione nell'ordine sbagliato causerà un errore:

ERROR:  cannot create foreign key constraint
DETAIL:  Referenced table must be a distributed table or a reference table.

Se non è possibile distribuire nell'ordine corretto, eliminare le chiavi esterne, distribuire le tabelle e ricreare le chiavi esterne.

Quando si esegue la migrazione dei dati da un database esterno, ad esempio da Amazon RDS ad Azure Cosmos DB per PostgreSQL, creare prima le tabelle distribuite di Azure Cosmos DB per PostgreSQL tramite create_distributed_table, quindi copiare i dati nella tabella. La copia in tabelle distribuite evita di esaurire lo spazio nel nodo coordinatore.

Condivisione di tabelle

La corilevazione significa posizionare le informazioni correlate sulle stesse macchine. Consente query efficienti, sfruttando al tempo stesso la scalabilità orizzontale per l'intero set di dati. Per altre informazioni, vedere colocation.

Le tabelle sono raggruppate in gruppi. Per controllare manualmente l'assegnazione del gruppo di corilevazione di una tabella, usare il parametro facoltativo colocate_with di create_distributed_table. Se non si è preoccupati della condivisione condivisa di una tabella, omettere questo parametro. Il valore 'default'predefinito è , che raggruppa la tabella con qualsiasi altra tabella di corilevazione predefinita con lo stesso tipo di colonna di distribuzione, numero di partizioni e fattore di replica. Se si vuole interrompere o aggiornare questa condivisione implicita, è possibile usare update_distributed_table_colocation().

-- these tables are implicitly co-located by using the same
-- distribution column type and shard count with the default
-- co-location group

SELECT create_distributed_table('A', 'some_int_col');
SELECT create_distributed_table('B', 'other_int_col');

Quando una nuova tabella non è correlata ad altri utenti nel gruppo di condivisione implicita, specificare colocated_with => 'none'.

-- not co-located with other tables

SELECT create_distributed_table('A', 'foo', colocate_with => 'none');

La suddivisione di tabelle non correlate in gruppi di condivisione consente di migliorare le prestazioni di ribilanciamento delle partizioni , perché le partizioni nello stesso gruppo devono essere spostate insieme.

Quando le tabelle sono effettivamente correlate (ad esempio quando verranno unite), può essere utile raggrupparle in modo esplicito. I guadagni della corilevazione appropriata sono più importanti di qualsiasi sovraccarico di ribilanciamento.

Per raggruppare in modo esplicito più tabelle, distribuirne una e quindi inserirle nel gruppo di condivisione. Ad esempio:

-- distribute stores
SELECT create_distributed_table('stores', 'store_id');

-- add to the same group as stores
SELECT create_distributed_table('orders', 'store_id', colocate_with => 'stores');
SELECT create_distributed_table('products', 'store_id', colocate_with => 'stores');

Le informazioni sui gruppi di condivisione vengono archiviate nella tabella pg_dist_colocation , mentre pg_dist_partition rivelano le tabelle assegnate ai gruppi.

Eliminazione di tabelle

È possibile usare il comando STANDARD POSTGRESQL DROP TABLE per rimuovere le tabelle distribuite. Come per le tabelle regolari, DROP TABLE rimuove tutti gli indici, le regole, i trigger e i vincoli esistenti per la tabella di destinazione. Inoltre, elimina anche le partizioni nei nodi di lavoro e ne pulisce i metadati.

DROP TABLE github_events;

Modifica delle tabelle

Azure Cosmos DB per PostgreSQL propaga automaticamente molti tipi di istruzioni DDL. La modifica di una tabella distribuita nel nodo coordinatore aggiornerà anche le partizioni nei ruoli di lavoro. Altre istruzioni DDL richiedono la propagazione manuale e alcune altre sono vietate, ad esempio quelle che modificano una colonna di distribuzione. Il tentativo di eseguire DDL non idoneo per la propagazione automatica genererà un errore e lascerà invariate le tabelle nel nodo coordinatore.

Di seguito è riportato un riferimento alle categorie di istruzioni DDL propagate.

Aggiunta/modifica di colonne

Azure Cosmos DB per PostgreSQL propaga automaticamente la maggior parte dei comandi ALTER TABLE . L'aggiunta di colonne o la modifica dei valori predefiniti funzionano come in un database PostgreSQL a computer singolo:

-- Adding a column

ALTER TABLE products ADD COLUMN description text;

-- Changing default value

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

Anche le modifiche significative apportate a una colonna esistente, ad esempio la ridenominazione o la modifica del tipo di dati, vanno bene. Non è tuttavia possibile modificare il tipo di dati della colonna di distribuzione . Questa colonna determina il modo in cui i dati della tabella vengono distribuiti tramite il cluster e la modifica del tipo di dati richiederebbe lo spostamento dei dati.

Se si tenta di eseguire questa operazione, viene generato un errore:

-- assumining store_id is the distribution column
-- for products, and that it has type integer

ALTER TABLE products
ALTER COLUMN store_id TYPE text;

/*
ERROR:  XX000: cannot execute ALTER TABLE command involving partition column
LOCATION:  ErrorIfUnsupportedAlterTableStmt, multi_utility.c:2150
*/

Aggiunta/rimozione di vincoli

L'uso di Azure Cosmos DB per PostgreSQL consente di continuare a sfruttare la sicurezza di un database relazionale, inclusi i vincoli di database (vedere la documentazione di PostgreSQL). A causa della natura dei sistemi distribuiti, Azure Cosmos DB per PostgreSQL non farà riferimento a vincoli di univocità tra riferimenti o integrità referenziale tra nodi di lavoro.

Per configurare una chiave esterna tra tabelle distribuite con percorso condiviso, includere sempre la colonna di distribuzione nella chiave. L'inclusione della colonna di distribuzione può comportare la composizione della chiave.

Le chiavi esterne possono essere create in queste situazioni:

  • tra due tabelle locali (non distribuite),
  • tra due tabelle di riferimento,
  • tra due tabelle distribuite con percorso condiviso quando la chiave include la colonna di distribuzione o
  • come tabella distribuita che fa riferimento a una tabella di riferimento

Le chiavi esterne dalle tabelle di riferimento alle tabelle distribuite non sono supportate.

Nota

Le chiavi primarie e i vincoli di univocità devono includere la colonna di distribuzione. L'aggiunta a una colonna non di distribuzione genererà un errore

In questo esempio viene illustrato come creare chiavi primarie ed esterne nelle tabelle distribuite:

--
-- Adding a primary key
-- --------------------

-- We'll distribute these tables on the account_id. The ads and clicks
-- tables must use compound keys that include account_id.

ALTER TABLE accounts ADD PRIMARY KEY (id);
ALTER TABLE ads ADD PRIMARY KEY (account_id, id);
ALTER TABLE clicks ADD PRIMARY KEY (account_id, id);

-- Next distribute the tables

SELECT create_distributed_table('accounts', 'id');
SELECT create_distributed_table('ads',      'account_id');
SELECT create_distributed_table('clicks',   'account_id');

--
-- Adding foreign keys
-- -------------------

-- Note that this can happen before or after distribution, as long as
-- there exists a uniqueness constraint on the target column(s) which
-- can only be enforced before distribution.

ALTER TABLE ads ADD CONSTRAINT ads_account_fk
  FOREIGN KEY (account_id) REFERENCES accounts (id);
ALTER TABLE clicks ADD CONSTRAINT clicks_ad_fk
  FOREIGN KEY (account_id, ad_id) REFERENCES ads (account_id, id);

Analogamente, includere la colonna di distribuzione nei vincoli di univocità:

-- Suppose we want every ad to use a unique image. Notice we can
-- enforce it only per account when we distribute by account id.

ALTER TABLE ads ADD CONSTRAINT ads_unique_image
  UNIQUE (account_id, image_url);

I vincoli non Null possono essere applicati a qualsiasi colonna (distribuzione o meno) perché non richiedono ricerche tra i ruoli di lavoro.

ALTER TABLE ads ALTER COLUMN image_url SET NOT NULL;

Utilizzo di vincoli NOT VALID

In alcune situazioni può essere utile applicare vincoli per le nuove righe, consentendo al tempo stesso che le righe esistenti non conformi rimangano invariate. Azure Cosmos DB per PostgreSQL supporta questa funzionalità per i vincoli CHECK e le chiavi esterne, usando la designazione del vincolo "NOT VALID" di PostgreSQL.

Si consideri ad esempio un'applicazione che archivia i profili utente in una tabella di riferimento.

-- we're using the "text" column type here, but a real application
-- might use "citext" which is available in a postgres contrib module

CREATE TABLE users ( email text PRIMARY KEY );
SELECT create_reference_table('users');

Nel corso del tempo si immagini che alcuni indirizzi non si trovano nella tabella.

INSERT INTO users VALUES
   ('foo@example.com'), ('hacker12@aol.com'), ('lol');

Si desidera convalidare gli indirizzi, ma PostgreSQL non consente normalmente di aggiungere un vincolo CHECK che non riesce per le righe esistenti. Tuttavia , consente un vincolo contrassegnato non valido:

ALTER TABLE users
ADD CONSTRAINT syntactic_email
CHECK (email ~
   '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$'
) NOT VALID;

Le nuove righe sono ora protette.

INSERT INTO users VALUES ('fake');

/*
ERROR:  new row for relation "users_102010" violates
        check constraint "syntactic_email_102010"
DETAIL:  Failing row contains (fake).
*/

In un secondo momento, durante le ore non di punta, un amministratore del database può tentare di correggere le righe non corrette e riconvalidare il vincolo.

-- later, attempt to validate all rows
ALTER TABLE users
VALIDATE CONSTRAINT syntactic_email;

La documentazione di PostgreSQL contiene altre informazioni su NOT VALID e VALIDATE CONSTRAINT nella sezione ALTER TABLE .

Aggiunta/rimozione di indici

Azure Cosmos DB per PostgreSQL supporta l'aggiunta e la rimozione di indici:

-- Adding an index

CREATE INDEX clicked_at_idx ON clicks USING BRIN (clicked_at);

-- Removing an index

DROP INDEX clicked_at_idx;

L'aggiunta di un indice accetta un blocco di scrittura, che può essere indesiderato in un "system-of-record". Per ridurre al minimo i tempi di inattività dell'applicazione, creare invece l'indice contemporaneamente . Questo metodo richiede più lavoro totale di una compilazione di indice standard e richiede più tempo per completare. Tuttavia, poiché consente di continuare le normali operazioni durante la compilazione dell'indice, questo metodo è utile per aggiungere nuovi indici in un ambiente di produzione.

-- Adding an index without locking table writes

CREATE INDEX CONCURRENTLY clicked_at_idx ON clicks USING BRIN (clicked_at);

Tipi e funzioni

Creazione di tipi SQL personalizzati e funzioni definite dall'utente propogate ai nodi di lavoro. Tuttavia, la creazione di tali oggetti di database in una transazione con operazioni distribuite comporta compromessi.

Azure Cosmos DB per PostgreSQL parallelizza le operazioni, create_distributed_table() ad esempio tra partizioni usando più connessioni per ogni ruolo di lavoro. Mentre, quando si crea un oggetto di database, Azure Cosmos DB per PostgreSQL lo propaga ai nodi di lavoro usando una singola connessione per ogni ruolo di lavoro. La combinazione delle due operazioni in una singola transazione può causare problemi, perché le connessioni parallele non potranno visualizzare l'oggetto creato su una singola connessione ma non ancora eseguito il commit.

Si consideri un blocco di transazione che crea un tipo, una tabella, carica i dati e distribuisce la tabella:

BEGIN;

-- type creation over a single connection:
CREATE TYPE coordinates AS (x int, y int);
CREATE TABLE positions (object_id text primary key, position coordinates);

-- data loading thus goes over a single connection:
SELECT create_distributed_table(‘positions’, ‘object_id’);

SET client_encoding TO 'UTF8';
\COPY positions FROM ‘positions.csv’

COMMIT;

Prima di Citus 11.0, Citus rinvierebbe la creazione del tipo nei nodi di lavoro e la eseguirà separatamente durante la creazione della tabella distribuita. Ciò ha abilitato la copia dei dati in create_distributed_table() modo da verificarsi in parallelo. Tuttavia, significa anche che il tipo non è sempre presente nei nodi di lavoro Citus , o se la transazione viene eseguito il rollback, il tipo rimarrà nei nodi di lavoro.

Con Citus 11.0, il comportamento predefinito cambia per assegnare priorità alla coerenza dello schema tra il coordinatore e i nodi di lavoro. Il nuovo comportamento presenta un lato negativo: se la propagazione degli oggetti si verifica dopo un comando parallelo nella stessa transazione, la transazione non può più essere completata, come evidenziato dall'ERRORE nel blocco di codice seguente:

BEGIN;
CREATE TABLE items (key text, value text);
-- parallel data loading:
SELECT create_distributed_table(‘items’, ‘key’);
SET client_encoding TO 'UTF8';
\COPY items FROM ‘items.csv’
CREATE TYPE coordinates AS (x int, y int);

ERROR:  cannot run type command because there was a parallel operation on a distributed table in the transaction

Se si verifica questo problema, sono disponibili due semplici soluzioni alternative:

  1. Usare impostato citus.create_object_propagation su per automatic rinviare la creazione del tipo in questa situazione, in questo caso potrebbe verificarsi una certa incoerenza tra i quali sono presenti oggetti di database in nodi diversi.
  2. Usare impostato citus.multi_shard_modify_mode su per sequential disabilitare il parallelismo per nodo. Il caricamento dei dati nella stessa transazione potrebbe essere più lento.

Passaggi successivi