前言
對 PostgreSQL 中數(shù)據(jù)的并發(fā)訪問由多版本并發(fā)控制 (MVCC) 模型管理癣丧。 為每個 SQL 語句維護(hù)數(shù)據(jù)快照，以便它們始終獲得一致的數(shù)據(jù)栈妆，即使其他事務(wù)正在同時對其進(jìn)行修改坎缭。 當(dāng)行已被一個或多個事務(wù)修改時，這將導(dǎo)致管理同一行的多個版本签钩。 從用戶的角度來看掏呼，可能只有一行數(shù)據(jù)，但 PostgreSQL 內(nèi)部可能維護(hù)該行的一個或多個版本铅檩。
行版本是否對事務(wù)可見是通過堆中的行數(shù)據(jù)來維護(hù)的憎夷。 為了優(yōu)化可見性信息的獲取，PostgreSQL 還維護(hù)了一個“_vm”關(guān)系分支昧旨，它跟蹤那些只包含對所有活躍事務(wù)可見的元組的頁面拾给。
對任何事務(wù)不再可見的死版本將由 vacuum 進(jìn)程清除。 在此之前兔沃，索引和堆頁面可能包含大量死元組（這實際上取決于您的工作負(fù)載的性質(zhì)）蒋得。 對于更新密集型工作負(fù)載，這可能是一個巨大的數(shù)字乒疏！
乍一看似乎無害额衙，但這種死索引元組的累積會產(chǎn)生級聯(lián)效應(yīng)，從而導(dǎo)致性能顯著下降。 在 PostgreSQL 13 中完成重復(fù)數(shù)據(jù)刪除工作后窍侧，下一個合乎邏輯的步驟是通過減少頁面拆分來防止 btree 索引膨脹县踢。
物理數(shù)據(jù)存儲
PostgreSQL 將數(shù)據(jù)保存在被稱為頁面的固定大小存儲單元中。 頁面的大小是在 PostgreSQL 服務(wù)器編譯過程中定義的伟件。 默認(rèn)頁面大小為 8k硼啤，但可以將其更改為更高的值。 雖然更改頁面大小會使事情變得復(fù)雜斧账，因為其他工具可能也需要重新編譯或重新配置谴返。
每個表和索引都存儲在頁數(shù)組中。 將數(shù)據(jù)插入表中時咧织，會將數(shù)據(jù)寫入具有足夠可用空間的頁面亏镰。 否則，將創(chuàng)建一個新頁面拯爽。
然而，索引有點不同钧忽。 索引中的第一頁是一個元頁面毯炮，其中包含有關(guān)索引的控制信息。 也有一些特殊的頁面來維護(hù)索引相關(guān)的信息耸黑。 對于 btree 索引桃煎，數(shù)據(jù)必須根據(jù)索引列和堆元組 ID（元組在表中的物理位置）進(jìn)行排序。 因此大刊，插入和更新必須在正確的頁面上進(jìn)行为迈，以保持排序順序。 如果頁面沒有足夠的空間容納傳入的元組缺菌，則必須創(chuàng)建新頁面葫辐，并將溢出頁面中的一些項目移動到新頁面。 如果需要伴郁，這些葉子頁面的父頁面會被遞歸拆分耿战。
避免頁面分裂
當(dāng)新的元組或新的非 HOT 元組版本要添加到索引中時，會發(fā)生 B-Tree 索引頁拆分焊傅。 HOT 是“heap only tuple”的縮寫剂陡。 基本而言，它是一種刪除給定頁面上的死行（碎片整理）并因此為新行騰出空間的方法狐胎。 通過避免或延遲頁面拆分鸭栖，我們可以避免或減慢索引擴(kuò)展，從而減少膨脹握巢。 現(xiàn)在這很令人興奮晕鹊！
雖然對新元組沒有太多可做的事情，但可以管理更新，以便可以增量刪除邏輯上未更改的索引元組（即未更改的索引列）的過時版本捏题，以保持新版本的可用空間玻褪。 這個過程得到了規(guī)劃器的幫助，規(guī)劃器向索引方法提供了一個提示公荧，“索引未更改”带射。 如果沒有任何索引列由于此更新而更改，則為 true循狰。
自下而上的刪除是在索引操作期間完成的窟社，當(dāng)預(yù)期“版本攪動頁面拆分”時（“索引未更改”提示為真）。 邏輯上未更改的索引元組的過時版本將被刪除绪钥，從而在頁面上為較新版本騰出空間灿里。 這種方法潛在地避免了頁面拆分。
自下而上的刪除操作
為了看到這種自下而上的刪除方法的實際好處程腹，讓我們更深入地研究一下 B-Tree 索引匣吊。 我們將比較 PostgreSQL 版本 13 和 14 之間的 btree 索引大小。為了更詳細(xì)地檢查索引數(shù)據(jù)寸潦，我將使用 contrib 模塊中提供的“pageinspect”擴(kuò)展色鸳。 “pageinspect”擴(kuò)展允許我們查看索引或表的底層頁面內(nèi)容。
讓我們從創(chuàng)建 pageinspect 擴(kuò)展開始见转。您可能需要安裝 contrib 模塊命雀，或者如果您是從源代碼構(gòu)建，請安裝它然后繼續(xù)斩箫。
CREATE EXTENSION IF NOT EXISTS pageinspect;
現(xiàn)在讓我們創(chuàng)建一個包含兩列的表“foo”吏砂，創(chuàng)建兩個包含一個覆蓋索引的索引，并分析該表乘客。
DROP TABLE IF EXISTS foo;
CREATE TABLE foo WITH (autovacuum_enabled = false) AS (SELECT GENERATE_SERIES(1, 1000) AS col1, SUBSTR(MD5(RANDOM()::TEXT), 0, 25) AS value);
CREATE INDEX ON foo(col1);
CREATE INDEX ON foo(col1) INCLUDE(value);
是時候檢查“foo”表的頁面狐血、元組和關(guān)系大小了。
SELECT relname
, relkind
, relpages
, reltuples
, PG_SIZE_PRETTY(PG_RELATION_SIZE(oid))
FROM pg_class
WHERE relname LIKE '%foo%'
ORDER
BY relkind DESC;
relname | relkind | relpages | reltuples | pg_size_pretty
--------------------+---------+----------+-----------+----------------
foo | r | 8 | 1000 | 64 kB
foo_col1_idx | i | 5 | 1000 | 40 kB
foo_col1_value_idx | i | 9 | 1000 | 72 kB
(3 rows)
14.1 和 13.5 都為上述查詢提供完全相同的輸出易核。
禁用順序掃描和位圖掃描以強(qiáng)制進(jìn)行索引掃描氛雪。這將強(qiáng)制此示例中的查詢使用索引掃描
SET enable_seqscan = false;
SET enable_bitmapscan = false;
創(chuàng)建四個新版本的元組
UPDATE foo SET value = value || 'x';
UPDATE foo SET value = value || 'x';
UPDATE foo SET value = value || 'x';
UPDATE foo SET value = value || 'x';
上述語句每次更新 1000 行。 ANALYZE 表以確保我們的統(tǒng)計數(shù)據(jù)準(zhǔn)確無誤耸成。還讓我們回顧一下“foo”表的頁數(shù)报亩、元組和關(guān)系大小。
ANALYZE foo;
SELECT relname
, relkind
, relpages
, reltuples
, PG_SIZE_PRETTY(PG_RELATION_SIZE(oid))
FROM pg_class
WHERE relname LIKE '%foo%'
ORDER
BY relkind DESC;
--PostgreSQL 14.1
relname | relkind | relpages | reltuples | pg_size_pretty
--------------------+---------+----------+-----------+----------------
foo | r | 8 | 1000 | 288 kB
foo_col1_idx | i | 5 | 1000 | 88 kB
foo_col1_value_idx | i | 9 | 1000 | 216 kB
(3 rows)
--PostgreSQL 13.5
--------------------+---------+----------+-----------+----------------
foo | r | 8 | 1000 | 288 kB
foo_col1_idx | i | 5 | 1000 | 104 kB
foo_col1_value_idx | i | 9 | 1000 | 360 kB
(3 rows)
兩個版本的表大小都增加了相同的數(shù)量井氢，但是 14.1 中的索引與 13.5 相比明顯更小弦追。 很好，讓我們檢查頁面內(nèi)容以了解幕后發(fā)生的事情花竞。
查看第一個索引頁面（不是元頁面）的內(nèi)容清楚地顯示了自下而上的刪除如何使索引大小保持較小劲件。
SELECT itemoffset
, ctid
, itemlen
, nulls
, vars
, dead
, htid
FROM bt_page_items('foo_col1_value_idx', 1)
LIMIT 15;
PostgreSQL 14.1
itemoffset | ctid | itemlen | nulls | vars | dead | htid
------------+---------+---------+-------+------+------+---------
1 | (7,1) | 16 | f | f | |
2 | (7,181) | 40 | f | t | f | (7,181)
3 | (7,225) | 48 | f | t | f | (7,225)
4 | (7,182) | 40 | f | t | f | (7,182)
5 | (7,226) | 48 | f | t | f | (7,226)
6 | (7,183) | 40 | f | t | f | (7,183)
7 | (7,227) | 48 | f | t | f | (7,227)
8 | (7,184) | 40 | f | t | f | (7,184)
9 | (7,228) | 48 | f | t | f | (7,228)
10 | (7,185) | 40 | f | t | f | (7,185)
11 | (7,229) | 48 | f | t | f | (7,229)
12 | (7,186) | 40 | f | t | f | (7,186)
13 | (7,230) | 48 | f | t | f | (7,230)
14 | (7,187) | 40 | f | t | f | (7,187)
15 | (7,231) | 48 | f | t | f | (7,231)
(15 rows)
PostgreSQL 13.5
itemoffset | ctid | itemlen | nulls | vars | dead | htid
------------+---------+---------+-------+------+------+---------
1 | (0,1) | 16 | f | f | |
2 | (0,1) | 40 | f | t | f | (0,1)
3 | (7,49) | 40 | f | t | f | (7,49)
4 | (7,137) | 40 | f | t | f | (7,137)
5 | (7,181) | 40 | f | t | f | (7,181)
6 | (7,225) | 48 | f | t | f | (7,225)
7 | (0,2) | 40 | f | t | f | (0,2)
8 | (7,50) | 40 | f | t | f | (7,50)
9 | (7,138) | 40 | f | t | f | (7,138)
10 | (7,182) | 40 | f | t | f | (7,182)
11 | (7,226) | 48 | f | t | f | (7,226)
12 | (0,3) | 40 | f | t | f | (0,3)
13 | (7,51) | 40 | f | t | f | (7,51)
14 | (7,139) | 40 | f | t | f | (7,139)
15 | (7,183) | 40 | f | t | f | (7,183)
(15 rows)
查看 14.1 的 2 到 3 和 13.5 的 2 到 6 的“itemoffset”可以告訴我們整個故事掸哑。 13.5 攜帶了整套元組版本，而 14.1 清理了死元組以騰出空間零远。 版本越少苗分，頁面就越少，從而減少膨脹牵辣，并為我們提供更小的索引大小摔癣。
結(jié)論
在 PostgreSQL 14 版本中，由于底部刪除而減少索引大小是一個巨大的優(yōu)勢纬向。Btree 索引具有一種機(jī)制择浊，其中普通索引掃描設(shè)置 LP_DEAD 標(biāo)志。 這不是為位圖索引掃描設(shè)置的逾条。 一旦設(shè)置好琢岩，就可以在不需要真空的情況下回收空間。 然而师脂，這是完全不同的一類死元組担孔。 從長遠(yuǎn)來看，這種自下而上的刪除策略有助于顯著減少特定類別的重復(fù)項吃警。 它不僅減少了vacuum 的負(fù)載糕篇，還有助于保持索引更健康，從而提高訪問速度汤徽。 因此，如果您的更新工作量很大灸撰，那么在提供更好性能的同時谒府，肯定會節(jié)省資源利用率和成本。
原文地址
PostgreSQL 14 B-Tree Index: Reduced Bloat with Bottom-Up Deletion
