Tutorial: Criar um banco de dados multilocatário com clusters elásticos

Neste tutorial, você usará o Banco de Dados do Azure para PostgreSQL com clusters elásticos para aprender a projetar um aplicativo multilocatário que se beneficia da expansão horizontal.

• Pré-requisitos
• Usar o utilitário psql para criar um esquema
• Fragmentar tabelas entre nós
• Ingerir dados de exemplo
• Consultar dados do locatário
• Consultar dados entre locatários
• Personalizar o esquema por locatário

Pré-requisitos

Crie um cluster elástico de uma das seguintes maneiras:

• Criar um cluster elástico usando o Portal
• Criar um cluster elástico usando Bicep
• Criar um cluster elástico com o modelo do ARM

Usar o utilitário psql para criar um esquema

Depois de se conectar ao cluster elástico usando psql, você pode configurar seu cluster elástico. Este tutorial orienta você na criação de uma plataforma de aplicativos que permite que as empresas acompanhem suas campanhas publicitárias.

Observação

Ao distribuir dados em seu cluster, todas as restrições de dados exclusivas têm como escopo o "fragmento" de distribuição. Em nosso exemplo multilocatário, a exclusividade dos dados do aplicativo é imposta por locatário (por exemplo, ID da empresa). Por esse motivo, nossas definições de tabela distribuída para restrições de chave primária e estrangeira sempre incluem a coluna ID da empresa.

Crie uma tabela para armazenar suas informações multilocatários da empresa e outra tabela para suas campanhas. No console do psql, execute estes comandos:

CREATE TABLE companies (
  id bigserial PRIMARY KEY,
  name text NOT NULL,
  image_url text,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL
);

CREATE TABLE campaigns (
  id bigserial,
  company_id bigint REFERENCES companies (id),
  name text NOT NULL,
  cost_model text NOT NULL,
  state text NOT NULL,
  monthly_budget bigint,
  blocked_site_urls text[],
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,

  PRIMARY KEY (company_id, id)
);

Cada campanha paga para veicular anúncios. Adicione sua tabela de anúncios ao psql com o seguinte código:

CREATE TABLE ads (
  id bigserial,
  company_id bigint,
  campaign_id bigint,
  name text NOT NULL,
  image_url text,
  target_url text,
  impressions_count bigint DEFAULT 0,
  clicks_count bigint DEFAULT 0,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,

  PRIMARY KEY (company_id, id),
  FOREIGN KEY (company_id, campaign_id)
    REFERENCES campaigns (company_id, id)
);

Por fim, você deseja acompanhar estatísticas com base em seleções de anúncios e impressões:

CREATE TABLE clicks (
  id bigserial,
  company_id bigint,
  ad_id bigint,
  clicked_at timestamp without time zone NOT NULL,
  site_url text NOT NULL,
  cost_per_click_usd numeric(20,10),
  user_ip inet NOT NULL,
  user_data jsonb NOT NULL,

  PRIMARY KEY (company_id, id),
  FOREIGN KEY (company_id, ad_id)
    REFERENCES ads (company_id, id)
);

CREATE TABLE impressions (
  id bigserial,
  company_id bigint,
  ad_id bigint,
  seen_at timestamp without time zone NOT NULL,
  site_url text NOT NULL,
  cost_per_impression_usd numeric(20,10),
  user_ip inet NOT NULL,
  user_data jsonb NOT NULL,

  PRIMARY KEY (company_id, id),
  FOREIGN KEY (company_id, ad_id)
    REFERENCES ads (company_id, id)
);

Agora você pode ver as tabelas recém-criadas do psql executando:

\dt

Fragmentar tabelas entre nós

Até este ponto, você criou tabelas postgres padrão, mas, em última análise, precisa criar tabelas distribuídas em seu cluster elástico. Tabelas distribuídas em um cluster elástico armazenam dados em nós diferentes com base nos valores definidos pela coluna de distribuição. Esta coluna é usada para determinar o posicionamento de linha entre os nós de trabalho subjacentes.

Configure sua coluna de distribuição para ser company_id, que atua como seu identificador multilocatário. No psql, execute estas funções:

SELECT create_distributed_table('companies',   'id');
SELECT create_distributed_table('campaigns',   'company_id');
SELECT create_distributed_table('ads',         'company_id');
SELECT create_distributed_table('clicks',      'company_id');
SELECT create_distributed_table('impressions', 'company_id');

Observação

Para aproveitar os clusters elásticos com os recursos de desempenho do Banco de Dados do Azure para PostgreSQL, você precisa distribuir tabelas. A menos que você distribua suas tabelas e esquemas, os nós de cluster não participam de nenhuma operação ou consulta distribuída.

Ingerir dados de exemplo

Fora do psql, na linha de comando normal, baixe os conjuntos de dados de exemplo:

for dataset in companies campaigns ads clicks impressions geo_ips; do
  curl -O https://raw.githubusercontent.com/Azure-Samples/azure-postgresql-elastic-clusters/main/multi-tenant/${dataset}.csv
done

Novamente dentro do psql, carregue em massa os dados. Certifique-se de executar o psql no mesmo diretório em que você baixou os arquivos de dados.

SET client_encoding TO 'UTF8';

\copy companies from 'companies.csv' with csv
\copy campaigns from 'campaigns.csv' with csv
\copy ads from 'ads.csv' with csv
\copy clicks from 'clicks.csv' with csv
\copy impressions from 'impressions.csv' with csv

Seus dados em suas tabelas distribuídas agora estão distribuídos entre os nós de trabalho do cluster elástico.

Consultar dados do locatário

Quando seu aplicativo solicita dados para uma empresa específica, o banco de dados agora pode executar a consulta com eficiência no nó de trabalho apropriado. Por exemplo, a consulta a seguir (company_id = 5) filtra anúncios e impressões. Tente executá-la no psql para ver os resultados.

SELECT a.campaign_id,
       RANK() OVER (
         PARTITION BY a.campaign_id
         ORDER BY a.campaign_id, count(*) DESC
       ), count(*) AS n_impressions, a.id
  FROM ads AS a
  JOIN impressions AS i
    ON i.company_id = a.company_id
   AND i.ad_id      = a.id
 WHERE a.company_id = 5
GROUP BY a.campaign_id, a.id
ORDER BY a.campaign_id, n_impressions DESC;

Consultar dados entre locatários

Até agora, você distribuiu todas as tabelas em seu cluster por company_id. No entanto, alguns tipos de dados naturalmente "pertencem" a todos os locatários e podem ser colocados junto com todas as distribuições de locatário. Por exemplo, todas as empresas em sua plataforma de anúncios podem querer obter informações geográficas para seu público-alvo com base nos detalhes do endereço IP.

Crie uma tabela de referência para conter essas informações de IP geográfico. Executar os seguintes comandos na psql:

CREATE TABLE geo_ips (
  addrs cidr NOT NULL PRIMARY KEY,
  latlon point NOT NULL
    CHECK (-90  <= latlon[0] AND latlon[0] <= 90 AND
           -180 <= latlon[1] AND latlon[1] <= 180)
);
CREATE INDEX ON geo_ips USING gist (addrs inet_ops);

Em seguida, identifique geo_ips como uma "tabela de referência". Seu cluster gerencia essa tabela armazenando uma tabela sincronizada em cada nó de trabalho clusterizado.

SELECT create_reference_table('geo_ips');

Agora, carregue sua tabela de referência com seus dados de exemplo. Lembre-se de executar esse comando no diretório em que você baixou o arquivo do conjunto de dados.

\copy geo_ips from 'geo_ips.csv' with csv

As instruções SQL que juntam a tabela selecionada com geo_ips agora são eficientes em todos os nós. Observe esta junção para identificar as localizações de cada IP selecionado no anúncio 290. Tente executar a consulta no psql:

SELECT c.id, clicked_at, latlon
  FROM geo_ips, clicks c
 WHERE addrs >> c.user_ip
   AND c.company_id = 5
   AND c.ad_id = 290;

Personalizar o esquema por locatário

Em alguns casos, seus locatários individuais podem precisar armazenar informações especiais de que outros locatários não precisam. No entanto, todos os locatários compartilham uma definição comum com um esquema de banco de dados idêntico. Onde você pode colocar os dados extras?

Uma solução é usar um tipo de coluna flexível, como o JSONB do PostgreSQL. Nosso esquema tem um campo JSONB clicks chamado user_data. Uma empresa (digamos, empresa 5) pode usar essa coluna para acompanhar informações sobre se um usuário está em um dispositivo móvel.

Aqui está uma consulta que a empresa 5 pode usar para encontrar quem seleciona mais: visitantes móveis ou tradicionais.

SELECT
  user_data->>'is_mobile' AS is_mobile,
  count(*) AS count
FROM clicks
WHERE company_id = 5
GROUP BY user_data->>'is_mobile'
ORDER BY count DESC;

O PostgreSQL inclui um recurso poderoso que permite indexar uma parte ou subconjunto específico de seus dados. Você pode otimizar ainda mais sua consulta para a empresa 5 criando um índice parcial.

CREATE INDEX click_user_data_is_mobile
ON clicks ((user_data->>'is_mobile'))
WHERE company_id = 5;

Além disso, outra maneira de melhorar o desempenho em consultas que incluem sua coluna JSONB é criar um índice GIN em cada chave e valor subjacentes em sua coluna JSONB.

CREATE INDEX click_user_data
ON clicks USING gin (user_data);

-- this speeds up queries like, "which clicks have the is_mobile key present in user_data?"

SELECT id
  FROM clicks
 WHERE user_data ? 'is_mobile'
   AND company_id = 5;

Próxima etapa

Saiba mais sobre clusters elásticos