添加 JSONB 索引是否會使數據庫膨脹？
我們正在執行：
user@host:~$ psql -d database -c "SELECT version();" version --------------------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 10.7 (Ubuntu 10.7-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit (1 row)在：
user@host:~$ lsb_release -a No LSB modules are available. Distributor ID: Ubuntu Description: Ubuntu 16.04.6 LTS Release: 16.04 Codename: xenial並具有以下設置：
database=# \d+ schema.table Table "schema.table" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -----------------------------+-----------------------------+-----------+----------+-------------------------------------------------+----------+--------------+------------- column_1 | bigint | | not null | nextval('table_id_seq'::regclass) | plain | | column_2 | character varying | | not null | | extended | | column_3 | character varying | | not null | | extended | | column_4 | character varying | | not null | | extended | | column_5 | timestamp without time zone | | not null | | plain | | column_6 | timestamp without time zone | | | | plain | | column_7 | character varying | | not null | | extended | | column_8 | jsonb | | not null | | extended | | column_9 | jsonb | | | | extended | | column_10 | character varying | | not null | | extended | | column_11 | character varying | | not null | | extended | | column_12 | character varying | | | | extended | | column_13 | character varying | | | | extended | | column_14 | timestamp with time zone | | not null | | plain | | column_15 | timestamp with time zone | | not null | | plain | | Indexes: "table_pkey" PRIMARY KEY, btree ( column_1 ) "table_idx_1" btree ( column_11) "table_idx_2" btree ( column_4, column_2, column_7, column_5, column_6 ) "table_idx_3" btree ( column_7, column_11, column_15 ) "table_idx_4" btree ( column_7, column_11, column_14 ) "table_idx_5" btree ( column_7, column_11, column_5 ) "table_idx_6" btree ( column_7, ( ( column_8 ->> 'string_1'::TEXT )::INTEGER ), column_5 ) "table_idx_7" btree ( column_15 ) "table_idx_8" btree ( column_4, column_2, column_7, column_5, ( ( column_8 ->> 'string_1'::TEXT )::INTEGER ) ) "table_idx_9" btree ( column_4, column_2, column_7, ( ( column_8 ->> 'string_1'::TEXT )::INTEGER) ) "table_idx_a" btree ( column_7, column_4, column_2, ( ( column_8 ->> 'string_1'::TEXT )::INTEGER), ( ( column_8 ->> 'string_2'::TEXT )::INTEGER ) ) WHERE column_7::TEXT = 'string_3'::TEXT Check constraints: "table_check_constraints" CHECK ( lower( column_10::TEXT ) <> 'string_4'::TEXT OR column_9 IS NOT NULL AND column_6 IS NOT NULL )Autovacuum 已打開並配置為：
user@host:~$ psql -d database -c "SELECT name, setting, pending_restart FROM pg_settings WHERE NAME ILIKE '%autovacuum%' ORDER BY name;" name | setting | pending_restart -------------------------------------+-----------------------+----------------- autovacuum | on | f autovacuum_analyze_scale_factor | 0.002 | f autovacuum_analyze_threshold | 10 | f autovacuum_freeze_max_age | 200000000 | f autovacuum_max_workers | 5 | f autovacuum_multixact_freeze_max_age | 400000000 | f autovacuum_naptime | 30 | f autovacuum_vacuum_cost_delay | 10 | f autovacuum_vacuum_cost_limit | 1000 | f autovacuum_vacuum_scale_factor | 0.001 | f autovacuum_vacuum_threshold | 25 | f autovacuum_work_mem | -1 | f log_autovacuum_min_duration | 0 (env 1) /-1 (env 2) | f (13 rows)以下事件序列發生在環境 1中，在此期間
autovacuum已按上述方式開啟和配置：
- 每晚
VACUUM (VERBOSE, ANALYZE)添加數據庫。- 一段時間過去，膨脹處於正常執行水平。
- Nightly
VACUUM (VERBOSE, ANALYZE)的數據庫被刪除。table_idx_8添加了包含 JSONB 數據類型列的索引。table_idx_9添加了包含 JSONB 數據類型列的索引。- 膨脹的生長突增開始並持續 2 天，直到達到頂峰。
VACUUM (VERBOSE, FULL)的表。- 膨脹恢復到正常的操作水平並保持在那裡。
在這一系列事件中，環境 1中的數據庫大小 (GB) 如下所示：
這就是環境 1中膨脹 (GB) 的樣子：
環境 1中的活動行數：
環境 1中的死行數：
以下事件序列發生在環境 2中，在所有這些事件中都
autovacuum按上述方式打開和配置：
- 每晚
VACUUM (VERBOSE, ANALYZE)添加數據庫。- 一段時間過去，膨脹處於正常執行水平。
- Nightly
VACUUM (VERBOSE, ANALYZE)的數據庫被刪除。table_idx_8添加了包含 JSONB 數據類型列的索引。table_idx_9添加了包含 JSONB 數據類型列的索引。- 膨脹的增長突增開始並持續 2 天，直到達到峰值並使 DB 下降（磁碟已滿）。
TRUNCATE TABLE schema.table.- 再次填充 schema.table 表。
- 膨脹並沒有穩定下來並增長，直到它再次達到頂峰。
TRUNCATE TABLE schema.table在磁碟再次填滿之前。- 數據庫的 VACUUM (VERBOSE, FULL)。
- 再次填充 schema.table 表。
- 膨脹繼續增長！
在這一系列事件中，環境 2中的數據庫大小 (GB)如下所示：
這就是環境 2中膨脹 (GB) 的樣子：
這兩種環境之間的唯一區別是它們的規格略有不同（2 的功能較弱）。在這些事件序列中，每個環境的寫入/讀取量都沒有變化。我們正在使用此查詢來衡量膨脹（以字節為單位）。
我已經交叉檢查了 PostgreSQL 日誌、監控日誌和送出日誌 (Git)，並確定添加兩個索引作為膨脹的觸發器，但是：
- 是對的嗎？增加一個指數會引發如此膨脹的增長嗎？
- 如果確實如此，為什麼添加索引會觸發膨脹？
- 為什麼環境 1 穩定而環境 2 不穩定？
- 我們如何穩定環境 2？
任何回答這些問題的幫助將不勝感激，不用說，我很樂意提供我錯過的任何其他可能有用的資訊。
我認為考古方法在這裡不會很有用。缺少太多資訊和混淆變數。例如，人們通常不會無緣無故地添加索引。如果工作負載的變化推動了索引的創建，則可能是獨立於索引的工作負載的變化導致了膨脹。
有很多理論可以解釋你所看到的，但實際上沒有辦法根據給出的歷史來區分它們。每個索引都為真空提供了更多工作要做，因此您的新索引可能只是因為它已經接近臨界點而將其推到了臨界點，而不管索引的內容是什麼。或者，當表被鎖定以創建索引時，可能會堆積很多工作，然後一旦鎖定被釋放，活動的狂熱就會把它推到邊緣。不僅僅是更多的索引為真空創造了更多的工作——膨脹也是如此。這可能導致惡性循環，更多的膨脹會減緩真空，導致更多的膨脹。這可能是 VACUUM FULL 後環境 1 穩定的原因，它打破了惡性循環，以至於正常真空現在可以跟上。
autovacuum_analyze_scale_factor | 0.002 | f autovacuum_analyze_threshold | 10 | f autovacuum_vacuum_scale_factor | 0.001 | f autovacuum_vacuum_threshold | 25 | f乍一看，這些設置似乎相當荒謬。他們有理由嗎？它可能會花費大量時間清理和分析並不真正需要它們的表，以至於它無法跟上需要清理的表（但如果你只有一個大表，那可能不是很多憂慮）。降低比例因子是有意義的，但通常只能與門檻值的增加結合使用。
我經常將“vacuum_cost_page_hit”設置為零“vacuum_cost_page_miss”設置為零。以我的經驗，autovac引起的並發性能問題一般是由寫入引起的，而不是由讀取引起的，因此在讀取端進行節流是沒有意義的。當您擁有已經膨脹的表和索引時，這一點尤其重要，因為您可以進行更多的閱讀而不是寫作。
設置 log_autovacuum_min_duration=0 的輸出可以幫助區分各種理論。此外，使用pg_freespacemap來查看 PostgreSQL 認為該表有多少 sum(avail)，雖然它處於膨脹狀態，但可以提供資訊。