Partilhar via

Distribuir e modificar tabelas no Azure Cosmos DB para PostgreSQL

  • Artigo

APLICA-SE A: Azure Cosmos DB para PostgreSQL (alimentado pela extensão de banco de dados Citus para PostgreSQL)

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, onde cada fragmento possui uma parte de um espaço de hash e é replicado com base no valor de configuração de citus.shard_replication_fator padrão. As réplicas de estilhaços criadas no trabalhador têm as mesmas definições de esquema, índice e restrição de tabela 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 estilhaço. 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 o UDF usado nesta seção na tabela e referência DDL de estilhaço.

Tabelas de Referência

O método acima 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 entrar na mesa sem sobrecarga de states 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.

Distribuindo 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ê saberá 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 causará 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, solte 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 via e, em create_distributed_tableseguida, 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 colocalizadas em grupos. Para controlar manualmente a atribuição de grupo de colocation de uma tabela, use 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 com o mesmo tipo de coluna de distribuição, contagem de estilhaços e fator de replicação. Se você quiser quebrar ou atualizar esse colocation implícito, você pode usar update_distributed_table_colocation()o .

-- 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 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 melhorará 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 serão juntas), pode fazer sentido co-colocá-las explicitamente. Os ganhos de uma partilha adequada são mais importantes do que qualquer despesa geral de reequilíbrio.

Para colocalizar explicitamente várias tabelas, distribua uma e, em seguida, coloque as outras em seu grupo de colocation. 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.

Mesas suspensas

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, ele também solta os fragmentos nos nós de trabalho e limpa seus metadados.

DROP TABLE github_events;

Modificando tabelas

O Azure Cosmos DB para PostgreSQL propaga automaticamente muitos tipos de instruções DDL. Modificar uma tabela distribuída no nó coordenador também atualizará fragmentos nos trabalhadores. Outras instruções DDL requerem propagação manual, e algumas outras são proibidas, como qualquer outra que modifique uma coluna de distribuição. A tentativa de executar DDL não qualificada para propagação automática gerará um erro e deixará 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 fará referência cruzada a restrições de exclusividade ou integridade referencial entre 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 de chave.

Chaves estrangeiras podem ser criadas nestas situações:

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

Não há suporte para chaves estrangeiras de tabelas de referência para tabelas distribuídas.

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;

Usando 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 um aplicativo que armazena perfis de usuário em uma 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 não-endereços entram na mesa.

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 gravação, o que pode ser indesejável em um "sistema de registro" multilocatário. Para minimizar o tempo de inatividade do aplicativo, 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 usuário propõe nós de trabalho. 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 banco de dados, o Azure Cosmos DB para PostgreSQL o propaga para nós de trabalho usando uma única conexão por trabalhador. Combinar as duas operações em uma única transação pode causar problemas, porque as conexões paralelas não serão capazes de 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 acontecesse create_distributed_table() 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 nessa situação, caso em que pode haver alguma inconsistência entre quais objetos de banco de dados existem em nós diferentes.
  2. Use set citus.multi_shard_modify_mode to 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