Citus|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 tableCREATE TABLE states (
code char(2) PRIMARY KEY,
full_name text NOT NULL,
general_sales_tax numeric(4,3)
);
-- distribute it to all workersSELECT 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 groupSELECT 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 tablesSELECT 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 columnALTER TABLE products ADD COLUMN description text;
-- Changing default valueALTER 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 integerALTER 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 tablesSELECT 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 moduleCREATE 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 indexCREATE INDEX clicked_at_idx ON clicks USING BRIN (clicked_at);
-- Removing an indexDROP INDEX clicked_at_idx;
- 添加和删除索引
- https://www.postgresql.org/docs/current/static/sql-createindex.html
-- Adding an index without locking table writesCREATE 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
- 在 Kubernetes 上快速测试 Citus 分布式 PostgreSQL 集群(分布式表,共置,引用表,列存储)
推荐阅读
- 华为云GaussDB(用分布式计算和存算分离的集合力量打造云原生数据库)
- 分布式|面试官(ZooKeeper分布式锁实现,你了解了吗())
- java|面试官(ZK(ZooKeeper)分布式锁实现,你了解了吗())
- 搭建分布式事务组件|搭建分布式事务组件 seata 的Server 端和Client 端详解(小白都能看懂)
- Netty分布式客户端处理接入事件handle源码解析
- 分布式|浅谈分布式锁
- 从架构思维角度分析分布式锁方案
- 项目实战!接入分布式定时任务框架
- Mysql的分布式(XA)真面目
- redis分布式Jedis类型转换的异常深入研究