在 Azure Cosmos DB for PostgreSQL 中散發及修改資料表

發行項
06/01/2023

適用於： Azure Cosmos DB for PostgreSQL (由 PostgreSQL 的 Citus 資料庫延伸模組提供)

散發式資料表

若要建立散發式資料表，您必須先定義資料表架構。若要執行上述動作，您可以使用 CREATE TABLE 語句來定義資料表，就像使用一般 PostgreSQL 資料表一樣。

CREATE TABLE github_events
(
    event_id bigint,
    event_type text,
    event_public boolean,
    repo_id bigint,
    payload jsonb,
    repo jsonb,
    actor jsonb,
    org jsonb,
    created_at timestamp
);

接下來，您可以使用 create_distributed_table () 函式來指定資料表散發式資料行，並建立背景工作分區。

SELECT create_distributed_table('github_events', 'repo_id');

函式呼叫會通知 Azure Cosmos DB for PostgreSQL，github_events 資料表的散發應在 repo_id 資料行上進行 (透過雜湊資料行值)。

根據預設，此功能共會建立 32 個分區，其中每個分區均會擁有一部分的雜湊空間，並根據預設的 citus.shard_replication_factor 的組態值來進行複寫。在背景工作角色上建立的分區複本具有與協調器上資料表相同的資料表架構、索引和條件約束定義。建立複本之後，函式會將所有散發式中繼資料儲存在協調器上。

每個所建分區均會指派唯一的分區識別碼，而且其所有複本均有相同的分區識別碼。分區會在背景工作節點上顯示為名為 'tablename_shardid' 的一般 PostgreSQL 資料表，其中 tablename 是散發式資料表的名稱，而分區識別碼是指派的唯一識別碼。您可以連線到背景工作角色 postgres 執行個體，以檢視或執行個別分區上的命令。

您現在已準備好將資料插入分散發式資料表，並在該資料表上執行查詢。您也可以在資料表和分區 DDL 參考中深入了解本節中使用的 UDF。

參考資料表

上述方法會將資料表散發至多個水平分區。另一個可能性是將資料表散發至單一分區，並將分區複寫至每個背景工作節點。以這種方式散發的資料表稱為參考資料表。這些資料表會用於儲存需要由叢集中多個節點經常存取的資料。

參考資料表的常見候選項目包含：

需要與較大散發式資料表聯結的較小資料表。
缺少租用戶識別碼資料行或未與租用戶相關聯的多租用戶應用程式中資料表。 (或者再移轉期間，即使是與租用戶相關聯的部分資料表。)
在多個資料行間需要唯一條件約束且夠小的資料表。

例如，假設多租使用者電子商務網站需要針對其任何商店中交易計算銷售稅。稅務資訊並非專屬於任何租用戶。將此資訊放在共用資料表中是合理作法。以美國為中心的參考資料表可能如下所示：

-- a reference table

CREATE TABLE states (
  code char(2) PRIMARY KEY,
  full_name text NOT NULL,
  general_sales_tax numeric(4,3)
);

-- distribute it to all workers

SELECT create_reference_table('states');

現在，例如一個計算購物車稅金的查詢可以在資料表上 states 聯結，而不需要網路額外負荷，且可以將外部索引鍵新增至狀態碼，以取得更好的驗證。

除了將資料表散發為單一複寫分區之外，create_reference_table UDF 也會將其標示為 Azure Cosmos DB for PostgreSQL 中繼資料表中的參考資料表。 Azure Cosmos DB for PostgreSQL 會自動執行兩階段交易認可 (2PC)，來修改以這種方式標示的資料表，進而提供強制性的一致性保證。

如需使用參考資料表的另一個範例，請參閱多租用戶資料庫教學課程。

散發協調器資料

如果將現有的 PostgreSQL 資料庫轉換成叢集的協調器節點，其資料表中的資料可以有效率地散發，並且將對應用程式造成的中斷降到最低。

稍早所述的 create_distributed_table 函式同時適用於空白和非空白的資料表，而後者則會自動將資料表資料列分散到整個叢集。您將藉由「注意：正在從本機資料表複製資料...」訊息是否存在而了解此函式是否複製資料。例如：

CREATE TABLE series AS SELECT i FROM generate_series(1,1000000) i;
SELECT create_distributed_table('series', 'i');
NOTICE:  Copying data from local table...
 create_distributed_table
 --------------------------

 (1 row)

移轉資料時，資料表上的寫入會遭到封鎖，而待處理寫入會在函式認可後當做散發式查詢處理。 (如果函式失敗，則查詢會再次改為本機執行。)讀取可以繼續正常運作，並在函式認可後變為散發式查詢。

散發資料表 A 和 B 時，其中 A 具有 B 的外部索引鍵，請先散發索引鍵目的地資料表 B。以錯誤的順序執行將造成錯誤：

ERROR:  cannot create foreign key constraint
DETAIL:  Referenced table must be a distributed table or a reference table.

如果無法以正確的順序散發，請卸除外部索引鍵、散發資料表，然後重新建立外部索引鍵。

從外部資料庫移轉資料時，例如從 Amazon RDS 移轉至 Azure Cosmos DB for PostgreSQL 時，請先透過 create_distributed_table 建立 Azure Cosmos DB for PostgreSQL 分散式資料表，然後將資料複製到資料表中。複製到分散式資料表可避免協調器節點上的空間不足。

共置資料表

共置表示將相關資訊放在相同的電腦上。此資料表可讓您進行有效率的查詢，同時利用整個資料集的水準延展性。如需詳細資訊，請參閱共置。

資料表會共置在群組中。若要手動控制資料表的共置群組指派，請使用 create_distributed_table 的選擇性 colocate_with 參數。如果您不在意資料表的共置，請省略此參數。此參數會預設為值 'default'，此值會將資料表與其他具有相同散發資料行、分區計數和複本因數的預設共置資料表分組。如果您想要中斷或更新此隱含共置，您可以使用 update_distributed_table_colocation()。

-- these tables are implicitly co-located by using the same
-- distribution column type and shard count with the default
-- co-location group

SELECT create_distributed_table('A', 'some_int_col');
SELECT create_distributed_table('B', 'other_int_col');

當新的資料表與其為隱含共置群組中的其他項目無關時，請指定 colocated_with => 'none'。

-- not co-located with other tables

SELECT create_distributed_table('A', 'foo', colocate_with => 'none');

將不相關的資料表分割成自己的共置群組，可改善分區重新平衡效能，因為相同群組中的分區必須一起移動。

資料表確實建立關聯時 (例如當此表加入時)，明確共置此資料表是合理作法。提升適當共置比任何重新平衡額外負荷更加重要。

若要明確共置多個資料表，請散發一個資料表，然後將另一個資料表放在其共置群組中。例如：

-- distribute stores
SELECT create_distributed_table('stores', 'store_id');

-- add to the same group as stores
SELECT create_distributed_table('orders', 'store_id', colocate_with => 'stores');
SELECT create_distributed_table('products', 'store_id', colocate_with => 'stores');

共置群組的相關資訊會儲存在 pg_dist_colocation 資料表中，而 pg_dist_partition 會顯示給哪些群組的資料表。

卸除資料表

您可以使用標準的 PostgreSQL DROP TABLE 命令來移除散發式資料表。如同一般資料表，DROP TABLE 會移除目標資料表中存在的任何索引、規則、觸發程式和條件約束。此外，此命令也會卸除背景工作節點上的分區，並清除其中繼資料。

DROP TABLE github_events;

修改資料表

Azure Cosmos DB for PostgreSQL 會自動傳播許多種類的 DDL 陳述式。修改協調器節點上的散發式資料表也會更新背景工作節點上的分區。其他 DDL 語句需要手動傳播，而其他特定語句則禁止，例如任何會修改散發資料行的語句。嘗試執行不符合的自動傳播資格 DDL 將會引發錯誤，並導致協調器節點的資料表保留不變。

以下是傳播 DDL 語句類別的參考。

新增/修改資料行

Azure Cosmos DB for PostgreSQL 會自動傳播大部分的 ALTER TABLE 命令。新增資料行或變更其預設值的運作方式，就像在單一電腦 PostgreSQL 資料庫中一樣：

-- Adding a column

ALTER TABLE products ADD COLUMN description text;

-- Changing default value

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

現有資料行的重大變更，例如重新命名或變更其資料類型也可以。不過，無法改變散發資料行的資料類型。此資料行會決定資料表資料如何透過叢集散發，以及修改其資料類型需要移動資料。

嘗試執行上述行為會導致錯誤：

-- assumining store_id is the distribution column
-- for products, and that it has type integer

ALTER TABLE products
ALTER COLUMN store_id TYPE text;

/*
ERROR:  XX000: cannot execute ALTER TABLE command involving partition column
LOCATION:  ErrorIfUnsupportedAlterTableStmt, multi_utility.c:2150
*/

新增/移除條件約束

使用 Azure Cosmos DB for PostgreSQL 可讓您繼續享有關聯式資料庫的安全性，包括資料庫限制式 (請參閱 PostgreSQL 文件)。由於散發式系統的本質，Azure Cosmos DB for PostgreSQL 不會交叉參照背景工作角色節點之間的唯一性限制式或參考完整性。

若要設定共置散發式資料表之間的外部索引鍵，請一律在索引鍵中包含散發資料行。包括散發資料行可能涉及建立索引鍵複合。

在下列情況下，可能會建立外部索引鍵：

在兩個本機 (非散發式) 資料表之間，
在兩個參考資料表之間，
當索引鍵包含散發資料行時，在兩個共置散發式資料表，或
作為參考參考資料表的散發式資料表

不支援從參考資料表到散發式資料表的外部索引鍵。

注意

主索引鍵和唯一限制必須包含散發資料行。將資料行新增至非散發資料行會產生錯誤

此範例示範如何在散發式資料表上建立主索引鍵和外部索引鍵：

--
-- Adding a primary key
-- --------------------

-- We'll distribute these tables on the account_id. The ads and clicks
-- tables must use compound keys that include account_id.

ALTER TABLE accounts ADD PRIMARY KEY (id);
ALTER TABLE ads ADD PRIMARY KEY (account_id, id);
ALTER TABLE clicks ADD PRIMARY KEY (account_id, id);

-- Next distribute the tables

SELECT create_distributed_table('accounts', 'id');
SELECT create_distributed_table('ads',      'account_id');
SELECT create_distributed_table('clicks',   'account_id');

--
-- Adding foreign keys
-- -------------------

-- Note that this can happen before or after distribution, as long as
-- there exists a uniqueness constraint on the target column(s) which
-- can only be enforced before distribution.

ALTER TABLE ads ADD CONSTRAINT ads_account_fk
  FOREIGN KEY (account_id) REFERENCES accounts (id);
ALTER TABLE clicks ADD CONSTRAINT clicks_ad_fk
  FOREIGN KEY (account_id, ad_id) REFERENCES ads (account_id, id);

同樣地在唯一性條件約束中包含散發資料行方式如下：

-- Suppose we want every ad to use a unique image. Notice we can
-- enforce it only per account when we distribute by account id.

ALTER TABLE ads ADD CONSTRAINT ads_unique_image
  UNIQUE (account_id, image_url);

非 Null 條件約束可以套用至任何資料行 (散發，或非散發式)，因為此類資料行無須背景工作節點間的查閱。

ALTER TABLE ads ALTER COLUMN image_url SET NOT NULL;

使用 NOT VALID 條件約束

在某些情況下，強制執行新資料列的條件約束十分有用，可同時允許現有不符規範的資料列維持不變。 Azure Cosmos DB for PostgreSQL 使用 PostgreSQL 的 "NOT VALID" 限制式指定，支援 CHECK 限制式和外部索引鍵的這項功能。

例如，請考慮將使用者設定檔儲存在參考資料表中的應用程式。

-- we're using the "text" column type here, but a real application
-- might use "citext" which is available in a postgres contrib module

CREATE TABLE users ( email text PRIMARY KEY );
SELECT create_reference_table('users');

隨著時間推移，假設部分非位址的資料進入資料表中。

INSERT INTO users VALUES
   ('foo@example.com'), ('hacker12@aol.com'), ('lol');

我們想要驗證位址，但 PostgreSQL 通常不允許我們新增 CHECK 條件約束，用於針對現有資料列的失敗。不過，此項目允許標示為無效的條件約束：

ALTER TABLE users
ADD CONSTRAINT syntactic_email
CHECK (email ~
   '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$'
) NOT VALID;

新的資料列現在受到保護。

INSERT INTO users VALUES ('fake');

/*
ERROR:  new row for relation "users_102010" violates
        check constraint "syntactic_email_102010"
DETAIL:  Failing row contains (fake).
*/

稍後，在非尖峰時段，資料庫管理員可以嘗試修正錯誤的資料列，並重新驗證條件約束。

-- later, attempt to validate all rows
ALTER TABLE users
VALIDATE CONSTRAINT syntactic_email;

PostgreSQL 文件在 ALTER TABLE 章節中有有關 NOT VALID 和 VALIDATE CONSTRAINT 的詳細資訊。

新增/移除索引

Azure Cosmos DB for PostgreSQL 支援索引的新增和移除：

-- Adding an index

CREATE INDEX clicked_at_idx ON clicks USING BRIN (clicked_at);

-- Removing an index

DROP INDEX clicked_at_idx;

新增索引會進行寫入鎖定，這在多租用戶的「記錄系統」中可能不受歡迎。若要將應用程式停機時間降至最低，請改為同時建立索引。這個方法需要比標準索引組建更多的總工時，而且需要較長的時間才能完成。不過，由於此方法允許在建置索引時繼續正常作業，因此此方法對於在生產環境中新增索引十分有用。

-- Adding an index without locking table writes

CREATE INDEX CONCURRENTLY clicked_at_idx ON clicks USING BRIN (clicked_at);

類型和函式

建立自訂 SQL 類型和使用者定義函式會傳播至背景工作節點。不過，在具有分散式作業的交易中建立這類資料庫物件涉及權衡取捨。

Azure Cosmos DB for PostgreSQL 會針對每個背景工作角色使用多個連線，以跨分區平行處理作業 (例如 create_distributed_table())。而建立資料庫物件時，Azure Cosmos DB for PostgreSQL 會針對每個背景工作角色使用單一連線，將該物件傳播至背景工作角色節點。將兩個作業合併為單一交易可能會導致問題，因為平行連線將無法看到透過單一連線建立但尚未認可的物件。

請考慮使用交易區塊來建立類型、資料表、載入資料，以及散發資料表：

BEGIN;

-- type creation over a single connection:
CREATE TYPE coordinates AS (x int, y int);
CREATE TABLE positions (object_id text primary key, position coordinates);

-- data loading thus goes over a single connection:
SELECT create_distributed_table(‘positions’, ‘object_id’);

SET client_encoding TO 'UTF8';
\COPY positions FROM ‘positions.csv’

COMMIT;

在 Citus 11.0 之前，Citus 會延遲在背景工作節點上建立類型，並在建立分散式資料表時個別予以認可。這可讓 create_distributed_table() 中的資料複製以平行方式進行。不過，這也表示該類型不一定存在於 Citus 背景工作節點上，或者如果交易復原，該類型會保留在背景工作節點上。

在 Citus 11.0 中，此預設行為會變更為排定協調器與背景工作節點之間的結構描述一致性優先順序。此新行為有缺點：如果在相同交易中的平行命令之後發生物件傳播，則無法再完成交易，如下列程式碼區塊中的 ERROR 所醒目提示：

BEGIN;
CREATE TABLE items (key text, value text);
-- parallel data loading:
SELECT create_distributed_table(‘items’, ‘key’);
SET client_encoding TO 'UTF8';
\COPY items FROM ‘items.csv’
CREATE TYPE coordinates AS (x int, y int);

ERROR:  cannot run type command because there was a parallel operation on a distributed table in the transaction

如果您遇到此問題，有兩個簡單的因應措施：

將 citus.create_object_propagation 設定為 automatic 以在此情況下延遲建立類型，此時存在於不同節點上的資料庫物件之間可能會有一些不一致的情況。
將 citus.multi_shard_modify_mode 設定為 sequential 以停用每個節點的平行處理原則。相同交易中的資料載入速度可能會變慢。

下一步

有用的診斷查詢