☰

PostgreSQL：索引膨胀导致性能下降

tag-icon • tag-icon performance postgresql
PostgreSQL：索引膨胀导致性能下降


我在 CentOS 4.4 系统上运行 PgSQL 8.1（遗憾的是无法升级）。PgSQL
守护进程上运行着一个 Java 应用程序，我们大约每 2 个月重新索引一次数据库。同样重要的是：数据库没有增长。

看起来膨胀现在比以前来得更快，而且有增加的趋势。
我的配置在这里可用，自动清理守护程序已启用并经常运行：pastebin.com/RytNj7dK

您还可以找到此查询的输出 wiki.postgresql.org/wiki/Show_database_bloat

运行重新索引 3 小时后：http://pastebin.com/raw.php?i=75fybKyd
运行重新索引 72 小时后：http://pastebin.com/raw.php?i=89VKd7PC

有人知道我应该做哪些调整才能消除日益严重的肿胀吗？

感谢您的帮助

附言：由于反垃圾邮件预防系统，我必须删除前两个链接的前两个 http:// 前缀。

答案1

不幸的是，如果你有一个活跃的数据库（有大量插入/更新/删除操作的数据库），你将要经历索引膨胀——这只是数据库生活中的现实。您能做的最好的事情就是希望将膨胀速度减慢到重新索引间隔合理的程度。

在这方面我能给你的最好建议是升级到 Postgres 的较新版本（8.3 或更高版本）：这是 Postgres 引入的仅堆元组支持。
现在就在您的 (8.1) 系统上任何就索引而言，更新一行相当于删除/插入，因此索引会膨胀。8.3 及更高版本不会触及索引，除非必须（“如果该行仍然适合它所在的页面”）。

在升级到具有 HOT 支持的 Postgres 版本之后，如果您的UPDATEs 接触到索引列，或者您的UPDATEs 大幅增加了行的大小而必须将其移动到新页面，您可能仍然会遇到索引膨胀，但是只要您的索引策略是合理的并且您的行大小相对稳定，这些情况应该相对不常见，因此索引膨胀问题应该不是什么大问题。

处理索引膨胀的一些其他一般策略：

  1. 主键索引
    你在这里很不走运——你需要REINDEX拿走桌子锁。
  2. 其他指数
    • 选项1： DROP并重新使用CREATE非关键索引
      这样做的好处是不会锁定表，但缺点是在重建索引时会删除索引。
    • 选项 2：关键索引的索引改组
      与上面的删除/创建过程不同，首先创建一个新的索引，完成后删除旧索引并重命名新索引以取代旧索引。
      这样做的好处是不会锁定表，并且可以让原始索引继续工作（尽管有点臃肿）。主要缺点是您必须重命名索引以保持命名约定的合理性——这是一个额外的手动步骤。

相关内容