Partilhar via

Distribuir e modificar tabelas no Azure Cosmos DB para PostgreSQL

  • Aplica-se a: ✅ PostgreSQL

Importante

O Azure Cosmos DB para PostgreSQL não tem mais suporte para novos projetos. Não use este serviço para novos projetos. Em vez disso, use um destes dois serviços:

  • Use o Azure Cosmos DB para NoSQL para obter uma solução de banco de dados distribuído projetada para cenários de alta escala com um SLA (contrato de nível de serviço) de disponibilidade de 99.999%, dimensionamento automático instantâneo e failover automático em várias regiões.

  • Use a funcionalidade de Clusters Elásticos do Azure para PostgreSQL para PostgreSQL fragmentado, utilizando a extensão Citus de código aberto.

Distribuição de tabelas

Para criar uma tabela distribuída, você precisa primeiro definir o esquema da tabela. Para fazer isso, você pode definir uma tabela usando a instrução CREATE TABLE da mesma forma que faria com uma tabela PostgreSQL normal.

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

Em seguida, você pode usar a função create_distributed_table() para especificar a coluna de distribuição da tabela e criar os fragmentos de trabalho.

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

A chamada de função informa ao Azure Cosmos DB para PostgreSQL que a tabela github_events deve ser distribuída na coluna repo_id (fazendo hash do valor da coluna).

Ele cria um total de 32 fragmentos por padrão, nos quais cada fragmento possui uma parte de um espaço de hash e é replicado com base no valor de configuração padrão de citus.shard_replication_factor. As réplicas de fragmentos criadas no trabalhador têm o mesmo esquema de tabela, definições de índices e restrições que a tabela no coordenador. Depois que as réplicas são criadas, a função salva todos os metadados distribuídos no coordenador.

A cada fragmento criado é atribuído um ID de fragmento exclusivo, e todas as suas réplicas têm o mesmo ID de fragmento. Os fragmentos são representados no nó de trabalho como tabelas PostgreSQL regulares chamadas 'tablename_shardid', onde tablename é o nome da tabela distribuída e shard ID é a ID exclusiva atribuída. Você pode se conectar às instâncias do postgres de trabalho para exibir ou executar comandos em fragmentos individuais.

Agora você está pronto para inserir dados na tabela distribuída e executar consultas nela. Você também pode saber mais sobre a função definida pelo utilizador (UDF) usada nesta seção na referência de tabela e DDL de shard.

Tabelas de Referência

O método anterior distribui tabelas em vários fragmentos horizontais. Outra possibilidade é distribuir tabelas em um único fragmento e replicar o fragmento para cada nó de trabalho. As tabelas distribuídas desta forma são chamadas tabelas de referência. Eles são usados para armazenar dados que precisam ser acessados com frequência por vários nós em um cluster.

Os candidatos comuns para quadros de referência incluem:

  • Tabelas menores que precisam se unir a tabelas distribuídas maiores.
  • Tabelas em aplicativos multilocatários que não possuem uma coluna de ID de locatário ou que não estão associadas a um locatário. (Ou, durante a migração, até mesmo para algumas tabelas associadas a um locatário.)
  • Tabelas que precisam de restrições exclusivas em várias colunas e são pequenas o suficiente.

Por exemplo, suponha que um site de comércio eletrônico multilocatário precise calcular o imposto sobre vendas para transações em qualquer uma de suas lojas. As informações fiscais não são específicas de nenhum inquilino. Faz sentido colocá-lo numa mesa partilhada. Uma tabela de referência centrada nos EUA pode ter esta aparência:

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

Agora, consultas como uma que calcula o imposto para um carrinho de compras podem juntar-se à tabela states sem sobrecarga de rede e podem adicionar uma chave estrangeira ao código do estado para uma melhor validação.

Além de distribuir uma tabela como um único fragmento replicado, o create_reference_table UDF a marca como uma tabela de referência nas tabelas de metadados do Azure Cosmos DB para PostgreSQL. O Azure Cosmos DB para PostgreSQL executa automaticamente confirmações bifásicas (2PC) para modificações em tabelas marcadas dessa forma, o que fornece fortes garantias de consistência.

Para obter outro exemplo de uso de tabelas de referência, consulte o tutorial de banco de dados multilocatário.

Distribuição de Dados do Coordenador

Se um banco de dados PostgreSQL existente for convertido no nó coordenador de um cluster, os dados em suas tabelas poderão ser distribuídos de forma eficiente e com interrupção mínima para um aplicativo.

A create_distributed_table função descrita anteriormente funciona em tabelas vazias e não vazias e, para esta última, distribui automaticamente as linhas da tabela por todo o cluster. Você sabe se ele copia dados pela presença da mensagem "AVISO: Copiando dados da tabela local..." Por exemplo:

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)

As gravações na tabela são bloqueadas enquanto os dados são migrados, e as gravações pendentes são tratadas como consultas distribuídas quando a função é confirmada. (Se a função falhar, as consultas se tornarão locais novamente.) As leituras podem continuar normalmente e se tornarão consultas distribuídas assim que a função for confirmada.

Ao distribuir as tabelas A e B, em que A tem uma chave estrangeira para B, distribua primeiro a tabela de destino da chave B. Fazê-lo na ordem errada causa um erro:

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

Se não for possível distribuir na ordem correta, elimine as chaves estrangeiras, distribua as tabelas e recrie as chaves estrangeiras.

Ao migrar dados de um banco de dados externo, como do Amazon RDS para o Azure Cosmos DB para PostgreSQL, primeiro crie as tabelas distribuídas do Azure Cosmos DB para PostgreSQL através de create_distributed_table, e em seguida copie os dados para a tabela. Copiar para tabelas distribuídas evita ficar sem espaço no nó coordenador.

Colocação de tabelas

Colocation significa colocar informações relacionadas nas mesmas máquinas. Ele permite consultas eficientes, enquanto aproveita a escalabilidade horizontal para todo o conjunto de dados. Para obter mais informações, consulte colocation.

As mesas são agrupadas em grupos. Para controlar manualmente a atribuição do grupo de colocation de uma tabela, utilize o parâmetro opcional colocate_with de create_distributed_table. Se você não se importa com a colocalização de uma tabela, omita esse parâmetro. O padrão é o valor 'default', que agrupa a tabela com qualquer outra tabela de colocation padrão que tenha o mesmo tipo de coluna de distribuição, número de estilhaços e fator de replicação. Se pretender quebrar ou atualizar esta localização implícita, pode usar 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 uma nova tabela não estiver relacionada a outras em seu respetivo grupo de colocation implícito, especifique colocated_with => 'none'.

-- not co-located with other tables

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

Dividir tabelas não relacionadas em seus próprios grupos de colocation melhora o desempenho de reequilíbrio de estilhaços , porque fragmentos no mesmo grupo precisam ser movidos juntos.

Quando as tabelas estão de facto relacionadas (por exemplo, quando são unidas), pode fazer sentido colocá-las explicitamente no mesmo local. Os ganhos de uma colocação adequada são mais importantes do que qualquer sobrecarga de reequilíbrio.

Para colocalizar várias tabelas explicitamente, distribua uma e, em seguida, coloque as outras no seu grupo de colocação. Por exemplo:

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

As informações sobre grupos de colocation são armazenadas na tabela pg_dist_colocation , enquanto pg_dist_partition revela quais tabelas são atribuídas a quais grupos.

Eliminando tabelas

Você pode usar o comando padrão PostgreSQL DROP TABLE para remover suas tabelas distribuídas. Assim como nas tabelas regulares, DROP TABLE remove todos os índices, regras, gatilhos e restrições existentes para a tabela de destino. Além disso, deposita os fragmentos nos nós de trabalho e limpa os seus metadados.

DROP TABLE github_events;

Modificando tabelas

O Azure Cosmos DB para PostgreSQL propaga automaticamente muitos tipos de instruções DDL. A modificação de uma tabela distribuída no nó coordenador também atualiza fragmentos nos trabalhadores. Outras instruções DDL requerem propagação manual, e algumas são proibidas, como qualquer instrução que modifique uma coluna de distribuição. A tentativa de executar DDL que não é elegível para propagação automática gera um erro e mantém as tabelas no nó coordenador inalteradas.

Aqui está uma referência das categorias de instruções DDL que se propagam.

Adicionar/modificar colunas

O Azure Cosmos DB para PostgreSQL propaga a maioria dos comandos ALTER TABLE automaticamente. Adicionar colunas ou alterar seus valores padrão funcionam como em um banco de dados PostgreSQL de máquina única:

-- Adding a column

ALTER TABLE products ADD COLUMN description text;

-- Changing default value

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

Alterações significativas em uma coluna existente, como renomeá-la ou alterar seu tipo de dados, também são boas. No entanto, o tipo de dados da coluna de distribuição não pode ser alterado. Esta coluna determina como os dados da tabela se distribuem pelo cluster, e modificar seu tipo de dados exigiria mover os dados.

Tentar fazer isso causa um erro:

-- 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
*/

Adicionando/removendo restrições

Usar o Azure Cosmos DB para PostgreSQL permite que você continue a aproveitar a segurança de um banco de dados relacional, incluindo restrições de banco de dados (consulte os documentos do PostgreSQL). Devido à natureza dos sistemas distribuídos, o Azure Cosmos DB para PostgreSQL não realiza verificação cruzada de restrições de unicidade ou de integridade referencial entre os nós de trabalho.

Para configurar uma chave estrangeira entre tabelas distribuídas colocalizadas, inclua sempre a coluna de distribuição na chave. A inclusão da coluna de distribuição pode envolver a fabricação do composto principal.

Chaves estrangeiras podem ser criadas nestas situações:

  • entre duas tabelas locais (não distribuídas),
  • entre dois quadros de referência,
  • entre duas tabelas colocalizadas distribuídas quando a chave inclui a coluna de distribuição, ou
  • como uma tabela distribuída fazendo referência a uma tabela de referência

Chaves estrangeiras de tabelas de referência para tabelas distribuídas não são suportadas.

Nota

As chaves primárias e as restrições de exclusividade devem incluir a coluna de distribuição. Adicioná-los a uma coluna de não distribuição gerará um erro

Este exemplo mostra como criar chaves primárias e estrangeiras em tabelas distribuídas:

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

Da mesma forma, inclua a coluna de distribuição em restrições de exclusividade:

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

As restrições não nulas podem ser aplicadas a qualquer coluna (distribuição ou não) porque não exigem pesquisas entre trabalhadores.

ALTER TABLE ads ALTER COLUMN image_url SET NOT NULL;

Utilização de restrições NÃO VÁLIDAS

Em algumas situações, pode ser útil impor restrições para novas linhas, permitindo que as linhas não conformes existentes permaneçam inalteradas. O Azure Cosmos DB para PostgreSQL dá suporte a esse recurso para restrições CHECK e chaves estrangeiras, usando a designação de restrição "NOT VALID" do PostgreSQL.

Por exemplo, considere uma aplicação que armazena perfis de utilizador numa tabela de referência.

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

Com o passar do tempo, imagine que alguns dados sem endereços entram na tabela.

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

Gostaríamos de validar os endereços, mas o PostgreSQL normalmente não nos permite adicionar uma restrição CHECK que falha para linhas existentes. No entanto, ele permite uma restrição marcada como não válida:

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;

Novas linhas estão agora protegidas.

INSERT INTO users VALUES ('fake');

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

Mais tarde, fora do horário de pico, um administrador de banco de dados pode tentar corrigir as linhas incorretas e revalidar a restrição.

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

A documentação do PostgreSQL tem mais informações sobre NOT VALID e VALIDATE CONSTRAINT na seção ALTER TABLE .

Adicionando/removendo índices

O Azure Cosmos DB para PostgreSQL suporta a adição e remoção de índices:

-- Adding an index

CREATE INDEX clicked_at_idx ON clicks USING BRIN (clicked_at);

-- Removing an index

DROP INDEX clicked_at_idx;

Adicionar um índice requer um bloqueio de escrita, o que pode ser indesejável num "sistema de registo" multi-inquilino. Para minimizar o tempo de inatividade da aplicação, crie o índice simultaneamente. Esse método requer mais trabalho total do que uma compilação de índice padrão e leva mais tempo para ser concluído. No entanto, como ele permite que as operações normais continuem enquanto o índice é construído, esse método é útil para adicionar novos índices em um ambiente de produção.

-- Adding an index without locking table writes

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

Tipos e funções

A criação de tipos SQL personalizados e funções definidas pelo utilizador propaga-se para os nós de processamento. No entanto, a criação desses objetos de banco de dados em uma transação com operações distribuídas envolve compensações.

O Azure Cosmos DB para PostgreSQL paraleliza operações como create_distributed_table() entre fragmentos usando várias conexões por trabalhador. Considerando que, ao criar um objeto de base de dados, o Azure Cosmos DB para PostgreSQL o propaga para nodos de trabalho usando uma única conexão por nodo. Combinar as duas operações em uma única transação pode causar problemas, porque as conexões paralelas não poderão ver o objeto que foi criado em uma única conexão, mas ainda não confirmado.

Considere um bloco de transação que cria um tipo, uma tabela, carrega dados e distribui a tabela:

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;

Antes do Citus 11.0, o Citus adiava a criação do tipo nos nós de trabalho e o confirmava separadamente ao criar a tabela distribuída. Isso permitiu que a cópia de dados em create_distributed_table() acontecesse em paralelo. No entanto, isso também significava que o tipo nem sempre estava presente nos nós de trabalho do Citus – ou se a transação fosse revertida, o tipo permaneceria nos nós de trabalho.

Com o Citus 11.0, o comportamento padrão muda para priorizar a consistência do esquema entre os nós coordenador e trabalhador. O novo comportamento tem uma desvantagem: se a propagação do objeto acontecer após um comando paralelo na mesma transação, a transação não poderá mais ser concluída, conforme destacado pelo ERRO no bloco de código abaixo:

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 você se deparar com esse problema, há duas soluções simples:

  1. Use set citus.create_object_propagation to automatic para adiar a criação do tipo neste caso, caso em que pode haver alguma inconsistência entre quais objetos de banco de dados existem em nós diferentes.
  2. Use o conjunto citus.multi_shard_modify_mode para sequential para desativar o paralelismo por nó. A carga de dados na mesma transação pode ser mais lenta.

Próximos passos

Consultas de diagnóstico úteis

  • Last updated on