Tutoriel : Concevoir une base de données mutualisée avec des clusters élastiques

Dans ce tutoriel, vous utilisez Azure Database pour PostgreSQL avec des clusters élastiques pour apprendre à concevoir une application multilocataire qui tire parti du scale-out horizontal.

Prérequis
Utiliser l’utilitaire psql pour créer un schéma
Partitionner des tables entre des nœuds
Ingérer des exemples de données
Interroger les données de locataire
Partager des données entre les locataires
Personnaliser le schéma par locataire

Prérequis

Créez un cluster élastique de l’une des manières suivantes :

Utiliser l’utilitaire psql pour créer un schéma

Après vous être connecté au cluster élastique à l’aide de psql, vous pouvez configurer votre cluster élastique. Ce tutoriel vous guide tout au long de la création d’une plateforme d’applications qui permet aux entreprises de suivre leurs campagnes publicitaires.

Remarque

Lors de la distribution des données sur votre cluster, toutes les contraintes de données uniques sont limitées à leur « partition » de distribution. Dans notre exemple multilocataire, l’unicité des données d’application est appliquée par locataire (par exemple, ID d’entreprise). Pour cette raison, nos définitions de table distribuée pour les contraintes de clé primaire et étrangère incluent toujours la colonne ID d’entreprise.

Créez une table pour stocker les informations de votre entreprise multi-locataires et une autre table pour les campagnes de celles-ci. Dans la console psql, exécutez les commandes suivantes :

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)
);

Chaque campagne paie pour afficher des publicités. Ajoutez votre table d’annonces dans psql avec le code suivant :

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)
);

Enfin, vous souhaitez suivre les statistiques en fonction des sélections publicitaires et des impressions :

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)
);

Vous pouvez maintenant voir les tables nouvellement créées à partir de psql en exécutant :

\dt

Partitionner des tables entre des nœuds

Jusqu’à ce stade, vous avez créé des tables Postgres standard, mais vous devez finalement créer des tables distribuées sur votre cluster élastique. Les tables distribuées au sein d’un cluster élastique stockent des données sur différents nœuds en fonction des valeurs définies par votre colonne de distribution. Cette colonne sert à déterminer l'emplacement des lignes sur les nœuds Worker sous-jacents.

Configurez votre colonne de distribution sur company_id, qui sert d'identifiant multitenant. Dans psql, exécutez les fonctions suivantes :

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');

Remarque

Pour tirer parti des clusters élastiques avec les fonctionnalités de performances d’Azure Database pour PostgreSQL, vous devez distribuer des tables. Sauf si vous distribuez vos tables et schémas, vos nœuds de cluster ne participent à aucune requête ou opération distribuée.

Ingérer des exemples de données

En dehors de psql, dans la ligne de commande normale, téléchargez des exemples de jeux de données :

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 nouveau dans psql, effectuez le chargement en masse des données. Veillez à exécuter psql dans le répertoire où vous avez téléchargé les fichiers de données.

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

Vos données dans vos tables distribuées sont désormais réparties entre les nœuds de travail de votre cluster élastique.

Interroger les données de locataire

Lorsque votre application demande des données pour une entreprise spécifique, la base de données peut désormais exécuter efficacement la requête sur le nœud Worker approprié. Par exemple, la requêtecompany_id = 5 suivante filtre les publicités et les impressions. Essayez de l’exécuter dans psql pour voir les résultats.

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;

Jusqu’à présent, vous avez distribué toutes vos tables sur votre cluster par company_id. Toutefois, certains types de données « appartiennent » naturellement à tous les locataires et peuvent être placés en même temps que toutes les distributions de locataires. Par exemple, toutes les entreprises de votre plateforme publicitaire peuvent souhaiter obtenir des informations géographiques pour leur public en fonction des détails de l’adresse IP.

Créez une table de référence pour contenir ces informations IP géographiques. Exécutez les commandes suivantes dans 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);

Ensuite, identifiez geo_ips comme une « table de référence ». Votre cluster gère cette table en stockant une table synchronisée sur chaque nœud Worker en cluster.

SELECT create_reference_table('geo_ips');

À présent, chargez votre table de référence avec vos exemples de données. N’oubliez pas d’exécuter cette commande à partir du répertoire où vous avez téléchargé le fichier de jeu de données.

\copy geo_ips from 'geo_ips.csv' with csv

Les instructions SQL qui joignent la table sélectionnée avec geo_ips sont désormais efficaces sur tous les nœuds. Notez que cette jointure recherche les emplacements de chaque adresse IP sélectionnée sur la publicité 290. Essayez d’exécuter la requête dans 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;

Personnaliser le schéma par locataire

Dans certains cas, vos locataires individuels peuvent avoir besoin de stocker des informations spéciales dont d’autres locataires n’ont pas besoin. Toutefois, tous les locataires partagent une définition commune avec un schéma de base de données identique. Où pouvez-vous placer les données supplémentaires ?

Une solution consiste à utiliser un type de colonne flexible comme JSONB de PostgreSQL. Notre schéma a un champ JSONB dans clicks appelé user_data. Une entreprise (par exemple, société 5) peut utiliser cette colonne pour suivre les informations sur le fait qu’un utilisateur se trouve sur un appareil mobile.

Voici une requête que l'entreprise 5 peut utiliser pour déterminer qui sélectionne le plus : les visiteurs mobiles ou les visiteurs traditionnels.

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;

PostgreSQL inclut une fonctionnalité puissante qui vous permet d’indexer une partie ou un sous-ensemble spécifique de vos données. Vous pouvez optimiser davantage votre requête pour l’entreprise 5 en créant un index partiel.

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

En outre, une autre façon d’améliorer les performances entre les requêtes qui incluent votre colonne JSONB consiste à créer un index GIN sur chaque clé et valeur sous-jacente dans votre colonne 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;

Étape suivante

En savoir plus sur les clusters élastiques

Rétroaction

Cette page vous a-t-elle été utile ?

Last updated on 2025-11-19