Tabellen distribueren en wijzigen in Azure Cosmos DB for PostgreSQL
VAN TOEPASSING OP: 
Azure Cosmos DB for PostgreSQL (mogelijk gemaakt door de Citus-database-extensie naar PostgreSQL)
Tabellen distribueren
Als u een gedistribueerde tabel wilt maken, moet u eerst het tabelschema definiëren. Hiervoor kunt u een tabel definiëren met behulp van de instructie CREATE TABLE op dezelfde manier als met een gewone PostgreSQL-tabel.
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
);
Vervolgens kunt u de functie create_distributed_table() gebruiken om de kolom tabeldistributie op te geven en de werkhards te maken.
SELECT create_distributed_table('github_events', 'repo_id');
De functie-aanroep informeert Azure Cosmos DB for PostgreSQL dat de github_events tabel moet worden gedistribueerd op de repo_id kolom (door de kolomwaarde te hashen).
Er worden standaard 32 shards gemaakt, waarbij elke shard eigenaar is van een gedeelte van een hash-ruimte en wordt gerepliceerd op basis van de standaardconfiguratiewaarde citus.shard_replication_factor. De shardreplica's die op de werkrol zijn gemaakt, hebben hetzelfde tabelschema, dezelfde index- en beperkingsdefinities als de tabel in de coördinator. Zodra de replica's zijn gemaakt, slaat de functie alle gedistribueerde metagegevens op de coördinator op.
Aan elke gemaakte shard wordt een unieke shard-id toegewezen en alle replica's hebben dezelfde shard-id. Shards worden weergegeven op het werkknooppunt als reguliere PostgreSQL-tabellen met de naam 'tablename_shardid', waarbij tablename de naam van de gedistribueerde tabel is en de shard-id de unieke id is die is toegewezen. U kunt verbinding maken met de postgres-exemplaren van de werkrol om opdrachten op afzonderlijke shards weer te geven of uit te voeren.
U kunt nu gegevens invoegen in de gedistribueerde tabel en er query's op uitvoeren. U vindt ook meer informatie over de UDF die in deze sectie wordt gebruikt in de tabel- en shard-DDL-verwijzing .
Referentietabellen
De bovenstaande methode distribueert tabellen in meerdere horizontale shards. Een andere mogelijkheid is het distribueren van tabellen in één shard en het repliceren van de shard naar elk werkknooppunt. Tabellen die op deze manier worden gedistribueerd, worden verwijzingstabellen genoemd . Ze worden gebruikt voor het opslaan van gegevens die vaak moeten worden geopend door meerdere knooppunten in een cluster.
Veelvoorkomende kandidaten voor referentietabellen zijn:
- Kleinere tabellen die moeten worden samengevoegd met grotere gedistribueerde tabellen.
- Tabellen in apps met meerdere tenants die geen tenant-id-kolom hebben of die niet zijn gekoppeld aan een tenant. (Of, tijdens de migratie, zelfs voor sommige tabellen die zijn gekoppeld aan een tenant.)
- Tabellen die unieke beperkingen voor meerdere kolommen nodig hebben en klein genoeg zijn.
Stel dat een e-commercesite met meerdere tenants btw moet berekenen voor transacties in een van de winkels. Belastinggegevens zijn niet specifiek voor een tenant. Het is logisch om deze in een gedeelde tabel te plaatsen. Een amerikaanse referentietabel kan er als volgt uitzien:
-- 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');
Query's zoals één berekening van belasting voor een winkelwagen kunnen nu zonder netwerkoverhead aan de states tabel worden toegevoegd en kunnen een refererende sleutel toevoegen aan de statuscode voor betere validatie.
Naast het distribueren van een tabel als één gerepliceerde shard, markeert de create_reference_table UDF deze als referentietabel in de metagegevenstabellen van Azure Cosmos DB for PostgreSQL. Azure Cosmos DB for PostgreSQL voert automatisch doorvoeringen in twee fasen (2PC) uit voor wijzigingen in tabellen die op deze manier zijn gemarkeerd, wat sterke consistentiegaranties biedt.
Zie de zelfstudie voor database met meerdere tenants voor een ander voorbeeld van het gebruik van referentietabellen.
Coördinatorgegevens distribueren
Als een bestaande PostgreSQL-database wordt geconverteerd naar het coördinatorknooppunt voor een cluster, kunnen de gegevens in de tabellen efficiënt en met minimale onderbreking van een toepassing worden gedistribueerd.
De create_distributed_table eerder beschreven functie werkt zowel voor lege als niet-lege tabellen, en voor de laatste functie worden tabelrijen automatisch verdeeld over het hele cluster. U weet of er gegevens worden gekopieerd op basis van de aanwezigheid van het bericht: 'KENNISGEVING: Gegevens uit lokale tabel kopiëren...' Bijvoorbeeld:
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)
Schrijfbewerkingen in de tabel worden geblokkeerd terwijl de gegevens worden gemigreerd en in behandeling zijnde schrijfbewerkingen worden verwerkt als gedistribueerde query's zodra de functie doorvoert. (Als de functie mislukt, worden de query's weer lokaal.) Leesbewerkingen kunnen als normaal worden voortgezet en worden gedistribueerde query's zodra de functie doorvoert.
Wanneer u tabellen A en B distribueert, waarbij A een refererende sleutel heeft naar B, distribueert u eerst de doeltabel B van de sleutel. Als u dit in de verkeerde volgorde doet, treedt er een fout op:
ERROR: cannot create foreign key constraint
DETAIL: Referenced table must be a distributed table or a reference table.
Als het niet mogelijk is om in de juiste volgorde te distribueren, verwijdert u de refererende sleutels, distribueert u de tabellen en maakt u de refererende sleutels opnieuw.
Wanneer u gegevens migreert van een externe database, zoals van Amazon RDS naar Azure Cosmos DB for PostgreSQL, maakt u eerst de gedistribueerde tabellen van Azure Cosmos DB for PostgreSQL via create_distributed_tableen kopieert u de gegevens vervolgens naar de tabel.
Kopiëren naar gedistribueerde tabellen voorkomt dat er onvoldoende ruimte beschikbaar is op het coördinatorknooppunt.
Tabellen coloceren
Colocatie betekent het plaatsen van gerelateerde informatie op dezelfde machines. Het maakt efficiënte query's mogelijk, terwijl u profiteert van de horizontale schaalbaarheid voor de hele gegevensset. Zie colocatie voor meer informatie.
Tabellen worden in groepen op een punt weergegeven. Als u handmatig de toewijzing van een colocatiegroep van een tabel wilt beheren, gebruikt u de optionele colocate_with parameter van create_distributed_table. Als u niet om de colocatie van een tabel geeft, laat u deze parameter weg. Deze wordt standaard ingesteld op de waarde 'default', waarmee de tabel wordt gegroepeerd met een andere standaardcolocatietabel met hetzelfde type distributiekolom, shardaantal en replicatiefactor. Als u deze impliciete colocatie wilt onderbreken of bijwerken, kunt u dit gebruiken 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');
Wanneer een nieuwe tabel niet is gerelateerd aan andere tabellen in de bijbehorende impliciete colocatiegroep, geeft u colocated_with => 'none'op.
-- not co-located with other tables
SELECT create_distributed_table('A', 'foo', colocate_with => 'none');
Het splitsen van niet-gerelateerde tabellen in hun eigen colocatiegroepen verbetert de prestaties van shardherverdeling , omdat shards in dezelfde groep samen moeten worden verplaatst.
Wanneer tabellen inderdaad gerelateerd zijn (bijvoorbeeld wanneer ze worden samengevoegd), kan het zinvol zijn om ze expliciet te koppelen. De winsten van de juiste colocatie zijn belangrijker dan elke herverdeling van overhead.
Als u meerdere tabellen expliciet wilt plaatsen, distribueert u er een en plaatst u de andere in de colocatiegroep. Voorbeeld:
-- 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');
Informatie over colocatiegroepen wordt opgeslagen in de pg_dist_colocation tabel, terwijl pg_dist_partition aangeeft welke tabellen aan welke groepen zijn toegewezen.
Tabellen neerzetten
U kunt de standaardopdracht PostgreSQL DROP TABLE gebruiken om uw gedistribueerde tabellen te verwijderen. Net als bij gewone tabellen verwijdert DROP TABLE alle indexen, regels, triggers en beperkingen die voor de doeltabel bestaan. Daarnaast worden de shards op de werkknooppunten niet meer weergegeven en worden de metagegevens opgeschoond.
DROP TABLE github_events;
Tabellen wijzigen
In Azure Cosmos DB for PostgreSQL worden automatisch veel soorten DDL-instructies doorgegeven. Als u een gedistribueerde tabel wijzigt op het coördinatorknooppunt, worden ook shards op de werkrollen bijgewerkt. Voor andere DDL-instructies is handmatige doorgifte vereist, en bepaalde andere instructies zijn verboden, zoals elke die een distributiekolom zou wijzigen. Als u DDL probeert uit te voeren die niet in aanmerking komt voor automatische doorgifte, wordt er een fout gegenereerd en blijven tabellen ongewijzigd op het coördinatorknooppunt.
Hier volgt een verwijzing naar de categorieën DDL-instructies die worden doorgegeven.
Kolommen toevoegen/wijzigen
Azure Cosmos DB for PostgreSQL geeft automatisch de meeste ALTER TABLE-opdrachten door. Kolommen toevoegen of de standaardwaarden wijzigen, werken zoals in een PostgreSQL-database met één machine:
-- Adding a column
ALTER TABLE products ADD COLUMN description text;
-- Changing default value
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
Belangrijke wijzigingen in een bestaande kolom, zoals het wijzigen van de naam ervan of het wijzigen van het gegevenstype, zijn ook prima. Het gegevenstype van de distributiekolom kan echter niet worden gewijzigd. Deze kolom bepaalt hoe tabelgegevens via het cluster worden gedistribueerd en het gegevenstype moet worden gewijzigd. Hiervoor moeten de gegevens worden verplaatst.
Als u dit probeert, treedt er een fout op:
-- 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
*/
Beperkingen toevoegen/verwijderen
Met Azure Cosmos DB for PostgreSQL kunt u blijven profiteren van de veiligheid van een relationele database, inclusief databasebeperkingen (zie de PostgreSQL-documenten). Vanwege de aard van gedistribueerde systemen, worden in Azure Cosmos DB for PostgreSQL geen beperkingen voor uniekheid of referentiële integriteit tussen werkknooppunten kruisverwijzingen gebruikt.
Als u een refererende sleutel wilt instellen tussen gedistribueerde tabellen met een punt, moet u altijd de distributiekolom in de sleutel opnemen. Het opnemen van de distributiekolom kan betrekking hebben op het maken van de sleutelverbinding.
In deze situaties kunnen refererende sleutels worden gemaakt:
- tussen twee lokale (niet-gedistribueerde) tabellen,
- tussen twee referentietabellen,
- tussen twee gedistribueerde tabellen met een punt als de sleutel de distributiekolom bevat of
- als een gedistribueerde tabel die verwijst naar een verwijzingstabel
Refererende sleutels van referentietabellen naar gedistribueerde tabellen worden niet ondersteund.
Notitie
Primaire sleutels en beperkingen voor uniekheid moeten de distributiekolom bevatten. Als u deze toevoegt aan een niet-distributiekolom, wordt er een fout gegenereerd
In dit voorbeeld ziet u hoe u primaire en refererende sleutels maakt voor gedistribueerde tabellen:
--
-- 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);
Neem ook de distributiekolom op in uniekheidsbeperkingen:
-- 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);
Niet-null-beperkingen kunnen worden toegepast op elke kolom (distributie of niet), omdat er geen zoekacties tussen werkrollen nodig zijn.
ALTER TABLE ads ALTER COLUMN image_url SET NOT NULL;
ONGELDIGE beperkingen gebruiken
In sommige situaties kan het handig zijn om beperkingen af te dwingen voor nieuwe rijen, terwijl bestaande niet-conforme rijen ongewijzigd blijven. Azure Cosmos DB for PostgreSQL ondersteunt deze functie voor CHECK-beperkingen en refererende sleutels, met behulp van de beperkingsaanduiding 'ONGELDIG' van PostgreSQL.
Denk bijvoorbeeld aan een toepassing waarin gebruikersprofielen in een referentietabel worden opgeslagen.
-- 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');
Stel dat er in de loop van de tijd een paar niet-adressen in de tabel komen.
INSERT INTO users VALUES
('foo@example.com'), ('hacker12@aol.com'), ('lol');
We willen de adressen valideren, maar PostgreSQL staat ons gewoonlijk niet toe om een CHECK-beperking toe te voegen die mislukt voor bestaande rijen. Er is echter wel een beperking toegestaan die niet geldig is gemarkeerd:
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;
Nieuwe rijen zijn nu beveiligd.
INSERT INTO users VALUES ('fake');
/*
ERROR: new row for relation "users_102010" violates
check constraint "syntactic_email_102010"
DETAIL: Failing row contains (fake).
*/
Later kan een databasebeheerder tijdens niet-piekuren proberen de slechte rijen te herstellen en de beperking opnieuw tevalideren.
-- later, attempt to validate all rows
ALTER TABLE users
VALIDATE CONSTRAINT syntactic_email;
De PostgreSQL-documentatie bevat meer informatie over NOT VALID en VALIDATE CONSTRAINT in de sectie ALTER TABLE .
Indexen toevoegen/verwijderen
Azure Cosmos DB for PostgreSQL biedt ondersteuning voor het toevoegen en verwijderen van indexen:
-- Adding an index
CREATE INDEX clicked_at_idx ON clicks USING BRIN (clicked_at);
-- Removing an index
DROP INDEX clicked_at_idx;
Als u een index toevoegt, wordt een schrijfvergrendeling gebruikt. Dit kan ongewenst zijn in een 'systeem-van-record' met meerdere tenants. Als u de downtime van toepassingen wilt minimaliseren, maakt u de index gelijktijdig . Deze methode vereist meer totale hoeveelheid werk dan een standaardindexbuild en duurt langer om te voltooien. Omdat normale bewerkingen echter kunnen worden voortgezet terwijl de index wordt gebouwd, is deze methode handig voor het toevoegen van nieuwe indexen in een productieomgeving.
-- Adding an index without locking table writes
CREATE INDEX CONCURRENTLY clicked_at_idx ON clicks USING BRIN (clicked_at);
Typen en functies
Aangepaste SQL-typen en door de gebruiker gedefinieerde functies propogaaten naar werkknooppunten. Het maken van dergelijke databaseobjecten in een transactie met gedistribueerde bewerkingen omvat echter compromissen.
Azure Cosmos DB for PostgreSQL parallelliseert bewerkingen zoals create_distributed_table() shards met behulp van meerdere verbindingen per werkrol. Terwijl azure Cosmos DB voor PostgreSQL dit bij het maken van een databaseobject doorgeeft aan werkknooppunten met behulp van één verbinding per werkrol. Het combineren van de twee bewerkingen in één transactie kan problemen veroorzaken, omdat de parallelle verbindingen het object dat is gemaakt via één verbinding niet kunnen zien, maar nog niet zijn doorgevoerd.
Overweeg een transactieblok dat een type, een tabel maakt, gegevens laadt en de tabel distribueert:
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;
Vóór Citus 11.0 zou Citus het maken van het type op de werkknooppunten uitstellen en deze afzonderlijk doorvoeren bij het maken van de gedistribueerde tabel. Hierdoor kunnen de gegevens parallel worden gekopieerd create_distributed_table() . Het betekende echter ook dat het type niet altijd aanwezig was op de Citus-werkknooppunten, of als de transactie werd teruggedraaid, het type op de werkrolknooppunten zou blijven.
Met Citus 11.0 verandert het standaardgedrag om prioriteit te geven aan schemaconsistentie tussen coördinator- en werkknooppunten. Het nieuwe gedrag heeft een nadeel: als objectdoorgifte plaatsvindt na een parallelle opdracht in dezelfde transactie, kan de transactie niet meer worden voltooid, zoals is gemarkeerd door de ERROR in het onderstaande codeblok:
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
Als u dit probleem ondervindt, zijn er twee eenvoudige tijdelijke oplossingen:
- Gebruik deze optie
citus.create_object_propagationom het maken van het type in deze situatie uit teautomaticstellen. In dat geval kan er sprake zijn van inconsistentie tussen welke databaseobjecten op verschillende knooppunten bestaan. - Gebruik deze instelling
citus.multi_shard_modify_modeom parallellisme per knooppunt uit tesequentialschakelen. Het laden van gegevens in dezelfde transactie kan langzamer zijn.