教學：設計帶有彈性叢集的多租戶資料庫

在這個教學中，你將使用 Azure Database for PostgreSQL 搭配彈性叢集，學習如何設計一個能從水平擴展中受益的多租戶應用程式。

先決條件
使用 psql 公用程式建立結構描述
進行跨節點的資料表分區化
內嵌範例資料
查詢租用戶資料
在租用戶之間共用資料
自訂每一租用戶的結構描述

先決條件

以下列其中一種方式建立彈性叢集：

使用 psql 公用程式建立結構描述

當你用 psql 連接彈性叢集後，就可以配置你的彈性叢集。本教學將引導你建立一個應用程式平台，讓企業能夠追蹤其廣告活動。

附註

在叢集中分配資料時，任何獨特的資料限制都會被限制在其發佈「分區」範圍內。在我們的多租戶範例中，應用程式資料的唯一性會被強制執行到每個租戶（例如公司識別碼）。因此，我們分散式表中對主鍵與外鍵約束的定義總是包含公司 ID 欄位。

建立一張表格來存放多租戶公司的資訊，還有另一張表格存放他們的活動。在 psql 主控台中，執行下列命令：

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

每個行銷活動都會支付執行廣告的費用。用以下程式碼在 psql 中新增你的廣告表：

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

最後，你需要根據廣告選擇和曝光次數來追蹤統計數據：

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

你現在可以透過執行以下操作，從 psql 看到新建立的資料表：

\dt

進行跨節點的資料表分區化

在這之前，你建立的是標準的 Postgres 表格，但最終你必須在彈性叢集中建立分散式表格。彈性叢集中的分散式資料表會根據你的分發欄位定義的值，將資料存放在不同節點上。此欄位用於決定底層背景工作角色節點的資料列位置。

將您的散發資料行設為 company_id，做為多租戶識別碼。在 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');

附註

要利用 Azure Database 的彈性叢集來獲得 PostgreSQL 效能功能，你需要分發資料表。除非你把資料表和結構分發出來，否則你的叢集節點不會參與任何分散式查詢或操作。

內嵌範例資料

在 psql 之外，在一般命令列中，下載範例資料集：

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

回到 psql 內，進行資料的大量載入。請務必在您下載資料檔案的相同目錄中執行 psql。

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

你分散式資料表中的資料現在分散在彈性叢集工作節點上。

查詢租用戶資料

當您的應用程式為特定公司請求資料時，資料庫現在能有效地在適當的工作節點執行查詢。例如，以下查詢（company_id = 5）會篩選廣告和展示次數。請嘗試在 psql 中執行該查詢，並查看結果。

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;

直到現在，你是用 company_id 將所有資料表分布在你的叢集上。然而，某些類型的資料自然「屬於」所有租戶，並可與所有租戶分布並列。例如，你廣告平台上的所有公司都可能想根據 IP 位址細節取得受眾的地理資訊。

建立一個參考表來存放這些地理 IP 資訊。在 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);

接著，將geo_ips 識別為「參考表」。你的叢集透過在每個叢集工作節點上儲存一個同步的表格來管理這個資料表。

SELECT create_reference_table('geo_ips');

現在，載入你的參考表，裡面有你的樣本資料。記得從你下載資料集檔案的目錄執行這個指令。

\copy geo_ips from 'geo_ips.csv' with csv

將所選資料表 geo_ips 與連接的 SQL 語句，現在在所有節點上都能有效執行。請注意此連結，以查找選取於廣告 290 上的每個 IP 的位置。試著用 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;

依據租戶自訂模式

在某些情況下，您的個別租戶可能需要儲存其他租戶不需要的特殊資訊。然而，所有租戶共享相同的定義，資料庫結構相同。額外的資料可以放置在哪裡？

一個解決方案是使用像 PostgreSQL 的 JSONB 這種彈性欄位類型。我們的結構描述在 clicks 中有名為 user_data 的 JSONB 欄位。公司（例如第五家公司）可以利用此欄位追蹤使用者是否使用行動裝置的資訊。

這裡有一家 5 公司可用來查詢誰選取較多：行動訪客還是傳統訪客。

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 包含一項強大的功能，允許你索引特定部分或子集的資料。你還可以透過建立部分索引來進一步優化你對公司 5 的查詢。

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

此外，另一種提升包含 JSONB 欄位查詢效能的方法是對 JSONB 欄位中的每個底層鍵值建立 GIN 索引。

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;

後續步驟

進一步了解彈性叢集

意見反應

此頁面對您有幫助嗎？

Last updated on 2025-11-20