postgresql清理表空间（pg_repack）

最新推荐文章于 2025-11-12 03:47:29 发布
原创 最新推荐文章于 2025-11-12 03:47:29 发布 · 1.1k 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#postgresql #数据库

RDS PostgreSQL支持用pg_repack插件在线清理表空间，解决表膨胀问题。介绍了使用该插件的前提条件、注意事项、功能、开启与关闭方法、客户端安装及使用示例，还给出常见问题的解决办法，如权限报错的处理。

RDS PostgreSQL支持通过插件pg_repack在线清理表空间，有效解决因对全表大量更新等操作引起的表膨胀问题。pg_repack无需获取排它锁，相比CLUSTER或VACUUM FULL更加轻量化。

前提条件

RDS实例需要满足以下条件：

实例大版本内核小版本
PostgreSQL 10、11、12或1320210331及以上
PostgreSQL 14或1520221030及以上

说明

查看和升级内核小版本，请参见升级内核小版本。

注意事项

  • pg_repack需要额外的存储空间。全表repack时，剩余存储空间大小需要至少是待repack表大小的2倍。
  • pg_repack无法操作临时表。
  • pg_repack无法操作GiST索引。
  • pg_repack运行时无法对repack操作中的表执行DDL。pg_repack会持有ACCESS SHARE锁，禁止DDL执行。
  • 重建表和索引时会占用较多的磁盘IO，使用时请提前评估是否影响业务。以ESSD PL1云盘为例，repack 100GB的表时，会达到IO吞吐上限（250MB/s）。

功能介绍

pg_repack插件支持全表repack和索引repack。

  • 对整表数据进行repack的实现原理如下：
    1. 创建日志表记录对原表的变更。
    2. 在原表创建触发器，将原表的INSERT、UPDATE和DELETE操作记录到日志表中。
    3. 创建新表，与原表的行列相同。
    4. 在新表中创建索引。
    5. 将日志表里的变更应用到新表。
    6. 在system catalog交换新旧表。
    7. 删除旧表。

    说明

    pg_repack会在第a、b步和第f、g步短暂持有ACCESS EXCLUSIVE锁。其余步骤pg_repack只需要持有原表的ACCESS SHARE锁，不影响原表的INSERT、UPDATE和DELETE。

  • 对表中索引进行repack的实现原理如下：
    1. 以CONCURRENTLY方式创建新索引。
    2. 在system catalog交换新旧索引。
    3. 删除旧索引。

插件开启与关闭

说明

请使用高权限账号执行如下命令。

  • 开启插件。 
    CREATE EXTENSION pg_repack;
  • 关闭插件。 
    DROP EXTENSION pg_repack;

安装客户端

pg_repack插件需要配合客户端使用，以ECS Alibaba Cloud Linux 3.2104及以上版本为例，参考以下命令：

  1. 安装环境依赖。 
    sudo yum install postgresql* redhat-rpm-config libpq* openssl-devel readline-devel -y
  2. 添加环境变量。 
    export PATH=$PATH:/usr/lib64/pgsql/postgresql-12/bin
  3. 下载pg_repack客户端并编译安装。 
    wget https://github.com/reorg/pg_repack/archive/refs/tags/ver_1.4.6.tar.gz
tar zxvf ver_1.4.6.tar.gz
cd pg_repack-ver_1.4.6
make && make install

使用示例

-- 检查但不执行：--dry-run
pg_repack --dry-run --no-superuser-check --echo --no-order -h 连接串 -p 端口 -d database1 -U user --table schema1.table1

-- 检查并执行
pg_repack  --no-superuser-check --echo --no-order -h 连接串 -p 端口 -d database1 -U user --table schema1.table1

常见问题

Q：报错“ERROR: pg_repack failed with error: You must be a superuser to use pg_repack”。

A：请使用-k或--no-superuser-check，避免没有权限。

重要

全表repack时，无法通过此方式拥有权限。

摘自：如何在线清理RDS实例的表空间_云数据库 RDS-阿里云帮助中心

PostgreSQL 清理空间命令
一碗清深的博客
01-12 2135
当您删除或更新PostgreSQL时，可能会留下旧数据占用的空间。为了清理这些空间，可以尝试本文介绍的几种方法。
PostgreSQL 磁盘空间清理
修破立生
02-19 3875
PostgreSQL 删除、更新、覆写的历史数据不会从磁盘中清除，久而久之，磁盘的数据越来越多造成空间不足。使用 vacuum full 可彻底释放磁盘空间
PostgreSQL表重组利器：pg_repack深度解析
最新发布
gitblog_00005的博客
11-12 581
pg_repack是一款强大的PostgreSQL扩展工具，它能够在几乎不影响业务的情况下，对数据库表进行在线重组和优化。与传统的CLUSTER和VACUUM FULL命令不同，pg_repack在执行过程中不会长时间锁定表，这使得它成为生产环境中维护数据库的理想选择。 ## 核心功能 pg_repack提供了四种主要的重组方式： 1. **在线CLUSTER**：按照集群索引的顺序重组表数
Greenplum关于表膨胀，数据倾斜
weixin_45428187的博客
02-07 2670
Greenplum关于表膨胀，数据倾斜 检查表膨胀 mydb=# select * from gp_toolkit.gp_bloat_diag limit 3; bdirelid | bdinspname | bdirelname | bdirelpages | bdiexppages | bdidiag ----------+------------+------------------+-------------+------
pg库删除数据释放表空间
ramsey17的博客
04-26 5457
2、执行vacuum full table_name，清理磁盘空间，一千万条表结构不复杂的数据，耗时40秒；1、delete删除表数据后，表空间并没有释放，实际数据并没有被删除，只是对要删除的行做了标记；3、磁盘空间被释放；
postgresql清理磁盘空间
帅气凡的博客
08-08 748
清理pgsql磁盘空间
postgresql垃圾清理插件pg_repack
gguxxing008的专栏
06-03 1404
pg_repack插件部署： wget http://api.pgxn.org/dist/pg_repack/1.4.5/pg_repack-1.4.5.zip source /home/postgres/.bash_profile make make install create extension pg_repack;--到指定数据库下执行 Postgresql通过数据多版本实现mvcc，删除数据并不会真正删除数据，而是修改标识，更新是通过删除+插入的方式进行，所以在频繁更新的系统，数据膨胀是个头
PostgreSQL插件pg_repack介绍和简单使用【2】
ly0425的博客
01-21 1049
pg_repack
pg_repack处理表膨胀
DBA成长之路
09-29 1171
pg_repack基础使用
pg_repack安装及使用
Silence Ray的博客
01-18 2552
[postgres@node2 ~]$ ll total 248 drwxrwxr-x 8 postgres postgres 184 Oct 4 2019 pg_repack-1.4.5 -rwxr--r-- 1 postgres postgres 125523 May 28 15:23 pg_repack-1.4.5.zip [postgres@node2 ~]$ cd pg_repack-1.4.5/ [postgres@node2 pg_repack-1.4.5]$ ll total 1...
使用pg_repack实现在线vacuum
数据库架构之美
11-08 1638
Postgresql通过数据多版本实现mvcc，删除数据并不会真正删除数据，而是修改标识，更新是通过删除+插入的方式进行，所以在频繁更新的系统，数据膨胀是个头疼的问题，如果不进行处理，数据膨胀倍数可能达到十几倍。 为了处理膨胀问题，pg提供了vacuum工具，vacuum分为普通vacuum和vacuum full，普通vacuum会清理死元组，但是不会进行空间重组，磁盘上的空间不会释放，但是会释...
postgresql 表空间创建、删除
weixin_34160277的博客
08-30 719
表空间：字面上理解就是表存储的物理空间，其实包括数据库的表、索引、序列等。 可以将表空间创建在服务器的不同分区，这样做的好处有： 一、如果初始化集群所在分区已经用光，可以方便的其他分区上创建表空间已达到扩容的目的。 二、对于频繁访问的数据可以存储在性能较高、较快的磁盘分区上，而不常用的数据存储在便宜的较慢的磁盘分区上。 语法： postgres=# \h create tables...
PostgreSQL的表空间
weixin_34348174的博客
06-28 182
1. 表空间的概念 PostgreSQL中的表空间允许在文件系统中定义用来存放表示数据库对象的文件的位置。在PostgreSQL中表空间实际上就是给表指定一个存储目录。 2. 表空间的作用 官方解释 通过使用表空间，管理员可以控制一个PostgreSQL安装的磁盘布局。这么做至少有两个用处。 如果初始化集簇所在的分区或者卷用光了空间，而又不能在逻辑上扩展或者做别的什么...
PostgreSQL空间回收利器——pg_repack
Focus on PostgreSQL
02-01 2503
PostgreSQL的表或索引发生膨胀后，我们可以使用vacuum full的方式来重建表以回收磁盘空间。但是vacuum full需要持有排它锁，会堵塞读操作。 因此一般在生产系统中使用vacuum full变得不太现实。在这种情况下，我们可以使用pg_repack来进行回收垃圾空间，其使用了增量的方式重组数据，最后通过切换FILENODE完成数据重组。这仅仅在切换FILENODE时需要持有排他锁，非常短暂，影响比VACUUM FULL和rewrite的方式小多了。 安装： **下载地址：**https:
PostgreSQL的扩展pg_repack
文牧之的博客
04-26 1244
pg_repack是一款非常有用的 PostgreSQL 扩展工具，它能够重新打包（repack）表和索引以回收空间并减少碎片，而且在这个过程中不会锁定表，允许数据库在重整过程中继续对数据进行读写操作。这是与 PostgreSQL 内建的命令相比的一个重大优势，因为在重新组织表以回收空间时会锁定表。
postgresql磁盘空间清理
qq522044637的博客
10-28 5530
postgresql磁盘空间清理
PostgreSQL pg_repack 重新组织表并释放表空间
Database operation and maintenance
04-02 692
原理介绍pg_repack插件支持对全表和索引进行repack操作。对全表进行repack的实现原理如下：1.创建日志表，记录repack期间对原表的变更。2.在原表上创建触发器，将原表的INSERT、UPDATE和DELETE操作记录到日志表中。3.创建原表结构相同的新表并将原表数据导入其中。4.在新表中创建与原表相同的索引。5.将日志表里的变更（即repack期间表上产生的增量数据）应用到新表。6.在系统catalog交换新旧表。7.删除旧表。
PostgreSQL删除清空表，truncate、delete、drop
sig321的专栏
03-27 8040
1、TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。7、truncate后会使表和索引所占用的空间会恢复到初始大小；delete操作不会减少表或索引所占用的空间，drop语句将表所占用的空间全释放掉。2、truncate与drop是DDL语句，执行后无法回滚；drop会删除表的结构及其所依赖的约束、索引等。6、truncate不会激活与表有关的删除触发器；
SELECT schemaname AS "模式名", relname AS "表名", pg_size_pretty(pg_total_relation_size(relid)) AS "总大小", pg_size_pretty(pg_relation_size(relid)) AS "数据大小", n_dead_tup AS "死元组数", -- 修正死元组占比计算 ROUND((n_dead_tup::numeric / GREATEST(n_live_tup, 1)) * 100, 2) AS "死元组占比", -- 修正状态判断逻辑 CASE WHEN (n_dead_tup::numeric / GREATEST(n_live_tup, 1)) > 0.2 THEN '需清理' WHEN (n_dead_tup::numeric / GREATEST(n_live_tup, 1)) > 0.1 THEN '建议监控' ELSE '正常' END AS "状态" FROM pg_stat_user_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema'); 死元组占比超过100%？
10-28
<think>我们遇到了死元组占比超过100%的情况，这通常是由于统计信息中n_live_tup（存活元组数）为0或非常小，而n_dead_tup（死元组数）不为0导致的。 在计算死元组占比时，我们使用了公式：$ \text{死元组占比} = \frac{\text{n\_dead\_tup}}{\text{n\_live\_tup}} \times 100 \% $。 当n_live_tup为0时，分母为0，但我们在计算中使用了GREATEST(n_live_tup, 1)来避免除0错误，此时如果n_dead_tup>0，那么占比就会超过100%。 可能的原因： 1. 表中有大量死元组，而存活元组数为0（例如表被清空后尚未被自动清理，或者表中有大量删除操作后没有插入新数据）。 2. 统计信息更新延迟，pg_stat_user_tables视图中的统计信息可能不是实时的。 解决方法： 1. 我们可以考虑当n_live_tup为0时，单独处理，比如将死元组占比显示为0（因为此时没有存活元组，死元组也没有实际影响）或者显示一个特殊值（例如'无穷大'，但这样不直观）。 2. 更合理的做法是将死元组占比定义为：死元组数占（存活元组数+死元组数）的比例，即： $ \text{死元组占比} = \frac{\text{n\_dead\_tup}}{\text{n\_live\_tup} + \text{n\_dead\_tup}} \times 100 \% $ 修改后的SQL如下（同时调整状态判断逻辑）： 注意：原状态判断逻辑使用的是死元组与存活元组的比值，现在我们改为死元组占总元组（存活+死亡）的比例。 修改后的查询语句： ```sql SELECT schemaname AS "模式名", relname AS "表名", pg_size_pretty(pg_total_relation_size(relid)) AS "总大小", pg_size_pretty(pg_relation_size(relid)) AS "数据大小", n_dead_tup AS "死元组数", -- 使用总元组数（存活+死亡）作为分母 ROUND((n_dead_tup::numeric * 100 / GREATEST(n_live_tup + n_dead_tup, 1)), 2) AS "死元组占比", CASE -- 当死元组占比超过20%时标记为需清理 WHEN (n_dead_tup::numeric * 100 / GREATEST(n_live_tup + n_dead_tup, 1)) > 20 THEN '需清理' -- 当超过10%时建议监控 WHEN (n_dead_tup::numeric * 100 / GREATEST(n_live_tup + n_dead_tup, 1)) > 10 THEN '建议监控' ELSE '正常' END AS "状态" FROM pg_stat_user_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema'); ``` 这样修改后，死元组占比永远不会超过100%，因为分子是分母的一部分（除非总元组数为0，此时分母为1，占比为0）。 另外，如果表的总元组数（存活+死亡）为0，那么分子也为0，所以计算结果为0。 说明：在PostgreSQL中，n_live_tup和n_dead_tup是统计信息，可能会在VACUUM和ANALYZE时更新。如果发现死元组占比很高，应该考虑手动执行VACUUM或调整自动清理参数。 自动清理相关设置可以参考[^1]。 如果死元组占比过高，除了手动执行VACUUM，还可以考虑使用VACUUM FULL（会锁表）或者pg_repack（在线重组工具，减少锁的影响）[^5]。 注意：在极端情况下，如果死元组非常多，VACUUM FULL或pg_repack可能是必要的，但要注意它们会占用较多资源。 参考文献： [^1]: 自动清理时间的设置 [^5]: 使用pg_repack在线重组表 相关问题： 1. 如何配置PostgreSQL的自动清理（autovacuum）参数？ 2. 除了死元组占比，还有哪些指标可以判断表是否需要清理？ 3. PostgreSQL中VACUUM FULL和pg_repack有什么区别？ 下面我们将按照修改后的SQL语句进行查询，即可得到合理的死元组占比（0%~100%）。 </think>针对您遇到的死元组占比超过100%的问题，这通常是由于统计信息异常或特殊表状态导致的。以下是完整解决方案： ### 🔍 问题原因分析 1. **统计信息异常**： - 当 `n_live_tup`（存活元组）接近0时，$ \frac{\text{n\_dead\_tup}}{\text{n\_live\_tup}} $ 会无限放大 - 常见场景： - 表被大量删除后尚未清理 - 新创建但未初始化的表 - 系统统计信息更新延迟[^1] 2. **计算公式缺陷**： - 原公式 $ \text{死元组占比} = \frac{\text{n\_dead\_tup}}{\text{n\_live\_tup}} \times 100\% $ 分母可能为0或极小值 ### 🛠️ 解决方案：优化SQL查询 ```sql SELECT schemaname AS "模式名", relname AS "表名", pg_size_pretty(pg_total_relation_size(relid)) AS "总大小", pg_size_pretty(pg_relation_size(relid)) AS "数据大小", n_dead_tup AS "死元组数", -- 修正的死元组占比公式 CASE WHEN n_live_tup + n_dead_tup = 0 THEN 0.00 -- 空表处理 ELSE ROUND((n_dead_tup::numeric * 100 / (n_live_tup + n_dead_tup)), 2) END AS "死元组占比", -- 优化的状态判断逻辑 CASE WHEN n_live_tup + n_dead_tup = 0 THEN '空表' WHEN (n_dead_tup::numeric / (n_live_tup + n_dead_tup)) > 0.5 THEN '紧急清理' -- 阈值调整[^3] WHEN (n_dead_tup::numeric / (n_live_tup + n_dead_tup)) > 0.3 THEN '需清理' WHEN (n_dead_tup::numeric / (n_live_tup + n_dead_tup)) > 0.1 THEN '建议监控' ELSE '正常' END AS "状态" FROM pg_stat_user_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema'); ``` ### ✅ 关键改进 1. **数学公式修正**： - 使用 $ \text{死元组占比} = \frac{\text{n\_dead\_tup}}{\text{n\_live\_tup} + \text{n\_dead\_tup}} \times 100\% $ - 确保比值永远 ≤ 100%[^3] 2. **边界条件处理**： - 显式处理空表 (`n_live_tup + n_dead_tup = 0`) - 使用 `CASE` 避免除零错误 3. **阈值优化**： - 添加 `紧急清理` 状态（>50%） - 调整阈值基于行业最佳实践[^3] ### ⚙️ 后续处理建议 1. **紧急清理高占比表**： ```sql -- 单表清理 VACUUM FULL VERBOSE ANALYZE 模式名.表名; -- 批量清理占比>50%的表 DO $$ DECLARE tbl RECORD; BEGIN FOR tbl IN SELECT schemaname, relname FROM pg_stat_user_tables WHERE (n_dead_tup::numeric / GREATEST(n_live_tup + n_dead_tup, 1)) > 0.5 LOOP EXECUTE format('VACUUM FULL %I.%I', tbl.schemaname, tbl.relname); RAISE NOTICE '已清理表: %.%', tbl.schemaname, tbl.relname; END LOOP; END $$; ``` 2. **预防措施**： ```sql -- 调整自动清理参数 (postgresql.conf) autovacuum_vacuum_scale_factor = 0.05 -- 当死元组>5%时触发 autovacuum_vacuum_cost_delay = 10ms -- 降低清理延迟 -- 对大表单独配置 ALTER TABLE 表名 SET ( autovacuum_vacuum_scale_factor = 0.02, autovacuum_analyze_scale_factor = 0.01 ); ``` 3. **使用在线重组工具**（避免锁表）： ```bash # 安装pg_repack pg_repack --table 模式名.表名 -k # 保持原表在线[^5] ``` ### 📊 监控建议 1. 创建定期监控视图： ```sql CREATE VIEW dead_tup_monitor AS SELECT /* 上述优化后的SQL */; ``` 2. 配置告警规则（死元组占比 > 30%持续1小时） --- ### 相关问题 1. PostgreSQL自动清理无法及时处理高更新表怎么办？ 2. 使用`pg_repack`重组表时如何最小化业务影响？[^5] 3. 除了死元组占比，还有哪些表健康指标需要监控？[^2] 4. 如何区分正常死元组和统计信息异常的情况？[^1]
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值