Citus 分布式 PostgreSQL 集群-SQL Reference(創建和修改分布式表 DDL)
創建和分布表
要創建分布式表，您需要首先定義表 schema。為此，您可以使用 CREATE TABLE 語句定義一個表，就像使用常規 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() 函數指定表分布列并創建工作分片。
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 的更多信息。
- Citus Utility Functions
https://docs.citusdata.com/en/v11.0-beta/develop/api_udf.html#user-defined-functions
引用表
上述方法將表分布到多個水平分片中，但另一種可能是將表分布到單個分片中并將分片復制到每個工作節點。以這種方式分布的表稱為引用表。它們用于存儲集群中多個節點需要頻繁訪問的數據。
- 引用表的常見候選包括：
- 較小的表需要與較大的分布式表連接。
- 多租戶應用程序中缺少租戶 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) 以修改以這種方式標記的表，這提供了強大的一致性保證。
- 2PC
https://en.wikipedia.org/wiki/Two-phase_commit_protocol
如果您有一個現有的分布式表，您可以通過運行將其更改為引用表：
SELECT undistribute_table('table_name');
SELECT create_reference_table('table_name');有關在多租戶應用程序中使用引用表的另一個示例，請參閱在租戶之間共享數據。
- 在租戶之間共享數據
https://docs.citusdata.com/en/v11.0-beta/use_cases/multi_tenant.html#mt-ref-tables
分布協調器數據
如果將現有的 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 查詢無法訪問分布式表中剩余的本地數據，并且可能導致協調器上的不相關約束違規。
- truncate_local_data_after_distributing_table
https://docs.citusdata.com/en/v11.0-beta/develop/api_udf.html#truncate-local-data-after-distributing-table
從外部數據庫遷移數據時，例如從 Amazon RDS 遷移到 Citus Cloud，首先通過 create_distributed_table 創建 Citus 分布式表，然后將數據復制到表中。復制到分布式表中可以避免協調節點上的空間不足。
共置表
共置是一種策略性地劃分數據的做法，將相關信息保存在同一臺機器上以實現高效的關系操作，同時利用整個數據集的水平可擴展性。有關更多信息和示例，請參閱表共置。
- 表共置
https://docs.citusdata.com/en/v11.0-beta/sharding/data_modeling.html#colocation
表在組中是共置(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 組將提高分片再平衡性能，因為同一組中的分片必須一起移動。
- 分片再平衡
https://docs.citusdata.com/en/v11.0-beta/admin_guide/cluster_management.html#shard-rebalancing
當表確實相關時(例如，當它們將被連接時)，顯式地將它們放在一起是有意義的。適當的 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 創建的表沒有在元數據中明確標記為位于同一位置，即使這些表在物理上位于同一位置。
由于 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']);
- mark_tables_colocated
https://docs.citusdata.com/en/v11.0-beta/develop/api_udf.html#mark-tables-colocated
該函數要求表以相同的方法、列類型、分片數和復制方法分布。它不會重新分片或物理移動數據，它只是更新 Citus 元數據。
刪除表
您可以使用標準的 PostgreSQL DROP TABLE 命令來刪除您的分布式表。與常規表一樣，DROP TABLE 刪除目標表存在的任何索引、規則、觸發器和約束。此外，它還會刪除工作節點上的分片并清理它們的元數據。
DROP TABLE github_events;
修改表
Citus 會自動傳播多種 DDL 語句，這意味著修改協調器節點上的分布式表也會更新工作器上的分片。其他 DDL 語句需要手動傳播，并且禁止某些其他語句，例如那些會修改分布列的語句。嘗試運行不符合自動傳播條件的 DDL 將引發錯誤并使協調節點上的表保持不變。
以下是傳播的 DDL 語句類別的參考。請注意，可以使用配置參數啟用或禁用自動傳播。
- 配置參數
https://docs.citusdata.com/en/v11.0-beta/develop/api_guc.html#enable-ddl-prop
添加/修改列
Citus 會自動傳播大多數 ALTER TABLE 命令。添加列或更改其默認值的工作方式與在單機 PostgreSQL 數據庫中一樣：
- 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;
對現有列進行重大更改(例如重命名或更改其數據類型)也可以。但是，不能更改分布列的數據類型。此列確定表數據如何在 Citus 集群中分布，修改其數據類型將需要移動數據。
- 分布列
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 可以讓您繼續享受關系數據庫的安全性，包括數據庫約束(請參閱 PostgreSQL 文檔)。由于分布式系統的性質，Citus 不會交叉引用工作節點之間的唯一性約束或引用完整性。
- 數據庫約束
https://www.postgresql.org/docs/current/static/ddl-constraints.html
在這些情況下可能會創建外鍵：
- 在兩個本地(非分布式)表之間
- 在兩個引用表之間
- 在引用表和本地表之間(默認啟用，通過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(引用本地)。
主鍵和唯一性約束必須包括分布列。將它們添加到非分布列將產生錯誤(請參閱無法創建唯一性約束)。
- 無法創建唯一性約束
https://docs.citusdata.com/en/v11.0-beta/reference/common_errors.html#non-distribution-uniqueness
這個例子展示了如何在分布式表上創建主鍵和外鍵：
--
-- 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 約束和外鍵支持此功能。
例如，考慮將用戶配置文件存儲在引用表中的應用程序。
- 引用表
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 通常不允許我們添加對現有行失敗的 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 的信息。
- 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
添加索引需要寫鎖，這在多租戶“記錄系統”中可能是不可取的。為了最大限度地減少應用程序停機時間，請改為同時創建索引。與標準索引構建相比，此方法需要更多的總工作量，并且需要更長的時間才能完成。但是，由于它允許在構建索引時繼續正常操作，因此此方法對于在生產環境中添加新索引很有用。
-- 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
