记一次数据库膨胀问题的解决过程
问题描述 表中记录了图片的base64 表大概有200w数据 占用了300G左右的空间 问题解决 查询了wiki和咨询了总部的pg数据库的同事得知了pg数据库存在膨胀的问题，即垃圾清理问题。所以及时删除了数据，空间也不会得到释放，只有手动进行垃圾清理后空间才能够得到释放，所以根据总部同事提供的排查方案，首先进行数据膨胀的排查。 pg数据库膨胀的问题排查 通过排查后发现，真正大的表是pg_toast_oid表，并且根据排查也发现其dead_tup比较大，实际上这是pg数据库存储大型数据的逻辑toast，后面知识点归纳也会提到这一点。pg_toast_oid表我们分析可知为表中的base64图片的压缩切片存储。 解决方案 大数据量的表VACUUM FULL很慢并且会锁表(VACUUM FULL的原理是复制新表，然后删除旧表)，所以直接在业务空闲期进行表的删除和重建。（由于数据已经实时的推送给了三方，在中间数据库中也有数据存储，所以这里没有进行备份，选择了直接删除。删除的时候对于这种大数据的表要进行批量删除，先将部分数据删除，然后在删除整个表，避免长时间的执行删除命令导致业务中断的时间过长。） 引申出来的一点关于数据库操作的建议：尽量避免长连接的操作，如果数据库的并发量比较高，长连接一直占用，将会导致数据库连接不够用，从而产生报错。 后续的过程中删除保留时间过长的数据，并且执行VACUUM FULL命令(kettle流程深夜执行) 此问题产生的思考 BASE64的图片不要存库。选择图片存储地址等方案。 所有的东西设计之初就要想好，并且日后有优化的想法时第一时间优化。 知识点总结归纳 https://juejin.cn/post/7016165148020703246 事务特性-ACID 原子性（Atomicity）：对数据库的操作要么执行，要么不执行。 一致性（Consistency）：不管操作是否执行，数据保持不变。 隔离性（Isolation）：多个事务并发访问，相互隔离。 持久性（Durability）：事务操作完成后，将永久保存。 事务并发产生的问题 脏读: 一个事务读取到了另一个未提交事务修改过的数据 不可重复读：同一个事务内，前后多次读取，读取到的数据内容不一致 幻读: 如果一个事务先根据某些搜索条件查询出一些记录，在该事务未提交时，另一个事务写入了一些符合那些搜索条件的记录（如insert、delete、update），就意味着发生了幻读。 MVCC-Multi-Version Concurrency Control Multi-Version Concurrency Control，多版本并发控制 通俗的讲，数据库中同时存在多个版本的数据，并不是整个数据库的多个版本，而是某一条记录的多个版本同时存在，在某个事务对其进行操作的时候，需要查看这一条记录的隐藏列事务版本id，比对事务id并根据事物隔离级别去判断读取哪个版本的数据。 MVCC实现的关键点 事务版本号： 事务每次开启前，都会从数据库获得一个自增长的事务ID，可以从事务ID判断事务的执行先后顺序。这就是事务版本号。 + 有关版本号的经验和思考： 版本号多用于控制并发，常用于乐观锁来解决并发问题，如MySQL的乐观锁解决并发。其基本做法是在数据库的表中引入版本的字段(version)，数据每进行一次更新，version+1，当提交更新的时候，比对版本号+1是否相等，不相等则认为当前数据是过期数据。 隐式字段：以MySQL为例，InnoDB存储引擎 trx_id: 记录操作数据事务的事务id roll_pointer: 指针，指向回滚的undo日志 row_id: 如果没主键和非NULL惟一键，有该主键列 undo log: 回滚日志 版本链：通过roll_pointer来指向undo_log 快照读和当前读： 快照读：读的是记录数据的可见版本 当前读：读的是记录数据的最新版本 ReadView：事务执行SQL时产生的读视图 PostgreSQL toast https://zhmin.github.io/posts/postgresql-toast/ toast说白了就是为了存储大型数据，表中的某个列的数据如果过大，则压缩，切分，放到toast表中，在之前问题的排查中，也可以看到真正数据量大的并不是数据存储的本表，而是pg_toast_oid表。 PostgreSQL VACUUM https://www.cnblogs.com/dbadaily/p/vacuum1.html https://www.cnblogs.com/dbadaily/p/vacuum2.html https://www.cnblogs.com/dbadaily/p/vacuum3.html https://www.cnblogs.com/dbadaily/p/vacuum4.html https://www.cnblogs.com/dbadaily/p/vacuum5.html 推荐阅读（我没读） https://www.interdb.jp/pg/