Distribuire e modificare tabelle in Azure Cosmos DB for PostgreSQL

  • Articolo

SI APPLICA A: Azure Cosmos DB for PostgreSQL (basato su estensione database Citus per 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
);

Successivamente, è 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 for PostgreSQL che la tabella github_events deve essere distribuita nella colonna repo_id (eseguendo l'hash del valore della colonna).

Per modalità predefinita viene creato un totale di 32 partizioni e ciascuna possiede parte di uno spazio hash e viene replicata in base al valore di configurazione predefinito citus.shard_replication_factor. 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 nel coordinatore tutti i metadati distribuiti.

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

Ora è possibile inserire i dati nella tabella distribuita ed eseguirvi query. Per altre informazioni sulla UDF usata in questa sezione, vedere le informazioni di riferimento in DDL della tabella e della 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 sono denominate tabelle di riferimento. Sono usate per archiviare i dati a cui è necessario accedere di frequente da più nodi in un cluster.

Le scelte frequenti per le tabelle di riferimento includono:

  • Tabelle più piccole che devono unirsi 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).
  • Tabelle che richiedono vincoli univoci tra più colonne e sono sufficientemente piccole.

Si supponga, ad esempio, che un sito eCommerce multi-tenant debba calcolare l’imposta sulle vendite per le transazioni in uno dei propri store. Le informazioni fiscali non sono specifiche di un tenant. Quindi è opportuno inserirle in una tabella condivisa. Una tabella di riferimento incentrata sugli Stati Uniti potrebbe avere un aspetto simile al 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 delle imposte per un carrello acquisti, possono unirsi alla tabella states senza sovraccarico di rete e, per una migliore convalida, possono aggiungere una chiave esterna al codice di stato.

Oltre a distribuire una tabella come singola partizione replicata, la UDF create_reference_table la contrassegna come tabella di riferimento nelle tabelle di metadati di Azure Cosmos DB for PostgreSQL. Per le modifiche alle tabelle contrassegnate in questo modo, Azure Cosmos DB for PostgreSQL esegue automaticamente commit in due fasi (2PC) così da garantire una coerenza assoluta.

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

Distribuzione dei dati del coordinatore

Se un database PostgreSQL esistente viene convertito nel nodo coordinatore per un cluster, i dati nelle sue tabelle possono essere distribuiti in un’applicazione in modo efficiente e con interruzioni minime.

La funzione create_distributed_table descritta in precedenza funziona su tabelle vuote e non vuote e in questo secondo caso distribuisce automaticamente le righe della tabella in tutto il cluster. La conferma dell’azione di copia dei dati si ottiene dalla presenza del messaggio "AVVISO: copia dei dati dalla tabella locale..." Ad 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)

Durante la migrazione dei dati le scritture nella tabella sono bloccate; le scritture in sospeso vengono gestite come query distribuite dopo i commit della funzione (se la funzione ha esito negativo, le query diventano nuovamente locali). Le letture possono continuare come di consueto e diventeranno query distribuite dopo i commit della funzione.

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

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

Se non è possibile eseguire la distribuzione nell'ordine corretto, rimuovere 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 Servizi Desktop remoto di Amazon ad Azure Cosmos DB for PostgreSQL, creare prima di tutto le tabelle distribuite di Azure Cosmos DB for PostgreSQL tramite create_distributed_table, quindi copiare i dati nella tabella. La copia in tabelle distribuite consente di evitare di esaurire spazio nel nodo coordinatore.

Tabelle di coubicazione

Coubicazione significa posizionare le informazioni correlate negli stessi computer. Nono solo consente query efficienti ma al contempo sfruttala scalabilità orizzontale per l'intero set di dati. Per altre informazioni, vedere coubicazione.

Le tabelle sono coubicate in gruppi. Per controllare manualmente l'assegnazione del gruppo di coubicazione di una tabella, usare il parametro facoltativo colocate_with di create_distributed_table. Se non si è interessati alla coubicazione di una tabella, omettere questo parametro. Per impostazione predefinita assume il valore 'default', che raggruppa la tabella con qualsiasi altra tabella di coubicazione predefinita con tipo di colonna di distribuzione, numero di partizioni e fattore di replica uguali. Per interrompere o aggiornare questa coubicazione implicita, 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 elementi nel potenziale gruppo di coubicazione 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 nei rispettivi gruppi di coubicazione migliorerà le prestazioni di ribilanciamento delle partizioni. Questo avviene perché le partizioni nello stesso gruppo devono essere spostate insieme.

Quando le tabelle sono effettivamente correlate (ad esempio, quando saranno unite), può essere utile coubicarle in modo esplicito. I vantaggi di una coubicazione appropriata sono più importanti di qualsiasi sovraccarico di ribilanciamento.

Per coubicare più tabelle in modo esplicito, distribuirne una e quindi inserire le altre nel gruppo di coubicazione. 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 coubicazione vengono archiviate nella tabella pg_dist_colocation, mentre pg_dist_partition rivela quali tabelle sono assegnate ai gruppi.

Eliminazione di tabelle

Per rimuovere le tabelle distribuite, è possibile usare il comando DROP TABLE standard di PostgreSQL. Come per le tabelle normali, il comando DROP TABLE rimuove gli indici, le regole, i trigger e i vincoli che esistono per la tabella di destinazione. Inoltre, elimina anche le partizioni nei nodi di lavoro e ne pulisce i metadati.

DROP TABLE github_events;

Modifica di tabelle

Azure Cosmos DB for 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 mentre altre sono vietate, ad esempio quelle che andrebbero a modificare 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 for 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 di un 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;

È comunque possibile modifiche significative apportate a una colonna esistente, ad esempio la ridenominazione o la modifica del tipo di dati. 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 for PostgreSQL consente di continuare a sfruttare la sicurezza di un database relazionale, inclusi i vincoli di database (vedere le documentazioni di PostgreSQL). Data la natura dei sistemi distribuiti, Azure Cosmos DB for PostgreSQL non farà riferimento a vincoli di univocità tra riferimenti o all’integrità referenziale tra nodi di lavoro.

Per configurare una chiave esterna tra tabelle distribuite coubicate, includere sempre la colonna di distribuzione nella chiave. L'inclusione della colonna di distribuzione può comportare la creazione dell’istruzione composta 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 coubicate quando la chiave include la colonna di distribuzione, o
  • come tabella distribuita che si riferisce a una tabella di riferimento

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

Nota

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

Questo esempio mostra 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 (di 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 contempo che le righe esistenti non conformi rimangano invariate. Azure Cosmos DB for 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 supponga che vengano inseriti nella tabella alcuni dati non-indirizzo.

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

Il desiderio è convalidare gli indirizzi, ma PostgreSQL in genere non consente di aggiungere un vincolo CHECK che restituisce un errore per le righe esistenti. Tuttavia consente un vincolo contrassegnato come 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 seguito, fuori le ore di punta, un amministratore del database potrà 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 sezione ALTER TABLE della documentazione di PostgreSQL contiene altre informazioni su NON VALIDO e CONVALIDA VINCOLO.

Aggiunta/rimozione di indici

Azure Cosmos DB for PostgreSQL supporta l'aggiunta e la rimozione degli 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 in un "system-of-record" multi-tenant potrebbe essere non desiderabile. Per ridurre al minimo i tempi di inattività dell'applicazione, creare l'indice in simultanea. Questo metodo richiede più lavoro totale rispetto a una compilazione di indice standard e richiede più tempo per il completamento. Tuttavia, poiché consente di proseguire le normali operazioni durante la compilazione dell'indice, questo metodo è utile per l'aggiunta di 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

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

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

Si consideri un blocco di transazioni 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 della versione 11.0, Citus rinviava la creazione del tipo nei nodi di lavoro ed eseguiva il commit separatamente durante la creazione della tabella distribuita. Questo abilitava l’esecuzione in parallelo della copia dei dati in create_distributed_table(). Tuttavia, questo comportava che il tipo non era sempre presente nei nodi del ruolo di lavoro di Citus o se la transazione eseguiva il rollback, il tipo rimaneva nei nodi di lavoro.

Con Citus 11.0, il comportamento predefinito cambia e classifica in ordine di priorità la coerenza dello schema tra il coordinatore e i nodi di lavoro. Il nuovo comportamento presenta uno svantaggio: se la propagazione dell’oggetto avviene dopo un comando parallelo nella stessa transazione, la transazione non può più essere completata, come evidenziato da ERROR 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 incontra questo problema, esistono due soluzioni alternative semplici:

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

Passaggi successivi

Query di diagnostica utili