内容摘要：准备工作这里假设，你已经在 k8s 上部署好了基于 Citus 扩展的分布式 PostgreSQL 集群。查看 Citus 集群(kubectl get po -n citus)，1 个 Coordi

准备工作

这里假设，上快速测试C式你已经在 k8s 上部署好了基于 Citus 扩展的分布分布式 PostgreSQL 集群。

查看 Citus 集群(kubectl get po -n citus)，上快速测试C式1 个 Coordinator(协调器) 节点 + 3 个 Worker(工作器) 节点。分布

复制NAME READY STATUS RESTARTS AGE

citus-coordinator-0 2/2 Running 0 3

h55m

citus-worker-0 2/2 Running 0 22

m

citus-worker-1 2/2 Running 0 21

m

citus-worker-2 2/2 Running 0 21m1.2.3.4.5.

进入 coordinator 节点(kubectl -n citus exec -it citus-coordinator-0 -- bash)，上快速测试C式查看活动的分布 worker 节点(psql host=citus-coordinator user=postgres -c "SELECT * FROM citus_get_active_worker_nodes();")。

复制 node_name |

node_port

-----------------------------------------------------+----------- citus-worker-1.citus-worker.citus.svc.cluster.local | 6432 citus-worker-2.citus-worker.citus.svc.cluster.local | 6432 citus-worker-0.citus-worker.citus.svc.cluster.local | 6432(3 rows)1.2.3.4.5.6.

一旦拥有 Citus 集群，上快速测试C式就可以开始创建分布式表、分布引用表和使用列存储。上快速测试C式

创建分布式表

create_distributed_table 将在本地或工作节点之间透明地切分您的分布表。

进入命令行工具：psql host=citus-coordinator user=postgres。上快速测试C式

建表 复制CREATE TABLE events ( device_id bigint,分布 event_id bigserial, event_time timestamptz default now(), data jsonb not null, PRIMARY KEY (device_id, event_id));-- 将事件表分布在本地或工作节点上的分片上SELECT create_distributed_table(events, device_id);1.2.3.4.5.6.7.8.9.10.

执行此操作后，对特定设备 ID 的上快速测试C式查询将有效地路由到单个工作节点，而跨设备 ID 的分布查询将在集群中并行化。

插入一些事件 复制INSERT INTO events (device_id,上快速测试C式 data)SELECT s % 100, ({"measurement":||random()||})::jsonb FROM generate_series(1,1000000) s;-- INSERT 0 10000001.2.3. 获取设备 1 的最后 3 个事件，路由到单个节点

命令行开启计时：postgres=# \timing。

复制SELECT * FROM events WHERE device_id = 1 ORDER BY event_time DESC, event_id DESC LIMIT 3;1. 复制 device_id | event_id | event_time |

data

-----------+----------+-------------------------------+------------------------------------- 1 | 999901 | 2022-03-24 02:30:50.205478+00 | {"measurement": 0.8822990134507691} 1 | 999801 | 2022-03-24 02:30:50.205478+00 | {"measurement": 0.5239176115816448} 1 | 999701 | 2022-03-24 02:30:50.205478+00 | {"measurement": 0.9900647926398349}(3 rows)Time: 4.779 ms1.2.3.4.5.6.7. 解释跨分片并行化的查询的计划，以下显示了查询其中一个分片的计划以及如何完成跨分片的聚合

执行 sql 语句：

复制EXPLAIN (VERBOSE ON) SELECT count(*) FROM events;1.

复制 QUERY PLAN

--------------------------------------------------------------------------------------------------------- Aggregate (cost=250.00..250.02 rows=1 width=8) Output: COALESCE((pg_catalog.sum(remote_scan.count))::bigint, 0::bigint) -> Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=100000 width=8) Output: remote_scan.count Task Count: 32 Tasks Shown: One of 32 ->

Task

Query: SELECT count(*) AS count FROM public.events_102008 events WHERE true Node: host=citus-worker-0.citus-worker.citus.svc.cluster.local port=6432 dbname=

postgres

-> Aggregate (cost=725.00..725.01 rows=1 width=8) Output: count(*) -> Seq Scan on public.events_102008 events (cost=0.00..650.00 rows=30000 width=0) Output: device_id, event_id, event_time,

data

(13 rows)Time: 5.427 ms1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.

使用共置创建分布式表

具有相同分布列的b2b供应网分布式表可以位于同一位置，以实现分布式表之间的高性能分布式连接(join)和外键。默认情况下，分布式表将根据分布列的类型位于同一位置，但您可以使用 create_distributed_table 中的 colocate_with 参数显式定义同一位置。

建表 复制CREATE TABLE devices ( device_id bigint primary key, device_name text, device_type_id int);CREATE INDEX ON devices (device_type_id);-- 将设备表与事件表放在一起SELECT create_distributed_table(devices, device_id, colocate_with := events);1.2.3.4.5.6.7.8.9. 插入设备元数据 复制INSERT INTO devices (device_id, device_name, device_type_id)SELECT s, device-||s, 55 FROM generate_series(0, 99) s;1.2. 可选：确保应用程序只能插入已知设备的事件 复制ALTER TABLE

events ADD CONSTRAINT device_id_fk

FOREIGN KEY (device_id) REFERENCES devices (device_id);1.2. 获得跨分片并行的所有类型 55 设备的平均测量值 复制SELECT avg((data->>measurement)::double precision)FROM events JOIN devices USING (device_id)WHERE device_type_id = 55;1.2.3.

复制 avg

-------------------- 0.4997412230952178(1 row)Time: 122.548 ms1.2.3.4.5.

Co-location 还可以帮助您扩展 INSERT..SELECT、存储过程和分布式事务。

INSERT..SELECT：

https://docs.citusdata.com/en/stable/articles/aggregation.html。

存储过程：

https://www.citusdata.com/blog/2020/11/21/making-postgres-stored-procedures-9x-faster-in-citus/。

分布式事务：

https://www.citusdata.com/blog/2017/06/02/scaling-complex-sql-transactions/。

创建引用表

当您需要不包含分布列的快速 join 或外键时，您可以使用 create_reference_table 在集群中的所有节点之间复制表。

建表 复制CREATE TABLE device_types ( device_type_id int primary key, device_type_name text not null

unique

);1.2.3.4. 跨所有节点复制表以在任何列上启用外键和 join 复制SELECT create_reference_table(device_types);1. 插入设备类型 复制INSERT INTO device_types (device_type_id, device_type_name) VALUES (55, laptop);1. 可选：确保应用程序只能插入已知类型的设备 复制ALTER TABLE

devices ADD CONSTRAINT device_type_fk

FOREIGN KEY (device_type_id) REFERENCES device_types (device_type_id);1.2. 获取类型名称以笔记本电脑开头的设备的最后 3 个事件，跨分片并行 复制SELECT device_id, event_time, data->>measurement AS value, device_name,

device_type_name

FROM events JOIN devices USING (device_id) JOIN device_types USING (device_type_id)WHERE device_type_name LIKE laptop% ORDER BY event_time DESC LIMIT 3;1.2.3. 复制device_id | event_time | value | device_name |

device_type_name

-----------+-------------------------------+---------------------+-------------+------------------ 31 | 2022-03-24 02:30:50.205478+00 | 0.9994211581289107 | device-31 |

laptop

31 | 2022-03-24 02:30:50.205478+00 | 0.13771543211483106 | device-31 |

laptop

88 | 2022-03-24 02:30:50.205478+00 | 0.5585740912470349 | device-88 |

laptop

(3 rows)Time: 96.537 ms1.2.3.4.5.6.7.

引用表使您能够扩展复杂的数据模型并充分利用关系数据库的功能。

使用列式存储创建表

要在 PostgreSQL 数据库中使用列式存储，源码库您只需将 USING columnar 添加到 CREATE TABLE 语句中，您的数据将使用列式访问方法自动压缩。

建表 复制CREATE TABLE events_columnar ( device_id bigint, event_id bigserial, event_time timestamptz default now(), data jsonb not null)USING columnar;1.2.3.4.5.6.7. 插入一些数据 复制INSERT INTO events_columnar (device_id, data)SELECT d, {"hello":"columnar"} FROM generate_series(1,10000000) d;1.2. 创建一个基于行的表进行比较 复制CREATE TABLE events_row AS SELECT * FROM events_columnar;1. 查看表大小

注意 events_row(806 MB) 与 events_columnar(25 MB) 的对比。压缩了几十倍，效果非常的惊人，大大节省了存储空间。

复制postgres=# \d+

List of relations

Schema | Name | Type | Owner | Persistence | Access method | Size |

Description

--------+------------------------------+----------+----------+-------------+---------------+------------+------------- public | citus_tables | view | postgres | permanent | | 0 bytes | public | device_types | table | postgres | permanent | heap | 8192 bytes | public | devices | table | postgres | permanent | heap | 8192 bytes | public | events | table | postgres | permanent | heap | 8192 bytes | public | events_columnar | table | postgres | permanent | columnar | 25 MB | public | events_columnar_event_id_seq | sequence | postgres | permanent | | 8192 bytes | public | events_event_id_seq | sequence | postgres | permanent | | 8192 bytes | public | events_row | table | postgres | permanent | heap | 806 MB |(8 rows)1.2.3.4.5.6.7.8.9.10.11.12.13.

您可以单独使用列存储，也可以在分布式表中使用，以结合压缩和分布式查询引擎的优势。

使用列式存储时，您应该只使用 COPY 或 INSERT..SELECT 批量加载数据以实现良好的压缩。柱状表目前不支持更新、删除和外键。但是，您可以使用分区表，其中较新的分区使用基于行的存储，而较旧的分区使用列存储进行压缩。

云南idc服务商