给数据库瘦身

如何给数据库瘦身?

群友问: 如何从一个老的臃肿的数据库, 重新设计一个新的轻量的数据库?

这个问题要分2种情况来看

一、原地瘦身

先来说说原地瘦身的方法. 首先要找到“肥肉”在哪? 然后针对性的瘦身.

常见的“肥肉”:

1、膨胀的表和索引

如果数据库在大量的delete、update操作后, 未及时的清理垃圾tuple, 可能导致表和索引膨胀.

索引则在大量的插入后, 可能因为page分裂导致膨胀. 还有一些索引可能由于实时更新代价太高, 本身就不具备垃圾回收的功能(例如某些向量索引), 在大量DML操作后可能导致膨胀.

不同的数据库有不同的查询方法, 例如

• 《PostgreSQL DBA最常用SQL》
• 《别找了, 这才是最好的膨胀检查SQL》

使用在线垃圾回收工具(例如 pg_repack)或vacuum full(注意有排它表)可进行回收.

• 《PostgreSQL 收缩膨胀表或索引 - pg_squeeze or pg_repack》

vacuum 不可回收非尾端空页, 如果水位已很高, 建议以上方法.

如何避免膨胀?

• 《膨胀点解释 - 全局catalog,库级catalog,普通表,wal文件 - 哪些垃圾(dead tuple), wal文件不能被回收reuse - 什么情况下可能膨胀》
• 《解读用户最常问的PostgreSQL垃圾回收、膨胀、多版本管理、存储引擎等疑惑 - 经典》
• 《PostgreSQL 垃圾回收原理以及如何预防膨胀 - How to prevent object bloat in PostgreSQL》

2、wal 日志

很多原因可能导致wal堆积, 例如

• 归档失败
• 开启了逻辑/物理复制并且使用了slot, 复制延迟高, 或各种原因导致的下游未及时接收wal, 或下游已删除
• 配置了太大的wal_keep_segemnts

根据情况, 安全的删除不需要的wal文件. 注意一定要避免删除最后一次checkpoint后生成的WAL, 因为这些WAL文件要用于崩溃恢复.

3、log

数据库实例的报错日志、审计日志等.

按需删除即可.

4、未被引用的large object

如果你的应用在数据库中使用了大对象, 在大对象OID删除后一定要记得从 pg_largeobject 中清理对应的大对象.

清理方法参考PG文档.

5、未被引用的对象(表、物化视图、索引等)

这里指未被应用使用的“dead”对象, 例如在数据库经过了大量人的使用、应用的变迁后, 可能会有一些“dead”对象. 例如

• 人为的创建过一些临时表, 忘记清理.
• 应用已迁移
• 应用经过迭代后, 旧的不需要的对象忘记删除
• 应用迭代后, 某些索引可能不再需要

这些对象, 在甄别后可以在逻辑备份数据和结构后从数据库中删除.

6、冷数据

数据库中通常会有一些应用设计的类日志表, 例如用户的交易数据, 用户的访问记录, 传感器上报的数据, 都是时序属性.

越老的数据可能访问频率越低, 可考虑存储到冷存储(如OSS对象存储、数据仓库、数据湖中).

访问冷数据的方法举例:

• 应用自己去冷存储访问
• 数据库中使用类似 parquet_FDW 或 DuckDB引擎的方式外挂外部存储, 进行访问. 参考
  • 《既生瑜何生亮 | 有了 pg_duckdb/pg_mooncake 为什么还要 pg_parquet》
  • 《PostgreSQL iceberg 实时数据湖表 by pg_mooncake + moonlink》
  • 《Mooncake-Labs 架构进化, 核心是 moonlink》
  • 《pg_duckdb 1.0.0 正式发布, 有哪些重大更新?》
  • 《PG被DuckDB碾压,该反省哪些方面? DuckDB v0.10.3 在Macmini 2023款上的tpch性能表现如何? PostgreSQL使用duckdb_fdw 的tpch加速性能表现如何?》

7、不合理的索引. 可从占用空间较大的索引进行分析, 先搞定“捣蛋鬼”. 例如

• 不合理的多字段索引. 条件是a, 但是创建了a,b的复合索引
• 不合理的索引, 虽然被使用, 但是使用场合极少, 而且和非索引扫描的性能相差不大, 删掉不影响业务时, 可考虑删除.

二、重新设计 Schema

如果需要重新设计数据库结构, 需要结合应用需求文档, 与应用程序一起重构.