Citus 分布式 PostgreSQL 使用指南

原创于 2025-09-22 16:56:37 发布 · 804 阅读

12 ·

CC 4.0 BY-SA版权

文章标签：

#分布式 #postgresql #数据库

PG 专栏收录该内容

344 篇文章

订阅专栏

Citus 分布式 PostgreSQL 使用指南

环境中的 Citus 配置

当前环境信息

PostgreSQL 实例: postgres-025970ca-0-0
Citus 版本: 10.2.5
shared_preload_libraries: citus,pg_stat_statements,pgaudit,pg_cron
重要: Citus 必须在 shared_preload_libraries 中首先加载

错误原因

“FATAL: Citus has to be loaded first” 错误通常因为:

shared_preload_libraries 配置中 Citus 未列在第一位
配置文件未正确加载
PostgreSQL 服务未重启以应用配置

Citus 基本使用示例

1. 启用 Citus 扩展

-- 创建 Citus 扩展
CREATE EXTENSION IF NOT EXISTS citus;

-- 验证扩展已安装
SELECT * FROM pg_extension WHERE extname = 'citus';

2. 配置 Citus 集群

配置协调节点 (Coordinator)

-- 在协调节点上添加工作节点
SELECT master_add_node('worker1-hostname', 5432);
SELECT master_add_node('worker2-hostname', 5432);

-- 查看所有工作节点
SELECT * FROM master_get_active_worker_nodes();

3. 创建分布式表

-- 创建示例表
CREATE TABLE companies (
    id bigint NOT NULL,
    name text NOT NULL,
    image_url text,
    created_at timestamp NOT NULL,
    updated_at timestamp NOT NULL
);

CREATE TABLE campaigns (
    id bigint NOT NULL,
    company_id bigint NOT NULL,
    name text NOT NULL,
    cost_model text NOT NULL,
    state text NOT NULL,
    monthly_budget bigint,
    blacklisted_site_urls text[],
    created_at timestamp NOT NULL,
    updated_at timestamp NOT NULL
);

CREATE TABLE ads (
    id bigint NOT NULL,
    company_id bigint NOT NULL,
    campaign_id bigint NOT NULL,
    name text NOT NULL,
    image_url text,
    target_url text,
    impressions_count bigint DEFAULT 0,
    clicks_count bigint DEFAULT 0,
    created_at timestamp NOT NULL,
    updated_at timestamp NOT NULL
);

-- 将表转换为分布式表
-- 使用 company_id 作为分片键，确保相关数据在同一节点
SELECT create_distributed_table('companies', 'id');
SELECT create_distributed_table('campaigns', 'company_id');
SELECT create_distributed_table('ads', 'company_id');

-- 创建参考表（在所有节点上复制的小表）
CREATE TABLE ad_types (
    id bigint PRIMARY KEY,
    name text NOT NULL
);
SELECT create_reference_table('ad_types');

4. 查询分布式表

-- 简单查询（自动路由到正确的分片）
SELECT name, monthly_budget
FROM campaigns
WHERE company_id = 5;

-- 跨分片聚合查询
SELECT company_id, SUM(clicks_count) as total_clicks
FROM ads
GROUP BY company_id
ORDER BY total_clicks DESC
LIMIT 10;

-- 连接查询（同位连接，高效）
SELECT c.name as company_name,
       COUNT(DISTINCT a.id) as ad_count,
       SUM(a.clicks_count) as total_clicks
FROM companies c
JOIN ads a ON c.id = a.company_id
WHERE c.created_at >= '2023-01-01'
GROUP BY c.name;

5. 分片管理

-- 查看表的分布信息
SELECT * FROM pg_dist_partition WHERE logicalrelid = 'campaigns'::regclass;

-- 查看分片分布
SELECT
    logicalrelid::regclass AS table_name,
    shardid,
    nodename,
    nodeport
FROM pg_dist_shard_placement p
JOIN pg_dist_shard s ON p.shardid = s.shardid
WHERE logicalrelid = 'campaigns'::regclass
ORDER BY shardid;

-- 查看分片大小
SELECT
    tablename,
    pg_size_pretty(sum(pg_total_relation_size(tablename::regclass))) as total_size
FROM pg_tables
WHERE tablename LIKE 'campaigns_%'
GROUP BY tablename;

6. 重新平衡分片

-- 重新平衡表的分片（当添加新节点后）
SELECT rebalance_table_shards('campaigns');

-- 查看重新平衡进度
SELECT * FROM get_rebalance_progress();

高级功能

1. 列式存储（Columnar Storage）

-- 创建列式存储表
CREATE TABLE events (
    event_id bigint,
    event_type text,
    user_id bigint,
    event_time timestamp,
    payload jsonb
) USING columnar;

-- 将现有表转换为列式存储
SELECT alter_table_set_access_method('events', 'columnar');

2. 分布式事务

BEGIN;
    -- 这些操作将在一个分布式事务中执行
    UPDATE campaigns SET monthly_budget = 50000 WHERE company_id = 3;
    INSERT INTO ads (company_id, campaign_id, name) VALUES (3, 10, 'New Ad');
COMMIT;

3. 实时数据聚合

-- 创建实时聚合视图
CREATE VIEW campaign_analytics AS
SELECT
    company_id,
    COUNT(*) as campaign_count,
    AVG(monthly_budget) as avg_budget,
    SUM(monthly_budget) as total_budget
FROM campaigns
GROUP BY company_id;

-- 使用物化视图加速查询
CREATE MATERIALIZED VIEW campaign_summary AS
SELECT
    c.company_id,
    c.name as campaign_name,
    COUNT(a.id) as ad_count,
    SUM(a.impressions_count) as total_impressions
FROM campaigns c
LEFT JOIN ads a ON c.id = a.campaign_id
GROUP BY c.company_id, c.name;

性能优化建议

1. 选择正确的分片键

选择高基数列（如 user_id, tenant_id）
确保相关表使用相同的分片键
避免使用时间戳作为分片键

2. 配置优化

-- 调整并行度
SET citus.max_adaptive_executor_pool_size = 32;

-- 启用二进制协议（提高性能）
SET citus.enable_binary_protocol = true;

-- 启用本地执行优化
SET citus.enable_local_execution = true;

3. 监控查询

-- 查看分布式查询执行计划
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM campaigns WHERE company_id = 100;

-- 启用查询日志
SET citus.log_remote_commands = true;

常见问题解决

1. “Citus has to be loaded first” 错误

# 修改 postgresql.conf
shared_preload_libraries = 'citus,pg_stat_statements,pgaudit'

# 重启 PostgreSQL
pg_ctl restart

2. 添加工作节点失败

-- 检查网络连接
SELECT * FROM master_get_active_worker_nodes();

-- 移除有问题的节点
SELECT master_remove_node('worker-hostname', 5432);

-- 重新添加节点
SELECT master_add_node('worker-hostname', 5432);

3. 查询性能问题

-- 使用 EXPLAIN 分析查询
EXPLAIN (ANALYZE, VERBOSE) SELECT ...;

-- 检查分片分布是否均匀
SELECT
    nodename,
    count(*) as shard_count
FROM pg_dist_shard_placement
GROUP BY nodename;