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

http://www.zzvips.com/uploads/allimg/1xefrfs3rgy.html data-id="h6e90be6-V4KV8WZM" id="h6e90be6-V4KV8WZM">引用表

上述方法将表分布到多个水平分片中，但另一种可能是将表分布到单个分片中并将分片复制到每个工作节点。以这种方式分布的表称为引用表。它们用于存储集群中多个节点需要频繁访问的数据。

• 引用表的常见候选包括：
• 较小的表需要与较大的分布式表连接。
• 多租户应用程序中缺少租户 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');

有关在多租户应用程序中使用引用表的另一个示例，请参阅在租户之间共享数据。

• 在租户之间共享数据

http://www.zzvips.com/uploads/allimg/4j02vsdubug.html data-id="h6e90be6-8ndjRLMT" id="h6e90be6-8ndjRLMT">分布协调器数据

如果将现有的 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

http://www.zzvips.com/uploads/allimg/jakbrzilrui.html data-id="pd157317-NP7DwuG0">从外部数据库迁移数据时，例如从 Amazon RDS 迁移到 Citus Cloud，首先通过 create_distributed_table 创建 Citus 分布式表，然后将数据复制到表中。复制到分布式表中可以避免协调节点上的空间不足。

共置表

共置是一种策略性地划分数据的做法，将相关信息保存在同一台机器上以实现高效的关系操作，同时利用整个数据集的水平可扩展性。有关更多信息和示例，请参阅表共置。

• 表共置

http://www.zzvips.com/uploads/allimg/zjt25yugif5.html data-id="pd157317-lI3oNRCo">表在组中是共置(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 组将提高分片再平衡性能，因为同一组中的分片必须一起移动。

• 分片再平衡

http://www.zzvips.com/uploads/allimg/cjoi0intqv0.html data-id="pd157317-tMaJgyTh">当表确实相关时(例如，当它们将被连接时)，显式地将它们放在一起是有意义的。适当的 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

http://www.zzvips.com/uploads/allimg/ght2pknkcno.html data-id="ucd67dc5-Fu0KFQWK">

http://www.zzvips.com/uploads/allimg/gt5ufb10e3k.html data-id="h6e90be6-9QFPmrPL" id="h6e90be6-9QFPmrPL">从 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

http://www.zzvips.com/uploads/allimg/diypqtnxivz.html data-id="pd157317-gRNxdKnS">该函数要求表以相同的方法、列类型、分片数和复制方法分布。它不会重新分片或物理移动数据，它只是更新 Citus 元数据。

删除表

您可以使用标准的 PostgreSQL DROP TABLE 命令来删除您的分布式表。与常规表一样，DROP TABLE 删除目标表存在的任何索引、规则、触发器和约束。此外，它还会删除工作节点上的分片并清理它们的元数据。

DROP TABLE github_events;

修改表

Citus 会自动传播多种 DDL 语句，这意味着修改协调器节点上的分布式表也会更新工作器上的分片。其他 DDL 语句需要手动传播，并且禁止某些其他语句，例如那些会修改分布列的语句。尝试运行不符合自动传播条件的 DDL 将引发错误并使协调节点上的表保持不变。

以下是传播的 DDL 语句类别的参考。请注意，可以使用配置参数启用或禁用自动传播。

• 配置参数

http://www.zzvips.com/uploads/allimg/4bvirv4dib3.html data-id="h6e90be6-g73aCnZ1" id="h6e90be6-g73aCnZ1">添加/修改列

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 集群中分布，修改其数据类型将需要移动数据。

• 分布列

http://www.zzvips.com/uploads/allimg/3xvqnl00ue5.html data-id="pd157317-fmSUcyq9">尝试这样做会导致错误：

-- 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
*/

作为一种解决方法，您可以考虑更改分布列，更新它，然后再改回来。

• 更改分布列

http://www.zzvips.com/uploads/allimg/14ft0d0wd5m.html data-id="h6e90be6-7Cl8jQ2n" id="h6e90be6-7Cl8jQ2n">添加/删除约束

使用 Citus 可以让您继续享受关系数据库的安全性，包括数据库约束(请参阅 PostgreSQL 文档)。由于分布式系统的性质，Citus 不会交叉引用工作节点之间的唯一性约束或引用完整性。

• 数据库约束

https://www.postgresql.org/docs/current/static/ddl-constraints.html

在这些情况下可能会创建外键：

• 在两个本地(非分布式)表之间
• 在两个引用表之间
• 在引用表和本地表之间(默认启用，通过citus.enable_local_reference_table_foreign_keys (boolean))

http://www.zzvips.com/uploads/allimg/whmcw54vy5o.html data-id="ucd67dc5-Dj9kmELy">

http://www.zzvips.com/uploads/allimg/2iti25teq4y.html data-id="ucd67dc5-ClRI0uHs">

http://www.zzvips.com/uploads/allimg/qgeyrj5ecsm.html data-id="pd157317-CCIAscN3">不支持从引用表到分布式表的外键。

Citus 支持从本地到引用表的所有外键引用操作，但不支持反向支持 ON DELETE/UPDATE CASCADE(引用本地)。

主键和唯一性约束必须包括分布列。将它们添加到非分布列将产生错误(请参阅无法创建唯一性约束)。

• 无法创建唯一性约束

http://www.zzvips.com/uploads/allimg/5v2bgw2dusd.html data-id="pd157317-8xogIRdp">这个例子展示了如何在分布式表上创建主键和外键：

--
-- 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 约束和外键支持此功能。

例如，考虑将用户配置文件存储在引用表中的应用程序。

• 引用表

http://www.zzvips.com/uploads/allimg/qgeyrj5ecsm.htmlre class="cm-s-default" style="color: rgb(89, 89, 89); margin: 0px; padding: 0px; background: none 0% 0% / auto repeat scroll padding-box border-box rgba(0, 0, 0, 0);">-- 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 命令不会自动传播，但是，您可以手动传播更改。请参阅手动查询传播。

• 手动查询传播

http://www.zzvips.com/uploads/allimg/lhmphe00uaz.html