一、定義

表膨脹是指表的數(shù)據(jù)和索引所占文件系統(tǒng)的空間在有效數(shù)據(jù)量并未發(fā)生大的變化的情況下不斷增大。這種現(xiàn)象會(huì)導(dǎo)致關(guān)系文件被大量空洞填滿，從而浪費(fèi)大量的磁盤空間。

二、原因

表膨脹在PostgreSQL中通常是由于UNDO數(shù)據(jù)（用于回滾事務(wù)和維護(hù)事務(wù)的一致性視圖）和表數(shù)據(jù)混合存儲(chǔ)引起的。具體原因包括以下幾個(gè)方面：

1. MVCC（多版本并發(fā)控制）機(jī)制：

• 舊版本數(shù)據(jù)保留：PostgreSQL使用MVCC機(jī)制來處理并發(fā)訪問，允許讀取操作在不鎖定表的情況下進(jìn)行。當(dāng)一條記錄被更新或刪除時(shí)，原始記錄不會(huì)立即從磁盤上移除，而是被標(biāo)記為不可見，以支持未提交的事務(wù)回滾或者用于快照讀。這些舊版本的數(shù)據(jù)如果不能得到及時(shí)清理，就會(huì)占用磁盤空間，導(dǎo)致表膨脹。
• 死元組（dead tuple）：隨著時(shí)間的推移，表中會(huì)積累大量的“死”行（即不再可達(dá)的行），這些死元組如果不及時(shí)清理，就會(huì)占用磁盤空間。

2. 頻繁的更新和刪除操作：

• 死元組積累：頻繁的更新和刪除操作直接導(dǎo)致表中大量的“死”行。在高更新和刪除率的環(huán)境中，表膨脹尤為嚴(yán)重，因?yàn)槊看芜@些操作發(fā)生時(shí)，都會(huì)留下不再可達(dá)的行。

3. 未提交的事務(wù)：

• 資源占用：長(zhǎng)時(shí)間未提交或終止的事務(wù)會(huì)占用資源，導(dǎo)致“死”行的積累，進(jìn)而導(dǎo)致表膨脹。

4. 填充因子（fillfactor）設(shè)置：

• 空閑空間：表的填充因子設(shè)置也會(huì)影響表膨脹。較低的填充因子意味著每個(gè)數(shù)據(jù)頁中會(huì)留出更多的空閑空間，以減少頁面因更新而頻繁分裂的可能性。但這會(huì)增加每個(gè)頁面的空閑空間，導(dǎo)致表的實(shí)際磁盤使用量增加。相反，較高的填充因子可能導(dǎo)致數(shù)據(jù)行更新時(shí)空間不足，需要重新分配頁面。

5. autovacuum機(jī)制不足：

• 清理不及時(shí)：雖然PostgreSQL提供了自動(dòng)的autovacuum機(jī)制來定期清理“死”行，但在某些情況下，如高并發(fā)事務(wù)、長(zhǎng)事務(wù)等，autovacuum可能無法及時(shí)清理死元組，導(dǎo)致表膨脹。

6. 其他因素：

• 失效復(fù)制槽：失效的復(fù)制槽可能導(dǎo)致autovacuum無法正常工作。
• 索引狀態(tài)問題：表和索引的并發(fā)訪問可能影響VACUUM的效果。
• 磁盤I/O性能：磁盤I/O性能差可能導(dǎo)致VACUUM的效率低下，死元組不能及時(shí)清理。

三、影響

表膨脹對(duì)數(shù)據(jù)庫的性能和穩(wěn)定性有顯著影響，具體包括以下幾個(gè)方面：

1. 存儲(chǔ)成本增加：

膨脹的表占用更多磁盤空間，增加存儲(chǔ)成本。

2. 查詢性能下降：

• 數(shù)據(jù)集增大：數(shù)據(jù)庫需要在更大的數(shù)據(jù)集中搜索，導(dǎo)致查詢執(zhí)行時(shí)間延長(zhǎng)。
• 索引效率降低：表膨脹可能導(dǎo)致索引結(jié)構(gòu)效率下降。

3. 備份恢復(fù)時(shí)間延長(zhǎng)：

表變大后相應(yīng)的備份恢復(fù)時(shí)間也會(huì)延長(zhǎng)。

4. 系統(tǒng)資源消耗增加：

• CPU、內(nèi)存和I/O資源：處理膨脹的表需要更多的CPU、內(nèi)存和I/O資源。

5. 數(shù)據(jù)碎片化：

表膨脹可能導(dǎo)致數(shù)據(jù)碎片化，進(jìn)一步影響性能并增加數(shù)據(jù)庫管理的復(fù)雜性。

四、解決方案

解決表膨脹問題通常涉及到以下幾個(gè)步驟：

1. 定期執(zhí)行VACUUM操作：

• 普通VACUUM：清理死元組，但不會(huì)進(jìn)行空間重組，磁盤上的空間不會(huì)釋放，但后續(xù)的插入會(huì)根據(jù)空閑空間管理優(yōu)先插入空閑空間。
• VACUUM FULL：清理釋放磁盤空間，但獲取的鎖級(jí)別較高，會(huì)阻塞一切訪問，適用于經(jīng)常進(jìn)行大批量更新數(shù)據(jù)的表，可以在業(yè)務(wù)低峰期執(zhí)行。
• 手動(dòng)VACUUM：通過調(diào)整VACUUM的行為（如VACUUM(FULL, FREEZE)），可以更快地清理UNDO數(shù)據(jù)。

2. 啟用和配置autovacuum機(jī)制：

• 確保autovacuum開啟：PostgreSQL提供了自動(dòng)的autovacuum機(jī)制，可以根據(jù)閾值自動(dòng)觸發(fā)vacuum操作。
• 調(diào)整autovacuum參數(shù)：如autovacuum_vacuum_cost_delay和autovacuum_naptime，以確保autovacuum進(jìn)程能夠及時(shí)清理“死”行。
• 監(jiān)控autovacuum效果：定期檢查autovacuum的執(zhí)行情況和效果，確保其正常工作。

3. 使用pg_repack或pg_reorg工具：

• 在線重組：對(duì)于膨脹嚴(yán)重的表，可以使用pg_repack或pg_reorg等工具重新組織表和索引以回收空間。這些工具可以在不鎖定表的情況下工作，對(duì)生產(chǎn)環(huán)境影響較小。
• 執(zhí)行過程：
  • 準(zhǔn)備階段：預(yù)留足夠的磁盤空間，調(diào)整數(shù)據(jù)庫參數(shù)（如idle_in_transaction_session_timeout）。
  • 執(zhí)行階段：創(chuàng)建新表，復(fù)制數(shù)據(jù)，建立索引，交換表等。
  • 監(jiān)控和日志：監(jiān)控重組過程，記錄日志以便問題排查。

4. 合理設(shè)計(jì)數(shù)據(jù)庫和查詢：

• 避免頻繁的更新和刪除操作：減少“死”行的積累。
• 使用分區(qū)表：對(duì)于頻繁更新的大表，可以考慮分區(qū)表以減少單個(gè)表的大小和膨脹程度。
• 合理設(shè)置填充因子：根據(jù)表的更新頻率和數(shù)據(jù)量合理設(shè)置填充因子以減少表膨脹的可能性。

5. 監(jiān)控和預(yù)警：

• 建立監(jiān)控體系：對(duì)表的膨脹情況進(jìn)行實(shí)時(shí)監(jiān)測(cè)并設(shè)置閾值告警，一旦發(fā)現(xiàn)表膨脹現(xiàn)象能快速響應(yīng)處理。
• 定期分析：定期分析表的膨脹情況和原因，采取相應(yīng)的優(yōu)化措施。

6. 其他優(yōu)化措施：

• 配置REDO日志：如果可能，可以配置REDO日志使得UNDO數(shù)據(jù)和REDO日志分離以減少表膨脹的影響。
• 數(shù)據(jù)庫維護(hù)最佳實(shí)踐：定期的數(shù)據(jù)庫維護(hù)活動(dòng)如索引優(yōu)化、統(tǒng)計(jì)信息更新等也有助于管理UNDO數(shù)據(jù)。

五、實(shí)施說明

1. 啟用和配置autovacuum機(jī)制：

確保autovacuum開啟：

ALTER SYSTEM SET autovacuum = on;
SELECT pg_reload_conf();

調(diào)整autovacuum參數(shù)：

ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 20ms;
ALTER SYSTEM SET autovacuum_naptime = 1min;
SELECT pg_reload_conf();

監(jiān)控autovacuum效果：

SELECT * FROM pg_stat_autovacuum;

2. 定期執(zhí)行VACUUM操作：

手動(dòng)執(zhí)行VACUUM：

VACUUM FULL tablename;

設(shè)置定時(shí)任務(wù)：

0 2 * * * psql -d yourdatabase -c "VACUUM FULL tablename"

3. 使用pg_repack工具：

安裝pg_repack擴(kuò)展：

CREATE EXTENSION pg_repack;

執(zhí)行pg_repack：

pg_repack -h your_host -p your_port -d your_database -t your_table

監(jiān)控重組過程：

SELECT * FROM pg_stat_activity WHERE query LIKE '%pg_repack%';

4. 合理設(shè)計(jì)數(shù)據(jù)庫和查詢：

使用分區(qū)表：

CREATE TABLE your_table (
    id serial PRIMARY KEY,
    data text
) PARTITION BY RANGE (id);

CREATE TABLE your_table_partition1 PARTITION OF your_table
FOR VALUES FROM (1) TO (1000000);

CREATE TABLE your_table_partition2 PARTITION OF your_table
FOR VALUES FROM (1000001) TO (2000000);

合理設(shè)置填充因子：

ALTER TABLE your_table SET (fillfactor = 80);

5. 監(jiān)控和預(yù)警：

建立監(jiān)控體系：

CREATE OR REPLACE FUNCTION check_table_bloat() RETURNS void AS $$
DECLARE
   r RECORD;
BEGIN
   FOR r IN
       SELECT schemaname, tablename, bloat
       FROM (
           SELECT
               schemaname,
               tablename,
               ROUND(CASE WHEN otta=0 OR relpages=0 OR relpages=otta THEN 0.0 ELSE relpages/otta::numeric END, 2) AS bloat
           FROM (
               SELECT
                   nn.nspname AS schemaname,
                   cc.relname AS tablename,
                   COALESCE(cc.reltuples, 0) AS reltuples,
                   COALESCE(cc.relpages, 0) AS relpages,
                   COALESCE(ce.reltuples, 0) AS expected_reltuples,
                   CASE WHEN ce.reltuples > 0 THEN
                       (cc.reltuples::bigint * cc.rellen)::bigint / (ce.reltuples::bigint * (SELECT setting FROM pg_settings WHERE name='block_size')::int)
                   ELSE
                       0
                   END AS otta
               FROM
                   pg_class cc
                   JOIN pg_namespace nn ON cc.relnamespace = nn.oid
                   LEFT JOIN (
                       SELECT
                           c.relname,
                           c.reltuples,
                           (c.reltuples * (c.rellen + pg_column_size(c.oid, 'ctid') + 24))::bigint AS total_bytes
                       FROM
                           pg_class c
                           LEFT JOIN pg_stat_all_tables s ON c.relname = s.relname
                       WHERE
                           s.schemaname NOT IN ('pg_catalog', 'information_schema')
                           AND c.relkind = 'r'
                   ) ce ON cc.relname = ce.relname
               WHERE
                   nn.nspname NOT IN ('pg_catalog', 'information_schema')
                   AND cc.relkind = 'r'
           ) a
       ) b
       WHERE bloat > 1.0
   LOOP
       RAISE NOTICE 'Schema: %, Table: %, Bloat: %', r.schemaname, r.tablename, r.bloat;
   END LOOP;
END;
$$ LANGUAGE plpgsql;

這個(gè)函數(shù)check_table_bloat的目的是檢查PostgreSQL數(shù)據(jù)庫中的表是否存在“膨脹”（bloat）現(xiàn)象，即表占用的磁盤空間是否超過了其實(shí)際存儲(chǔ)的數(shù)據(jù)量所需的空間。函數(shù)通過一系列嵌套的查詢來計(jì)算每個(gè)表的“膨脹率”（bloat），并對(duì)于膨脹率大于1.0的表，使用RAISE NOTICE語句輸出相關(guān)信息。

說明

1. 外層查詢：遍歷所有計(jì)算出的膨脹率大于1.0的表，并輸出其模式名（schemaname）、表名（tablename）和膨脹率（bloat）。

2. 內(nèi)層查詢：計(jì)算每個(gè)表的膨脹率。這里使用了多個(gè)嵌套的子查詢：

   • 第一個(gè)子查詢（別名為a）計(jì)算了每個(gè)表的實(shí)際頁數(shù)（relpages）與理想頁數(shù)（otta）的比率，即膨脹率。理想頁數(shù)是根據(jù)表的行數(shù)（reltuples）和每行的大?。?code>rellen）以及塊大?。ㄍㄟ^查詢pg_settings表中的block_size設(shè)置得到）計(jì)算出來的。
   • 第二個(gè)子查詢（別名為ce）計(jì)算了每個(gè)表預(yù)期的行數(shù)和總字節(jié)數(shù)，用于后續(xù)計(jì)算理想頁數(shù)。

3. 過濾條件：排除了系統(tǒng)模式（pg_catalog和information_schema）和非常規(guī)表（relkind不等于’r’，即不是普通表）。

4. 函數(shù)定義：使用CREATE OR REPLACE FUNCTION語句定義了一個(gè)名為check_table_bloat的函數(shù)，該函數(shù)沒有參數(shù)，返回類型為void，表示不返回任何值。函數(shù)體使用PL/pgSQL語言編寫。

5. 循環(huán)和輸出：使用FOR ... IN ... LOOP語句遍歷查詢結(jié)果，并使用RAISE NOTICE語句輸出膨脹信息。

請(qǐng)根據(jù)您的實(shí)際數(shù)據(jù)庫環(huán)境和需求，對(duì)函數(shù)進(jìn)行適當(dāng)?shù)恼{(diào)整和優(yōu)化。這個(gè)函數(shù)可以作為數(shù)據(jù)庫維護(hù)的一部分，定期運(yùn)行以檢查并處理表的膨脹問題。

總結(jié)

表膨脹是PostgreSQL數(shù)據(jù)庫中常見的問題，主要表現(xiàn)為表數(shù)據(jù)和索引占用空間不斷增大，而實(shí)際數(shù)據(jù)量并未顯著變化。這主要由MVCC機(jī)制、頻繁更新刪除、未提交事務(wù)、填充因子設(shè)置及autovacuum機(jī)制不足等因素引起。膨脹的表會(huì)導(dǎo)致存儲(chǔ)成本增加、查詢性能下降、備份恢復(fù)時(shí)間延長(zhǎng)及系統(tǒng)資源消耗增加等問題。為解決這些問題，可以定期執(zhí)行VACUUM操作，啟用和配置autovacuum機(jī)制，使用pg_repack或pg_reorg工具進(jìn)行在線重組，合理設(shè)計(jì)數(shù)據(jù)庫和查詢，以及建立監(jiān)控和預(yù)警體系。特別是，可以通過創(chuàng)建check_table_bloat函數(shù)來定期檢查表的膨脹情況，并及時(shí)采取措施處理，以確保數(shù)據(jù)庫的性能和穩(wěn)定性。

以上就是PostgreSQL表膨脹問題解析及解決方案的詳細(xì)內(nèi)容，更多關(guān)于PostgreSQL表膨脹問題的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！