Citus 分散式 PostgreSQL 叢集 - SQL Reference(建立和修改分散式表 DDL)

為少發表於2022-03-27
分散式SQL

image

建立和分佈表

要建立分散式表，您需要首先定義表 schema。 為此，您可以使用 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');

該函式通知 Citus github_events 表應該分佈在 repo_id 列上（通過雜湊列值）。該函式還使用 citus.shard_count 和 citus.shard_replication_factor 配置值在工作節點上建立分片。

此示例將建立總共 citus.shard_count 個分片，其中每個分片擁有一部分雜湊令牌空間並根據預設的 citus.shard_replication_factor 配置值進行復制。在 worker 上建立的 shard 副本與 coordinator 上的表具有相同的表 schema、索引和約束定義。 建立副本後，此函式將所有分散式後設資料儲存在協調器上。

每個建立的分片都分配有一個唯一的分片 ID，並且它的所有副本都具有相同的分片 ID。 每個分片在工作節點上表示為一個名為 tablename_shardid 的常規 PostgreSQL 表，其中 tablename 是分散式表的名稱，shardid 是分配給該分片的唯一 ID。 您可以連線到工作節點(worker) postgres 例項以檢視或在各個分片上執行命令。

您現在已準備好將資料插入分散式表並對其執行查詢。您還可以在文件的 Citus Utility Functions 中瞭解有關本節中使用的 UDF 的更多資訊。

引用表

上述方法將表分佈到多個水平分片中，但另一種可能是將表分佈到單個分片中並將分片複製到每個工作節點。以這種方式分佈的表稱為引用表。 它們用於儲存叢集中多個節點需要頻繁訪問的資料。

引用表的常見候選包括：

  • 較小的表需要與較大的分散式表連線。
  • 多租戶應用程式中缺少租戶 ID 列或不與租戶關聯的表。 （在某些情況下，為了減少遷移工作，使用者甚至可以選擇從與租戶關聯但當前缺少租戶 ID 的表中建立引用表。）
  • 需要跨多個列的唯一約束並且足夠小的表。

例如，假設一個多租戶電子商務網站需要為其任何商店的交易計算銷售稅。 稅務資訊並非特定於任何租戶。 將其合併到共享表中是有意義的。 以美國為中心的引用表可能如下所示：

-- 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 表，並且可以將外來鍵新增到 state 程式碼中以進行更好的驗證。

除了將表分佈為單個複製分片之外，create_reference_table UDF 將其標記為 Citus 後設資料表中的引用表。Citus 自動執行兩階段提交 (2PC) 以修改以這種方式標記的表，這提供了強大的一致性保證。

如果您有一個現有的分散式表，您可以通過執行將其更改為引用表：

SELECT undistribute_table('table_name');
SELECT create_reference_table('table_name');

有關在多租戶應用程式中使用引用表的另一個示例，請參閱在租戶之間共享資料。

分佈協調器資料

如果將現有的 PostgreSQL 資料庫轉換為 Citus 叢集的協調器節點，則其表中的資料可以高效地分佈，並且對應用程式的中斷最小。

前面描述的 create_distributed_table 函式適用於空表和非空表，對於後者，它會自動在整個叢集中分佈錶行。您將通過訊息 NOTICE: Copying data from local 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...
NOTICE:  copying the data has completed
DETAIL:  The local data in the table is no longer visible, but is still on disk.
HINT:  To remove the local data, run: SELECT truncate_local_data_after_distributing_table($$public.series$$)
 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.

如果無法以正確的順序分佈，則刪除外來鍵，分佈表，然後重新建立外來鍵。

表分佈後，使用 truncate_local_data_after_distributing_table 函式刪除本地資料。Citus 查詢無法訪問分散式表中剩餘的本地資料，並且可能導致協調器上的不相關約束違規。

從外部資料庫遷移資料時，例如從 Amazon RDS 遷移到 Citus Cloud，首先通過 create_distributed_table 建立 Citus 分散式表，然後將資料複製到表中。 複製到分散式表中可以避免協調節點上的空間不足。

共置表

共置是一種策略性地劃分資料的做法，將相關資訊儲存在同一臺機器上以實現高效的關係操作，同時利用整個資料集的水平可擴充套件性。 有關更多資訊和示例，請參閱表共置。

表在組中是共置(co-location)的。要手動控制表的 co-location 分配，請使用 create_distributed_table 的可選 colocate_with 引數。 如果您不關心表的 co-location，請忽略此引數。它預設為 'default' 值，它將表與具有相同分佈列型別、分片計數和複製因子的任何其他預設 co-location 表分組。如果要中斷或更新此隱式 colocation，可以使用 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');

當新表與其潛在的隱式 co-location 組中的其他表不相關時，請指定 colocated_with => 'none'。

-- not co-located with other tables

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

將不相關的表拆分為它們自己的 co-location 組將提高分片再平衡效能，因為同一組中的分片必須一起移動。

當表確實相關時（例如，當它們將被連線時），顯式地將它們放在一起是有意義的。 適當的 co-location 所帶來的收益比任何重新平衡開銷都更重要。

要顯式共置多個表，請分佈一張表，然後將其他表放入其 co-location 組。 例如：

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

有關 co-location 組的資訊儲存在 pg_dist_colocation 表中，而 pg_dist_partition 顯示哪些表分配給了哪些組。

從 Citus 5.x 升級

從 Citus 6.0 開始，我們將 co-location 作為 first-class 的概念，並開始在 pg_dist_colocation 中跟蹤表對 co-location 組的分配。由於 Citus 5.x 沒有這個概念，因此使用 Citus 5 建立的表沒有在後設資料中明確標記為位於同一位置，即使這些表在物理上位於同一位置。

由於 Citus 使用託管後設資料資訊進行查詢優化和下推，因此通知 Citus 以前建立的表的此 co-location 變得至關重要。要修復後設資料，只需使用 mark_tables_colocated 將表標記為 co-located：

-- Assume that stores, products and line_items were created in a Citus 5.x database.

-- Put products and line_items into store's co-location group
SELECT mark_tables_colocated('stores', ARRAY['products', 'line_items']);

該函式要求表以相同的方法、列型別、分片數和複製方法分佈。它不會重新分片或物理移動資料，它只是更新 Citus 後設資料。

刪除表

您可以使用標準的 PostgreSQL DROP TABLE 命令來刪除您的分散式表。與常規表一樣，DROP TABLE 刪除目標表存在的任何索引、規則、觸發器和約束。此外，它還會刪除工作節點上的分片並清理它們的後設資料。

DROP TABLE github_events;

修改表

Citus 會自動傳播多種 DDL 語句，這意味著修改協調器節點上的分散式表也會更新工作器上的分片。其他 DDL 語句需要手動傳播，並且禁止某些其他語句，例如那些會修改分佈列的語句。嘗試執行不符合自動傳播條件的 DDL 將引發錯誤並使協調節點上的表保持不變。

以下是傳播的 DDL 語句類別的參考。 請注意，可以使用配置引數啟用或禁用自動傳播。

新增/修改列

Citus 會自動傳播大多數 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;

對現有列進行重大更改（例如重新命名或更改其資料型別）也可以。但是，不能更改分佈列的資料型別。此列確定表資料如何在 Citus 叢集中分佈，修改其資料型別將需要移動資料。

嘗試這樣做會導致錯誤：

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

ALTER TABLE products
ALTER COLUMN store_id TYPE text;

/*
ERROR:  cannot execute ALTER TABLE command involving partition column
*/

作為一種解決方法，您可以考慮更改分佈列，更新它，然後再改回來。

新增/刪除約束

使用 Citus 可以讓您繼續享受關聯式資料庫的安全性，包括資料庫約束（請參閱 PostgreSQL 文件）。由於分散式系統的性質，Citus 不會交叉引用工作節點之間的唯一性約束或引用完整性。

在這些情況下可能會建立外來鍵：

不支援從引用表到分散式表的外來鍵。

Citus 支援從本地到引用表的所有外來鍵引用操作，但不支援反向支援 ON DELETE/UPDATE CASCADE（引用本地）。

主鍵和唯一性約束必須包括分佈列。 將它們新增到非分佈列將產生錯誤（請參閱無法建立唯一性約束）。

這個例子展示瞭如何在分散式表上建立主鍵和外來鍵：

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

非空約束可以應用於任何列（分佈與否），因為它們不需要工作節點之間的查詢。

ALTER TABLE ads ALTER COLUMN image_url SET NOT NULL;

使用 NOT VALID 約束

在某些情況下，對新行實施約束，同時允許現有的不符合要求的行保持不變是很有用的。Citus 使用 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 的資訊。

新增/刪除索引

Citus 支援新增和刪除索引：

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

手動修改

目前其他 DDL 命令不會自動傳播，但是，您可以手動傳播更改。請參閱手動查詢傳播。

更多

相關文章