Zelfstudie: Een multitenant-database ontwerpen met elastische clusters (preview)

In deze zelfstudie gebruikt u Azure Database for PostgreSQL met elastische clusters om te leren hoe u een multitenant-toepassing ontwerpt om te profiteren van horizontaal uitschalen.

Vereisten

Maak een elastisch cluster op een van de volgende manieren:

• Een elastisch cluster maken met EEN ARM-sjabloon
• Een elastisch cluster maken met CLI
• Een elastisch cluster maken met behulp van de portal

Het hulpprogramma psql gebruiken om een schema te maken

Zodra u verbinding hebt gemaakt met het elastische cluster met behulp van psql, kunt u enkele basistaken uitvoeren. In deze zelfstudie krijgt u instructies voor het maken van een web-app waarmee adverteerders hun campagnes kunnen volgen.

Meerdere bedrijven kunnen gebruik maken van de app, dus we maken een tabel waarin we bedrijven opslaan en een tweede tabel voor hun campagnes. Voer op de psql-console deze opdrachten uit:

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

Elke campagne betaalt om advertenties uit te voeren. Voeg ook een tabel voor advertenties toe door de volgende code uit te voeren in psql nadat u bedrijven en campagnes hebt gemaakt:

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

Ten slotte houden we statistieken bij over klikken en indrukken voor elke advertentie:

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

U kunt de zojuist gemaakte tabellen nu in de lijst met tabellen in psql zien door het volgende uit te voeren:

\dt

Multitenant-toepassingen kunnen alleen uniekheid per tenant afdwingen. Daarom bevatten alle primaire en refererende sleutels de bedrijfs-id.

Sharding toepassen op tabellen tussen knooppunten

In een elastische clusterimplementatie worden tabelrijen op verschillende knooppunten opgeslagen op basis van de waarde van een door de gebruiker aangewezen kolom. Deze 'distributiekolom' geeft aan welke tenant de eigenaar is van welke rijen.

Laten we de distributiekolom instellen op company_id, de tenant-id. Voer in psql deze functies uit:

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

Voorbeeldgegevens opnemen

Download nu buiten psql, in de normale opdrachtregel, voorbeeldgegevenssets:

for dataset in companies campaigns ads clicks impressions geo_ips; do
  curl -O https://examples.citusdata.com/mt_ref_arch/${dataset}.csv
done

Ga terug naar psql en laad de gegevens bulksgewijs. Vergeet niet dat u psql moet uitvoeren in dezelfde map als waarin u de gegevensbestanden hebt gedownload.

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

Deze gegevens worden verspreid over werkknooppunten.

Query's uitvoeren op tenantgegevens

Wanneer de toepassing om gegevens voor één tenant vraagt, kunt u via de database de query op één werkknooppunt uitvoeren. Query's voor één tenant worden op één tenant-id gefilterd. Met de volgende query wordt bijvoorbeeld company_id = 5 gefilterd voor advertenties en indrukken. Probeer de query in psql uit te voeren om de resultaten te zien.

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;

Gegevens tussen tenants delen

Tot nu toe zijn alle tabellen gedistribueerd door company_id. Sommige gegevens behoren echter niet van nature tot een tenant in het bijzonder en kunnen worden gedeeld. Alle bedrijven op het voorbeeldadvertentieplatform zoeken bijvoorbeeld geografische informatie voor hun doelgroep op basis van IP-adressen.

Maak een tabel om daarin gedeelde geografische informatie op te slaan. Voer de volgende opdrachten uit in 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);

Maak nu geo_ips een 'referentietabel' om een kopie van de tabel op te slaan op elk werkknooppunt.

SELECT create_reference_table('geo_ips');

Laad hierin voorbeeldgegevens. Vergeet niet deze opdracht in psql uit te voeren vanuit de map waarin u de gegevensset hebt gedownload.

\copy geo_ips from 'geo_ips.csv' with csv

Het samenvoegen van de clicks-tabel met geo_ips is efficiënt op alle knooppunten. Hier volgt een join om de locaties te vinden van iedereen die op advertentie 290 heeft geklikt. Voer de query uit in 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;

Het schema per tenant aanpassen

Mogelijk moet elke tenant speciale informatie opslaan die niet door anderen nodig is. Alle tenants delen echter een algemene infrastructuur met een identiek databaseschema. Waar kunt u de extra gegevens opslaan?

U kunt bijvoorbeeld een kolomtype met een open einde gebruiken, zoals JSONB van PostgreSQL. Ons schema bevat een JSONB-veld in clicks met de naam user_data. Een bedrijf (laten we het bedrijf Vijf noemen), kan de kolom gebruiken om bij te houden of de gebruiker een mobiel apparaat gebruikt.

Hier ziet u een query om te ontdekken wie er vaker klikt: mobiele of traditionele bezoekers.

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;

We kunnen deze query voor één bedrijf optimaliseren door het maken van een gedeeltelijke index.

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

Algemener gezegd: we kunnen GIN-indices maken op elke sleutel en waarde binnen de kolom.

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;

Volgende stap

In deze zelfstudie hebt u geleerd hoe u een elastisch cluster maakt. U hebt psql gebruikt om hiermee verbinding te maken, u hebt een schema gemaakt en u hebt gegevens gedistribueerd. U hebt geleerd om een query uit te voeren op gegevens zowel in als tussen tenants en om het schema per tenant aan te passen.