Citus 分布式 PostgreSQL 集群-SQL Reference(創(chuàng)建和修改分布式表 DDL)
創(chuàng)建和分布表
要創(chuàng)建分布式表，您需要首先定義表 schema。為此，您可以使用 CREATE TABLE 語句定義一個表，就像使用常規(guī) PostgreSQL 表一樣。
- CREATE TABLE
http://www.postgresql.org/docs/current/static/sql-createtable.html
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() 函數(shù)指定表分布列并創(chuàng)建工作分片。
SELECT create_distributed_table('github_events', 'repo_id');該函數(shù)通知 Citus github_events 表應該分布在 repo_id 列上(通過哈希列值)。該函數(shù)還使用 citus.shard_count 和 citus.shard_replication_factor 配置值在工作節(jié)點上創(chuàng)建分片。
此示例將創(chuàng)建總共 citus.shard_count 個分片，其中每個分片擁有一部分哈希令牌空間并根據(jù)默認的 citus.shard_replication_factor 配置值進行復制。在 worker 上創(chuàng)建的 shard 副本與 coordinator 上的表具有相同的表 schema、索引和約束定義。創(chuàng)建副本后，此函數(shù)將所有分布式元數(shù)據(jù)保存在協(xié)調器上。
每個創(chuàng)建的分片都分配有一個唯一的分片 ID，并且它的所有副本都具有相同的分片 ID。每個分片在工作節(jié)點上表示為一個名為 tablename_shardid 的常規(guī) PostgreSQL 表，其中 tablename 是分布式表的名稱，shardid 是分配給該分片的唯一 ID。您可以連接到工作節(jié)點(worker) postgres 實例以查看或在各個分片上運行命令。
您現(xiàn)在已準備好將數(shù)據(jù)插入分布式表并對其運行查詢。您還可以在文檔的 Citus Utility Functions 中了解有關本節(jié)中使用的 UDF 的更多信息。
- Citus Utility Functions
https://docs.citusdata.com/en/v11.0-beta/develop/api_udf.html#user-defined-functions
引用表
上述方法將表分布到多個水平分片中，但另一種可能是將表分布到單個分片中并將分片復制到每個工作節(jié)點。以這種方式分布的表稱為引用表。它們用于存儲集群中多個節(jié)點需要頻繁訪問的數(shù)據(jù)。
- 引用表的常見候選包括：
- 較小的表需要與較大的分布式表連接。
- 多租戶應用程序中缺少租戶 ID 列或不與租戶關聯(lián)的表。(在某些情況下，為了減少遷移工作，用戶甚至可以選擇從與租戶關聯(lián)但當前缺少租戶 ID 的表中創(chuàng)建引用表。)
需要跨多個列的唯一約束并且足夠小的表。
例如，假設一個多租戶電子商務網(wǎng)站需要為其任何商店的交易計算銷售稅。稅務信息并非特定于任何租戶。將其合并到共享表中是有意義的。以美國為中心的引用表可能如下所示：
-- 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');
現(xiàn)在，諸如為購物車計算稅款之類的查詢可以在沒有網(wǎng)絡開銷的情況下加入 states 表，并且可以將外鍵添加到 state 代碼中以進行更好的驗證。
除了將表分布為單個復制分片之外，create_reference_table UDF 將其標記為 Citus 元數(shù)據(jù)表中的引用表。Citus 自動執(zhí)行兩階段提交 (2PC) 以修改以這種方式標記的表，這提供了強大的一致性保證。
- 2PC
https://en.wikipedia.org/wiki/Two-phase_commit_protocol
如果您有一個現(xiàn)有的分布式表，您可以通過運行將其更改為引用表：
SELECT undistribute_table('table_name');
SELECT create_reference_table('table_name');有關在多租戶應用程序中使用引用表的另一個示例，請參閱在租戶之間共享數(shù)據(jù)。
- 在租戶之間共享數(shù)據(jù)
https://docs.citusdata.com/en/v11.0-beta/use_cases/multi_tenant.html#mt-ref-tables
分布協(xié)調器數(shù)據(jù)
如果將現(xiàn)有的 PostgreSQL 數(shù)據(jù)庫轉換為 Citus 集群的協(xié)調器節(jié)點，則其表中的數(shù)據(jù)可以高效地分布，并且對應用程序的中斷最小。
前面描述的 create_distributed_table 函數(shù)適用于空表和非空表，對于后者，它會自動在整個集群中分布表行。您將通過消息 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)
遷移數(shù)據(jù)時會阻止對表的寫入，一旦函數(shù)提交，掛起的寫入將作為分布式查詢處理。(如果函數(shù)失敗，則查詢再次變?yōu)楸镜亍?讀取可以正常繼續(xù)，一旦函數(shù)提交，將變?yōu)榉植际讲樵儭?/p>
分布表 A 和 B 時，其中 A 對 B 有外鍵，首先需對目標表 B 設置分布鍵。當以錯誤的順序執(zhí)行會導致錯誤：
ERROR: cannot create foreign key constraint
DETAIL: Referenced table must be a distributed table or a reference table.
如果無法以正確的順序分布，則刪除外鍵，分布表，然后重新創(chuàng)建外鍵。
表分布后，使用 truncate_local_data_after_distributing_table 函數(shù)刪除本地數(shù)據(jù)。Citus 查詢無法訪問分布式表中剩余的本地數(shù)據(jù)，并且可能導致協(xié)調器上的不相關約束違規(guī)。
- truncate_local_data_after_distributing_table
https://docs.citusdata.com/en/v11.0-beta/develop/api_udf.html#truncate-local-data-after-distributing-table
從外部數(shù)據(jù)庫遷移數(shù)據(jù)時，例如從 Amazon RDS 遷移到 Citus Cloud，首先通過 create_distributed_table 創(chuàng)建 Citus 分布式表，然后將數(shù)據(jù)復制到表中。復制到分布式表中可以避免協(xié)調節(jié)點上的空間不足。
共置表
共置是一種策略性地劃分數(shù)據(jù)的做法，將相關信息保存在同一臺機器上以實現(xiàn)高效的關系操作，同時利用整個數(shù)據(jù)集的水平可擴展性。有關更多信息和示例，請參閱表共置。
- 表共置
https://docs.citusdata.com/en/v11.0-beta/sharding/data_modeling.html#colocation
表在組中是共置(co-location)的。要手動控制表的 co-location 分配，請使用 create_distributed_table 的可選 colocate_with 參數(shù)。如果您不關心表的 co-location，請忽略此參數(shù)。它默認為 'default' 值，它將表與具有相同分布列類型、分片計數(shù)和復制因子的任何其他默認 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 組將提高分片再平衡性能，因為同一組中的分片必須一起移動。
- 分片再平衡
https://docs.citusdata.com/en/v11.0-beta/admin_guide/cluster_management.html#shard-rebalancing
當表確實相關時(例如，當它們將被連接時)，顯式地將它們放在一起是有意義的。適當?shù)?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 顯示哪些表分配給了哪些組。
- pg_dist_colocation
https://docs.citusdata.com/en/v11.0-beta/develop/api_metadata.html#colocation-group-table
- pg_dist_partition
https://docs.citusdata.com/en/v11.0-beta/develop/api_metadata.html#partition-table
從 Citus 5.x 升級
從 Citus 6.0 開始，我們將 co-location 作為 first-class 的概念，并開始在 pg_dist_colocation 中跟蹤表對 co-location 組的分配。由于 Citus 5.x 沒有這個概念，因此使用 Citus 5 創(chuàng)建的表沒有在元數(shù)據(jù)中明確標記為位于同一位置，即使這些表在物理上位于同一位置。
由于 Citus 使用托管元數(shù)據(jù)信息進行查詢優(yōu)化和下推，因此通知 Citus 以前創(chuàng)建的表的此 co-location 變得至關重要。要修復元數(shù)據(jù)，只需使用 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']);
- mark_tables_colocated
https://docs.citusdata.com/en/v11.0-beta/develop/api_udf.html#mark-tables-colocated
該函數(shù)要求表以相同的方法、列類型、分片數(shù)和復制方法分布。它不會重新分片或物理移動數(shù)據(jù)，它只是更新 Citus 元數(shù)據(jù)。
刪除表
您可以使用標準的 PostgreSQL DROP TABLE 命令來刪除您的分布式表。與常規(guī)表一樣，DROP TABLE 刪除目標表存在的任何索引、規(guī)則、觸發(fā)器和約束。此外，它還會刪除工作節(jié)點上的分片并清理它們的元數(shù)據(jù)。
DROP TABLE github_events;
修改表
Citus 會自動傳播多種 DDL 語句，這意味著修改協(xié)調器節(jié)點上的分布式表也會更新工作器上的分片。其他 DDL 語句需要手動傳播，并且禁止某些其他語句，例如那些會修改分布列的語句。嘗試運行不符合自動傳播條件的 DDL 將引發(fā)錯誤并使協(xié)調節(jié)點上的表保持不變。
以下是傳播的 DDL 語句類別的參考。請注意，可以使用配置參數(shù)啟用或禁用自動傳播。
- 配置參數(shù)
https://docs.citusdata.com/en/v11.0-beta/develop/api_guc.html#enable-ddl-prop
添加/修改列
Citus 會自動傳播大多數(shù) ALTER TABLE 命令。添加列或更改其默認值的工作方式與在單機 PostgreSQL 數(shù)據(jù)庫中一樣：
- ALTER TABLE
https://www.postgresql.org/docs/current/static/ddl-alter.html
-- Adding a column
ALTER TABLE products ADD COLUMN description text;
-- Changing default value
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
對現(xiàn)有列進行重大更改(例如重命名或更改其數(shù)據(jù)類型)也可以。但是，不能更改分布列的數(shù)據(jù)類型。此列確定表數(shù)據(jù)如何在 Citus 集群中分布，修改其數(shù)據(jù)類型將需要移動數(shù)據(jù)。
- 分布列
https://docs.citusdata.com/en/v11.0-beta/sharding/data_modeling.html#distributed-data-modeling
嘗試這樣做會導致錯誤：
-- 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
*/
作為一種解決方法，您可以考慮更改分布列，更新它，然后再改回來。
- 更改分布列
https://docs.citusdata.com/en/v11.0-beta/develop/api_udf.html#alter-distributed-table
添加/刪除約束
使用 Citus 可以讓您繼續(xù)享受關系數(shù)據(jù)庫的安全性，包括數(shù)據(jù)庫約束(請參閱 PostgreSQL 文檔)。由于分布式系統(tǒng)的性質，Citus 不會交叉引用工作節(jié)點之間的唯一性約束或引用完整性。
- 數(shù)據(jù)庫約束
https://www.postgresql.org/docs/current/static/ddl-constraints.html
在這些情況下可能會創(chuàng)建外鍵：
- 在兩個本地(非分布式)表之間
- 在兩個引用表之間
- 在引用表和本地表之間(默認啟用，通過citus.enable_local_reference_table_foreign_keys (boolean))
https://docs.citusdata.com/en/v11.0-beta/develop/api_guc.html#enable-local-ref-fkeys
- 當鍵包含分布列時，在兩個共置的分布式表之間
https://docs.citusdata.com/en/v11.0-beta/sharding/data_modeling.html#colocation
- 作為引用表的分布式表
https://docs.citusdata.com/en/v11.0-beta/develop/reference_ddl.html#reference-tables
不支持從引用表到分布式表的外鍵。
Citus 支持從本地到引用表的所有外鍵引用操作，但不支持反向支持 ON DELETE/UPDATE CASCADE(引用本地)。
主鍵和唯一性約束必須包括分布列。將它們添加到非分布列將產(chǎn)生錯誤(請參閱無法創(chuàng)建唯一性約束)。
- 無法創(chuàng)建唯一性約束
https://docs.citusdata.com/en/v11.0-beta/reference/common_errors.html#non-distribution-uniqueness
這個例子展示了如何在分布式表上創(chuàng)建主鍵和外鍵：
--
-- 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);
非空約束可以應用于任何列(分布與否)，因為它們不需要工作節(jié)點之間的查找。
ALTER TABLE ads ALTER COLUMN image_url SET NOT NULL;
使用 NOT VALID 約束
在某些情況下，對新行實施約束，同時允許現(xiàn)有的不符合要求的行保持不變是很有用的。Citus 使用 PostgreSQL 的 “NOT VALID” 約束指定，為 CHECK 約束和外鍵支持此功能。
例如，考慮將用戶配置文件存儲在引用表中的應用程序。
- 引用表
https://docs.citusdata.com/en/v11.0-beta/develop/reference_ddl.html#reference-tables
-- 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 通常不允許我們添加對現(xiàn)有行失敗的 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).
*/稍后，在非高峰時段，數(shù)據(jù)庫管理員可以嘗試修復錯誤行并重新驗證約束。
-- later, attempt to validate all rows
ALTER TABLE users
VALIDATE CONSTRAINT syntactic_email;
PostgreSQL 文檔在 ALTER TABLE 部分中有更多關于 NOT VALID 和 VALIDATE CONSTRAINT 的信息。
- ALTER TABLE
https://www.postgresql.org/docs/current/sql-altertable.html
添加/刪除索引
Citus 支持添加和刪除索引：
-- Adding an index
CREATE INDEX clicked_at_idx ON clicks USING BRIN (clicked_at);
-- Removing an index
DROP INDEX clicked_at_idx;
- 添加和刪除索引
https://www.postgresql.org/docs/current/static/sql-createindex.html
添加索引需要寫鎖，這在多租戶“記錄系統(tǒng)”中可能是不可取的。為了最大限度地減少應用程序停機時間，請改為同時創(chuàng)建索引。與標準索引構建相比，此方法需要更多的總工作量，并且需要更長的時間才能完成。但是，由于它允許在構建索引時繼續(xù)正常操作，因此此方法對于在生產(chǎn)環(huán)境中添加新索引很有用。
-- Adding an index without locking table writes
CREATE INDEX CONCURRENTLY clicked_at_idx ON clicks USING BRIN (clicked_at);
- SQL-CREATEINDEX-CONCURRENTLY
https://www.postgresql.org/docs/current/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY
手動修改
目前其他 DDL 命令不會自動傳播，但是，您可以手動傳播更改。請參閱手動查詢傳播。
- 手動查詢傳播
https://docs.citusdata.com/en/v11.0-beta/develop/reference_propagation.html#manual-prop
