嘿。
我叫 Vanya，是一名 Java 开发人员。 碰巧我对 PostgreSQL 的工作很多——设置数据库、优化结构、性能，以及在周末玩一点 DBA。
最近整理了我们微服务中的几个数据库，写了一个java库 ，这使这项工作变得更容易，节省了我的时间，并帮助我避免了开发人员犯的一些常见错误。 今天我们要讲的就是这个库。
免责声明
我使用的 PostgreSQL 主要版本是 10。 我使用的所有 SQL 查询也在版本 11 上进行了测试。 支持的最低版本是 9.6。
史前
这一切都始于大约一年前，当时的情况对我来说很奇怪：索引的竞争性创建突然以错误结束。 像往常一样，索引本身在数据库中仍处于无效状态。 日志分析显示存在不足 。 然后我们开始......深入挖掘，我发现了数据库配置中的一大堆问题，并且卷起袖子，开始修复它们，我的眼睛闪闪发光。
问题一-默认配置
可能每个人都已经厌倦了 Postgres 的比喻，它可以在咖啡机上运行，但是......默认配置确实引发了许多问题。 至少值得关注 维护工作内存, 临时文件限制, 语句超时 и 锁超时.
在我们的例子中 维护工作内存 默认为 64 MB，并且 临时文件限制 大约 2 GB - 我们根本没有足够的内存来在大表上创建索引。
因此，在 pg-索引-健康 我收集了一个系列 ，我认为，每个数据库应该配置的参数。
问题二——重复索引
我们的数据库位于 SSD 驱动器上，并且我们使用 HA-配置多个数据中心、主控主机和 n- 副本数量。 磁盘空间对我们来说是非常宝贵的资源； 它的重要性不亚于性能和 CPU 消耗。 因此，一方面我们需要索引来快速读取，另一方面我们也不希望在数据库中看到不必要的索引，因为它们会占用空间并减慢数据更新速度。
而现在，一切都恢复了 并且已经看够了 ，我决定组织一次“大”清洗。 事实证明，开发人员不喜欢阅读数据库文档。 他们不太喜欢它。 因此，会出现两个典型的错误 - 主键上手动创建的索引和唯一列上类似的“手动”索引。 事实上，它们是不需要的——Postgres 会自己做所有的事情。 可以安全地删除此类索引，并且为此目的出现了诊断程序 .
问题三 - 相交索引
大多数新手开发人员都会在单个列上创建索引。 渐渐地，在彻底体验了这项业务之后，人们开始优化查询并添加包含多个列的更复杂的索引。 这就是列上索引的显示方式 A, A + B, A + B + C 等等。 这些索引中的前两个可以安全地丢弃，因为它们是第三个索引的前缀。 这也节省了大量的磁盘空间，并且有针对此的诊断 .
问题四-没有索引的外键
Postgres 允许您创建外键约束而无需指定后备索引。 在许多情况下，这不是问题，甚至可能不会表现出来......暂时......
我们也一样：只是在某个时间点，主控主机开始向我们“添加”一个按计划运行并清除测试订单数据库的作业。 CPU和IO浪费了，请求减慢并且超时，服务五百。 快速分析 显示查询如下：
delete from <table> where id in (…)当然，本例中目标表中存在按id的索引，根据条件删除的记录很少。 似乎一切都应该有效，但是，可惜的是，事实并非如此。
奇妙的人来救援了 解释分析 并表示除了删除目标表中的记录外，还有引用完整性检查，并且在其中一个相关表上此检查失败 顺序扫描 由于缺乏合适的索引。 于是诊断学诞生了 .
问题五——索引中的空值
默认情况下，Postgres 在 btree 索引中包含空值，但通常不需要它们。 因此，我努力尝试抛出这些空值（诊断 )，按类型在可为空的列上创建部分索引 where <A> is not null。 通过这种方式，我能够将其中一个索引的大小从 1877 MB 减少到 16 KB。 在其中一项服务中，由于从索引中排除了空值，数据库大小总共减少了 16%（绝对数量减少了 4.3 GB）。 通过非常简单的修改即可节省大量磁盘空间。 🙂
问题六——缺少主键
由于机制的性质 这种情况是有可能的 当表的大小由于大量死记录而快速增长时。 我天真的以为这不会威胁到我们，也不会发生在我们的基地，因为，哇！！！，我们是普通的开发者……我是多么愚蠢和天真啊……
有一天，一次精彩的迁移获取并更新了一个大型且经常使用的表中的所有记录。 我们的表大小突然增加了 100 GB。 这真是太遗憾了，但我们的不幸经历并没有就此结束。 15 小时后，该桌子上的自动清理结束后，很明显物理位置不会返回。 我们无法停止服务并使 VACUUM FULL，因此我们决定使用 。 然后事实证明 pg_repack 不知道如何处理没有主键或其他唯一性约束的表，并且我们的表没有主键。 于是诊断学诞生了 .
在库版本中 0.1.5 添加了从大量表和索引中收集数据并及时响应的能力。
问题七、八——索引不足和未使用的索引
以下两个诊断是： и – 最近才以最终形式出现。 关键是它们不能只是被拿走和添加。
正如我已经写过的，我们使用具有多个副本的配置，并且不同主机上的读取负载根本不同。 结果发现，某些主机上的某些表和索引实际上没有被使用，为了分析，需要收集集群中所有主机的统计信息。 这对于集群中的每台主机也是必要的；您不能仅在主主机上执行此操作。
这种方法允许我们通过删除从未使用过的索引以及向很少使用的表添加缺失的索引来节省数十GB的空间。
作为结论
当然，对于几乎所有诊断，您都可以配置 。 这样，您可以快速在应用程序中实施检查，防止出现新错误，然后逐步修复旧错误。
一些诊断可以在推出数据库迁移后立即在功能测试中执行。 这也许是我的图书馆最强大的功能之一。 可以在以下位置找到使用示例 .
仅在真实数据库上对未使用或丢失的索引以及膨胀进行检查才有意义。 收集到的值可以记录在 或发送至监控系统。
我真的希望 pg-索引-健康 将是有用的并且有需求。 您还可以通过报告您发现的问题并提出新的诊断建议来为库的发展做出贡献。
来源： habr.com