Oktatóanyag: Multitenant adatbázis tervezése rugalmas klaszterekkel

Ebben az oktatóanyagban az Azure Database for PostgreSQL-t használja rugalmas (scale-out) fürtökkel, hogy megtanulja, hogyan tervezzen több-bérlős alkalmazást, amely kihasználja a horizontális felskálázás előnyeit.

• Előfeltételek
• Séma létrehozása a psql segédprogrammal
• Csomópontok közötti szegmenstáblák
• Mintaadatok betöltése
• Bérlői adatok lekérdezése
• Adatok megosztása bérlők között
• A séma testreszabása bérlőnként

Előfeltételek

Hozzon létre egy rugalmas fürtöt az alábbi módszerek egyikével:

• Rugalmas fürt létrehozása a Portál használatával
• Hozzon létre egy rugalmas klasztert Bicep használatával
• Hozzon létre rugalmas fürtöt ARM-sablonnal

Séma létrehozása a psql segédprogrammal

Miután a psql használatával csatlakozott a rugalmas fürthöz, konfigurálhatja a rugalmas fürtöt. Ez az oktatóanyag végigvezeti egy alkalmazásplatform létrehozásán, amely lehetővé teszi a vállalatok számára a hirdetéskampányok nyomon követését.

Feljegyzés

Ha adatokat oszt ki a klaszterben, az egyedi adathoz kötődő megkötések hatóköre az elosztási "darabokra" terjed ki. Több-bérlős példánkban az alkalmazásadatok egyediségét bérlőnként (például cégazonosító) kényszerítjük ki. Ezért az elsődleges és idegen kulcsokra vonatkozó elosztott tábladefiníciók mindig tartalmazzák a cégazonosító oszlopot.

Hozzon létre egy táblát a több-bérlős vállalati adatok tárolásához, és egy másik táblát a kampányaikhoz. A psql-konzolon futtassa az alábbi parancsokat:

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

Minden kampány fizet a hirdetések futtatásáért. Adja hozzá a hirdetéstáblát a psql-ben a következő kóddal:

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

Végül a statisztikát a hirdetésválasztások és a megjelenítések alapján szeretné nyomon követni:

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

Az újonnan létrehozott táblák a psql-ből a következő futtatásával láthatók:

\dt

Csomópontok közötti szegmenstáblák

Eddig a pontig szabványos Postgres-táblákat hozott létre, de végül elosztott táblákat kell létrehoznia a rugalmas fürtben. Egy rugalmas fürt elosztott táblái a terjesztési oszlop által meghatározott értékek alapján különböző csomópontokon tárolják az adatokat. Ez az oszlop a mögöttes feldolgozó csomópontok sorelhelyezésének meghatározására szolgál.

Állítsa be a terjesztési oszlopot company_id értékre, amely több-bérlős azonosítóként működik. A psql-ben futtassa az alábbi függvényeket:

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

Feljegyzés

Ahhoz, hogy kihasználhassa az Azure Database for PostgreSQL teljesítményjellemzőinek rugalmas fürtök általi előnyeit, el kell osztania a táblákat. Ha nem osztja el a táblákat és sémákat, a fürtcsomópontok nem vesznek részt elosztott lekérdezésekben vagy műveletekben.

Mintaadatok betöltése

A psql-en kívül, a normál parancssorban töltse le a mintaadatkészleteket:

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

Vissza a psql-be, tömegesen töltse be az adatokat. Mindenképpen futtassa a psql-t ugyanabban a könyvtárban, ahol az adatfájlokat letöltötte.

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

Az elosztott táblákban lévő adatok mostantól elosztva lesznek a rugalmas fürt feldolgozó csomópontjai között.

Bérlői adatok lekérdezése

Amikor az alkalmazás adatokat kér egy adott vállalattól, az adatbázis mostantól hatékonyan végrehajthatja a lekérdezést a megfelelő feldolgozó csomóponton. Az alábbi lekérdezés (company_id = 5) például szűri a hirdetéseket és a megjelenítéseket. Próbálja meg futtatni a psql-ben az eredmények megtekintéséhez.

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;

Adatok megosztása bérlők között

Eddig az összes táblát a fürtön company_id segítségével osztotta el. Bizonyos típusú adatok azonban természetesen "tartoznak" az összes bérlőhöz, és az összes bérlői disztribúció mellett helyezhetők el. Előfordulhat például, hogy a hirdetésplatform minden vállalata az IP-cím adatai alapján szeretné lekérni a célközönség földrajzi adatait.

Hozzon létre egy referenciatáblát a földrajzi IP-adatok tárolásához. Futtassa a következő parancsokat a psql-ben:

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

Ezután azonosítsa geo_ips a "referenciatáblázatot". Az Ön klasztere úgy kezeli ezt a táblát, hogy minden munkavégző csomóponton tárol egy szinkronizált táblát.

SELECT create_reference_table('geo_ips');

Most töltse be a referenciatáblát a mintaadatokkal. Ne felejtse el futtatni ezt a parancsot abból a könyvtárból, ahová az adathalmazfájlt letöltötte.

\copy geo_ips from 'geo_ips.csv' with csv

A kijelölt táblához geo_ips csatlakozó SQL-utasítások mostantól minden csomóponton hatékonyak. Figyelje meg ezt a csatlakozást, és keresse meg a hirdetés 290-ben kiválasztott IP-címek helyét. Próbálja meg futtatni a lekérdezést a psql-ben:

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;

A séma testreszabása bérlőnként

Egyes esetekben előfordulhat, hogy az egyes bérlőknek olyan speciális információkat kell tárolniuk, amelyekre más bérlőknek nincs szükségük. Azonban minden bérlő közös definícióval rendelkezik egy azonos adatbázissémával. Hol helyezheti el a további adatokat?

Az egyik megoldás egy rugalmas oszloptípus használata, például a PostgreSQL JSONB-ja. A sémánkban van egy JSONB-mező a hívott clickshelyenuser_data. A vállalatok (például az 5. vállalat) ezen oszlop segítségével nyomon követhetik, hogy egy felhasználó mobileszközön van-e.

Az alábbi lekérdezést a 5. számú vállalat használhatja annak megállapítására, hogy a mobil vagy a hagyományos látogatókat választják-e többen.

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;

A PostgreSQL egy hatékony funkciót tartalmaz, amely lehetővé teszi az adatok egy adott részének vagy részhalmazának indexeléséhez. A lekérdezést tovább optimalizálhatja az 5. vállalat számára egy részleges index létrehozásával.

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

Emellett a JSONB-oszlopot tartalmazó lekérdezések teljesítményének javítására is lehetőség van, ha a JSONB-oszlopban lévő összes mögöttes kulcson és értéken létrehoz egy GIN-indexet .

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;

Következő lépés

További információ a rugalmas fürtökről