Tutorial: Desenhe uma base de dados multitenant com clusters elásticos

Neste tutorial, utiliza o Azure Database para PostgreSQL com clusters elásticos para aprender a desenhar uma aplicação multitenant que beneficie da escalabilidade horizontal.

• Pré-requisitos
• Use o utilitário psql para criar um esquema
• Tabelas de estilhaços entre nós
• Ingerir dados de exemplo
• Consultar dados do locatário
• Partilhar dados entre inquilinos
• 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
• Crie um cluster elástico usando Bicep
• Criar um cluster elástico com modelo ARM

Use o utilitário psql para criar um esquema

Depois de se ligar ao cluster elástico usando psql, pode configurar o cluster elástico. Este tutorial guia-o na criação de uma plataforma de aplicação que permita às empresas acompanhar as suas campanhas publicitárias.

Nota

Ao distribuir dados pelo seu cluster, quaisquer restrições de dados únicas estão limitadas ao seu "fragmento" de distribuição. No nosso exemplo multiinquilino, a unicidade dos dados da aplicação é aplicada por inquilino (por exemplo, ID da empresa). Por esta razão, as nossas definições de tabelas distribuídas para restrições de chave primária e estrangeira incluem sempre a coluna ID da empresa.

Crie uma tabela para guardar a informação da sua empresa multiinquilina e outra para as suas campanhas. No console 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 a sua tabela de anúncios em 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, quer acompanhar estatísticas baseadas 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 pode ver as tabelas recém-criadas a partir do psql executando:

\dt

Tabelas de estilhaços entre nós

Até este ponto, criaste tabelas Postgres padrão, mas no fim de contas tens de criar tabelas distribuídas pelo teu cluster elástico. Tabelas distribuídas dentro de um cluster elástico armazenam dados em diferentes nós com base nos valores definidos pela tua coluna de distribuição. Esta coluna é usada para determinar a distribuição das linhas pelos nós de processamento subjacentes.

Configure a sua coluna de distribuição para ser company_id, que atua como o seu identificador multitenant. 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');

Nota

Para tirar partido dos clusters elásticos com as funcionalidades de desempenho do Azure Database para PostgreSQL, é necessário distribuir tabelas. A menos que distribua as suas tabelas e esquemas, os seus nós de cluster não participam em quaisquer consultas ou operações distribuídas.

Ingerir dados de exemplo

Fora do psql, na linha de comandos normal, descarregue 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

De volta ao psql, carregue os dados em massa. Certifique-se de executar psql no mesmo diretório onde 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

Os seus dados dentro das tabelas distribuídas estão agora espalhados pelos nós de trabalho elásticos do cluster.

Consultar dados do locatário

Quando a sua aplicação solicita dados para uma empresa específica, a base de dados pode agora executar eficientemente a consulta no nó de trabalho apropriado. Por exemplo, a consulta seguinte (company_id = 5) filtra anúncios e impressões. Tente executá-lo em 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;

Partilhar dados entre inquilinos

Até agora, distribuía todas as suas tabelas pelo cluster por company_id. No entanto, alguns tipos de dados "pertencem" naturalmente a todos os inquilinos e podem ser colocados ao lado de todas as distribuições de inquilinos. Por exemplo, todas as empresas na sua plataforma publicitária podem querer obter informações geográficas para o seu público com base nos detalhes do endereço IP.

Crie uma tabela de referência para armazenar esta informação geográfica de IP. Execute os seguintes comandos no 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);

De seguida, identifique geo_ips como uma "tabela de referência". O seu cluster gere esta tabela armazenando uma tabela sincronizada em cada nó worker clusterizado.

SELECT create_reference_table('geo_ips');

Agora, carregue a sua tabela de referência com os dados de amostra. Lembra-te de executar este comando a partir do diretório onde descarregaste o ficheiro 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 são agora eficientes em todos os nós. Repare nesta junção para encontrar as localizações de cada IP selecionado no anúncio 290. Tenta executar a consulta em 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;

Personalize o esquema por inquilino

Em alguns casos, os seus inquilinos individuais podem precisar de armazenar informações especiais que outros inquilinos não precisam. No entanto, todos os inquilinos partilham uma definição comum com um esquema de base de dados idêntico. Onde podes colocar os dados extra?

Uma solução é usar um tipo de coluna flexível como o JSONB do PostgreSQL. Nosso esquema tem um campo JSONB chamado clicksuser_data. Uma empresa (digamos a empresa 5) pode usar esta coluna para rastrear informações sobre se um utilizador está num dispositivo móvel.

Aqui está uma consulta que a empresa 5 pode usar para descobrir quem escolhe 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 uma funcionalidade poderosa que permite indexar uma porção ou subconjunto específico dos seus dados. Pode otimizar ainda mais a 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 forma de melhorar o desempenho em consultas que incluam a sua coluna JSONB é criar um índice GIN para cada chave e valor subjacente dentro da 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óximo passo

Saiba mais sobre clusters elásticos