★ FEATURED ARTICLE PG用户做OLAP别总想着换库先说个我经常遇到的场景业务库是PostgreSQL跑了几年数据量到了几百GB甚至几个TB的量级日常的增删改查一点问题没有但一到月底出报表、跑聚合、算留存查询时间直接从秒级掉到分钟级。DBA群里一问回答永远是“上数仓”“换ClickHouse”“上分布式”。说得都对但代价呢团队要学一套新东西运维要接新组件业务方要改查询方式数据同步还要折腾ETL。一个几百GB的PG库真的到了非换不可的地步吗答案是否定的。PG用户做OLAP未必非要“搬家”。用DuckDB做嵌入式分析引擎或者用Trino做联邦查询层两条路都能在不大动干戈的前提下把分析能力提上一个台阶。甚至这两者本身还可以组合使用——DuckDB处理小规模高迭代的分析任务Trino负责跨源、跨库的大规模联邦查询。这篇就围绕PG用户落地点OLAP的话题把这两套方案的定位、实现路径、选型逻辑和实战坑点一次说清楚。不管你是被报表查询卡到暴躁的开发还是被业务方追着要数的分析师这篇都值得看完。1. PG在分析场景的真实瓶颈为什么一到OLAP就卡1.1 行存储的“原罪”与MVCC的开销PostgreSQL默认的堆表是行式存储数据按行物理连续存放。OLTP场景下这几乎是完美形态——按主键取一行或者按索引定位几行磁盘上连续读取一小块就搞定了。但分析型查询天生是“扫全表、读大列、聚合计算”这时候行存储的劣势就暴露了查询要求返回5个列但行式存储必须把整行的所有列都读进内存哪怕其他20个列根本用不上。IO浪费严重。大宽表场景下同样一段数据列存的读取量可能是行存的五分之一甚至更少。PG的MVCC机制让更新会产生新版本老版本数据要靠vacuum清理。分析型查询经常批量写入数据vacuum跟不上表膨胀了扫描的块数只会越来越多。这不是PG的缺陷是行存储的物理特性决定的方向性短板。你没法要求一个OLTP引擎同时在OLAP上也做到极致就像你不能指望一辆轿车同时是皮卡。1.2 查询计划器在复杂聚合面前的表现另一个被吐槽最多的地方是PG的查询计划器。单表扫描、简单join、常规group byPG的代价模型表现相当好。但一旦遇到复杂的子查询嵌套、窗口函数叠加、多表join且关联键分布不均匀计划器经常给出让人摸不着头脑的执行计划。典型场景是统计类报表SQL写了几十行子查询套了两层join了七八张表PG十几个并行worker一开内存一吃紧临时文件一落盘查询性能立刻雪崩。更麻烦的是这类慢查询往往没有直观的调优手段——索引、分区、并行度能调的都调了瓶颈在计划器对数据分布的估算上。1.3 什么样的数据规模真正“压垮”PG聊数字才有意义。我按经验给一个粗糙的分段数据量级PG分析表现处理策略GB级以下基本无压力索引适当优化即可不需要额外方案单表数亿行 / 数十GB简单聚合可接受复杂join开始变慢可以考虑DuckDB或物化视图数百GB / 数十亿行全表扫描明显吃力报表查询分钟级起需要专门的OLAP方案TB级以上单机PG基本扛不住分析负载必须上分布式查询引擎或数仓注意“数亿行”这个关键点。很多PG用户卡在这个量级业务数据还没大到需要上数仓的程度但已经超出了PG分析能力的舒适区。这时候上DuckDB或Trino成本远低于引入一套完整的数仓体系。2. DuckDB这条路把PG数据“搬”进嵌入式分析引擎2.1 为什么是DuckDB而不是SQLite或内存数据库DuckDB这几年在OLAP圈子里热度极高核心定位是“嵌入式列式OLAP数据库”。和SQLite类似它不需要独立部署服务直接嵌入到应用进程里连接的是本地文件。但它和SQLite的差别是本质性的SQLite是行存储DuckDB是列存储。SQLite的强项是事务和小规模数据DuckDB的强项是分析型SQL执行效率。DuckDB支持向量化执行引擎意味着它在批处理分析场景下比SQLite快一到两个数量级。相比纯粹的内存数据库DuckDB的差异在于数据持久化在磁盘文件中内存只是执行时的计算空间。几百GB的数据量在DuckDB里跑分析几十GB内存的机器妥妥够用不需要像内存数据库那样all-in-RAM。2.2 安装DuckDB最小成本的第一步DuckDB的安装成本低到几乎可以忽略。它提供了各语言的客户端库Python环境一条pip命令搞定pip install duckdb服务端连独立部署都不需要CLI版本下载一个二进制文件就能跑curl -L -o duckdb.zip https://github.com/duckdb/duckdb/releases/download/v1.1.3/duckdb_cli-linux-amd64.zip unzip duckdb.zip ./duckdb如果你是PG重度用户还有更顺滑的方式——直接用pg_duckdb扩展。这个扩展让PG数据库内部就能直接执行DuckDB查询CREATE EXTENSION duckdb; SELECT * FROM duckdb.query(SELECT 1);注意pg_duckdb目前适合实验和小规模场景生产环境大规模分析我仍然推荐独立DuckDB进程或嵌入式方式避免两个引擎共享进程资源时互相干扰。2.3 从PG导数据COPY不是唯一答案但最稳妥DuckDB分析的是“搬过去”的数据所以第一步是把PG数据导入DuckDB。最直接的方式是用PG的COPY导出为文件再在DuckDB里加载# PG侧导出 psql -c COPY (SELECT * FROM orders WHERE order_date 2024-01-01) TO /tmp/orders.parquet (FORMAT PARQUET)这里用Parquet格式而不是CSV重要程度怎么强调都不为过Parquet是列式存储格式DuckDB读Parquet几乎是零成本转换。Parquet自带压缩和统计信息min/max等DuckDB可以利用这些元数据进行过滤下推。CSV要全部解析成文本再转类型速度慢且类型容易踩坑。如果你没有parquet格式的COPY支持请升级PG——PG 15以上的版本支持COPY TO ... (FORMAT PARQUET)前提是安装了parquet_fdw依赖或者使用新版本内置支持。导入DuckDBCREATE TABLE orders AS SELECT * FROM read_parquet(/tmp/orders.parquet);几亿行的数据量这个过程通常是分钟级。PG侧导出耗时DuckDB侧加载几乎不会成为瓶颈。2.4 在DuckDB里跑分析从慢查询到瞬间出数进入DuckDB之后分析体验和PG完全不同。拿一个典型留存计算举例WITH user_first AS ( SELECT user_id, MIN(created_at) AS first_date FROM user_event GROUP BY user_id ) SELECT date_diff(day, first_date, e.created_at) AS day_offset, count(DISTINCT e.user_id) AS retained_users FROM user_event e JOIN user_first f ON e.user_id f.user_id GROUP BY day_offset ORDER BY day_offset;在PG里这个查询跑了几分钟在DuckDB里往往秒级返回。原因不在于DuckDB比PG“更聪明”而在于列存向量化执行对这类批量扫描聚合任务天然友好。DuckDB甚至会为同一个查询动态决定用hash join还是merge join你几乎不需要手动干预执行计划。2.5 pg_duckdb扩展不搬数据也能跑的“快捷通道”前面提到pg_duckdb扩展这里展开说。这个扩展的核心思路是让PG把DuckDB当作一个分析执行引擎来调用。你可以直接在PG里执行DuckDB擅长的大规模聚合查询而查询的数据源仍然是PG的表。CREATE EXTENSION duckdb; SELECT * FROM duckdb.query(SELECT category, sum(amount) FROM pg_tables.orders GROUP BY category ORDER BY 2 DESC);实现原理是pg_duckdb通过PostgreSQL的扫描节点接口把DuckDB作为执行引擎嵌入PG进程。所有数据访问仍走PG的存储引擎但计算是在DuckDB的向量化执行器里完成的。实测下来简单聚合查询加速比能达到3到10倍。复杂的join提升没那么夸张因为数据还是要从PG的行存储里读出来再转换。但它最实在的价值在于你不用导数据不需要改ETL流程用PG的原生连接就能跑上DuckDB的分析能力。不过提醒大家pg_duckdb目前还比较年轻生产环境请务必充分测试尤其是内存和并行度配置。2.6 DuckDB的内存控制与性能调优DuckDB执行分析任务时内存管理是一个关键点。默认配置下DuckDB会根据系统内存自调但高并发场景下多个查询同时跑可能把内存吃满。建议在应用侧显式配置SET memory_limit16GB; SET threads8;memory_limit控制整个DuckDB实例的最大内存使用超出部分会溢出到临时磁盘文件。注意temp_directory要设置到有足够空间的目录否则大查询会直接报错SET temp_directory/data/duckdb_tmp;DuckDB的并行粒度也值得一说。默认threads设置为CPU核数但对OLAP查询而言过高的并行度不一定线性加速反而可能因为线程切换和数据分片开销造成收益递减。建议从CPU核数的一半开始测试找到最佳值。3. Trino这条路不搬数据用联邦查询直连PG3.1 Trino的定位分布式SQL查询引擎不是数据库Trino前身是PrestoSQL在很多人的概念里是“大数据查询引擎”听起来好像和几百GB的PG不太搭。但如果把Trino看作“跨数据源的统一SQL访问层”它分析PG数据的价值就清晰了数据不动Trino通过PostgreSQL连接器直连PG实时查询库里的表。同一套SQL还可以同时查MySQL、Hive、Iceberg、对象存储上的文件然后做跨源join。分布式架构天然支持大并发多个报表同时查询不怕相互挤占。与DuckDB的核心差异DuckDB是把数据拉到自己这里分析Trino是让查询去找数据。前者适合数据量大但可复制、可导出的场景后者适合数据必须保持原位、多系统间频繁联查的场景。3.2 Trino部署单机还是集群取决于并发Trino的部署没有想象中复杂。它由Coordinator和Worker两类节点组成Coordinator负责接收SQL、生成执行计划、调度任务Worker负责实际的数据读取和计算。最低配置可以单机部署Coordinator和Worker共用同一台服务器。官方也支持“单节点同时扮演两个角色”的配置。数据量几百GB、查询并发不高的场景单机Trino绰绰有余# 下载并解压 curl -L -o trino.tar.gz https://repo1.maven.org/maven2/io/trino/trino-server/470/trino-server-470.tar.gz tar -xzf trino.tar.gz # 创建配置目录 mkdir -p etc/catalog配置文件按下面步骤准备即可。首先是节点配置etc/node.propertiesnode.environmentproduction node.idtrino-node-001 node.data-dir/data/trino然后是JVM配置etc/jvm.config-server -Xmx16G -Xms16G注意Trino不太推荐设置过大的Xmx因为它内部对内存有精细化的池化管理query.max-memory-per-node、memory.heap-headroom-per-node等JVM堆太大反而可能造成GC停顿。16G是一个比较安全的起点。3.3 PostgreSQL连接器配置核心就是catalogTrino通过catalog访问外部数据源。一个catalog就是一组配置指向一个外部系统。PostgreSQL连接器配置放在etc/catalog/postgresql.propertiesconnector.namepostgresql connection-urljdbc:postgresql://192.168.1.10:5432/business_db connection-usertrino_query_user connection-passwordyour_password这里有几个关键点都是我实际踩过的第一个是用户权限设计。给Trino专用的查询账号不要用超级用户。这个账号需要具备SELECT权限如果需要Trino下推写入操作例如CREATE TABLE AS还需要CREATE权限。最小权限原则在跨系统集成场景下是绝对红线。第二个是schema模式。PG的publicschema在Trino里默认可见。如果数据库里还有其他schema需要在连接器的schema-pattern属性中指定。默认配置下Trino只会搜索public其他schema的表会显示为不存在。第三个有意思的特性是Trino对PG数据类型的映射。PG的timestamptz、numeric、json等类型Trino有对应的映射规则。特别提一下numericPG的任意精度numeric转到Trino的decimal时如果精度超过38位会被拒绝建议在ETL时先做好类型转换或CAST。3.4 查询下推Trino什么时候真正“快”很多人第一次用Trino查PG会觉得“这也没快到哪去啊”。原因在于Trino对PG的查询不一定做了完整的下推优化。Trino的PostgreSQL连接器支持一定的谓词下推predicate pushdown——WHERE条件可以下推到PG执行利用PG的索引。但GROUP BY和聚合操作默认情况下是拉到Trino这边算的。这意味着如果你直接执行一个几亿行的大聚合Trino会把全量原始数据从PG读出来再在Trino侧做聚合。IO开销不小但好处是PG的负载被减轻了而且Trino的分布式计算能力在这种场景下也优于PG单机。实际调优经验尽可能让大表先通过WHERE条件缩到最小再参与关联。在Trino里写SQL时子查询里先做聚合再joinsub比直接join大表快得多。这和DuckDB的“方案不同但原则一致”——数据量越小任何引擎都越轻松。如果你希望利用PG的执行计划做部分聚合下推可以把join-pushdown.enabled等连接器属性打开join-pushdown.enabledtrue打开后Trino会把join操作下推到PG执行而非在Trino侧拉数据再join。实测对有索引的维度表关联有显著提升。3.5 跨源联邦查询的场景价值Trino真正的杀手锏不在“单查PG”而在“同时查多个数据源”。举个例子订单数据在PG里用户行为日志在对象存储的Parquet文件里商品维表在MySQL里。传统做法要先把数据同步到一起才能分析Trino可以直接跑SELECT o.user_id, count(*) AS order_cnt, sum(o.amount) AS total_amount FROM postgresql.business_db.orders o JOIN mysql.dim.product p ON o.product_id p.id JOIN iceberg.logs.user_log l ON o.user_id l.user_id WHERE l.event_time TIMESTAMP 2025-01-01 GROUP BY o.user_id;这类“数据不动、统一查询”的价值在业务侧非常明显——报表团队不需要等ETL任务跑完随时可以基于最新数据出结果。4. DuckDB与Trino的选型对比集成方案到底怎么选4.1 一张表理清两套方案对比维度DuckDB路线Trino路线定位嵌入式OLAP引擎分布式查询引擎数据访问方式将PG数据导入/导出后分析直连PG实时查询部署成本极低进程内/本地文件需要独立服务有Coordinator和Worker学习成本低SQL标准兼容好低SQL界面接近ANSI标准数据规模单机数百GB到数TB可扩展到PB级并发能力单进程多线程适合低并发分布式多节点适合高并发实时性看数据导出频率直连PG实时性最好扩容方式换更大的机器加Worker节点与PG的耦合度低导出后独立分析或中pg_duckdb高通过连接器实时访问4.2 什么情况优先选DuckDB你的分析任务是**“先导出再分析”**的模式比如定时生成报表、跑数据科学特征的离线计算。团队没有专门的大数据运维人力不想维护一个独立的分析服务。分析任务本身是重CPU、重扫描的聚合计算数据规模在单机可承载范围内。你希望把分析能力嵌入现有应用进程如Python后端服务或作为分析师本地的数据探索工具。我从实际出发的判断如果你的PG数据还在几百GB到一两TB的量级DuckDB可能是性价比最高的方案。它带来的性能提升是“立竿见影”的——同一个查询在PG里跑60秒在DuckDB里可能3秒出结果而你的操作成本只是“导出一次数据”。4.3 什么情况优先选Trino数据必须保持“原汁原味”不允许离线导出的延迟——业务方要看实时数据。查询要跨多个数据源PG、MySQL、数据湖文件等做联邦分析。报表/分析系统的并发用户多需要独立的查询资源池不能挤压PG业务负载。数据量已经明显超出单机承载能力后续还要持续增长。Trino的选型信号最强烈的时刻是业务方频繁说“我要看实时数”并且你的数据已经分散到了两三个存储系统里。4.4 两条路的组合使用先Trino收敛后DuckDB精算这里再说一个高阶思路DuckDB和Trino不冲突可以串起来。Trino负责查询PG和其他数据源把结果缩小到“分析所需的最小集”然后DuckDB接过来做深度的探索性分析。这样可以同时获得Trino的实时联邦查询能力——SQL直接访问PG不搬数据。DuckDB的快速迭代和本地分析体验——结果集作为临时表/Parquet文件落到本地后续分析完全和远端无关。比如Trino查出一个亿级用户的口径表导出为Parquet本地文件DuckDB加载后做各种维度的透视、人群拆解、AB实验分析十几秒一轮迭代效率远高于在PG或Trino里反复跑。5. 实战中的坑与经验从“跑通”到“跑稳”5.1 大数据量查询的内存不足: DuckDB的OOM与临时磁盘前面提过memory_limit但我还是想单独拿出来讲一个真实案例。之前在一台64GB内存的服务器上用DuckDB分析一张8亿行的订单表跑一个高基数的group by直接OOM了。查了下日志发现是高基数分组导致hash table膨胀默认的80%内存上限不够用。解决办法不是无脑调大memory_limit而是先检查是否真的需要全量聚合。大部分报表面向的是“按天/按周/按城市/按用户”等常规维度可以先预聚合再分组数据量能缩小几个量级。另一个办法是SET temp_directory让hash table溢出到磁盘代价是查询变慢但不再OOM。我现在的习惯是任何超过10亿行的单表聚合先做数据预筛选或预汇总再交付分析结果。这不只是DuckDB的问题是OLAP查询设计的基本功。5.2 PG数据导出的类型陷阱时间格式、JSON字段与NULL从PG导出数据到DuckDB最常见的坑集中在类型映射上。timestamptz是从PG到DuckDB最麻烦的类型之一。PG的timestamptz存储的是带时区的绝对时间点但DuckDB的TIMESTAMP默认不带时区只有TIMESTAMPTZ带时区。如果导出CSV再加载时区信息很容易在处理过程中丢失。更省心的做法是直接用Parquet格式复制类型信息会保留在文件中。如果是旧版本PG不支持COPY ... (FORMAT PARQUET)那么可以借助外部工具比如pg_dump加--formatparquet的第三方实现或先导出为CSV、再到DuckDB里强制CAST。JSON字段在PG里是json/jsonb类型DuckDB有原生的JSON支持但导入时要做FROM read_json或者CAST。最稳妥的做法是导出为Parquet内置的JSON类型自动映射。NULL值的处理倒是简单Parquet格式天然支持NULL比CSV的“空字符串到底代表NULL还是空字符”这种坑干净太多。5.3 Trino连接器的类型映射坑numeric精度与timestamp精度Trino查PG时numeric精度超限是一个高频报错。PG允许定义numeric(38, 10)甚至更高精度的列但Trino的decimal精度上限是38位。一旦数据里的整数部分小数部分超过38位Trino会直接报“Decimal precision exceeds max precision”。解决办法有二在PG侧创建视图将超精度列CAST成double precision或numeric(38, 10)让Trino扫描时读视图。在Trino查询里显式CASTcast(pg_col AS decimal(38, 10))。另一个坑是timestamp(6)的精度问题。PG的timestamp精度默认是6位微秒Trino的timestamp(3)只有毫秒精度。直接查询没问题但如果你用Trino做CREATE TABLE AS写入PGPG侧的精度也会被截断到毫秒。对大多数报表场景没影响但如果精度敏感需要提前沟通口径。5.4 “查询慢”先别怪引擎先看SQL写法最后说一个玄学层面的问题。很多用户把数据从PG搬到DuckDB或通过Trino查询之后发现某些SQL还是慢。经验告诉我这时候多半不是引擎的问题而是SQL写法本身就带着PG的“OLTP思维”。典型的OLTP写法问题查询大量无关字段。列存引擎虽然避免了“读无用列”的IO浪费但传输和序列化开销仍存在。把所有维度都塞进GROUP BY没有做粒度裁剪。大表join大表且关联键分布极度倾斜比如一个热点用户占了一半订单量任何引擎遇到数据倾斜都会慢。针对数据倾斜Trino和DuckDB都有对应的处理手段。Trino可以在JOIN左侧小表加WITH (replicated)提示SELECT ... FROM big_orders o JOIN big_users u ...;DuckDB则建议先按用户维度做预聚合把热点用户单独拆出来处理再合并结果。这套方法论和底层引擎无关但确定能解决90%的“慢查询”困惑。5.5 运维监控视角别忘了观察PG侧的压力用了分析引擎之后有人会忽略对PG侧的资源监控。这里特别提醒Trino直连PG时高并发查询依然会给PG增加明显负载尤其是大型WHERE条件只能部分下推的场景。建议在PG侧建立独立的只读副本或者在连接器上配置只读账号并设置statement超时ALTER ROLE trino_query_user SET statement_timeout 30s;DuckDB路线虽然卸掉了分析负载但数据导出过程本身也会占用PG资源。尽量把导出任务安排在业务低峰期或使用COPY (SELECT ...)配合谓词分批导降低单次IO冲击。5.6 从实践看集成方案选择的“最小改动原则”踩过这些坑之后我形成了一个不错的判断框架——先问自己三个问题分析的数据形态是什么是“从PG复制一份更高效”还是“必须实时查原库”分析规模多大单机DuckDB能扛还是需要Trino做分布式团队现有技能栈是什么有没有人能维护分布式查询集群如果团队只有PG DBA没有专职大数据运维DuckDB是更平滑的起点如果团队已经有数据平台概念、后续要接更多数据源Trino的长线价值更明显。写在最后从我自己的实践体验来看PG用户转型OLAP最大的阻碍往往不是技术本身而是惯性思维——“分析慢就换库”。这条路成本高、周期长、风险大很多时候并不划算。先尝试DuckDB和Trino这类轻量级方案往往能以极低的成本解决大部分问题。具体到落地顺序我的建议是先用DuckDB在本地做一轮POC把最卡的那个报表查询拿出来看看加速效果能不能达到5倍以上。如果效果理想直接搭建一套“PG导出DuckDB分析”的流程先解决业务上最痛的报表问题。当业务量增长或跨源查询需求出现再引入Trino作为联邦查询层和DuckDB形成互补。这个路线能让你在控制复杂度的同时逐步推进分析能力的升级。最后分享一个小技巧在DuckDB和Trino里做运维排查时记得开EXPLAIN (ANALYZE)查看执行计划和实际耗时分布。很多看似“引擎不行”的问题看完执行计划就发现是SQL写法或数据类型映射的问题。分析之路的尽头不是某个特定工具而是你对数据分布和执行原理的理解深度。
阅读完成 · 觉得有帮助？
