Distribuir e modificar tabelas no Azure Cosmos DB para PostgreSQL

APLICA-SE A: Azure Cosmos DB para PostgreSQL (com tecnologia da extensão da base de dados Citus para PostgreSQL)

Distribuir tabelas

Para criar uma tabela distribuída, primeiro tem de definir o esquema da tabela. Para tal, pode definir uma tabela com 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, pode utilizar a função create_distributed_table() para especificar a coluna de distribuição da tabela e criar as partições horizontais de trabalho.

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

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

Cria um número total de 32 extensões por predefinição, em que cada extensão possui uma parte de um espaço hash e é replicada com base no valor de configuração citus.shard_replication_factor predefinido. As réplicas de partições horizontais criadas na função de trabalho têm o mesmo esquema de tabela, índice e definições de restrição que a tabela no coordenador. Assim que as réplicas forem criadas, a função guarda todos os metadados distribuídos no coordenador.

A cada partição horizontal criada é atribuído um ID de partição horizontal exclusivo e todas as respetivas réplicas têm o mesmo ID de partição horizontal. As partições horizontais são representadas no nó de trabalho como tabelas PostgreSQL normais denominadas "tablename_shardid" em que tablename é o nome da tabela distribuída e o ID de partição horizontal é o ID exclusivo atribuído. Pode ligar-se às instâncias postgres de trabalho para ver ou executar comandos em partições horizontais individuais.

Agora, está pronto para inserir dados na tabela distribuída e executar consultas na mesma. Também pode saber mais sobre a UDF utilizada nesta secção na tabela e referência de DDL de partição horizontal.

Tabelas de Referência

O método acima distribui as tabelas por várias partições horizontais. Outra possibilidade é distribuir tabelas numa única partição horizontal e replicar a partição horizontal para cada nó de trabalho. As tabelas distribuídas desta forma são denominadas tabelas de referência. São utilizados para armazenar dados que precisam de ser acedidos com frequência por vários nós num cluster.

Os candidatos comuns para tabelas de referência incluem:

  • Tabelas mais pequenas que precisam de ser associadas com tabelas distribuídas maiores.
  • Tabelas em aplicações multi-inquilino que não têm uma coluna de ID de inquilino ou que não estão associadas a um inquilino. (Ou, durante a migração, mesmo para algumas tabelas associadas a um inquilino.)
  • As tabelas que precisam de restrições exclusivas em múltiplas colunas e são suficientemente pequenas.

Por exemplo, suponha que um site de comércio eletrónico multi-inquilino precisa de calcular o imposto sobre vendas para transações em qualquer uma das suas lojas. As informações fiscais não são específicas de nenhum inquilino. Faz sentido colocá-la numa tabela partilhada. Uma tabela de referência centrada nos EUA pode ter o seguinte aspeto:

-- 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 um cálculo de impostos para um carrinho de compras podem ser associadas à states tabela sem sobrecarga de rede e podem adicionar uma chave externa ao código de estado para uma melhor validação.

Além de distribuir uma tabela como uma única partição horizontal replicada, o UDF marca-a create_reference_table como uma tabela de referência nas tabelas de metadados do Azure Cosmos DB para PostgreSQL. O Azure Cosmos DB para PostgreSQL executa automaticamente consolidações em duas fases (2PC) para modificações a tabelas marcadas desta forma, o que fornece garantias de consistência fortes.

Para obter outro exemplo de utilização de tabelas de referência, veja o tutorial da base de dados multi-inquilino.

Distribuir Dados de Coordenação

Se uma base de dados PostgreSQL existente for convertida no nó de coordenação de um cluster, os dados nas respetivas tabelas podem ser distribuídos de forma eficiente e com interrupções mínimas numa aplicação.

A create_distributed_table função descrita anteriormente funciona em tabelas vazias e não vazias e, para esta última, distribui automaticamente linhas de tabela em todo o cluster. Saberá se copia dados pela presença da mensagem "AVISO: Copiar 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 escritas na tabela são bloqueadas enquanto os dados são migrados e as escritas pendentes são processadas como consultas distribuídas assim que a função for consolidada. (Se a função falhar, as consultas tornam-se locais novamente.) As leituras podem continuar normalmente e tornar-se-ão consultas distribuídas assim que a função se consolidar.

Ao distribuir as tabelas A e B, em que A tem uma chave externa para B, distribua primeiro a tabela de destino de chave B. Fazê-lo pela 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 pela ordem correta, remova as chaves externas, distribua as tabelas e recrie as chaves externas.

Ao migrar dados de uma base de dados externa, como do Amazon RDS para o Azure Cosmos DB para PostgreSQL, crie primeiro as tabelas distribuídas do Azure Cosmos DB para PostgreSQL através create_distributed_tablede e, em seguida, copie os dados para a tabela. Copiar para tabelas distribuídas evita ficar sem espaço no nó de coordenação.

Colocalizar tabelas

Colocalização significa colocar informações relacionadas nos mesmos computadores. Permite consultas eficientes, ao mesmo tempo que tira partido da escalabilidade horizontal de todo o conjunto de dados. Para obter mais informações, veja Colocalização.

As tabelas estão colocalizados em grupos. Para controlar manualmente a atribuição do grupo de colocação de uma tabela, utilize o parâmetro opcional colocate_with de create_distributed_table. Se não se importar com a colocalização de uma tabela, omita este parâmetro. A predefinição é o valor 'default', que agrupa a tabela com qualquer outra tabela de colocalização predefinida com o mesmo tipo de coluna de distribuição, contagem de partições horizontais e fator de replicação. Se quiser interromper ou atualizar esta colocalização implícita, pode utilizar 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 com outras pessoas no seu grupo de colocalização implícita, especifique colocated_with => 'none'.

-- not co-located with other tables

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

A divisão de tabelas não relacionadas nos seus próprios grupos de colocalização melhorará o desempenho do reequilíbrio de partições horizontais , uma vez que as partições horizontais no mesmo grupo têm de ser movidas em conjunto.

Quando as tabelas estão realmente relacionadas (por exemplo, quando serão associadas), pode fazer sentido colocá-las explicitamente em colocalização. Os ganhos de colocalização adequada são mais importantes do que qualquer sobrecarga de reequilíbrio.

Para colocar explicitamente múltiplas tabelas, distribua uma e, em seguida, coloque as outras no grupo de colocalizaçã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 colocalização são armazenadas na tabela pg_dist_colocation , enquanto pg_dist_partition revela que tabelas são atribuídas a que grupos.

A largar tabelas

Pode utilizar o comando padrão PostgreSQL DROP TABLE para remover as tabelas distribuídas. Tal como acontece com as tabelas normais, DROP TABLE remove todos os índices, regras, acionadores e restrições existentes para a tabela de destino. Além disso, também remove as partições horizontais nos nós de trabalho e limpa os metadados.

DROP TABLE github_events;

Modificar tabelas

O Azure Cosmos DB para PostgreSQL propaga automaticamente muitos tipos de instruções DDL. Modificar uma tabela distribuída no nó de coordenação também atualizará as partições horizontais nos trabalhos. Outras instruções DDL requerem propagação manual e algumas outras são proibidas, como qualquer uma que modifique uma coluna de distribuição. Tentar executar o DDL que não é elegível para propagação automática gerará um erro e deixará as tabelas no nó de coordenação inalteradas.

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

Adicionar/Modificar Colunas

O Azure Cosmos DB para PostgreSQL propaga automaticamente a maioria dos comandos ALTER TABLE . Adicionar colunas ou alterar os valores predefinidos funciona como numa base 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;

As alterações significativas a uma coluna existente, como mudar o nome ou alterar o tipo de dados, também estão bem. No entanto, o tipo de dados da coluna de distribuição não pode ser alterado. Esta coluna determina a forma como os dados da tabela são distribuídos através do cluster e a modificação do respetivo tipo de dados exigiria mover os dados.

Tentar fazê-lo 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
*/

Adicionar/Remover Restrições

A utilização do Azure Cosmos DB para PostgreSQL permite-lhe continuar a desfrutar da segurança de uma base de dados relacional, incluindo restrições de bases de dados (veja 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 externa entre tabelas distribuídas colocalizadas, inclua sempre a coluna de distribuição na chave. Incluir a coluna de distribuição pode envolver a criação do composto da chave.

As chaves externas 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 colocalizadas quando a chave inclui a coluna de distribuição ou
  • como uma tabela distribuída que referencia uma tabela de referência

As chaves externas 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 têm de incluir a coluna de distribuição. Adicioná-los a uma coluna de não distribuição irá gerar um erro

Este exemplo mostra como criar chaves primárias e externas 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 necessitam de pesquisas entre trabalhadores.

ALTER TABLE ads ALTER COLUMN image_url SET NOT NULL;

Utilizar Restrições NÃO VÁLIDAS

Em algumas situações, pode ser útil impor restrições para novas linhas, ao mesmo tempo que permite que as linhas não conformes existentes permaneçam inalteradas. O Azure Cosmos DB para PostgreSQL suporta esta funcionalidade para restrições CHECK e chaves externas, utilizando a designação de restrição "NÃO VÁLIDO" 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');

No decorrer do tempo, imagine que alguns não-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 falhe nas linhas existentes. No entanto , 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;

As 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, durante as horas de não pico, um administrador de base de dados pode tentar corrigir as linhas incorretas e revalorizar 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 NÃO VÁLIDO E VALIDAR RESTRIÇÃO na secção ALTERAR TABELA .

Adicionar/Remover Í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 em simultâneo. Este método requer mais trabalho total do que uma compilação de índice padrão e demora mais tempo a ser concluído. No entanto, uma vez que permite que as operações normais continuem enquanto o índice é criado, este método é útil para adicionar novos índices num 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 utilizador propocia-se a nós de trabalho. No entanto, a criação desses objetos de base de dados numa transação com operações distribuídas envolve compromissos.

O Azure Cosmos DB para PostgreSQL paraleliza operações como create_distributed_table() entre partições horizontais através de várias ligações por função de trabalho. Enquanto que, ao criar um objeto de base de dados, o Azure Cosmos DB para PostgreSQL propaga-o para nós de trabalho através de uma única ligação por trabalho. Combinar as duas operações numa única transação pode causar problemas, porque as ligações paralelas não conseguirão ver o objeto que foi criado através de uma única ligação, mas ainda não foi consolidado.

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 diferia a criação do tipo nos nós de trabalho e consolidava-o separadamente ao criar a tabela distribuída. Isto permitiu que a cópia de dados em create_distributed_table() ocorresse em paralelo. No entanto, também significava que o tipo nem sempre estava presente nos nós de trabalho citus – ou se a transação fosse revertida, o tipo permaneceria nos nós de trabalho.

Com o Citus 11.0, o comportamento predefinido muda 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 objetos ocorrer após um comando paralelo na mesma transação, a transação já não pode ser concluída, conforme realçado 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 encontrar este problema, existem duas soluções simples:

  1. Utilize o conjunto citus.create_object_propagation para adiar a automatic criação do tipo nesta situação, caso em que pode existir alguma inconsistência entre os objetos de base de dados que existem em nós diferentes.
  2. Utilize o conjunto citus.multi_shard_modify_mode para sequential desativar o paralelismo por nó. O carregamento de dados na mesma transação pode ser mais lento.

Passos seguintes