数据库选型讨论里有个流传很广的说法MySQL 单表过了两千万行就开始明显变慢而 PostgreSQL 好像从来不吃这一套数据量翻几倍依然稳。这个说法一半是经验、一半是误解但背后确实藏着两种数据库在存储引擎、索引组织、查询优化上的根本差异。我自己维护过千万级到十亿级不等的 PostgreSQL 实例也帮人排查过不少“表大了就卡”的现场慢慢摸清了所谓“断崖”到底断在哪、为什么有的系统断、有的系统不断。这篇内容面向已经会用 PostgreSQL、但对其内部机制了解不深的后端开发和运维同学也适合正在做数据库选型、被“两千万行”这个数字吓到的人。我会从 B 树的组织方式讲起拆到页结构、索引与堆表的关系、可见性判断、查询计划、统计信息、维护操作这些具体环节把“为什么 PostgreSQL 没有那个固定阈值”这件事讲透同时给出可以直接抄的排查步骤和参数配置。看完你应该能判断你手上那张表变慢到底是数据量的问题还是别的东西在拖后腿。1. 先把“两千万行断崖”这个说法拆开看1.1 这个数字到底从哪来的“两千万行”不是一个技术常数它更像一个经验阈值而且这个经验主要来自早期 MySQL InnoDB 在特定硬件、特定配置下的表现。InnoDB 的主键索引是聚簇索引数据和主键 B 树绑在一起二级索引的叶子节点存的是主键值回表意味着再走一次主键树。当单表数据量增长到某个点B 树层高从 3 层变成 4 层每次查询多一次页读取同时 buffer pool 装不下热数据磁盘 IO 占比上升延迟就出现台阶式变化。这个台阶在机械盘时代特别明显因为一次随机 IO 的代价是毫秒级多一层树高就是实打实的延迟增加。到了 SSD 时代随机读代价下降台阶被抹平了不少但“两千万”这个数字已经作为口口相传的经验固化下来了。所以第一件要澄清的事断崖的本质不是行数而是“树高增加 缓存命中率下降 IO 模式变化”这三件事叠加行数只是它们的一个粗略代理指标。1.2 PostgreSQL 为什么不吃这个阈值PostgreSQL 的存储结构和 InnoDB 完全不同。它用的是堆表heap加独立索引的结构表数据按插入顺序堆在一个个 8KB 的页里主键、二级索引都是独立的 B 树索引叶子节点存的是指向堆表物理位置的 TID页号 页内偏移而不是数据本身。这个设计带来几个直接后果。第一索引树的高度增长和“数据本身在哪”解耦了。索引只存键值和 TID单个索引项很小同样 8KB 的页能塞下更多条目扇出fanout更大树高增长更慢。第二查询走索引拿到 TID 后回堆表取数据这一步是随机的但 PostgreSQL 有 visibility map 和索引-only 扫描等机制来减少回表。第三也是很多人忽略的一点PostgreSQL 的行数增长不会像聚簇索引那样导致“整棵树重排”插入是往堆的末尾追加索引分裂是局部的不会引发全局性的结构抖动。所以严格说PostgreSQL 不是“没有断崖”而是它的性能拐点不由一个固定行数决定而是由工作集大小、索引设计、查询模式、维护状况共同决定。一张设计糟糕的 PostgreSQL 表几百万行就能卡得你怀疑人生一张设计良好的表几亿行照样跑得飞快。下面几节就把这些“共同决定因素”一个个拆开。2. B 树在两种数据库里的真实差异2.1 聚簇索引与堆表数据放哪决定了什么InnoDB 的聚簇索引意味着“表就是索引”。主键 B 树的叶子节点直接存放整行数据所以按主键查询一次树遍历就拿到数据非常快但代价是插入必须按主键顺序维护这棵树主键无序插入比如 UUID会导致频繁的页分裂和碎片。二级索引查询要先走二级索引拿到主键再走主键树拿数据两次树遍历。PostgreSQL 的堆表则是“数据是数据索引是索引”。插入永远往堆的空闲位置写索引单独维护。按主键查询是“走主键索引拿 TID再回堆取行”也是两次访问但这两次访问的性质不同索引访问是 B 树遍历堆访问是直接按物理地址定位。这个差异在批量操作和更新场景下影响巨大因为 PostgreSQL 的更新是“写新版本行 旧行标记删除”索引可能不需要动如果键值没变而 InnoDB 的更新如果改了主键等于删了再插。这里有个实操层面的关键点PostgreSQL 的堆表插入是“找空闲空间”而不是“追加到末尾”。表上有空闲空间映射FSM插入时会优先复用之前删除或更新留下的空洞。这意味着长期高频更新的表堆的物理顺序会变得杂乱顺序扫描的代价上升。这也是为什么大表需要定期关注膨胀bloat问题后面会专门讲。2.2 扇出与树高为什么 PostgreSQL 的索引更“矮胖”B 树的查询代价和树高直接相关树高又由扇出决定。扇出 每个索引页能容纳的条目数。PostgreSQL 的索引项是“键值 6 字节 TID”假设主键是 8 字节 bigint加上页头、行指针等开销一个 8KB 的索引页大约能放 400 到 500 个条目。InnoDB 的聚簇索引叶子节点存整行假设一行 200 字节一个 16KB 的页只能放 80 行左右扇出小得多。粗略算一下PostgreSQL 主键索引扇出按 400 算三层树能覆盖 400 × 400 × 400 6400 万条目四层能覆盖 256 亿。也就是说一张表的主键索引要到几千万行才可能从三层变四层而且多一层只是多一次页访问在 SSD 上代价很小。InnoDB 聚簇索引扇出按 80 算三层只能覆盖 80 × 80 × 80 51 万行四层覆盖 4000 万行所以它在几百万到几千万这个区间就会经历树高变化正好和“两千万”这个经验值对上。注意上面的扇出是数量级估算实际值受行宽、页填充率、键类型影响很大。行越宽InnoDB 的扇出越小树高增长越早PostgreSQL 索引项大小相对稳定受行宽影响小这是它“抗行数增长”的结构性原因之一。2.3 回表代价两种数据库谁更疼回表是索引查询绕不开的一步。PostgreSQL 回表是按 TID 直接定位堆页如果堆页不在 shared buffers 里就要读盘。InnoDB 回表是再走一次主键 B 树如果主键树的热页不在 buffer pool 里也要读盘。两者都要读盘差别在于访问模式PostgreSQL 的 TID 是物理地址回表是纯随机InnoDB 的主键树遍历虽然也是随机但聚簇索引的局部性更好因为相邻主键的数据在物理上相邻。这个差异导致一个有意思的现象范围查询和顺序扫描PostgreSQL 的堆表局部性反而不如聚簇索引。所以 PostgreSQL 在大范围扫描时更依赖顺序扫描和位图扫描而不是大量随机回表。这也解释了为什么 PostgreSQL 的查询优化器对“走索引还是全表扫”的代价估算特别敏感统计信息一旦失真计划就可能选错性能瞬间掉档。这不是行数的问题是计划的问题。3. 真正让 PostgreSQL 大表变慢的几个开关3.1 工作集与 shared_buffers缓存装不下才是硬伤不管哪种数据库性能拐点最核心的驱动因素是工作集working set能不能装进内存。PostgreSQL 的 shared_buffers 默认值很小通常 128MB很多实例部署完就没调过。一张表加上它的索引如果热数据超过 shared_buffers每次查询都要读盘延迟自然上去。这跟行数没有直接关系跟“热数据字节数”有关系。我的经验是 shared_buffers 设成物理内存的 25% 左右比较稳妥剩下的留给操作系统页缓存因为 PostgreSQL 的双缓冲机制shared_buffers OS page cache意味着数据可能被缓存两次设太大反而浪费。但这不是绝对的要看负载类型OLTP 短查询多shared_buffers 可以适当大分析型大扫描多OS 缓存更重要。关键是你要能算出自己的工作集大小而不是拍脑袋。计算工作集的粗略方法把最热的几张表的堆大小加上它们常用索引的大小再乘以一个活跃比例比如 20% 到 30%看是否超过 shared_buffers。如果超了要么加内存要么优化索引减少扫描量要么做分区把热数据隔离出来。这个判断比盯着行数有用得多。3.2 统计信息与查询计划计划选错比数据量大更致命PostgreSQL 依赖统计信息来估算选择率统计信息来自 autovacuum 或手动 ANALYZE 采样的样本。大表如果统计信息陈旧优化器对“这个条件能过滤掉多少行”的判断就会错得离谱可能该走索引的走了全表扫或者该全表扫的走了索引然后疯狂回表。这种性能下降和行数无关纯粹是计划问题。我遇到过一个典型案例一张几千万行的订单表按状态字段查询状态分布极不均匀99% 是已完成1% 是待处理。统计信息没更新时优化器以为待处理也占很大比例选了全表扫描查询要几十秒手动 ANALYZE 之后优化器知道待处理只有 1%改走索引几十毫秒返回。同一张表、同样的数据量差别只在统计信息。所以大表一定要关注 autovacuum 的触发频率。默认的 autovacuum 触发阈值是 50 0.2 × 行数大表要积累很多变更才会触发一次期间统计信息可能严重滞后。对关键大表建议单独设置更激进的 autovacuum 参数或者定期手动 ANALYZE。这个操作的成本远低于它带来的收益。3.3 表膨胀与 vacuum更新留下的“垃圾”会拖垮扫描PostgreSQL 的 MVCC 实现方式是“更新不覆盖而是写新版本”旧版本行要等 vacuum 回收。如果 vacuum 跟不上表就会膨胀物理大小远大于有效数据顺序扫描要读更多页索引也可能膨胀导致树高虚增。这是 PostgreSQL 大表性能下降最常见的原因之一而且它和行数没有直接关系一张一千万行的表可能因为膨胀实际占了几千万行的空间。判断膨胀可以用 pgstattuple 扩展或者看 pg_stat_user_tables 里的 n_dead_tup。如果 dead tuple 占比超过 10% 到 20%就该考虑手动 VACUUM 或者调整 autovacuum 了。对于更新频繁的大表autovacuum 的默认参数往往不够用需要调低 autovacuum_vacuum_scale_factor比如从 0.2 调到 0.05让 vacuum 更频繁地跑。提示VACUUM FULL 能彻底回收空间但它会锁表并且重写整张表大表上要谨慎使用。更温和的做法是 pg_repack 扩展它在线重建表不阻塞读写适合生产环境。3.4 索引设计多一个索引就多一份维护成本索引不是越多越好。每个索引都是一棵独立的 B 树插入、更新、删除都要维护所有相关索引。大表上索引过多写放大会非常明显而且优化器在计划选择时的搜索空间变大可能选出奇怪的执行计划。更隐蔽的问题是冗余索引比如已经有了 (a, b) 的复合索引再建一个 (a) 的单列索引就是浪费因为复合索引的最左前缀已经能覆盖 a 的查询。我见过不少系统一张表上挂了十几个索引其中一半是重复或低效的。清理索引的思路先查 pg_stat_user_indexes 看每个索引的 idx_scan 次数长期为 0 的索引基本可以判定为无用除非是唯一约束索引再看复合索引的列顺序是否匹配实际查询的过滤条件顺序。索引列顺序的原则是“等值条件在前范围条件在后选择性高的列靠前”但也要结合具体查询模式不能教条。4. 分区表把“大表”拆成“小表”的正确姿势4.1 什么时候该上分区分区不是银弹它的价值在于“把维护操作和数据访问都缩小到单个分区”。适合分区的场景表非常大且访问有明显的时间或范围特征比如按月的日志、按区域的订单需要定期删除历史数据DROP 分区比 DELETE 快几个数量级单表膨胀严重且 vacuum 成本高。不适合的场景查询模式没有明显的分区键特征或者分区键选择不当导致跨分区查询频繁。PostgreSQL 10 之后原生支持声明式分区有 RANGE、LIST、HASH 三种策略。时间序列数据用 RANGE 按时间分区最自然枚举类字段用 LIST需要均匀打散写入的用 HASH。分区键的选择直接决定分区裁剪partition pruning能不能生效如果查询条件里不带分区键优化器就得扫描所有分区分区反而成了负担。4.2 分区表的实操配置与注意事项声明式分区的基本语法很直接但有几个坑要注意。第一分区表本身不存数据数据都在子分区里所以对父表的查询会自动路由但直接查子表要写对名字。第二索引要在父表上建PostgreSQL 会自动在每个子分区上创建对应索引但已有数据的分区在 attach 时要注意索引一致性。第三分区数量不是越多越好每个分区都有元数据开销几千个分区会让计划时间变长一般控制在几百个以内比较合理。一个实用的做法是按时间做 RANGE 分区提前创建好未来几个月的分区用定时任务自动建新分区、归档或删除老分区。删除老数据用 DROP PARTITION 而不是 DELETE前者是元数据操作瞬间完成后者要逐行删除并产生大量 dead tuple还要等 vacuum 回收。这个差别在十亿级表上就是分钟和小时的区别。5. 一套可复现的大表性能排查流程5.1 先定位瓶颈在哪一层排查大表变慢不要一上来就怀疑行数。按这个顺序走先看是不是慢在 IO用 pg_stat_statements 找 top SQL看 shared_blks_read 和 shared_blks_hit 的比例再看是不是慢在计划用 EXPLAIN (ANALYZE, BUFFERS) 看实际行数和估算行数差多少再看是不是慢在锁或并发查 pg_locks 和 pg_stat_activity最后才看表本身的大小和膨胀。这个顺序能把大部分“以为是数据量问题”的案例排除掉。pg_stat_statements 是必装扩展没有它基本没法做系统性的性能分析。它按 SQL 模板聚合统计能看到总耗时、调用次数、平均耗时、缓存命中情况。先按总耗时排序找最贵的 SQL再按平均耗时排序找单次最慢的 SQL两个视角结合很快能锁定问题语句。5.2 关键诊断 SQL 与参数速查下面这几条查询是我排查时最常用的直接抄就能用。第一条看表的膨胀和 vacuum 情况SELECT relname, n_live_tup, n_dead_tup, round(n_dead_tup::numeric / nullif(n_live_tup n_dead_tup, 0) * 100, 2) AS dead_pct, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;第二条看索引使用情况找出从没被用过的索引SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read FROM pg_stat_user_indexes WHERE idx_scan 0 ORDER BY pg_relation_size(indexrelid) DESC;第三条看当前正在跑的慢查询和等待事件SELECT pid, now() - query_start AS duration, wait_event_type, wait_event, state, query FROM pg_stat_activity WHERE state ! idle ORDER BY duration DESC;参数方面重点盯这几个shared_buffers缓存大小、work_mem排序和哈希的内存大表聚合查询容易不够、maintenance_work_memvacuum 和建索引用、effective_cache_size告诉优化器 OS 缓存大概多大影响计划选择、random_page_costSSD 上应该调低到 1.1 左右默认 4.0 是机械盘时代的假设。这几个参数调对了很多“大表慢”的问题会自然消失。5.3 一个完整的排查案例复盘某系统反馈一张八千万行的表查询越来越慢从几百毫秒涨到十几秒。按流程走pg_stat_statements 显示慢查询是一条带时间范围和状态过滤的查询EXPLAIN 显示优化器选了全表扫描估算行数和实际行数差了三个数量级查统计信息发现这张表的 last_autoanalyze 是几天前期间有大量数据写入手动 ANALYZE 后计划改走索引耗时回到几百毫秒。进一步查发现 autovacuum 在这张表上几乎不触发因为默认阈值对大表太宽松。调整该表的 autovacuum_vacuum_scale_factor 到 0.02、autovacuum_analyze_scale_factor 到 0.01并给 maintenance_work_mem 调大之后统计信息保持新鲜问题不再复现。整个过程没有加一行数据、没有改一个索引纯粹是维护策略的问题。这个案例很典型大表变慢十次里有六七次是统计信息或膨胀而不是行数本身。6. 几个容易被忽略的细节与个人经验6.1 行宽和 TOAST 对大表的影响PostgreSQL 的行有 8KB 页大小限制超长的字段大文本、大 JSON会被 TOAST 机制压缩或挪到单独的 TOAST 表里。这意味着“行数”不能直接换算成“数据量”一张一千万行的表如果每行带个大 JSON实际数据量可能几十 GB工作集远超内存。反过来行很窄的表几千万行可能才几个 GB轻松装进内存。所以评估大表性能永远看字节数和工作集不要只看行数。TOAST 还有个副作用带 TOAST 字段的查询如果不需要那个字段PostgreSQL 不会去读 TOAST 表这是好事但如果查询里 SELECT *就会把 TOAST 数据也拉出来代价陡增。大表查询养成只取需要字段的习惯比什么优化都实在。6.2 连接数与并发的隐性成本PostgreSQL 是进程模型每个连接一个后端进程。连接数一多上下文切换和内存开销都上来了而且高并发下锁竞争和快照管理成本增加大表上的并发查询可能互相拖累。很多“大表慢”其实是“并发一高就慢”根因在连接数而不是表大小。用连接池把活跃连接控制在合理范围比如 CPU 核数的几倍比盲目加索引有效。6.3 我踩过的几个坑早期我迷信“加索引就能解决大表慢”结果一张写入频繁的表加了五个索引写入性能掉了一半查询也没快多少因为优化器在多个索引之间选来选去计划不稳定。后来学会先看 pg_stat_user_indexes 的使用统计只留有实际扫描量的索引写入立刻回升。还有一次一张表做了分区但查询不带分区键优化器扫描所有分区比不分还慢。分区键必须和主要查询的过滤条件对齐否则分区就是负优化。这个教训让我在每次设计分区前先把 top 20 的查询拿出来确认分区键能命中大部分查询再动手。最后一个体会PostgreSQL 的性能调优七分靠设计表结构、索引、分区两分靠维护vacuum、统计信息一分靠参数。很多人把顺序搞反了一上来就调参数结果收效甚微。先把设计和维护做对参数只是锦上添花。所谓“没有两千万行断崖”本质是 PostgreSQL 把性能的决定权交还给了使用者的设计水平而不是一个固定的行数门槛。