程式員硬核“年終大掃除”，清理了資料庫 70GB 空間

作者 | 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

就像在索引中一樣，表也可能包含死元組，可能會導致碎片化，與包含關聯表中資料的索引不同，不能僅簡單地重新創建表，要重新創建表，必須創建一個新表，遷移資料，同步資料，在其他表中創建所有索引……等完成這操作后，才能將舊表切換為新表，

有幾種方法可以重建表：

1. 重新創建表：如上所述，使用這種方法通常需要大量的開發作業，尤其是在重建正在使用表的情況下，

2. 清理表：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，

將現有的完整索引遷移到部分索引

在遷移程序中，我們面臨的挑戰之一是用部分索引替換現有的完整索引，但要注意不會導致遷移期間的停機或性能下降，在確定了要替換的完整索引后，執行以下步驟：

1. 用部分索引替換完整索引：如上所示，調整相關的Django模型并用部分索引替換完整索引，Django生成的遷移將首先禁用FK約束（如果該欄位是外鍵），則洗掉現有的完整索引并創建新的部分索引，執行此遷移可能會導致停機和性能下降，我們實際上不會運行它，

2. 手動創建部分索引：使用Django的./manage.py sqlmigrate實用程式生成用于遷移的腳本，僅提取CREATE INDEX陳述句并進行調整以創建索引CONCURRENTLY，并在資料庫中手動創建索引，由于沒洗掉完整索引，因此查詢仍可以使用它們，在這個程序中不影響性能，在Django遷移中同時創建索引，我們建議最好手動進行，

3. 重置完整索引統計資訊計數器：為了確保洗掉完整索引的安全性，我們首先要確保正在使用新的部分索引，為了跟蹤它們的使用，我們使用重置完整索引的計數器pg_stat_reset_single_table_counters(<full index oid>)，

4. 顯示幕使用部分索引：重置統計資訊后，我們監測pg_stat_all_indexes表中

   的idx_scan，idx_tup_read、idx_tup_fetch，來觀察整體查詢性能和部分索引使用情況，

5. 洗掉完整索引：一旦使用了部分索引，就洗掉完整索引，這是檢查部分索引和完全索引大小的好方法，以便確定要釋放多少存盤空間，

6. 偽造Django遷移：一旦資料庫狀態有效地與模型狀態同步，我們就使用偽造遷移./manage.py migrate --fake，偽造遷移時，Django會將遷移注冊為已執行，但實際上不會執行任何操作，當需要更好地控制遷移程序時，這種情況很有用，請注意，在沒有停機時間考慮的其他環境，Django遷移將正常執行，并全部索引將替換為部分索引，

在本文中，我們清除了很多存盤空間：

• 洗掉未使用的索引

• 重新打包表和索引（在可能的情況下激活B樹重復資料洗掉）

• 利用部分索引僅對必要內容進行索引

原文鏈接：https://hakibenita.com/postgresql-unused-index-size

標籤：其他

上一篇：老程式員薪水被應屆生倒掛了，不爽就跳槽？還是老老實實提升自己的技術重要！

下一篇：Python留出法與n折交叉驗證的對比，5折交叉驗證和15折交叉驗證的區別（回圈繪制ROC曲線）