pg_mooncake
PostgreSQL列式存储表
Module:
Categories:
扩展总览
PIGSTY 第三方扩展: pg_mooncake
: PostgreSQL列式存储表
基本信息
- 扩展编号: 2440
- 扩展名称:
pg_mooncake
- 标准包名:
pg_mooncake
- 扩展类目:
OLAP
- 开源协议: MIT
- 官方网站: https://github.com/Mooncake-Labs/pg_mooncake
- 编程语言: C++
- 其他标签:
duckdb
- 备注信息: 无
元数据
- 默认版本: 0.1.2
- PG大版本:
17
,16
,15
,14
- 动态加载: 无需动态加载
- 需要DDL: 需要执行
CREATE EXTENSION
DDL - 可重定位: 可以重定位安装至其他模式下
- 信任程度: 未受信任,创建扩展需要超级用户权限
- 所需模式: 无
- 所需扩展: 无
软件包
- RPM仓库:PIGSTY
- RPM包名:
pg_mooncake_$v*
- RPM版本:
0.1.2
- RPM依赖:无
- DEB仓库:PIGSTY
- DEB包名:
postgresql-$v-pg-mooncake
- DEB版本:
0.1.2
- DEB依赖:无
最新版本
扩展安装
使用 pig
命令行工具安装 pg_mooncake
扩展:
pig ext install pg_mooncake
使用 Pigsty剧本 安装 pg_mooncake 扩展:
./pgsql.yml -t pg_extension -e '{"pg_extensions": ["pg_mooncake"]}' # -l <集群名>
从 YUM仓库 手工安装 pg_mooncake
RPM 包:
dnf install pg_mooncake_17*;
dnf install pg_mooncake_16*;
dnf install pg_mooncake_15*;
dnf install pg_mooncake_14*;
从 APT仓库 手工安装 pg_mooncake
DEB 包:
apt install postgresql-17-pg-mooncake;
apt install postgresql-16-pg-mooncake;
apt install postgresql-15-pg-mooncake;
apt install postgresql-14-pg-mooncake;
使用以下 SQL 命令在已经安装此扩展插件的 PG 集群上 启用 pg_mooncake
扩展:
CREATE EXTENSION pg_mooncake;
使用方法
THIS EXTENSION IS CONFLICT WITH pg_duckdb & duckdb_fdw, if it is under maintained, we may remove this extension in the future
Beware that this package is conflict with the official pg_duckdb
extension due to use the same libduckdb.so
under same path.
And this function will block the duckdb_fdw
functioning.
-- 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;
Example
Use mooncake with S3:
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)
Use mooncake with local columnstore:
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;
Run analytic queries
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;
The explain result could be:
postgres@u22:5432/postgres=# explain 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;
QUERY PLAN
------------------------------------------------------------
Custom Scan (DuckDBScan) (cost=0.00..0.00 rows=0 width=0)
DuckDB Execution Plan:
┌───────────────────────────┐
│ PROJECTION │
│ ──────────────────── │
│__internal_decompress_integ│
│ ral_bigint(#0, 1) │
│ #1 │
│ #2 │
│ #3 │
│ │
│ ~2 Rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ ORDER_BY │
│ ──────────────────── │
│ user_activity.user_id ASC │
│ user_activity │
│ .activity_type ASC │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ──────────────────── │
│__internal_compress_integra│
│ l_utinyint(#0, 1) │
│ #1 │
│ #2 │
│ #3 │
│ │
│ ~2 Rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ──────────────────── │
│__internal_decompress_integ│
│ ral_bigint(#0, 1) │
│ #1 │
│ #2 │
│ #3 │
│ │
│ ~2 Rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ HASH_GROUP_BY │
│ ──────────────────── │
│ Groups: │
│ #0 │
│ #1 │
│ │
│ Aggregates: │
│ sum(#2) │
│ count_star() │
│ │
│ ~2 Rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ──────────────────── │
│ user_id │
│ activity_type │
│ duration │
│ │
│ ~4 Rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ──────────────────── │
│__internal_compress_integra│
│ l_utinyint(#0, 1) │
│ #1 │
│ #2 │
│ │
│ ~4 Rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ COLUMNSTORE_SCAN │
│ ──────────────────── │
│ Function: │
│ COLUMNSTORE_SCAN │
│ │
│ Projections: │
│ user_id │
│ activity_type │
│ duration │
│ │
│ ~4 Rows │
└───────────────────────────┘
(90 rows)