PG数据湖&列存插件新贵 - pg_mooncake
digoal约 6 分钟参考文档点击文末阅读原文打开; 推荐《最好的PostgreSQL学习镜像》;
PG数据湖&列存插件新贵 - pg_mooncake
背景
pg_duckdb,pg_arrow后, 又一PostgreSQL 数据湖架构,列存储新贵 - pg_mooncake. https://github.com/Mooncake-Labs/pg_mooncake
pg_mooncake是一个PostgreSQL扩展，通过DuckDB执行本机列存储表。列存储表作为iceberg或Delta Lake表存储在对象存储中。
该扩展由Mooncake Labs维护，并已在Neon上提供。
-- Create a columnstore table in PostgreSQL
CREATE TABLE user_activity (....) USING columnstore; -- Insert data into a columnstore table
INSERT INTO user_activity VALUES ....;
-- Query a columnstore table in PostgreSQL
SELECT * FROM user_activity LIMIT 5;
主要特点
表语义支持：pg_mooncake列存储表支持事务和批量插入、更新和删除，以及与常规PostgreSQL表的JOIN操作。
DuckDB执行器：运行分析查询的速度比常规PostgreSQL表快1000倍，性能与Parquet上的DuckDB相似。
iceberg或Delta Lake存储：列存储表存储为带有iceberg或Delta Lake元数据的Parquet文件，允许外部引擎（例如Snowflake、DuckDB、Pandas、Polars）将它们作为本地表进行查询。
使用场景举例
1、在PostgreSQL中实时分析数据，在列存储表上运行事务，无需同步Parquet文件的情况下进行数据库里最新数据分析。
2、将PostgreSQL表写入Lake或Lakehouse，使PostgreSQL可访问实例以外的数据, 并和本机表进行关联访问，无需复杂的ETL、CDC或文件拼接。
3、在PostgreSQL中本地查询和更新现有的Lakehouse表（即将推出）连接现有的Lakehouse目录，并在PostgreSQL中将它们直接作为列存储表公开。
用法举例
1.（可选）添加S3秘密和桶
这将是您的列存储表的存储位置。如果没有指定S3配置，这些表将在您的本地文件系统中创建。
注意：如果您在Neon上使用pg_mooncake，您现在需要自带S3桶。我们正在努力改进这个DX。
SELECT mooncake.create_secret('<name>', 'S3', '<key_id>', '<secret>', '{"REGION": "<s3-region>"}'); SET mooncake.default_bucket = 's3://<bucket>';
SET mooncake.enable_local_cache = false; -- (if you are using Neon)
2.创建列存储表
创建一个列存储表：
CREATE TABLE user_activity(
user_id BIGINT,
activity_type TEXT,
activity_timestamp TIMESTAMP,
duration INT
) USING columnstore;
插入数据：
INSERT INTO user_activity VALUES
(1, 'login', '2024-01-01 08:00:00', 120),
(2, 'page_view', '2024-01-01 08:05:00', 30),
(3, 'logout', '2024-01-01 08:30:00', 60),
(4, 'error', '2024-01-01 08:13:00', 60); SELECT * FROM user_activity;
您也可以直接从镶木地板文件中插入数据（也可以来自S3）：
COPY user_activity FROM '<parquet_file>'
更新和删除行：
UPDATE user_activity
SET activity_timestamp = '2024-01-01 09:50:00'
WHERE user_id = 3 AND activity_type = 'logout'; DELETE FROM user_activity
WHERE user_id = 4 AND activity_type = 'error';
SELECT * from user_activity;
运行事务：
BEGIN; INSERT INTO user_activity VALUES
(5, 'login', '2024-01-02 10:00:00', 200),
(6, 'login', '2024-01-02 10:30:00', 90);
ROLLBACK;
SELECT * FROM user_activity;
3.在PostgreSQL中运行分析查询
运行聚合和分组：
SELECT
user_id,
activity_type,
SUM(duration) AS total_duration,
COUNT(*) AS activity_count
FROM
user_activity
GROUP BY
user_id, activity_type
ORDER BY
user_id, activity_type;
与常规的PostgreSQL表进行JOIN：
CREATE TABLE users (
user_id BIGINT,
username TEXT,
email TEXT
); INSERT INTO users VALUES
(1,'alice', '[email protected]'),
(2, 'bob', '[email protected]'),
(3, 'charlie', '[email protected]');
SELECT * FROM users u
JOIN user_activity a ON u.user_id = a.user_id;
4.查询PostgresSQL之外的列存储表
找到创建列存储表的路径：
SELECT * FROM mooncake.columnstore_tables;
直接从此表中创建一个Polars数据框架：
import polars as pl
from deltalake import DeltaTable delta_table_path = '<path>'
delta_table = DeltaTable(delta_table_path)
df = pl.DataFrame(delta_table.to_pyarrow_table())
更多请参考: https://github.com/Mooncake-Labs/pg_mooncake
今日荐书
彩蛋:国产数据库周边生态
当然一款数据库要流行起来, 除了自己要强大, 还离不开生态. 用好周边生态工具, 管理水平战胜90%老司机!!! 下面简单介绍一下国产数据库周边生态.
1、管控软件
鸣嵩(前阿里云数据库总经理 / 研究员)等大佬们创业创办的云猿生, 核心产品是KubeBlocks. 他们的理念是让管理数据库和搭积木一样简单, 如果你要管理很多套并且种类(OLTP\OLAP\NoSQL\KV\TS\MQ等)很多的数据库产品, 推荐首选.
https://github.com/apecloud/kubeblocks
PG中文社区核心委员唐成老师的公司乘数开源的Clup, 专用管理PostgreSQL和PolarDB的集群管理软件, 如果你要管理很多套数据库, 推荐选择. 并且Clup还提供了企业版、自研的连接池、分布式存储、一体机、备份平台等, 是企业用户推荐之选.
https://www.csudata.com/
若航开源的pigsty, 集成了300多个PG插件的PG集群和PolarDB集群管理软件, 如果你要管理很多套PG或PolarDB数据库, 且对插件有特别多的需求, 推荐选择.
https://pigsty.cc/zh/
2、审计监控诊断优化
翟总(曾经是我背后的男人)到海信聚好看后研发的 DBdoctor, 采用ebpf技术, 在对数据库几乎没有影响的情况下实时监控数据库和服务器的各项指标, 发现和诊断问题根因非常方便.
https://www.dbdoctor.cn/
天舟老哥的核心产品Bytebase 是位于您和数据库之间的中间件。它是数据库 DevOps 的 GitLab/GitHub，专为开发人员、DBA 和平台工程师打造。
https://bytebase.cc/docs/introduction/what-is-bytebase/
PawSQL, SQL优化和诊断产品.
D-Smart, Oracle老前辈白老大出品, 专注企业级市场, 将业界顶级DBA经验的产品化作品, 产品功能包括数据库监控、诊断、优化等.
https://www.modb.pro/db/567140
3、国产数据库IDE
IDE是开发者的必备工具，例如社区有pgAdmin, 国产IDE则可以看看老程序猿达刚老师的DeskUI:
https://www.deskui.com
4、数据同步&迁移&备份恢复
NineData, 老领导出去创业做的产品, 产品涵盖了数据同步、迁移、备份、比对、devops、chatDBA等.
https://www.ninedata.cloud/home
DSG, 非常老牌的数据库同步迁移企业级产品, 支持各种数据库的异构和同构迁移, 用他们的话说, 没有dsg搞不定的迁移, 比goldengate还牛.
https://www.dsgdata.com/
公开课
如果你对PolarDB学习感兴趣可以阅读这个公开课系列:
除了PolarDB还非常值得关注的几款PG栈国产数据库:
HaloDB(基于PG兼容PostgreSQL、Oracle、MySQL. http://www.halodbtech.com/ )、 IvorySQL(基于开源PG兼容PG、Oracle. https://www.ivorysql.org/zh-cn/ )、 ProtonBase(云原生分布式数仓. https://protonbase.com/ )、 成都文武数据库（https://ww-it.cn）
参考文档点击阅读原文获得
感谢关注我的github (https://github.com/digoal/blog) 及视频号: