Självstudie: Utforma en fleranvändardatabas med elastiska kluster

I den här självstudien använder du Azure Database for PostgreSQL med elastiska kluster för att lära dig hur du utformar ett program med flera klientorganisationer som drar nytta av horisontell utskalning.

• Förutsättningar
• Använda psql-verktyget för att skapa ett schema
• Fragmentera tabeller mellan noder
• Mata in exempeldata
• Fråga efter klientdata
• Dela data mellan klientorganisationer
• Anpassa schemat per klientorganisation

Förutsättningar

Skapa ett elastiskt kluster på något av följande sätt:

• Skapa ett elastiskt kluster med hjälp av portalen
• Skapa ett elastiskt kluster med Bicep
• Skapa ett elastiskt kluster med ARM-mall

Använda psql-verktyget för att skapa ett schema

När du har anslutit till det elastiska klustret med psql kan du konfigurera ditt elastiska kluster. Den här självstudien beskriver hur du skapar en programplattform som gör det möjligt för företag att spåra sina annonskampanjer.

Kommentar

När du distribuerar data i klustret begränsas alla unika databegränsningar till distributionens "shard". I vårt multitenantexempel framtvingas unikhet för programdata per klientorganisation (till exempel företags-ID). Därför inkluderar våra distribuerade tabelldefinitioner för primär- och sekundärnyckelbegränsningar alltid kolumnen företags-ID.

Skapa en tabell för att lagra din företagsinformation för flera klientorganisationer och en annan tabell för deras kampanjer. Kör följande kommandon i psql-konsolen:

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

Varje kampanj betalar för att köra annonser. Lägg till din annonstabell i psql med följande kod:

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

Slutligen vill du spåra statistik baserat på annonsval och visningar:

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

Du kan nu se de nyligen skapade tabellerna från psql genom att köra:

\dt

Fragmentera tabeller mellan noder

Fram tills nu har du skapat postgres-standardtabeller, men i slutändan måste du skapa distribuerade tabeller i ditt elastiska kluster. Distribuerade tabeller i ett elastiskt kluster lagrar data på olika noder baserat på de värden som definieras av distributionskolumnen. Den här kolumnen används för att fastställa radplacering över de underliggande arbetsnoderna.

Konfigurera din distributionskolumn så att den är company_id, som fungerar som din identifierare för flera klientorganisationer. Kör följande funktioner i psql:

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

Kommentar

Om du vill dra nytta av elastiska kluster med Prestandafunktioner för Azure Database for PostgreSQL måste du distribuera tabeller. Om du inte distribuerar dina tabeller och scheman deltar inte klusternoderna i några distribuerade frågor eller åtgärder.

Mata in exempeldata

Utanför psql laddar du ned exempeldatauppsättningar på den normala kommandoraden:

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

I psql kan du massinläsa data. Se till att köra psql i samma katalog där du laddade ned datafilerna.

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

Dina data i dina distribuerade tabeller är nu utspridda över dina elastiska klusterarbetsnoder.

Fråga efter klientdata

När ditt program begär data för ett visst företag kan databasen nu effektivt köra frågan på rätt arbetsnod. Följande fråga (company_id = 5) filtrerar till exempel ned annonser och visningar. Prova att köra den i psql för att se resultatet.

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;

Dela data mellan klientorganisationer

Hittills har du distribuerat alla tabeller i klustret med company_id. Vissa typer av data "tillhör" dock naturligt alla klienter och kan placeras tillsammans med alla klientdistributioner. Till exempel kanske alla företag på din annonsplattform vill få geografisk information för sin målgrupp baserat på IP-adressinformationen.

Skapa en referenstabell för att lagra den här geografiska IP-informationen. Kör följande kommandon i 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);

Identifiera sedan geo_ips som en "referenstabell". Klustret hanterar den här tabellen genom att lagra en synkroniserad tabell på varje klustrad arbetsnod.

SELECT create_reference_table('geo_ips');

Läs nu in referenstabellen med dina exempeldata. Kom ihåg att köra det här kommandot från katalogen där du laddade ned datamängdsfilen.

\copy geo_ips from 'geo_ips.csv' with csv

SQL-instruktioner som ansluter den valda tabellen med geo_ips är nu effektiva på alla noder. Observera den här kopplingen för att hitta platserna för varje IP-adress som valts på ad 290. Prova att köra frågan i 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;

Anpassa schemat för varje klientorganisation

I vissa fall kan dina enskilda klienter behöva lagra särskild information som andra klienter inte behöver. Alla hyresgäster delar dock en gemensam definition med ett identiskt databasschema. Var kan du placera extra data?

En lösning är att använda en flexibel kolumntyp som PostgreSQL:s JSONB. Vårt schema har ett JSONB-fält i clicks med namnet user_data. Ett företag (till exempel företag 5) kan använda den här kolumnen för att spåra information om huruvida en användare finns på en mobil enhet.

Företag nummer 5 kan använda följande fråga för att avgöra vilka som väljer mer: användare av mobila enheter eller traditionella enhetsanvändare.

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 innehåller en kraftfull funktion som gör att du kan indexera en viss del eller delmängd av dina data. Du kan ytterligare optimera din fråga för företag 5 genom att skapa ett partiellt index.

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

Ett annat sätt att förbättra prestanda för frågor som innehåller JSONB-kolumnen är att skapa ett GIN-index för varje underliggande nyckel och värde i JSONB-kolumnen.

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;

Gå vidare

Läs mer om elastiska kluster