标签
PostgreSQL , vacuum , freeze , 分区表 , 并行vacuum
背景
我们之前做过一个这样的测试，单表数据从1000万到10亿，对其进行增删改查的压测，性能几乎没有衰减。
| 数据量 | 写入吞吐 | 查询tps | 更新tps |
|---|---|---|---|
| 1000万 | 58万行/s | 67万 | 23.1万 |
| 1亿 | 53.2万行/s | 63.4万 | 24.5万 |
| 10亿 | 162.6万行/s | 60.6万 | 23.4万 |
《HTAP数据库 PostgreSQL 场景与性能测试之 45 - (OLTP) 数据量与性能的线性关系(10亿+无衰减), 暨单表多大需要分区》
是不是就意味着我们不需要对数据库进行分表了呢？
实际上单表太大，还可能引入其他问题，例如：
1、创建索引，大表创建索引时间会更久，当然PostgreSQL 11已经支持单表并行创建索引了，所以这个问题逐渐会不存在。
《PostgreSQL 11 preview - 并行排序、并行索引 (性能线性暴增) 单实例100亿TOP-K仅40秒》
2、加字段并加默认值，或者不能online DDL的操作。单表越大，操作时间会越久。
这个问题的解法：1 支持更多的ONLINE DDL(类似 pg_repack 的原理)。2 支持DDL并行。
3、垃圾回收，因为单表的垃圾回收目前只支持串行，所以单表越大，垃圾回收的时间越长。
这个问题的解法：支持并行VACUUM。社区已经在做这个PATCH。
4、FREEZE表，与垃圾回收类似的问题，单表的垃圾回收，目前只能串行。而FREEZE如果很慢，并且慢过产生TXID的速度，可能导致数据库因为XID耗尽，需要停止业务来进行冻结。
这个问题的解法：1 支持并行VACUUM。社区已经在做这个PATCH。 2 skip clean page(9.6开始已经支持)，使得freeze效率高了很多。3 支持64BIT txid，完全杜绝freeze操作。
5、单表可能打爆文件系统，因为单个表只能放在单个表空间中，表空间对应文件系统，所以单表的大小也受到文件系统大小的限制。
这个问题的解法：1 使用类似LVM，ZFS这样的卷管理，使得单个文件系统很大很大。 2 使用分区表。
以上问题是单表很大时，可能出现的问题。
所以单表多大需要使用分区表呢？主要考虑几个方面：
1、表上的DML频率。
2、数据库的硬件性能指标。
3、查询方面的优化需求，例如是否可以通过分区来降维，优化SQL性能。
比较傻瓜式的建议（SSD，多核）：
不频繁更新、删除的表：记录数20亿，表占用空间200 GB。就可以考虑分表了。
平反更新、删除、插入的表：记录数2亿，表占用空间20 GB。就可以考虑分表了。
如何平滑将单表切换为分区表？
1、pg_pathman提供了平滑切换到分区表的API。
https://github.com/postgrespro/pg_pathman
《PostgreSQL 9.5+ 高效分区表实现 - pg_pathman》
vacuum的内核改进
针对前面提到的垃圾回收，freeze操作，内核层面可以做出的改进。
1、并行vacuum
https://commitfest.postgresql.org/13/954/
目前这个PATCH还没有提交到主干。
2、跳过clean page(根据vm文件标记位)，9.6开始，就支持了SKIP CLEANUP PAGE，freeze性能大幅提升。
《PostgreSQL 9.6 vacuum freeze大幅性能提升 代码浅析》
3、64bit xid，治本的方法。
https://commitfest.postgresql.org/17/1178/
PostgresPRO 版本引入了64BIT的txid，所以不再需要全局freeze操作。
https://postgrespro.com/docs/enterprise/10/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
将数据库的PAGE转换为兼容postgrespro的PAGE，可以使用如果工具。
对于FREEZE操作来说，方法1和2都是治标不治本的方法，3是社区正在改进的方向，可以看到社区的commitfest已经在加快做64bit txid 的 refact了。
https://github.com/postgrespro/pg_pageprep
参考
《HTAP数据库 PostgreSQL 场景与性能测试之 45 - (OLTP) 数据量与性能的线性关系(10亿+无衰减), 暨单表多大需要分区》
《PostgreSQL 11 preview - 并行排序、并行索引 (性能线性暴增) 单实例100亿TOP-K仅40秒》
