你当前正在访问 Microsoft Azure Global Edition 技术文档网站。如果需要访问由世纪互联运营的 Microsoft Azure 中国技术文档网站，请访问 https://docs.azure.cn。

在 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 配置值进行复制。在工作器上创建的分片副本与协调器上的表具有相同的表架构、索引和约束定义。创建副本后，函数会将所有分布式元数据保存在协调器中。

为每个创建的分片分配一个唯一的分片 ID，其所有副本都具有相同的分片 ID。分片在工作器节点上表示为名为“tablename_shardid”的常规 PostgreSQL 表，其中 tablename 是分布式表的名称，而分片 ID 是分配的唯一 ID。可以连接到工作器 postgres 实例，以查看或运行单个分片上的命令。

现在已准备好将数据插入分布式表，并对其运行查询。还可以在表和分片 DDL 引用中了解有关此部分中使用的 UDF 的详细信息。

引用表

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

引用表的常见候选项包括：

需要与较大的分布式表联接的小型表。
多租户应用中缺少租户 ID 列或不与租户关联的表。（或者，在迁移期间，甚至与租户关联的某些表也是如此。）
需要跨多个列的唯一约束并足够小的表。

例如，假设多租户电子商务网站需要为其任何商店中的交易计算销售税。税务信息并不特定于任何租户。将其放在共享表中是有道理的。以US为中心的引用表可能如下所示：

-- 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 函数适用于空表和非空表，对于后者，它在整个群集中自动分发表行。可以通过消息“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...
 create_distributed_table
 --------------------------

 (1 row)

在迁移数据时，对表的写入将被阻止，并且在函数提交后，将挂起写入作为分布式查询处理。（如果该函数失败，则这些查询会再次成为本地查询。）读取可以继续正常运行，并且会在该函数提交后成为分布式查询。

在分发表 A 和表 B（其中 A 具有 B 的外键）时，首先分发 key destination 表 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 语句。尝试运行无法自动传播的 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);

Not-null 约束可以应用于任何列（分布式或非分布式），因为它们不需要在工作节点之间进行查找。

ALTER TABLE ads ALTER COLUMN image_url SET NOT NULL;

使用 NOT VALID 约束

在某些情况下，对新行强制实施约束可能会很有用，同时允许现有的非一致性行保持不变。 Azure Cosmos DB for PostgreSQL 使用 PostgreSQL 的“无效”约束名称，支持 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');

最后，假设有几个non-address进入了表中。

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 来禁用每个节点的并行度。同一事务中的数据加载速度可能会变慢。

后续步骤

有用的诊断查询