原文链接：https://hakibenita.com/postgresql-unused-index-size
作者 | Haki Benita
编译 | 伍杏玲
出品 | AI科技大本营（ID：rgznai100）
【导语】春节将至，俗话说“腊月二十四，掸尘扫房子”，很多人会在腊月二十四给家里做大扫除迎新春。
近年来数据呈爆发式增长，你是否和本文作者一样，常常收到数据库空间的告警呢？那来给数据库做一场“大扫除”试试看？
作者讲述亲身经历，在没有删除单个索引或删除任何数据下，最终释放了超过70GB的未优化和未利用的空间，还意外释放 20GB 未使用索引空间。
咱们一起看看他是如何做到的：
每隔几个月，我都会收到数据库即将用完空间的报警。一般我看到报警后，就再增加一些存储空间，不会多投入精力在那。
但这次我们想给数据库来一次“大扫除”，效果惊人：在没有删除单个索引或删除任何数据下，最终释放了超过 70GB 的未优化和未利用的空间！还有清除了额外的 20GB 未使用的索引值！
这是其中一个数据库的释放存储的图：
删除未被使用过的索引
未被使用的索引是一把“双刃剑”。我们创建它的本意是为了让搜索更快，但它也占用一定的空间，将会影响新增和更新的速度。所以没被使用的索引是我们在清除存储首先要检查的。
查找未使用的索引：
-
SELECT
-
relname,
-
indexrelname,
-
idx_scan,
-
idx_tup_read,
-
idx_tup_fetch,
-
pg_size_pretty(pg_relation_size(indexrelname::regclass))
as size
-
FROM
-
pg_stat_all_indexes
-
WHERE
-
schemaname =
'public'
-
AND indexrelname NOT LIKE
'pg_toast_%'
-
AND idx_scan =
0
-
AND idx_tup_read =
0
-
AND idx_tup_fetch =
0
-
ORDER BY
-
size DESC;
这个查询语句是查找自上次重置统计信息以来，未被扫描或获取的索引。
有一些索引看起来没有被使用，但实际上已被使用了：
可参考：https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-INDEXES-VIEW
用那些有一定的时间没更新的表里唯一或主键约束的索引。这些索引看起来好像没有被使用过，但我们也不能随意处置它们。
在实际找这些可删除的未使用的索引时，刚开始很耗时耗力，需要很多思考和决策的。
在这过程中，我发现在检查完列表后，重置统计信息计数器是个好方法。PostgreSQL 提供了一些功能来重置不同级别的统计信息。当我发现“疑似”未使用的索引时，或者添加新索引代替旧索引时，通常会重置表的计数器并等待一段时间：
-
-- Find table oid by name
-
SELECT oid
FROM pg_class c WHERE relname =
'table_name';
-
-- Reset counts
for all indexes of table
-
SELECT pg_stat_reset_single_table_counters(
14662536);
我们每隔一段时间执行一次上述操作来看看有没有要删除的未使用索引。
索引和表格
当我们在更新表中的行时，通常 PostgreSQL 将元组标记为无效，并在下一个可用空间中添加更新的元组，此过程将创建“bloat”，可能会导致表消耗超出实际所需的空间，因此我们需要清除索引bloat。
那我们需要重建索引，PostgreSQL提供了一种使用REINDEX命令就地重建现有索引的方法，无需自己删除和创建索引（https://www.postgresql.org/docs/current/sql-reindex.html）：
-
REINDEX INDEX index_name;
-
同时重建索引：先前的方法将在表上获得一个锁，防止在操作进行时更改，这似乎不大好使，如果在不锁定索引下重建索引的话，可以同时重建索引：
REINDEX INDEX CONCURRENTLY index_name;
使用 REINDEX CONCURRENTLY 时，PostgreSQL将创建一个名称后缀为“_ccnew”的新索引，并同步对该表更改。重建完成后，它将用新索引切换旧索引，并删除旧索引。
如果由于某种原因你不得不在中间停止重建，也不会删除新索引，它将处于无效状态并占用空间。为了识别在这些无效索引REINDEX，可使用以下查询：
-
SELECT
-
c.relname
as index_name,
-
pg_size_pretty(pg_relation_size(c.oid))
-
FROM
-
pg_index i
-
JOIN pg_class c ON i.indexrelid = c.oid
-
WHERE
-
--
New index built using REINDEX CONCURRENTLY
-
c.relname LIKE
'%_ccnew'
-
-- In INVALID state
-
AND NOT indisvalid
-
LIMIT
10;
一旦重建过程没有其他执行，应该可以安全删除所有剩余的无效索引。
激活 B 树索引 Deduplication
PostgreSQL 13引入了一种在B树索引存储重复值的新方法，称为“B树 Deduplication”（重复数据删除）。
对于每个索引值，B树索引将在其叶中同时保留值和指向行的指针（TID）。索引值越大，索引越大。PostgreSQL 12 当索引包含许多重复值时，这些重复值将存储在索引叶中。如此一来，将占用很多空间。
从PostgreSQL 13开始，将 B树Deduplication后，重复值仅存储一次，这对具有许多重复值的索引的大小产生影响。
在PostgreSQL 13中，索引 Deduplication 默认情况下处于启用状态：
-
-- Activating de-deduplication
for a B-Tree index, this is the
default:
-
CREATE INDEX index_name ON table_name(column_name) WITH (deduplicate_items = ON)
如果要从PostgreSQL 13 之前的版本迁移的话，需要使用 REINDEX 命令来重建索引，来充分利用索引去重复项的优势。
为了说明 B树 Deduplication 对索引大小的影响，可创建一个包含唯一列和非唯一列的表，填充1M行。在每列上创建两个B树索引，一个启用 Deduplication，另一个禁用 Deduplication：
-
db=
# CREATE test_btree_dedup (n_unique serial, n_not_unique integer);
-
CREATE TABLE
-
-
db=
# INSERT INTO test_btree_dedup (n_not_unique)
-
SELECT (random() *
100)::int FROM generate_series(
1,
1000000);
-
INSERT
0
1000000
-
-
db=
# CREATE INDEX ix1 ON test_btree_dedup (n_unique) WITH (deduplicate_items = OFF);
-
CREATE INDEX
-
-
db=
# CREATE INDEX ix2 ON test_btree_dedup (n_unique) WITH (deduplicate_items = ON);
-
CREATE INDEX
-
-
db=
# CREATE INDEX ix3 ON test_btree_dedup (n_not_unique) WITH (deduplicate_items = OFF);
-
CREATE INDEX
-
-
db=
# CREATE INDEX ix4 ON test_btree_dedup (n_not_unique) WITH (deduplicate_items = ON);
-
CREATE INDEX
我们比较下四个索引的大小：
可以看到，Deduplication对唯一索引没有影响，但对有重复值的索引却有重大影响。不巧的是，由于当时 PostgreSQL 13 刚推出，我们的云提供商未提供支持，因此我没使用Deduplication来清除空间。
清除表中的Bloat
就像在索引中一样，表也可能包含死元组，可能会导致碎片化。与包含关联表中数据的索引不同，不能仅简单地重新创建表。要重新创建表，必须创建一个新表，迁移数据，同步数据，在其他表中创建所有索引……等完成这操作后，才能将旧表切换为新表。
有几种方法可以重建表：
重新创建表：如上所述，使用这种方法通常需要大量的开发工作，尤其是在重建正在使用表的情况下。
清理表：PostgreSQL 提供 VACUUM FULL 命令回收表中死元组占用的空间的方法（https://www.postgresql.org/docs/current/sql-vacuum.html）
-
-- Will lock the table
-
VACUUM FULL table_name;
上面两种方法需要大量的精力或需要停机一段时间，这两种用于重建表的内置选项都不理想。
使用pg_repack
pg_repack 是一种在不停机的情况下重建表和索引较好的解决方案。创建扩展名来使用pg_repack：
CREATE EXTENSION pg_repack;
rebuild 表和索引：
$ pg_repack -k --table table_name db_name
为了在不停机的情况下重建表，该扩展程序将创建一个新表，将原始表中的数据加载到该表中，同时使其与新数据保持最新，然后再重建索引。该过程完成后，将切换两个表并删除原始表：https://reorg.github.io/pg_repack/#details
使用pg_repack重建表时注意两点：
所需的存储量大约为要重建表的容量：该扩展会创建另一个表来将数据复制到该表，因此它需要的附加存储量约为表及其索引的大小。
可能需要手动清理：如果rebuild过程失败或手动停止，可能会留下一些东向西，需手动清理。
在不停机 pg_repack 下重建表和索引，需额外的存储空间才能运行，所以当你已经没有存储空间时，这不是一个好选择。你需要先检查看看是否有可用的存储空间。
继续清除
看到这，我们已经使用了所有的常规技术来清理了很多空间，但是……还有更多的空间可以删除！重建索引后，在查看索引大小时，有件趣事引起我们注意。
我们其中较大的表是存储交易数据：用户付款后，可选择取消退款。这种情况很少发生，只有一小部分交易被取消。
在这个交易表，既有购买用户又有取消用户的外键，并且每个字段都定义了一个B树索引。采购用户对此具有 NOT NULL 约束，因此所有行均具有值。另一方面，取消用户可以为空，只有一小部分行保存任何数据，取消用户字段中的大多数值均为NULL。
我们希望取消用户的索引比购买用户的索引小得多，但原来它们是完全相同的。之前我总是被教导说 NULL 不被索引，但是在PostgreSQL中却被索引！这个“ Aha”时刻让我们意识到，之前无缘无故写了许多不必要的索引值。
这是我们为取消用户提供的原始索引：
CREATE INDEX transaction_cancelled_by_ix ON transactions(cancelled_by_user_id);
下面用不包含空值的部分索引替换了索引：
-
DROP INDEX transaction_cancelled_by_ix;
-
-
CREATE INDEX transaction_cancelled_by_part_ix ON transactions(cancelled_by_user_id)
-
WHERE cancelled_by_user_id IS NOT
NULL;
重新索引后的完整索引大小为769MB，空值超过99％。排除空值的部分索引小于5MB，减少了该指标的 99％ 以上！
为了确保不需要这些 NULL 值，我们重置了表上的统计信息，等了一段时间后，我们发现索引的使用就像旧索引一样！我们仅削减了超过 760MB 的未使用索引元组，并没有影响性能！
利用部分索引
一旦我们尝到了局部索引的“甜头”后，我们就会发现还会有更多这样的索引。为了找到他们，我们写了一个查询来搜索具有high字段的索引null_frac，PostgreSQL估计的列值百分比为NULL：
-
-- Find indexed columns with high null_frac
-
SELECT
-
c.oid,
-
c.relname
AS index,
-
pg_size_pretty(pg_relation_size(c.oid))
AS index_size,
-
i.indisunique
AS unique,
-
a.attname
AS indexed_column,
-
CASE s.null_frac
-
WHEN
0 THEN
''
-
ELSE to_char(s.null_frac *
100,
'999.00%')
-
END
AS null_frac,
-
pg_size_pretty((pg_relation_size(c.oid) * s.null_frac)::bigint)
AS expected_saving
-
-- Uncomment to
include the index definition
-
--, ixs.indexdef
-
-
FROM
-
pg_class c
-
JOIN pg_index i ON i.indexrelid = c.oid
-
JOIN pg_attribute a ON a.attrelid = c.oid
-
JOIN pg_class c_table ON c_table.oid = i.indrelid
-
JOIN pg_indexes ixs ON c.relname = ixs.indexname
-
LEFT JOIN pg_stats s ON s.tablename = c_table.relname
AND a.attname = s.attname
-
-
WHERE
-
-- Primary key cannot be partial
-
NOT i.indisprimary
-
-
-- Exclude already partial indexes
-
AND i.indpred IS
NULL
-
-
-- Exclude composite indexes
-
AND array_length(i.indkey,
1) =
1
-
-
-- Larger than
10MB
-
AND pg_relation_size(c.oid) >
10 *
1024 ^
2
-
-
ORDER BY
-
pg_relation_size(c.oid) * s.null_frac DESC;
查询结果为：
tx_cancelled_by_ix 是具有许多空值的大型索引：此处潜力巨大！
tx_op_1_ix 是大索引，几乎没有空值：潜力不大
tx_token_ix 是带有少量空值的小索引：不管它
tx_op_name_ix 是没有空值的大索引：没啥用
结果表明，通过将tx_cancelled_by_ix变成不包含null的部分索引，可节省约1.3GB。
从索引中排除空值是否总是有好处？NULL和任何其他值一样有意义。如果查询使用了 IS NULL，这些查询可能会受益于索引NULL。
这个方法仅对空值有用？使用部分索引排除不经常查询或根本不查询的值可能有益于任何值，而不仅仅是空值。NULL通常表示缺少值，我们没有很多查询在搜索空值，因此将它们从索引中排除是有意义的。
你最终如何清除超过20GB的空间呢？你可能已经注意到，上文提到了超过20GB的可用空间，但是图表仅显示一半，那就将索引从复制中删除！从主数据库释放10GB时，每个副本的存储量也大致相同。
Django ORM迁移
为了将上述技术与Django一起使用，需要注意几件事：
防止隐式创建外键索引
除非明确设置db_index=False，否则Django会在models.ForeignKeyfield上隐式创建B树索引。
-
from django.db import models
-
from django.contrib.auth.models import User
-
-
class Transaction(models.Model):
-
# ...
-
cancelled_by_user = models.ForeignKey(
-
to=User,
-
null=True,
-
on_delete=models.CASCADE,
-
)
-
这个模型用来跟踪交易数据，如果交易被取消，可保留对取消交易的用户引用。如前所述，大多数交易不会被取消，因此我们设置null=True。
我们没有显式设置db_index，因此Django将在该字段上隐式创建完整索引。要创建部分索引，可进行以下更改：
-
from django.db import models
-
from django.contrib.auth.models import User
-
-
class Transaction(models.Model):
-
# ...
-
cancelled_by_user = models.ForeignKey(
-
to=User,
-
null=True,
-
on_delete=models.CASCADE,
-
db_index=False,
-
)
-
-
class Meta:
-
indexes = (
-
models.Index(
-
fields=('cancelled_by_user_id', ),
-
name='%(class_name)s_cancelled_by_part_ix',
-
condition=Q(cancelled_by_user_id__isnull=False),
-
),
-
)
-
我们告诉Django先不要在FK字段上创建索引，然后使用来添加部分索引models.Index。
为了防止这类隐式功在不引起我们注意的情况下潜入索引，我们创建了Django检查来强制自己始终显式设置外键db_index。
将现有的完整索引迁移到部分索引
在迁移过程中，我们面临的挑战之一是用部分索引替换现有的完整索引，但要注意不会导致迁移期间的停机或性能下降。在确定了要替换的完整索引后，执行以下步骤：
用部分索引替换完整索引：如上所示，调整相关的Django模型并用部分索引替换完整索引。Django生成的迁移将首先禁用FK约束（如果该字段是外键），则删除现有的完整索引并创建新的部分索引。执行此迁移可能会导致停机和性能下降，我们实际上不会运行它。
手动创建部分索引：使用Django的./manage.py sqlmigrate实用程序生成用于迁移的脚本，仅提取CREATE INDEX语句并进行调整以创建索引CONCURRENTLY，并在数据库中手动创建索引。由于没删除完整索引，因此查询仍可以使用它们，在这个过程中不影响性能。在Django迁移中同时创建索引，我们建议最好手动进行。
重置完整索引统计信息计数器：为了确保删除完整索引的安全性，我们首先要确保正在使用新的部分索引。为了跟踪它们的使用，我们使用重置完整索引的计数器pg_stat_reset_single_table_counters(<full index oid>)。
显示器使用部分索引：重置统计信息后，我们监测pg_stat_all_indexes表中
的idx_scan，idx_tup_read、idx_tup_fetch，来观察整体查询性能和部分索引使用情况。
删除完整索引：一旦使用了部分索引，就删除完整索引。这是检查部分索引和完全索引大小的好方法，以便确定要释放多少存储空间。
伪造Django迁移：一旦数据库状态有效地与模型状态同步，我们就使用伪造迁移./manage.py migrate --fake。伪造迁移时，Django会将迁移注册为已执行，但实际上不会执行任何操作。当需要更好地控制迁移过程时，这种情况很有用。请注意，在没有停机时间考虑的其他环境，Django迁移将正常执行，并全部索引将替换为部分索引。
在本文中，我们清除了很多存储空间：
删除未使用的索引
重新打包表和索引（在可能的情况下激活B树重复数据删除）
利用部分索引仅对必要内容进行索引
本文为AI科技大本营翻译，转载请注明来源出处。
更多精彩推荐
-
☞打造 AI 语音新标杆，英特尔与腾讯云小微创新共赢
-
☞三种方法，用Python轻松提取PDF中的全部图片
-
☞这
25条极简Python代码，你还不知道
-
-
☞告别手敲 SQL ？GPT
-3 自动帮你写
点分享点收藏点点赞点在看
转载：https://blog.csdn.net/dQCFKyQDXYm3F8rB0/article/details/113577064
查看评论