如何优化 Postgres 数据库的存储?

如何优化 Postgres 数据库的存储，最终有助于优化有助于整体数据库性能的查询？

大多数时候，当存储空间似乎已满时，我们会继续向 Postgres 堆添加存储空间，因为添加存储空间很便宜。

这是处理不断增长的存储需求的正确方法，还是我们可以在不增加有助于降低成本和优化性能的存储的情况下优雅地处理它？

首先，了解存储量不断增加的原因。几个显着的潜力：

• 未使用的索引

• 膨胀

让我们详细讨论一下。

未使用的索引：

1. 索引是在表创建期间创建的，从未被使用过。
2. 使用错误的索引类型（b-tree、hash、gin）创建了索引，我们使用不受支持的运算符查询了这些列。
3. 我们最初创建并使用了索引，但在一段时间后我们停止使用它，它成为未使用索引的候选者。

查询以查找未使用的索引：

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
and indexrelname != 'SequelizeData_pkey'
order by
pg_relation_size(indexrelname::regclass) desc;

idx_scan = 索引扫描次数

size = 索引占用的总存储空间

idx_tup_read = 索引扫描返回的索引条目数

idx_tup_fetch = 简单索引扫描返回的活动表行数

我们无法删除所有未使用的索引，因为它们可能会在以后使用，或者 postgres 可能会显示一些错误的统计信息。

注意：建议定期重置索引统计信息，以更好地查看已使用的索引。要重置，我们首先需要找到表的对象标识符 id (oid)，然后重置。

SELECT oid FROM pg_class c WHERE relname = ‘test’; // test is table name
SELECT pg_stat_reset_single_table_counters(oid_fetched); // Reset to get new stats regularly

膨胀：

Postgres 与 MVCC（多版本并发控制）一起工作，最终为更新/插入创建一个新行。这有助于与可以访问旧版本数据的旧事务的并发事务（因为它们需要完成），但同时，它在系统中添加了大量陈旧存储。因此，一次只有一个版本的行将处于活动状态，而所有其他版本将显示为死（陈旧）行。

膨胀也存在于索引中。假设您更新表中的 tuple(row) 并为该表索引了 3 列。现在为更新部分创建了一个新元组（如果我们更新所有这些列的数据），将在所有三个索引中创建一个相应的新条目，并且早期的引用将被标记为过时。

注意： Postgres 使用 HOT（仅堆元组）优化，其中假设表有 3 个索引列，现在更新时我们只更新一个索引列，所以在这种情况下，2 个旧索引数据的引用将从旧的引用更改为新的引用元组，并且只为索引的更新列创建新的索引数据。

只有当行（元组）的创建率大于元组被清理的率时，才会在数据库中创建膨胀。此外，Autovaccum（后台进程）会从文件中删除记录，但仍然没有清除存储空间，我们需要进行碎片整理。

更多的膨胀会导致更多的 I/O，最终会降低查询性能。

查看膨胀的简单方法之一：

CREATE EXTENSION pgstattuple;
select * from pgstattuple(‘sales’); // sales is table name.

现在我将更新/删除一些行并查看结果。我们可以看到死对数和相应的长度。

现在从上面的讨论中我们了解到，膨胀是在以下位置创建的：

• 表
• 索引

Table Bloat 和 Index Bloat 可以通过一些很棒的人提供的查询轻松获取。简单复制并运行，我们将获得所有数据

Git 链接：https ://github.com/ioguix/pgsql-bloat-estimation/blob/master/table/table_bloat.sql

表膨胀演示：

psql blog; // blog is db name
create table bloat_demo (id int) with (autovacuum_enabled = off);
insert into bloat_demo select * from generate_series(1, 8000000);
select pg_size_pretty(pg_relation_size('bloat_demo'));

现在更新所有行并检查表格的大小；（我们将看到表格占用的尺寸现在将翻倍）

update bloat_demo set id = id + 100;
select pg_size_pretty(pg_relation_size('bloat_demo'));

现在在 bloat_demo 表上运行 Vacuum 以查看是否清除了额外的存储空间。

有趣的是，真空没有清除任何存储空间。

原因 - 如上所述，vacuum 将从表中删除死（陈旧）行，但该存储不会回收，因为数据文件仍包含这些存储。真空通过在存储文件（已删除的行）中创建空闲存储来提供存储以供重用。

现在检查我们的理论是否正确，即是否可以使用真空空间。要验证这一点，请再次运行更新命令并查看表是否占用了额外的空间。

我们可以从下面的屏幕截图中看到，表没有占用额外的空间，因为更新命令使用了通过 Vacuum 提供给我们的文件中的空间。

现在我们已经使用了真空空间，让我们再次更新，看看表是否占用了额外的空间。

由于创建了新的 800 万行，并且所有较早的行都被标记为死，表会占用额外的 277MB。

解决索引膨胀问题的方法：

1. 删除现有索引并重新创建它。它会以最好的方式做事，但一些间歇性的查询会受到影响。
2. 重新索引已经创建的索引——这将删除膨胀空间，但会使索引挂起（不可操作），直到重新索引完全完成，最终使数据库在重建时间上效率低下。我们可以通过并发执行来优化重新索引方式，这不会影响查询。

Reindex index sales; ---> Reindex index concurrently sales;

上述并发重新索引将在数据库中创建一个带有后缀（_ccnew）的新索引，并跟踪所有表的更改。当一个新索引完全创建后，旧索引将被删除，表开始使用新创建的索引。

同时重新索引的缺点：

如果出现意外情况并且由于某些问题需要停止或停止重建过程，则系统中将存在间歇性索引级别并占用额外空间。这些无效索引需要手动删除。

Query to find Invalid Indexes:
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
c.relname LIKE  '%_ccnew'
AND NOT indisvalid;

解决表格膨胀的方法：

Vacuum Full ：这将回收浮动和陈旧元组使用的所有空间，但反过来会产生很多问题，例如锁定表（挂起），最终使其不是生产使用解决方案。我们唯一可以使用它的时间可以让我们为维护所花费的应用程序停机时间。

VACCUM FULL bloat_demo;

我们可以从表大小中看到 553 MB 在真空已满时是空闲的。

2. Pg Repack：这是一个生产就绪的解决方案，有助于在不停机的情况下清除臃肿和陈旧的数据。此扩展需要先创建（安装），因为它仅在安装期间可用。

CREATE EXTENSION pg_repack;

Pg_repack 是如何工作的？

它创建一个新表，就像我们在重新索引中看到的那样，并将旧表中的所有数据复制到一个新表中，然后重建索引。完全复制完成后，新表作为主表，旧表被删除。

pg_repack -k --table sales_info sales;
sales_info = table name
sales = database name

pg_repack 的缺点：

1. 在新表创建期间，它将需要双重存储，因为同一个表被复制到存储中，因此我们需要在开始重新打包表之前检查存储可用性。
2. 如果重新打包过程在两者之间停止，那么我们需要手动删除间歇性表。