Kurz: Návrh víceklientských databází pomocí elastických clusterů

V tomto kurzu použijete Azure Database for PostgreSQL s elastickými clustery, abyste zjistili, jak navrhnout víceklientovou aplikaci, která přináší výhody horizontálního škálování na více instancí.

• Požadavky
• Vytvoření schématu pomocí nástroje psql
• Tabulky horizontálních oddílů napříč uzly
• Ingestace ukázkových dat
• Dotazování dat tenanta
• Sdílení dat mezi tenanty
• Přizpůsobení schématu pro jednotlivé tenanty

Požadavky

Vytvořte elastický cluster jedním z následujících způsobů:

• Vytvoření elastického clusteru pomocí portálu
• Vytvoření elastického clusteru pomocí Bicep
• Vytvoření elastického clusteru pomocí šablony ARM

Vytvoření schématu pomocí nástroje psql

Po připojení k elastickému clusteru pomocí psql můžete nakonfigurovat elastický cluster. Tento kurz vás provede vytvořením aplikační platformy, která umožňuje společnostem sledovat své reklamní kampaně.

Poznámka:

Při distribuci dat napříč clusterem jsou všechna jedinečná omezení dat vymezena na jejich distribuci "fragmentu". V našem příkladu s více tenanty se vynucuje jedinečnost dat aplikace na tenanta (například ID společnosti). Z tohoto důvodu definice distribuovaných tabulek pro omezení primárního a cizího klíče vždy obsahují sloupec ID společnosti.

Vytvořte tabulku pro uchovávání informací o organizacích ve vícenájemním prostředí a další tabulku pro jejich kampaně. V konzole psql spusťte tyto příkazy:

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

Každá kampaň platí za spouštění reklam. Přidejte do psql tabulku reklam s následujícím kódem:

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

Nakonec chcete sledovat statistiky na základě výběrů reklam a zobrazení:

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

Nově vytvořené tabulky z psql teď můžete zobrazit spuštěním následujícího příkazu:

\dt

Tabulky horizontálních oddílů napříč uzly

Až do této chvíle jste vytvořili standardní tabulky Postgres, ale nakonec potřebujete vytvořit distribuované tabulky napříč elastickým clusterem. Distribuované tabulky v rámci elastického clusteru ukládají data na různých uzlech na základě hodnot definovaných vaším distribučním sloupcem. Tento sloupec slouží k určení umístění řádků mezi základními pracovními uzly.

Nastavte distribuční sloupec na company_id, který slouží jako váš multitenantní identifikátor. V psql spusťte tyto funkce:

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

Poznámka:

Pokud chcete využívat elastické clustery s funkcemi výkonu Azure Database for PostgreSQL, musíte distribuovat tabulky. Pokud nedistribuujete tabulky a schémata, nebudou se uzly clusteru účastnit žádných distribuovaných dotazů ani operací.

Ingestace ukázkových dat

Mimo psql si na normálním příkazovém řádku stáhněte ukázkové datové sady:

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

Zpět uvnitř psql hromadně načtěte data. Nezapomeňte spustit psql ve stejném adresáři, ve kterém jste stáhli datové soubory.

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

Vaše data v distribuovaných tabulkách se teď rozprostírají mezi pracovní uzly elastického clusteru.

Dotazování dat tenanta

Když vaše aplikace požaduje data pro konkrétní společnost, může teď databáze efektivně spouštět dotaz na příslušném pracovním uzlu. Například následující dotaz (company_id = 5) filtruje reklamy a zobrazení. Zkuste ho spustit v psql, abyste viděli výsledky.

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;

Sdílení dat mezi tenanty

Doteď jste distribuovali všechny tabulky ve vašem clusteru company_id. Některé typy dat však přirozeně "patří" všem tenantům a mohou být umístěny společně ve všech distribučních vrstvách tenantů. Například všechny společnosti ve vaší reklamní platformě můžou chtít získat geografické informace pro cílovou skupinu na základě podrobností IP adresy.

Vytvořte referenční tabulku pro uchovávání těchto geografických IP adres. V psql spusťte následující příkazy:

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

Poté identifikujte geo_ips jako "referenční tabulku". Cluster tuto tabulku spravuje tak, že uloží synchronizovanou tabulku do každého clusterovaného pracovního uzlu.

SELECT create_reference_table('geo_ips');

Nyní načtěte referenční tabulku s ukázkovými daty. Nezapomeňte tento příkaz spustit z adresáře, do kterého jste stáhli soubor datové sady.

\copy geo_ips from 'geo_ips.csv' with csv

Příkazy SQL, které spojují vybranou tabulku s geo_ips , jsou teď efektivní na všech uzlech. Všimněte si tohoto spojení a vyhledejte umístění všech IP adres vybraných v reklamě 290. Zkuste spustit dotaz v 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;

Přizpůsobte schéma pro jednotlivého tenanta

V některých případech můžou jednotliví tenanti potřebovat ukládat speciální informace, které ostatní tenanti nepotřebují. Všichni tenanti ale sdílejí společnou definici se stejným schématem databáze. Kam můžete vložit další data?

Jedním z řešení je použití flexibilního typu sloupce, jako je JSONB v PostgreSQL. Naše schéma má volané clicksuser_datapole JSONB . Společnost (například společnost 5) může tento sloupec použít ke sledování informací o tom, jestli je uživatel na mobilním zařízení.

Tady je dotaz, který může společnost 5 použít ke zjištění, kdo si vybírá více: návštěvníci přistupující přes mobilní zařízení nebo tradiční uživatelé.

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 obsahuje výkonnou funkci, která umožňuje indexovat určitou část nebo podmnožinu dat. Dotaz pro společnost 5 můžete dále optimalizovat vytvořením částečného indexu.

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

Dalším způsobem, jak zlepšit výkon napříč dotazy, které obsahují sloupec JSONB, je vytvořit index GIN pro každý podkladový klíč a hodnotu ve sloupci 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;

Další krok

Další informace o elastických clusterech