Distribuir e modificar tabelas no Azure Cosmos DB for PostgreSQL
APLICA-SE AO: 
Azure Cosmos DB for PostgreSQL (da plataforma da extensão de dados Citus para PostgreSQL)
Como distribuir tabelas
Para criar uma tabela distribuída, primeiro você precisa definir o esquema da tabela. Para fazer isso, você pode definir uma tabela usando a instrução CREATE TABLE da mesma maneira que faria com uma tabela comum do 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
);
Depois, você pode usar a função create_distributed_table() para especificar a coluna de distribuição de 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 for PostgreSQL que a tabela github_events deve ser distribuída na coluna repo_id (criando hash do valor da coluna).
Ela cria um número total de 32 fragmentos por padrão, em que cada fragmento é proprietário de 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 fragmento criadas no trabalho têm as mesmas definições de esquema de tabela, índice e restrição que a tabela no coordenador. Depois que as réplicas são criadas, a função salva todos os metadados distribuídos no coordenador.
Cada fragmento criado recebe uma ID de fragmento exclusiva e todas as réplicas dele têm a mesma ID de fragmento. Os fragmentos são representados no nó de trabalho como tabelas regulares do PostgreSQL nomeadas como 'tablename_shardid', em que tablename é o nome da tabela distribuída e a ID do fragmento é a ID exclusiva atribuída. Você pode se conectar às instâncias de 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 obter mais informações sobre o UDF usado nesta seção na referência DDL de fragmento e tabela.
Tabela de referência
O método acima distribui tabelas em vários fragmentos horizontais. Outra possibilidade é distribuir tabelas em um fragmento e replicar o fragmento para cada nó de trabalho. As tabelas distribuías dessa forma são chamadas de tabelas de referência. Elas são usadas para armazenar dados que precisam ser acessados frequentemente por vários nós em um cluster.
Os candidatos comuns para tabelas de referência incluem:
- Tabelas menores que precisam se unir com tabelas distribuídas maiores.
- Tabelas em aplicativos multilocatário que não têm 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 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 de transações em qualquer uma das lojas. As informações tributárias não são específicas de nenhum locatário. Faz sentido colocá-las em uma tabela compartilhada. Uma tabela de referência centrada nos EUA pode ser parecida com esta:
-- 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 o cálculo de imposto de um carrinho de compras podem ser unidas com a tabela states sem sobrecarga de rede e podem adicionar uma chave estrangeira ao código de estado para melhor validação.
Além de distribuir uma tabela como um fragmento replicado, o UDF de create_reference_table a marca como uma tabela de referência nas tabelas de metadados do Azure Cosmos DB for PostgreSQL. O Azure Cosmos DB for PostgreSQL executa automaticamente confirmações de duas fases (2PC) para modificações em tabelas marcadas dessa forma, o que fornece garantias de coerência forte.
Para obter outro exemplo de como usar tabelas de referência, confira o tutorial de banco de dados de multilocatários.
Como distribuir dados do coordenador
Se um banco de dados do PostgreSQL existente for convertido no nó coordenador de um cluster, os dados nas tabelas poderão ser distribuídos com eficiência para um aplicativo e com interrupção mínima.
A função create_distributed_table descrita anteriormente funciona em tabelas vazias e não vazias e, para as últimas, ela distribui automaticamente linhas de tabela em todo o cluster. Você saberá se ela está copiando os dados pela presença da mensagem "OBSERVAÇÃO: 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 quando a função for confirmada.
Ao distribuir as tabelas A e B, em que um tem uma chave estrangeira para B, distribua primeiro a tabela de destino da chave B. Fazer isso 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, remova 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 for PostgreSQL, primeiro crie as tabelas distribuídas do Azure Cosmos DB for PostgreSQL por meio de create_distributed_table e copie os dados para a tabela.
A cópia em tabelas distribuídas evita que o nó coordenador fique sem espaço.
Como colocar tabelas
A colocação significa manter informações relacionadas nos mesmos computadores. Ela permite consultas eficientes, ao mesmo tempo que aproveita a escalabilidade horizontal de todo o conjunto de dados. Para obter mais informações, confira colocação.
As tabelas são colocadas em grupos. Para controlar manualmente a atribuição de grupo de colocação de uma tabela, use o parâmetro colocate_with opcional de create_distributed_table. Se a colocação da tabela não faz diferença, omita esse parâmetro. Ele usa como padrão o valor 'default', que agrupa a tabela com qualquer outra tabela de colocação padrão que tenha o mesmo tipo de coluna de distribuição, contagem de fragmentos e fator de replicação. Se você quiser interromper ou atualizar essa colocação implícita, poderá 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 no grupo de colocações implícitas hipotéticas, especifique colocated_with => 'none'.
-- not co-located with other tables
SELECT create_distributed_table('A', 'foo', colocate_with => 'none');
Dividir tabelas não relacionadas nos próprios grupos de colocação aprimora o desempenho do rebalanceamento de fragmentos, pois os fragmentos no mesmo grupo precisam ser movidos juntos.
Quando as tabelas estão de fato relacionadas (por exemplo, quando elas forem unidas), faz sentido colocá-las explicitamente. Os ganhos da colocação apropriada são mais importantes do que qualquer sobrecarga de rebalanceamento.
Para colocar explicitamente várias tabelas, distribua uma e coloque as outras no 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 colocação são armazenadas na tabela pg_dist_colocation, enquanto pg_dist_partition revela quais tabelas são atribuídas a quais grupos.
Como remover tabelas
Você pode usar o comando DROP TABLE padrão do PostgreSQL para remover as suas tabelas distribuídas. Assim como acontece com tabelas regulares, o DROP TABLE remove todos os índices, regras, gatilhos e restrições existentes na tabela de destino. Além disso, ele também remove os fragmentos nos nós de trabalho e limpa os metadados.
DROP TABLE github_events;
Como modificar tabelas
O Azure Cosmos DB for PostgreSQL propaga automaticamente muitos tipos de instruções DDL. A modificação de uma tabela distribuída no nó coordenador também atualizará os fragmentos nos trabalhos. Outras instruções DDL exigem a propagação manual e algumas outras são proibidas, como as que modificariam uma coluna de distribuição. A tentativa de executar a DDL que não é elegível para a propagação automática gera um erro e deixa as tabelas no nó coordenador inalteradas.
Veja abaixo uma referência das categorias de instruções DDL que se propagam.
Como adicionar/modificar colunas
O Azure Cosmos DB for PostgreSQL propaga automaticamente a maioria dos comandos ALTER TABLE. Adicionar colunas ou alterar os valores padrão delas funciona da mesma forma que em um banco de dados PostgreSQL de computador único:
-- 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 o tipo de dados dela, também podem ser usadas. No entanto, o tipo de dados da coluna de distribuição não pode ser alterado. Essa coluna determina como os dados de tabela são distribuídos por meio do cluster e modificar o tipo de dados exigiria a movimentação dos 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
*/
Como adicionar/remover restrições
Usar o Azure Cosmos DB for PostgreSQL permite que você continue a desfrutar da segurança de um banco de dados relacional, incluindo restrições de banco de dados (confira a documentação do PostgreSQL). Devido à natureza dos sistemas distribuídos, o Azure Cosmos DB for 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 colocadas, inclua sempre a coluna de distribuição na chave. A inclusão da coluna de distribuição pode envolver a criação do composto de chaves.
As chaves estrangeiras podem ser criadas nestas situações:
- entre duas tabelas locais (não distribuídas),
- entre duas tabelas de referência,
- entre duas tabelas distribuídas colocadas quando a chave inclui a coluna de distribuição ou
- como uma tabela distribuída que faz referência a uma tabela de referência
Não há suporte para chaves estrangeiras de tabelas de referência para tabelas distribuídas.
Observação
As chaves primárias e as restrições de exclusividade precisam incluir a coluna de distribuição. Adicioná-las a uma coluna de não distribuição gera 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 nas 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 (de distribuição ou não) porque elas não exigem pesquisas entre os trabalhos.
ALTER TABLE ads ALTER COLUMN image_url SET NOT NULL;
Como usar restrições NOT VALID
Em algumas situações, pode ser útil impor restrições em novas linhas e, ao mesmo tempo, permitir que linhas sem conformidade existentes permaneçam inalteradas. O Azure Cosmos DB for 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');
No decorrer do tempo, imagine que alguns não endereços são inseridos 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 com falha nas linhas existentes. No entanto, ele permite uma restrição marcada como invá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 agora estão 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 consertar as linhas inválidas 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.
Como adicionar/remover índices
O Azure Cosmos DB for PostgreSQL dá suporte à 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;
A adição de um índice usa um bloqueio de gravação, que pode ser indesejável em um sistema de registro de multilocatário. Para minimizar o tempo de inatividade do aplicativo, crie o índice simultaneamente. Esse método requer mais trabalho total do que um build 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 é criado, 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 de SQL personalizados e funções definidas pelo usuário é propagada para os nós de trabalho. No entanto, a criação desses objetos do banco de dados em uma transação com operações distribuídas tem desvantagens.
O Azure Cosmos DB for PostgreSQL paraleliza operações como create_distributed_table() em todos os fragmentos que usam várias conexões por trabalho. Enquanto que, ao criar um objeto do banco de dados, o Azure Cosmos DB for PostgreSQL o propaga para o nós de trabalho que usam uma única conexão por trabalho. A combinação das duas operações em uma única transação pode causar problemas, pois as conexões paralelas não poderão ver o objeto criado em uma única conexão, mas que ainda não foi confirmado.
Considere um bloco de transação que cria um tipo e uma tabela, carrega os 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 permitia que a cópia dos dados em create_distributed_table() ocorresse paralelamente. 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 permanecia nos nós de trabalho.
Com o Citus 11.0, o comportamento padrão mudou para priorizar a consistência do esquema entre os nós de coordenador e de trabalho. O novo comportamento tem uma desvantagem: se a propagação de objeto ocorrer 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ê encontrar esse problema, haverá duas soluções alternativas simples:
- Use o conjunto de
citus.create_object_propagationaautomaticpara adiar a criação do tipo nessa situação. Nesse caso, pode haver um pouco de inconsistência entre quais objetos do banco de dados existem em nós diferentes. - Use o conjunto de
citus.multi_shard_modify_modeasequentialpara desabilitar o paralelismo por nó. A carga de dados na mesma transação pode ser mais lenta.