Zelfstudie: Een multitenant-database ontwerpen met elastische clusters

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

• Vereisten
• Het hulpprogramma psql gebruiken om een schema te maken
• Sharding toepassen op tabellen tussen knooppunten
• Voorbeeldgegevens opnemen
• Query's uitvoeren op tenantgegevens
• Gegevens tussen tenants delen
• Het schema per tenant aanpassen

Vereisten

Maak een elastisch cluster op een van de volgende manieren:

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

Het hulpprogramma psql gebruiken om een schema te maken

Nadat u verbinding hebt gemaakt met het elastische cluster met behulp van psql, kunt u uw elastische cluster configureren. In deze zelfstudie leert u hoe u een toepassingsplatform maakt waarmee bedrijven hun advertentiecampagnes kunnen bijhouden.

Notitie

Wanneer u gegevens over uw cluster distribueert, worden eventuele unieke gegevensbeperkingen beperkt tot hun distributie -shard. In ons voorbeeld met meerdere tenants wordt de uniekheid van de toepassingsgegevens afgedwongen per tenant (bijvoorbeeld bedrijfs-ID). Daarom bevatten onze gedistribueerde tabeldefinities voor primaire en vreemde sleutelbeperkingen altijd de kolom bedrijfs-id.

Maak een tabel voor uw multitenant bedrijfsgegevens en een andere 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 uw advertentietabel toe in psql met de volgende code:

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 wilt u statistieken bijhouden op basis van advertentieselecties en indrukken:

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 nu de zojuist gemaakte tabellen van psql bekijken door het volgende uit te voeren:

\dt

Sharding toepassen op tabellen tussen knooppunten

Tot nu toe hebt u standaard Postgres-tabellen gemaakt, maar uiteindelijk moet u gedistribueerde tabellen maken in uw elastische cluster. Gedistribueerde tabellen in een elastisch cluster slaan gegevens op verschillende knooppunten op op basis van de waarden die zijn gedefinieerd door uw distributiekolom. Deze kolom wordt gebruikt om de plaatsing van rijen te bepalen op de onderliggende werkknooppunten.

Stel uw distributiekolom in op company_id, die fungeert als uw multitenant-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');

Notitie

Als u wilt profiteren van elastische clusters met azure Database for PostgreSQL-prestatiefuncties, moet u tabellen distribueren. Tenzij u uw tabellen en schema's distribueert, nemen uw clusterknooppunten niet deel aan gedistribueerde query's of bewerkingen.

Voorbeeldgegevens opnemen

Download voorbeeldgegevenssets buiten psql in de normale opdrachtregel:

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

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

Uw gegevens in uw gedistribueerde tabellen zijn nu verspreid over uw elastische clusterwerkknooppunten.

Query's uitvoeren op tenantgegevens

Wanneer uw toepassing gegevens voor een specifiek bedrijf aanvraagt, kan de database de query nu efficiënt uitvoeren op het juiste werkknooppunt. Met de volgende query (company_id = 5) worden bijvoorbeeld advertenties en indrukken gefilterd. 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 hebt u al uw tabellen gedistribueerd over uw cluster door company_id. Sommige typen gegevens behoren natuurlijk tot alle tenants en kunnen naast alle tenantverdelingen worden geplaatst. Alle bedrijven in uw advertentieplatform willen bijvoorbeeld geografische informatie voor hun doelgroep ophalen op basis van de IP-adresgegevens.

Maak een referentietabel voor het opslaan van deze geografische IP-gegevens. 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);

Identificeer geo_ips vervolgens als een referentietabel. Uw cluster beheert deze tabel door een gesynchroniseerde tabel op te slaan op elk geclusterd werkknooppunt.

SELECT create_reference_table('geo_ips');

Laad nu uw referentietabel met uw voorbeeldgegevens. Vergeet niet om deze opdracht uit te voeren vanuit de map waarin u het gegevenssetbestand hebt gedownload.

\copy geo_ips from 'geo_ips.csv' with csv

SQL-instructies die de geselecteerde tabel met geo_ips samenvoegen, zijn nu efficiënt op alle knooppunten. Merk op dat deze join de locaties vindt van elk IP-adres dat is geselecteerd bij advertentie 290. 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

In sommige gevallen moeten uw afzonderlijke tenants mogelijk speciale informatie opslaan die andere tenants niet nodig hebben. Alle tenants delen echter een gemeenschappelijke definitie met een identiek databaseschema. Waar kunt u de extra gegevens plaatsen?

Eén oplossing is het gebruik van een flexibel kolomtype, zoals JSONB van PostgreSQL. Ons schema bevat een JSONB-veld in clicks met de naam user_data. Een bedrijf (bijvoorbeeld bedrijf 5) kan deze kolom gebruiken om informatie bij te houden over of een gebruiker zich op een mobiel apparaat bevindt.

Bedrijf 5 kan de volgende query gebruiken om te bepalen wie vaker kiest: 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;

PostgreSQL bevat een krachtige functie waarmee u een specifiek gedeelte of een subset van uw gegevens kunt indexeren. U kunt uw query voor bedrijf 5 verder optimaliseren door een gedeeltelijke index te maken.

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

Daarnaast is een andere manier om de prestaties van query's met uw JSONB-kolom te verbeteren, door een GIN-index te maken voor elke onderliggende sleutel en waarde in uw JSONB-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

Meer informatie over elastische clusters