本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。
# 在 Amazon RDS for PostgreSQL 上使用 PostgreSQL 自動清空
強烈建議您使用自動資料清理功能，以維護 PostgreSQL 資料庫執行個體的運作狀態。自動資料清理會自動啟動 VACUUM 和 ANALYZE 指令。此功能會檢查含有大量輸入、更新或刪除元組的資料表。完成檢查後，即會透過從 PostgreSQL 資料庫移除已淘汰的資料或元組回收儲存空間。
預設情況下，使用任何預設 PostgreSQL 資料庫參數群組建立的 RDS for PostgreSQL 資料庫執行個體上，都開啟了自動清空功能。預設情況下，還會設定與自動資料清理功能相關聯的其他組態參數。因為這些預設值相當泛用，針對特定的工作負載調校與自動資料清理功能相關聯的某些參數，對您會有所幫助。
接下來，您可以找到更多有關自動清空功能以及如何在您的 RDS for PostgreSQL 資料庫執行個體上調校該功能一些參數的資訊。如需高階資訊，請參閱 [使用 PostgreSQL 的最佳實務](CHAP_BestPractices.md#CHAP_BestPractices.PostgreSQL)。
**Topics**
+ [配置自動資料清理的記憶體](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum.WorkMemory)
+ [降低交易 ID 包圍的可能性](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum.AdaptiveAutoVacuuming)
+ [判斷資料庫中的資料表是否需要清理](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.NeedVacuuming.md)
+ [判斷哪些資料表目前適合進行自動資料清理](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.EligibleTables.md)
+ [判斷自動資料清理目前是否執行中且執行多久時間](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.AutovacuumRunning.md)
+ [執行手動清理凍結](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.VacuumFreeze.md)
+ [在自動資料清理執行時重新為資料表建立索引](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.Reindexing.md)
+ [使用大型索引管理自動清空](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.LargeIndexes.md)
+ [影響自動資料清理的其他參數](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.OtherParms.md)
+ [設定自動資料清理參數資料表層級](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.TableParameters.md)
+ [記錄清理和自動資料清理活動](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.Logging.md)
+ [了解具有無效資料庫的自動清空行為](appendix.postgresql.commondbatasks.autovacuumbehavior.md)
+ [在 RDS for PostgreSQL 中識別並解決積極的清空封鎖程式](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.md)
## 配置自動資料清理的記憶體
[https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-AUTOVACUUM-WORK-MEM](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-AUTOVACUUM-WORK-MEM) 參數是影響自動資料清理效能的最重要參數之一。在 RDS for PostgreSQL 第 14 版及更舊版本中，`autovacuum_work_mem` 參數會設定為 -1，表示改用 `maintenance_work_mem` 的設定。對於所有其他版本，`autovacuum_work_mem` 是由 GREATEST(\$1DBInstanceClassMemory/32768\$1, 65536) 決定。
手動清空操作一律會使用 `maintenance_work_mem` 設定，預設設定為 GREATEST(\$1DBInstanceClassMemory/63963136\$11024\$1, 65536)，也可以使用 `SET` 命令在工作階段層級進行調整，以進行更精準的手動 `VACUUM` 操作。
`autovacuum_work_mem` 會決定自動清空的記憶體，以保留無效元組 (`pg_stat_all_tables.n_dead_tup`) 的識別符來清空索引。
執行計算以判斷 `autovacuum_work_mem` 參數的值時，請注意下列事項：
+ 如果您將參數設得太低，則清理程序可能必須掃描資料表多次才能完成其工作。多次的掃描可能會對效能產生負面影響。對於較大的執行個體，將 `maintenance_work_mem`或 `autovacuum_work_mem` 設定為至少 1 GB 可以改善清空具有大量無效元組之資料表的效能。不過，在 PostgreSQL 第 16 版和之前版本中，清空的記憶體用量上限為 1 GB，這足以在單次通過中處理大約 1.79 億個無效元組。如果資料表的無效元組超過此值，清空將需要多次通過資料表的索引，大幅增加所需的時間。從 PostgreSQL 第 17 版開始，沒有 1 GB 的限制，而自動清空可以透過使用基數樹來處理超過 1.79 億個元組。
元組識別符的大小為 6 個位元組。若要預估清空資料表索引所需的記憶體，請查詢 `pg_stat_all_tables.n_dead_tup` 以尋找無效元組的數量，然後將此數字乘以 6，以判斷在單次通過中清空索引所需的記憶體。您可以使用下列查詢：
```
SELECT
relname AS table_name,
n_dead_tup,
pg_size_pretty(n_dead_tup * 6) AS estimated_memory
FROM
pg_stat_all_tables
WHERE
relname = 'name_of_the_table';
```
+ `autovacuum_work_mem` 參數會搭配 `autovacuum_max_workers` 參數運作。`autovacuum_max_workers` 中的每個背景工作者可以使用您配置的記憶體。如果您有許多小型資料表，請配置較多 `autovacuum_max_workers` 和較少 `autovacuum_work_mem`。如果您有大型資料表 (大於 100 GB)，請配置較多記憶體和較少背景工作者處理程序。您必須配置足夠的記憶體，才能在最大的資料表上順利執行作業。因此，請確保背景工作者處理程序與記憶體的組合等於您想要配置的總記憶體。
## 降低交易 ID 包圍的可能性
在一些狀況中，自動資料清理相關的參數群組設定可能不夠積極以防止交易 ID 包圍。為了解決此問題，RDS for PostgreSQL 提供自動調整自動清空參數值的機制。*自動清空參數彈性調整*是 RDS for PostgreSQL 的孤能。PostgreSQL 文件中有詳細的 [交易 ID 包圍](https://www.postgresql.org/docs/current/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND) 的說明。
預設開啟 RDS for PostgreSQL 執行個體的自動清空參數彈性調整功能，且動態參數 `rds.adaptive_autovacuum` 設定為 [開啟]。我們強烈建議您開啟此選項。不過，若要關閉參數彈性調校功能，請將參數 `rds.adaptive_autovacuum` 設定為 0 或 OFF (關閉)。
Amazon RDS 調校自動清空參數時，交易 ID 包圍仍可能發生。我們鼓勵您實施交易 ID 包圍的Amazon CloudWatch 警報。如需詳細資訊，請參閱 AWS 資料庫部落格上的文章在 [RDS for PostgreSQL 中實作交易 ID 包裝的提早警告系統](https://aws.amazon.com/blogs/database/implement-an-early-warning-system-for-transaction-id-wraparound-in-amazon-rds-for-postgresql/)。
若開啟自動資料清理參數彈性調校，當 CloudWatch 指標 `MaximumUsedTransactionIDs` 達到 `autovacuum_freeze_max_age` 參數的值或 500,000,000 時 (以較大者為準)，Amazon RDS 將開始調整自動資料清理參數。
如果資料表繼續有交易 ID 包圍的趨勢，則 Amazon RDS 會繼續調整自動資料清理的參數。每次調整都提供更多自動資料清理的資源以避免包圍。Amazon RDS 會更新下列自動資料清理相關參數：
+ [autovacuum\$1vacuum\$1cost\$1delay](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-DELAY)
+ [ autovacuum\$1vacuum\$1cost\$1limit](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-LIMIT)
+ [https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-AUTOVACUUM-WORK-MEM](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-AUTOVACUUM-WORK-MEM)
+ [autovacuum\$1naptime](https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-NAPTIME)
RDS 只會在新的值能夠使自動資料清理更為積極時修改這些參數。參數在資料庫執行個體上的記憶體中修改。參數群組中的值不會變更。若要檢視目前的使用中記憶體設定，請使用 PostgreSQL [SHOW (顯示)](https://www.postgresql.org/docs/current/sql-show.html) SQL 指令。
當 Amazon RDS 修改任何自動資料清理參數時，即會對受影響的資料庫執行個體產生事件。此事件會顯示在 上， AWS 管理主控台 並透過 Amazon RDS API 顯示。`MaximumUsedTransactionIDs` CloudWatch 指標回到閥值以下時，Amazon RDS 就會將記憶體中的自動資料清理參數重設回參數群組中指定的值。系統隨即會產生與此變更相對應的另一個事件。
# 判斷資料庫中的資料表是否需要清理
您可以使用以下查詢可用來顯示資料庫中未凍結的交易數目。資料庫 `datfrozenxid` 列的 `pg_database` 欄是顯示於該資料庫正常交易 ID 的下線。此欄位是資料庫內每個資料表 `relfrozenxid` 最小的值。
```
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) desc limit 20;
```
例如，執行上述查詢的結果可能如下所示。
```
datname | age
mydb | 1771757888
template0 | 1721757888
template1 | 1721757888
rdsadmin | 1694008527
postgres | 1693881061
(5 rows)
```
當交易 ID 包圍的存留期觸及 20 億，則會發生交易 ID 包圍 (XID)，且資料庫將變成唯獨狀態。您可使用此查詢來產生指標且一天執行數次。根據預設，自動資料清理已將交易的存留期設定為不超過 200,000,000 ([https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE))。
範例監視策略可能如下所示：
+ 設定 `autovacuum_freeze_max_age` 值到 2 億交易數目
+ 如果資料表達到 5 億的未凍結交易，則觸發低安全性警報。這並非不合理的值，但可以表示該自動資料清理並未保持啟動狀態。
+ 如果資料表存留達到 10 億，這應視為採取動作的警報。一般而言，您會基於效能理由，而想讓存留期比較接近 `autovacuum_freeze_max_age`。我們建議您使用下列建議調查。
+ 如果資料表達到 15 億的未清理交易，則觸發高安全性警報。視資料庫使用交易的速度而定，此警報可指出系統來不及執行自動資料清理。在此情況下，我們建議您立即解決此問題。
如果資料表不斷違反這些閾值，請進一步修改自動資料清理參數。依預設，手動使用 VACUUM (已停用或以成本為基礎的延遲) 比使用預設自動資料清理更積極，但總體上對系統的干擾也更加嚴重。
我們建議下列作法：
+ 請注意並開啟監控機制，如此您才會注意最舊交易的存留期。
如需建立程序以警告您交易 ID 包裝的相關資訊，請參閱 AWS 資料庫部落格文章在 [Amazon RDS for PostgreSQL 中實作交易 ID 包裝的早期警告系統](https://aws.amazon.com/blogs/database/implement-an-early-warning-system-for-transaction-id-wraparound-in-amazon-rds-for-postgresql/)。
+ 對於比較忙碌的資料表，除了依賴自動資料清理以外，請在維護時段定期執行手動清理凍結。如需執行手動清理凍結的資訊，請參閱 [執行手動清理凍結](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.VacuumFreeze.md)。
# 判斷哪些資料表目前適合進行自動資料清理
通常有一或兩個資料表需要清理。表單中 `relfrozenxid` 的值大於 `autovacuum_freeze_max_age` 中交易的值，總是會成為自動資料清理的目標。否則，如果元組數因為最後一個 VACUUM 超出清理閾值而過時，則會清理資料表。
[自動資料清理閾值](https://www.postgresql.org/docs/current/static/routine-vacuuming.html#AUTOVACUUM)已定義如下：
```
Vacuum-threshold = vacuum-base-threshold + vacuum-scale-factor * number-of-tuples
```
其中 `vacuum base threshold`是 `autovacuum_vacuum_threshold`，`vacuum scale factor`是 `autovacuum_vacuum_scale_factor`，而 `number of tuples`是 `pg_class.reltuples`。
當您連線到資料庫時，執行以下查詢可查看自動資料清理功能認為符合清理條件的資料表清單。
```
WITH vbt AS (SELECT setting AS autovacuum_vacuum_threshold FROM
pg_settings WHERE name = 'autovacuum_vacuum_threshold'),
vsf AS (SELECT setting AS autovacuum_vacuum_scale_factor FROM
pg_settings WHERE name = 'autovacuum_vacuum_scale_factor'),
fma AS (SELECT setting AS autovacuum_freeze_max_age FROM pg_settings WHERE name = 'autovacuum_freeze_max_age'),
sto AS (select opt_oid, split_part(setting, '=', 1) as param,
split_part(setting, '=', 2) as value from (select oid opt_oid, unnest(reloptions) setting from pg_class) opt)
SELECT '"'||ns.nspname||'"."'||c.relname||'"' as relation,
pg_size_pretty(pg_table_size(c.oid)) as table_size,
age(relfrozenxid) as xid_age,
coalesce(cfma.value::float, autovacuum_freeze_max_age::float) autovacuum_freeze_max_age,
(coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) +
coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples)
AS autovacuum_vacuum_tuples, n_dead_tup as dead_tuples FROM
pg_class c join pg_namespace ns on ns.oid = c.relnamespace
join pg_stat_all_tables stat on stat.relid = c.oid join vbt on (1=1) join vsf on (1=1) join fma on (1=1)
left join sto cvbt on cvbt.param = 'autovacuum_vacuum_threshold' and c.oid = cvbt.opt_oid
left join sto cvsf on cvsf.param = 'autovacuum_vacuum_scale_factor' and c.oid = cvsf.opt_oid
left join sto cfma on cfma.param = 'autovacuum_freeze_max_age' and c.oid = cfma.opt_oid
WHERE c.relkind = 'r' and nspname <> 'pg_catalog'
AND (age(relfrozenxid) >= coalesce(cfma.value::float, autovacuum_freeze_max_age::float)
OR coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) +
coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) *
c.reltuples <= n_dead_tup)
ORDER BY age(relfrozenxid) DESC LIMIT 50;
```
# 判斷自動資料清理目前是否執行中且執行多久時間
如果您需要手動清理資料表，請判斷目前是否在執行自動資料清理功能。若是，您可能需要調整參數，使其更有效率地執行，或暫時關閉自動資料清理功能，以便您手動執行 VACUUM。
使用以下查詢來判斷自動資料清理是否執行中、已執行多久時間，以及是否正等待另一個工作階段。
```
SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query
FROM pg_stat_activity
WHERE upper(query) LIKE '%VACUUM%'
ORDER BY xact_start;
```
在執行此查詢之後，您應會看到類似底下的輸出：
```
datname | usename | pid | state | wait_event | xact_runtime | query
--------+----------+-------+--------+------------+-------------------------+--------------------------------------------------------------------------------------------------------
mydb | rdsadmin | 16473 | active | | 33 days 16:32:11.600656 | autovacuum: VACUUM ANALYZE public.mytable1 (to prevent wraparound)
mydb | rdsadmin | 22553 | active | | 14 days 09:15:34.073141 | autovacuum: VACUUM ANALYZE public.mytable2 (to prevent wraparound)
mydb | rdsadmin | 41909 | active | | 3 days 02:43:54.203349 | autovacuum: VACUUM ANALYZE public.mytable3
mydb | rdsadmin | 618 | active | | 00:00:00 | SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query+
| | | | | | FROM pg_stat_activity +
| | | | | | WHERE query like '%VACUUM%' +
| | | | | | ORDER BY xact_start; +
```
有幾個問題可能造成長時間執行自動資料清理階段 (長達數天)。最常見的問題就是資料表大小或更新率的 [https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM](https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM) 參數值設得太低。
建議您依照下列公式來設定 `maintenance_work_mem` 參數值。
```
GREATEST({DBInstanceClassMemory/63963136*1024},65536)
```
短時間執行的自動資料清理工作階段也可以指出問題：
+ 它可指出您的工作負載沒有足夠的 `autovacuum_max_workers`。在此情況下，您需要指出工作者數目。
+ 它可指出有索引損毀 (自動資料清理會當機並以相同的關聯重新啟動，但沒有任何進度)。在這種情況下，請手動執行 `vacuum freeze verbose table` 來查看確切原因。
# 執行手動清理凍結
您可以在清理程序已在執行中的資料表上執行手動清理。如果您已辨識出表單的交易將近 20 億 (或超出您所監控的任何閾值) 的資料表，此功能將很實用。
下列步驟是準則，而程序會有多種變化。例如，在測試期間，假設您會發現 [https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM](https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM) 參數值設定的太小而您需要對資料表採取立即行動。不過，或許您當下不想退回執行個體。使用上個區段所列的查詢，您可判斷哪個資料表有問題，並注意長時間執行的自動資料清理工作階段。您知道您必須變更 `maintenance_work_mem` 參數設定，但您也需要採取立即動作並清理有問題的資料表。以下程序說明在此情況下的處理方式。
**手動執行清理凍結**
1. 對包含要清理之資料表的資料庫，開啟兩個工作階段。在第二個工作階段中，如果連線中斷，請使用 "screen" 或其他公用程式來維持此工作階段。
1. 在第一個工作階段中，取得在資料表上執行之自動資料清理工作階段的處理程序 ID (PID)。
執行以下查詢來取得自動資料清理工作階段的 PID。
```
SELECT datname, usename, pid, current_timestamp - xact_start
AS xact_runtime, query
FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY
xact_start;
```
1. 在第二個工作階段中，計算您需要用於此作業的記憶體數量。在此範例中，我們判斷我們可以將最多 2 GB 的記憶體使用於此作業，所以將目前工作階段的 [https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM](https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM) 設定為 2 GB。
```
SET maintenance_work_mem='2 GB';
SET
```
1. 在第二個工作階段中，對表格發佈 `vacuum freeze verbose` 指令。詳細資訊設定很實用，因為即使 PostgreSQL 中目前沒有此工作階段的進度報告，您仍可查看活動。
```
\timing on
Timing is on.
vacuum freeze verbose pgbench_branches;
```
```
INFO: vacuuming "public.pgbench_branches"
INFO: index "pgbench_branches_pkey" now contains 50 row versions in 2 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pgbench_branches_test_index" now contains 50 row versions in 2 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pgbench_branches": found 0 removable, 50 nonremovable row versions
in 43 out of 43 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 9347 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
Time: 2.765 ms
```
1. 在第一個工作階段，如果自動清空封鎖清空工作階段，`pg_stat_activity` 會顯示清空工作階段的等待為 `T`。在此情況下，請按如下方式結束自動清空程序。
```
SELECT pg_terminate_backend('the_pid');
```
**注意**
某些較低版本的 Amazon RDS 無法使用上述命令終止自動清空程序，並失敗並顯示下列錯誤：`ERROR: 42501: must be a superuser to terminate superuser process LOCATION: pg_terminate_backend, signalfuncs.c:227`。
此時，您的工作階段會開始。自動清空會立即重新啟動，因為此資料表可能排在其工作清單的最前面。
1. 在第二個工作階段中啟動您的 `vacuum freeze verbose` 命令，然後在第一個工作階段中結束自動資料清理程序。
# 在自動資料清理執行時重新為資料表建立索引
如果索引毀損，自動資料清理會繼續處理資料表並且失敗。如果您在此情況下嘗試手動清理，則會收到如下錯誤訊息。
```
postgres=> vacuum freeze pgbench_branches;
ERROR: index "pgbench_branches_test_index" contains unexpected
zero page at block 30521
HINT: Please REINDEX it.
```
當索引毀損且自動資料清理嘗試在資料表上執行時，您全力應付已在執行中的自動資料清理工作階段。當您發佈 [REINDEX](https://www.postgresql.org/docs/current/static/sql-reindex.html) 指令，您開啟表單上的獨佔鎖定。寫入操作遭到封鎖，也使用特定索引讀取操作。
**在資料表上執行自動資料清理時重新為資料表建立索引**
1. 對包含要清理之資料表的資料庫，開啟兩個工作階段。在第二個工作階段中，如果連線中斷，請使用 "screen" 或其他公用程式來維持此工作階段。
1. 在第一個工作階段中，取得在資料表上執行之自動資料清理工作階段的 PID。
執行以下查詢來取得自動資料清理工作階段的 PID。
```
SELECT datname, usename, pid, current_timestamp - xact_start
AS xact_runtime, query
FROM pg_stat_activity WHERE upper(query) like '%VACUUM%' ORDER BY
xact_start;
```
1. 在第二個工作階段中，發出 reindex 命令。
```
\timing on
Timing is on.
reindex index pgbench_branches_test_index;
REINDEX
Time: 9.966 ms
```
1. 在第一個工作階段中，如果自動資料清理封鎖處理程序，您會在 `pg_stat_activity` 中看見清理工作階段的等待為 "T"。在此情況下，您就結束自動資料清理程序。
```
SELECT pg_terminate_backend('the_pid');
```
此時，您的工作階段會開始。請特別注意，自動資料清理會立即重新啟動，因為此資料表可能排在其工作清單的最前面。
1. 在第二個工作階段中啟動您的命令，然後在第一個工作階段中結束自動資料清理程序。
# 使用大型索引管理自動清空
作為其操作的一部分，*自動清空*會在資料表上執行時執行數個[清空階段](https://www.postgresql.org/docs/current/progress-reporting.html#VACUUM-PHASES)。在清除資料表之前，首先會清空其所有索引。移除多個大型索引時，此階段會耗用大量的時間和資源。因此，最佳實務是務必控制資料表上的索引數目，並清除未使用的索引。
在此程序中，首先檢查整體索引大小。然後，判斷是否有可以移除的潛在未用索引，如下列範例所示。
**檢查資料表及其索引的大小**
```
postgres=> select pg_size_pretty(pg_relation_size('pgbench_accounts'));
pg_size_pretty
6404 MB
(1 row)
```
```
postgres=> select pg_size_pretty(pg_indexes_size('pgbench_accounts'));
pg_size_pretty
11 GB
(1 row)
```
在此範例中，索引的大小大於資料表。這種差異可能會導致性能問題，因為索引膨脹或未使用，這會影響自動清空以及插入操作。
**檢查是否有未使用的索引**
使用 [https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-INDEXES-VIEW](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-INDEXES-VIEW) 檢視，您可以檢查索引與 `idx_scan` 資料欄搭配使用的頻率。在下列範例中，未使用的索引的 `idx_scan` 值為 `0`。
```
postgres=> select * from pg_stat_user_indexes where relname = 'pgbench_accounts' order by idx_scan desc;
relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+------------------+-----------------------+----------+--------------+---------------
16433 | 16454 | public | pgbench_accounts | index_f | 6 | 6 | 0
16433 | 16450 | public | pgbench_accounts | index_b | 3 | 199999 | 0
16433 | 16447 | public | pgbench_accounts | pgbench_accounts_pkey | 0 | 0 | 0
16433 | 16452 | public | pgbench_accounts | index_d | 0 | 0 | 0
16433 | 16453 | public | pgbench_accounts | index_e | 0 | 0 | 0
16433 | 16451 | public | pgbench_accounts | index_c | 0 | 0 | 0
16433 | 16449 | public | pgbench_accounts | index_a | 0 | 0 | 0
(7 rows)
```
```
postgres=> select schemaname, relname, indexrelname, idx_scan from pg_stat_user_indexes where relname = 'pgbench_accounts' order by idx_scan desc;
schemaname | relname | indexrelname | idx_scan
------------+------------------+-----------------------+----------
public | pgbench_accounts | index_f | 6
public | pgbench_accounts | index_b | 3
public | pgbench_accounts | pgbench_accounts_pkey | 0
public | pgbench_accounts | index_d | 0
public | pgbench_accounts | index_e | 0
public | pgbench_accounts | index_c | 0
public | pgbench_accounts | index_a | 0
(7 rows)
```
**注意**
這些統計資訊是從統計資訊重設時開始累計的。假設您有僅在營業季度結束時使用的索引，或僅用於特定報告的索引。自統計資訊重設以來，可能尚未使用此索引。如需詳細資訊，請參閱[統計資訊函數](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-STATS-FUNCTIONS)。用來強制執行唯一性的索引不會執行掃描，也不應將其識別為未使用的索引。若要識別未使用的索引，您應該對應用程式及其查詢有深入的理解。
若要檢查上次何時重設資料庫的統計資訊，請使用 [ https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW]( https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW)
```
postgres=> select datname, stats_reset from pg_stat_database where datname = 'postgres';
datname | stats_reset
----------+-------------------------------
postgres | 2022-11-17 08:58:11.427224+00
(1 row)
```
## 盡快清空資料表
**RDS for PostgreSQL 12 和更新版本**
如果大型資料表中有太多索引，您的資料庫執行個體可能接近交易 ID 包圍 (XID)，也就是當 XID 計數器包圍至零時。若保持取消核取的狀態，此情況可能會導致資料遺失。不過，您可以在不清除索引的情況下快速清空資料表。在 RDS for PostgreSQL 12 及更新版本中，您可以使用 VACUUM 搭配 [https://www.postgresql.org/docs/current/sql-vacuum.html](https://www.postgresql.org/docs/current/sql-vacuum.html) 子句。
```
postgres=> VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) pgbench_accounts;
INFO: vacuuming "public.pgbench_accounts"
INFO: table "pgbench_accounts": found 0 removable, 8 nonremovable row versions in 1 out of 819673 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 7517
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.
```
如果自動清空工作階段已在執行中，您必須終止它才能開始手動 VACUUM。如需執行手動清空凍結的相關資訊，請參閱 [執行手動清理凍結](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.VacuumFreeze.md)。
**注意**
定期略過索引清除可能會導致索引膨脹，這會降低掃描效能。索引會保留無效資料列，而資料表會保留無效資料行指標。因此，`pg_stat_all_tables.n_dead_tup` 會增加，直到自動清空或手動 VACUUM 搭配索引清除執行為止。作為最佳實務，只使用此程序來防止交易 ID 包圍。
**RDS for PostgreSQL 11 和更舊版本**
不過，在 RDS for PostgreSQL 11 和更舊版本中，允許清空更快完成的唯一方法是減少資料表上的索引數目。捨棄索引可能會影響查詢計畫。我們建議您先捨棄未使用的索引，然後在 XID 包圍非常接近時捨棄索引。在清空程序完成之後，您可以重新建立這些索引。
# 影響自動資料清理的其他參數
以下查詢顯示會直接影響自動資料清理及其行為的某些參數值。PostgreSQL 文件會完整說明[自動資料清理參數](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html)。
```
SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE name IN (
'autovacuum_max_workers',
'autovacuum_analyze_scale_factor',
'autovacuum_naptime',
'autovacuum_analyze_threshold',
'autovacuum_analyze_scale_factor',
'autovacuum_vacuum_threshold',
'autovacuum_vacuum_scale_factor',
'autovacuum_vacuum_threshold',
'autovacuum_vacuum_cost_delay',
'autovacuum_vacuum_cost_limit',
'vacuum_cost_limit',
'autovacuum_freeze_max_age',
'maintenance_work_mem',
'vacuum_freeze_min_age');
```
雖然這些全都會影響自動資料清理，其中最重要的參數如下：
+ [maintenance\$1work\$1mem](https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE_WORK_MEM)
+ [autovacuum\$1freeze\$1max\$1age](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE)
+ [autovacuum\$1max\$1workers](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-MAX-WORKERS)
+ [autovacuum\$1vacuum\$1cost\$1delay](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-DELAY)
+ [ autovacuum\$1vacuum\$1cost\$1limit](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-LIMIT)
# 設定自動資料清理參數資料表層級
您可以在資料表層級設定自動資料清理相關的[儲存參數](https://www.postgresql.org/docs/current/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS)，這比改變整個資料庫的行為更理想。對於大型資料表，您可能需要設定積極的設定值，而且不想讓自動資料清理以那種方式對待所有的資料表。
以下查詢顯示哪些資料表目前已備妥資料表層級選項。
```
SELECT relname, reloptions
FROM pg_class
WHERE reloptions IS NOT null;
```
在比您其餘的資料表大許多的資料表範例上，此查詢可能很實用。假設您有一個 300 GB 的表單，及 30 個少於 1 GB 的其他表單。在此情況下，您可以為大型資料表設定一些特定參數，您就不會改變整個系統的行為。
```
ALTER TABLE mytable set (autovacuum_vacuum_cost_delay=0);
```
這麼做會針對此資料表關閉成本型自動資料清理延遲，代價是在您系統上使用更多資源。通常情況下，每次達到 `autovacuum_cost_limit` 時，自動資料清理都會暫停 `autovacuum_vacuum_cost_delay`。如需詳細資訊，請參閱 PostgreSQL 文件中的[成本型清理](https://www.postgresql.org/docs/current/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST)。
# 記錄清理和自動資料清理活動
有關自動資料清理活動的資訊將根據 `rds.force_autovacuum_logging_level` 參數中指定的層級傳送到 `postgresql.log`。以下是此參數允許的值，以及預設為該值的 PostgreSQL 版本：
+ `disabled` (PostgreSQL 10、PostgreSQL 9.6)
+ `debug5`, `debug4`, `debug3`, `debug2`, `debug1`
+ `info` (PostgreSQL 12、PostgreSQL 11)
+ `notice`
+ `warning` (PostgreSQL 13 及以上)
+ `error`、日誌、`fatal`、`panic`
`rds.force_autovacuum_logging_level` 使用 `log_autovacuum_min_duration` 參數。`log_autovacuum_min_duration` 參數的值是記錄自動資料清理動作的閾值 (以毫秒為單位)。設定為 `-1`，表示不會記錄任何內容；設定為 0，則會記錄所有動作。如同 `rds.force_autovacuum_logging_level`，`log_autovacuum_min_duration` 的預設值取決於版本，如下所示：
+ `10000 ms`：PostgreSQL 14、PostgreSQL 13、PostgreSQL 12 和 PostgreSQL 11
+ `(empty)`：PostgreSQL 10 和 PostgreSQL 9.6 沒有預設值
建議您將 `rds.force_autovacuum_logging_level` 設定為 `WARNING`。我們也建議您將 `log_autovacuum_min_duration` 設定為 1000 到 5000 之間的值。設定為 5000，表示會記錄時間超過 5,000 毫秒的活動。如果鎖定衝突或同時刪除關係導致跳過自動資料清理動作，-1 以外的任何設定也會記錄訊息。如需詳細資訊，請參閱 PostgreSQL 文件中的[自動資料清理](https://www.postgresql.org/docs/current/runtime-config-autovacuum.html)。
若要解決問題，您可以將 `rds.force_autovacuum_logging_level` 參數變更為其中一個除錯等級 (從 `debug1` 至 `debug5`)，以取得詳盡資訊。我們建議您在短時間內使用除錯設定，並且僅用於疑難排解目的。如需進一步了解，請參閱 PostgreSQL 文件中的[何時記錄](https://www.postgresql.org/docs/current/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN)。
**注意**
PostgreSQL 可讓 `rds_superuser` 帳戶檢視 `pg_stat_activity` 中的自動資料清理工作階段。例如，您可以找出並終止會阻擋命令執行的自動資料清理工作階段，或是執行速度比手動發出的清理命令還要慢的自動資料清理工作階段。
# 了解具有無效資料庫的自動清空行為
系統會將新值 `-2` 引入 `pg_database` 目錄中的 `datconnlimit` 欄，以指出在 DROP DATABASE 操作中間中斷的資料庫無效。
此新值可從下列 RDS for PostgreSQL 版本取得：
+ 15.4 版和所有更新版本
+ 14.9 和更新版本
+ 13.12 和更新版本
+ 12.16 和更新版本
+ 11.21 和更新版本
無效的資料庫不會影響自動清空凍結有效資料庫功能的能力。自動清空會忽略無效的資料庫。因此，定期清空操作將繼續對 PostgreSQL 環境中的所有有效資料庫正常運作且有效率地運作。
**Topics**
+ [監控交易 ID](#appendix.postgresql.commondbatasks.autovacuum.monitorxid)
+ [調整監控查詢](#appendix.postgresql.commondbatasks.autovacuum.monitoradjust)
+ [解決無效的資料庫問題](#appendix.postgresql.commondbatasks.autovacuum.connissue)
## 監控交易 ID
`age(datfrozenxid)` 函數通常用於監控資料庫的交易 ID (XID) 存留期，以防止交易 ID 包圍。
由於自動清空會排除無效的資料庫，因此其交易 ID (XID) 計數器可以達到 `2 billion` 的最大值、包裝至 `- 2 billion`，並無限期地繼續此週期。用於監控交易 ID 包圍的典型查詢可能如下所示：
```
SELECT max(age(datfrozenxid)) FROM pg_database;
```
不過，隨著引入 `datconnlimit` 的 -2 值，無效的資料庫可能會扭曲此查詢的結果。由於這些資料庫無效，且不應成為定期維護檢查的一部分，因此可能會導致誤報，造成您認為 `age(datfrozenxid)` 高於實際值。
## 調整監控查詢
為了確保準確的監控，您應該調整監控查詢以排除無效的資料庫。遵循以下建議的查詢：
```
SELECT
max(age(datfrozenxid))
FROM
pg_database
WHERE
datconnlimit <> -2;
```
此查詢可確保 `age(datfrozenxid)` 計算中只會考慮有效的資料庫，以提供 PostgreSQL 環境中交易 ID 存留期的真實反映。
## 解決無效的資料庫問題
嘗試連線至無效的資料庫時，您可能會遇到類似以下的錯誤訊息：
```
postgres=> \c db1
connection to server at "mydb.xxxxxxxxxx.us-west-2.rds.amazonaws.com" (xx.xx.xx.xxx), port xxxx failed: FATAL: cannot connect to invalid database "db1"
HINT: Use DROP DATABASE to drop invalid databases.
Previous connection kept
```
此外，如果 `log_min_messages` 參數設定為 `DEBUG2` 或更高版本，您可能會注意到下列日誌項目，指出自動清空程序正在略過無效的資料庫：
```
2024-07-30 05:59:00 UTC::@:[32000]:DEBUG: autovacuum: skipping invalid database "db6"
2024-07-30 05:59:00 UTC::@:[32000]:DEBUG: autovacuum: skipping invalid database "db1"
```
若要解決問題，請遵循連線嘗試期間提供的 `HINT`。使用 RDS 主帳戶或具有 `rds_superuser` 角色的資料庫帳戶 (RDS 主帳戶) 連線到任何有效的資料庫，並捨棄無效的資料庫。
```
SELECT
'DROP DATABASE ' || quote_ident(datname) || ';'
FROM
pg_database
WHERE
datconnlimit = -2 \gexec
```
# 在 RDS for PostgreSQL 中識別並解決積極的清空封鎖程式
在 PostgreSQL 中，清空對於確保資料庫在回收儲存體時的運作狀態，並防止[交易 ID 包圍](https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND)問題至關重要。不過，有時候可以視需要防止清空操作，這可能會導致效能降低、儲存膨脹，甚至影響資料庫執行個體透過交易 ID 包圍的可用性。因此，識別和解決這些問題對於最佳的資料庫效能和可用性至關重要。閱讀[了解 Amazon RDS for PostgreSQL 環境中的自動清空功能](https://aws.amazon.com/blogs/database/understanding-autovacuum-in-amazon-rds-for-postgresql-environments/)以進一步了解自動清空功能。
`postgres_get_av_diag()` 函數有助於識別防止或延遲積極清空進度的問題。提供建議，其中可能包括用於解決可辨識問題的命令，或用於無法辨識問題之進一步診斷的指引。當存留期超過 RDS 的[彈性自動清空](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum.AdaptiveAutoVacuuming)閾值 5 億筆交易 ID 時，就會報告積極的清空封鎖程式。
**交易 ID 的存留期為何？**
交易 ID 的 `age()` 函數會計算自資料庫 (`pg_database.datfrozenxid`) 或資料表 (`pg_class.relfrozenxid`) 最舊的未凍結交易 ID 以來發生的交易數量。此值表示自上次積極清空操作以來的資料庫活動，並強調近期 VACUUM 程序的可能工作負載。
**什麼是積極清空？**
積極的 VACUUM 操作會全面掃描資料表中的所有頁面，包括在一般 VACUUM 期間通常會略過的頁面。此徹底掃描旨在「凍結」接近其最長存留期的交易 ID，有效防止稱為[交易 ID 包圍](https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND)的情況。
若要讓 `postgres_get_av_diag()` 報告封鎖程式，封鎖程式必須至少有 5 億筆舊的交易。
**Topics**
+ [在 RDS for PostgreSQL 中安裝自動清空監控和診斷工具](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Installation.md)
+ [RDS for PostgreSQL 中 postgres\$1get\$1av\$1diag() 的函數](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Functions.md)
+ [解決 RDS for PostgreSQL 中可識別的清空封鎖程式](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md)
+ [解決 RDS for PostgreSQL 中無法識別的清空封鎖程式](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Unidentifiable_blockers.md)
+ [解決 RDS for PostgreSQL 中的清空效能問題](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Performance.md)
+ [RDS for PostgreSQL 中的通知訊息說明](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.NOTICE.md)
# 在 RDS for PostgreSQL 中安裝自動清空監控和診斷工具
`postgres_get_av_diag()` 函數目前可在下列 RDS for PostgreSQL 版本中使用：
+ 17.2 版和更新的 17 版本
+ 16.7 版和更新的 16 版本
+ 15.11 版和更新的 15 版本
+ 14.16 版和更新的 14 版本
+ 13.19 版和更新的 13 版本
若要使用 `postgres_get_av_diag()`，請建立 `rds_tools` 延伸模組。
```
postgres=> CREATE EXTENSION rds_tools ;
CREATE EXTENSION
```
確認已安裝延伸模組。
```
postgres=> \dx rds_tools
List of installed extensions
Name | Version | Schema | Description
----------+---------+-----------+----------------------------------------------------------
rds_tools | 1.8 | rds_tools | miscellaneous administrative functions for RDS PostgreSQL
1 row
```
確認已建立函數。
```
postgres=> SELECT
proname function_name,
pronamespace::regnamespace function_schema,
proowner::regrole function_owner
FROM
pg_proc
WHERE
proname = 'postgres_get_av_diag';
function_name | function_schema | function_owner
----------------------+-----------------+----------------
postgres_get_av_diag | rds_tools | rds_superuser
(1 row)
```
# RDS for PostgreSQL 中 postgres\$1get\$1av\$1diag() 的函數
`postgres_get_av_diag()` 函數會擷取在 RDS for PostgreSQL 資料庫中封鎖或落後之自動清空程序的診斷資訊。查詢需要在具有最舊交易 ID 的資料庫中執行，以獲得準確的結果。如需使用具有最舊交易 ID 的資料庫的詳細資訊，請參閱[未連線至具有最舊交易 ID 存留期的資料庫](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.NOTICE.md)
```
SELECT
blocker,
DATABASE,
blocker_identifier,
wait_event,
TO_CHAR(autovacuum_lagging_by, 'FM9,999,999,999') AS autovacuum_lagging_by,
suggestion,
suggested_action
FROM (
SELECT
*
FROM
rds_tools.postgres_get_av_diag ()
ORDER BY
autovacuum_lagging_by DESC) q;
```
`postgres_get_av_diag()` 函數會傳回具有下列資訊的資料表。
**封鎖程式**
指定封鎖清空的資料庫活動類別。
+ [作用中陳述式](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Active_statement)
+ [交易閒置](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Idle_in_transaction)
+ [備妥交易](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Prepared_transaction)
+ [邏輯複寫槽](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Logical_replication_slot)
+ [僅供讀取複本和實體複寫槽](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Read_replicas)
+ [具有串流複寫的僅供讀取複本](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Read_replicas)
+ [暫時資料表](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Temporary_tables)
**資料庫**
指定適用且支援的資料庫名稱。這是活動正在進行並封鎖或將封鎖自動清空的資料庫。這是您需要連線並採取動作的資料庫。
**blocker\$1identifier**
指定封鎖或將封鎖自動清空之活動的識別符。識別符可以是程序 ID 以及 SQL 陳述式、備妥交易、僅供讀取複本的 IP 位址，以及複寫槽的名稱，可以是邏輯或實體。
**wait\$1event**
指定封鎖工作階段的[等待事件](PostgreSQL.Tuning.md)，並適用於下列封鎖程式：
+ 作用中陳述式
+ 交易閒置
**autovacum\$1lagging\$1by**
指定自動清空在每個類別的待處理項目工作中落後的交易數量。
**建議**
指定解決封鎖程式的建議。這些指示包括適用時活動所在的資料庫名稱、適用時工作階段的程序 ID (PID)，以及要採取的動作。
**suggested\$1action**
建議解決封鎖程式所需採取的動作。
# 解決 RDS for PostgreSQL 中可識別的清空封鎖程式
自動清空會執行積極的清空，並將交易 IDs 的存留期降至低於 RDS 執行個體 `autovacuum_freeze_max_age` 參數指定的閾值。您可以使用 Amazon CloudWatch 指標來追蹤此存留期。`MaximumUsedTransactionIDs`
若要尋找 Amazon RDS 執行個體的 `autovacuum_freeze_max_age` 設定 (預設為 2 億筆交易 ID)，您可以使用下列查詢：
```
SELECT
TO_CHAR(setting::bigint, 'FM9,999,999,999') autovacuum_freeze_max_age
FROM
pg_settings
WHERE
name = 'autovacuum_freeze_max_age';
```
請注意，只有在存留期超過 Amazon RDS 的 5 億筆交易 ID 的[彈性自動清空](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum.AdaptiveAutoVacuuming)閾值時，`postgres_get_av_diag()` 才會檢查積極的清空封鎖程式。若要讓 `postgres_get_av_diag()` 偵測封鎖程式，封鎖程式必須至少有 5 億筆舊的交易。
`postgres_get_av_diag()` 函數會識別下列類型的封鎖程式：
**Topics**
+ [作用中陳述式](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Active_statement)
+ [交易閒置](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Idle_in_transaction)
+ [備妥交易](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Prepared_transaction)
+ [邏輯複寫槽](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Logical_replication_slot)
+ [僅供讀取複本](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Read_replicas)
+ [暫時資料表](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Temporary_tables)
## 作用中陳述式
在 PostgreSQL 中，作用中陳述式是目前正在由資料庫執行的 SQL 陳述式。這包括查詢、交易或任何進行中的操作。透過 `pg_stat_activity` 監控時，狀態欄會指出具有對應 PID 的程序處於作用中狀態。
該 `postgres_get_av_diag()` 函數會在識別為作用中陳述式的陳述式時顯示類似下列的輸出。
```
blocker | Active statement
database | my_database
blocker_identifier | SELECT pg_sleep(20000);
wait_event | Timeout:PgSleep
autovacuum_lagging_by | 568,600,871
suggestion | Connect to database "my_database", review carefully and you may consider terminating the process using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action | {"SELECT pg_terminate_backend (29621);"}
```
**建議動作**
遵循 `suggestion` 欄中的指引，使用者可以連線到存在作用中陳述式的資料庫，並且如 `suggested_action` 欄中所指定，建議您仔細檢閱終止工作階段的選項。如果終止是安全的，您可以使用 `pg_terminate_backend()` 函數來終止工作階段。此動作可由管理員 (例如 RDS 主帳戶） 或具有必要`pg_terminate_backend()` 權限的使用者執行。
**警告**
終止的工作階段將復原 (`ROLLBACK`) 其所做的變更。根據您的需求，您可能想要重新執行陳述式。不過，建議只在自動清空程序完成其積極的清空操作之後才執行此操作。
## 交易閒置
交易陳述式的閒置是指已開啟明確交易 (例如透過發出 `BEGIN` 陳述式）、已執行一些工作，且現在正在等待用戶端透過發出 `COMMIT`、`ROLLBACK` 或 `END` (這會導致隱含 `COMMIT`) 傳遞更多工作或發出交易結束訊號的任何工作階段。
將 `idle in transaction` 陳述式識別為封鎖程式時，`postgres_get_av_diag()` 函數會顯示類似以下的輸出。
```
blocker | idle in transaction
database | my_database
blocker_identifier | INSERT INTO tt SELECT * FROM tt;
wait_event | Client:ClientRead
autovacuum_lagging_by | 1,237,201,759
suggestion | Connect to database "my_database", review carefully and you may consider terminating the process using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action | {"SELECT pg_terminate_backend (28438);"}
```
**建議動作**
如 `suggestion` 欄中所示，您可以連線到交易工作階段中存在閒置的資料庫，並使用 `pg_terminate_backend()` 函數終止工作階段。使用者可以是您的管理員 (RDS 主帳戶） 使用者或具有 `pg_terminate_backend()` 權限的使用者。
**警告**
終止的工作階段將復原 (`ROLLBACK`) 其所做的變更。根據您的需求，您可能想要重新執行陳述式。不過，建議只在自動清空程序完成其積極的清空操作之後才執行此操作。
## 備妥交易
PostgreSQL 允許屬於兩個階段遞交策略的交易，稱為[備妥交易](https://www.postgresql.org/docs/current/sql-prepare-transaction.html)。這些是透過將 `max_prepared_transactions` 參數設定為非零值來啟用。備妥交易旨在確保交易耐用性，即使在資料庫當機、重新啟動或用戶端中斷連線後仍然可用。與一般交易一樣，它們會獲指派交易 ID，並可能影響自動清空。如果保持備妥狀態，則自動清空無法執行凍結，並可能導致交易 ID 包圍。
當交易無限期準備而不由交易管理員解決時，它們會變成孤立的備妥交易。修正此問題的唯一方法是分別使用 `COMMIT PREPARED` 或 `ROLLBACK PREPARED` 命令遞交或轉返交易。
**注意**
請注意，在準備交易期間進行的備份在還原後仍會包含該交易。請參閱下列有關如何尋找和關閉此類交易的資訊。
該 `postgres_get_av_diag()` 函數會在識別為備妥交易的封鎖程式時顯示下列輸出。
```
blocker | Prepared transaction
database | my_database
blocker_identifier | myptx
wait_event | Not applicable
autovacuum_lagging_by | 1,805,802,632
suggestion | Connect to database "my_database" and consider either COMMIT or ROLLBACK the prepared transaction using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action | {"COMMIT PREPARED 'myptx';",[OR],"ROLLBACK PREPARED 'myptx';"}
```
**建議動作**
如建議欄中所述，連線至備妥交易所在的資料庫。根據 `suggested_action` 欄，仔細檢閱是否執行 `COMMIT` 或 `ROLLBACK`，以及相應動作。
若要監控一般備妥交易，PostgreSQL 提供稱為 `pg_prepared_xacts` 的目錄檢視。您可以使用下列查詢來尋找準備好的交易。
```
SELECT
gid,
prepared,
owner,
database,
transaction AS oldest_xmin
FROM
pg_prepared_xacts
ORDER BY
age(transaction) DESC;
```
## 邏輯複寫槽
複寫槽的目的是保留未使用的變更，直到這些變更複寫到目標伺服器為止。如需詳細資訊，請參閱 PostgreSQL 的[邏輯複寫](https://www.postgresql.org/docs/current/logical-replication.html)。
邏輯複寫槽有兩種類型。
**非作用中邏輯複寫槽**
複寫終止時，無法移除未使用的交易日誌，且複寫槽會變成非作用中。雖然訂閱者目前未使用非作用中的邏輯複寫槽，但其會保留在伺服器上，導致 WAL 檔案的保留，並防止移除舊的交易日誌。這可能會增加磁碟使用量，尤其是封鎖自動清空清除內部目錄資料表，因為系統必須保留 LSN 資訊以免遭到覆寫。如果未解決，這可能會導致目錄膨脹、效能降低，以及包圍清空的風險增加，進而可能導致交易停機時間。
**作用中但緩慢的邏輯複寫槽**
有時候，由於邏輯複寫的效能降低，移除目錄的無效元組會延遲。此複寫延遲會減緩更新 `catalog_xmin` 的速度，並可能導致目錄膨脹和包圍清空。
當 `postgres_get_av_diag()` 函數找到邏輯複寫插槽作為封鎖程式時，會顯示類似下列的輸出。
```
blocker | Logical replication slot
database | my_database
blocker_identifier | slot1
wait_event | Not applicable
autovacuum_lagging_by | 1,940,103,068
suggestion | Ensure replication is active and resolve any lag for the slot if active. If inactive, consider dropping it using the command in suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action | {"SELECT pg_drop_replication_slot('slot1') FROM pg_replication_slots WHERE active = 'f';"}
```
**建議動作**
若要解決此問題，請檢查複寫組態是否有可能終止套用程序之目標結構描述或資料的問題。最常見的原因如下：
+ 遺失資料欄
+ 不相容的資料類型
+ 資料不符
+ 遺失資料表
如果問題與基礎設施問題有關：
+ 網路問題 - [如何解決網路狀態不相容的 Amazon RDS 資料庫問題？](https://repost.aws/knowledge-center/rds-incompatible-network)
+ 由於下列原因，資料庫或資料庫執行個體無法使用：
+ 複本執行個體的用盡儲存體 - 檢閱 [Amazon RDS 資料庫執行個體用盡儲存體](https://repost.aws/knowledge-center/rds-out-of-storage)，以取得新增儲存體的相關資訊。
+ 不相容參數 - 檢閱[如何修正停滯在不相容參數狀態的 Amazon RDS 資料庫執行個體？](https://repost.aws/knowledge-center/rds-incompatible-parameters)以取得如何解決問題的詳細資訊。
如果您的執行個體不在 AWS 網路或 AWS EC2 上，請洽詢您的管理員，了解如何解決可用性或基礎設施相關問題。
**捨棄非作用中插槽**
**警告**
注意：捨棄複寫槽之前，請仔細確認它沒有進行中的複寫、處於非作用中狀態，而且處於無法復原的狀態。提早捨棄插槽可能會中斷複寫或導致資料遺失。
確認不再需要複寫槽後，請將其捨棄以允許自動清空繼續。條件 `active = 'f'` 可確保只會捨棄非作用中的插槽。
```
SELECT pg_drop_replication_slot('slot1') WHERE active ='f'
```
## 僅供讀取複本
為 [Amazon RDS 僅供讀取複本](USER_PostgreSQL.Replication.ReadReplicas.md)啟用 `hot_standby_feedback` 設定時，可防止主要資料庫上的自動清空移除在讀取複本上執行的查詢仍可能需要的無效資料列。這會影響所有類型的實體讀取複本，包括具有或沒有複寫插槽的複本。此行為是必要的，因為在待命複本上執行的查詢需要這些資料列在主要 上保持可用，以防止[查詢衝突](https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-CONFLICT)和取消。
**僅供讀取複本和實體複寫槽**
具有實體複寫槽的僅供讀取複本可大幅增強 RDS for PostgreSQL 中複寫的可靠性和穩定性。這些槽可確保主要資料庫會保留必要的預先寫入日誌檔案，直到複本處理它們為止，即使在網路中斷期間也能維持資料一致性。
從 RDS for PostgreSQL 第 14 版開始，所有複本都會使用複寫插槽。在舊版中，只有跨區域複本使用複寫槽。
當找到具有實體複寫槽作為封鎖程式的讀取複本時，`postgres_get_av_diag()` 函數會顯示類似以下的輸出。
```
blocker | Read replica with physical replication slot
database |
blocker_identifier | rds_us_west_2_db_xxxxxxxxxxxxxxxxxxxxx
wait_event | Not applicable
autovacuum_lagging_by | 554,080,689
suggestion | Run the following query on the replica "rds_us_west_2_db_xxxxxxxxxxxxxxxxxxxx" to find the long running query:
| SELECT * FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 757989377;
| Review carefully and you may consdier terminating the query on read replica using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide. + |
suggested_action | {"SELECT pg_terminate_backend(pid) FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 757989377;"," +
| [OR] +
| ","Disable hot_standby_feedback"," +
| [OR] +
| ","Delete the read replica if not needed"}
```
**具有串流複寫的僅供讀取複本**
Amazon RDS 允許在舊版中設定沒有實體複寫槽的僅供讀取複本，直到 13 版為止。這種方法允許主要資料庫更積極地回收 WAL 檔案以減少額外負荷，這在磁碟空間有限的環境中是有利的，並且可以容忍偶爾的 ReplicaLag。不過，如果沒有插槽，則待命必須保持同步，以避免缺少 WAL 檔案。Amazon RDS 使用封存的 WAL 檔案，協助複本在落後時趕上進度，但此程序需要仔細監控，而且速度可能很慢。
當 `postgres_get_av_diag()` 函數找到串流僅供讀取複本作為封鎖程式時，會顯示類似以下的輸出。
```
blocker | Read replica with streaming replication slot
database | Not applicable
blocker_identifier | xx.x.x.xxx/xx
wait_event | Not applicable
autovacuum_lagging_by | 610,146,760
suggestion | Run the following query on the replica "xx.x.x.xxx" to find the long running query: +
| SELECT * FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 348319343; +
| Review carefully and you may consdier terminating the query on read replica using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide. +
|
suggested_action | {"SELECT pg_terminate_backend(pid) FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 348319343;"," +
| [OR] +
| ","Disable hot_standby_feedback"," +
| [OR] +
| ","Delete the read replica if not needed"}
```
**建議動作**
如 `suggested_action` 欄中的建議，請仔細檢閱這些選項以解除封鎖自動清空。
+ **終止查詢** – 根據建議欄中的指引，您可以連線到僅限讀取複本，如 suggested\$1action 欄中所指定，建議您仔細檢閱終止工作階段的選項。如果終止被視為安全，您可以使用 `pg_terminate_backend()` 函數來終止工作階段。此動作可由管理員 (例如 RDS 主帳戶） 或具有必要 pg\$1terminate\$1backend() 權限的使用者執行。
您可以在僅限讀取複本上執行下列 SQL 命令，以終止導致主要資料庫上的清空無法清除舊資料列的查詢。`backend_xmin` 的值會在函數的輸出中報告：
```
SELECT
pg_terminate_backend(pid)
FROM
pg_catalog.pg_stat_activity
WHERE
backend_xmin::text::bigint = backend_xmin;
```
+ **停用熱待命回饋** – 如果參數造成嚴重的清空延遲，請考慮停用 `hot_standby_feedback` 參數。
`hot_standby_feedback` 參數允許僅供讀取複本通知主要資料庫其查詢活動，防止主要資料庫清空待命資料庫上使用的資料表或資料列。雖然這可確保待命資料庫上的查詢穩定性，但可能會大幅延遲主要資料庫上的清空。停用此功能可讓主要資料庫繼續進行清空，而無需等待待命資料庫趕上。不過，如果嘗試存取已由主要資料庫清空的資料列，這可能會導致查詢取消或待命資料庫失敗。
+ **如果不需要，請刪除僅供讀取複本** – 如果不再需要僅供讀取複本，您可以將其刪除。這將移除相關聯的複寫額外負荷，並允許主要資料庫回收交易日誌，而不會被複本保留。
## 暫時資料表
使用 `TEMPORARY` 關鍵字建立的[暫時資料表](https://www.postgresql.org/docs/current/sql-createtable.html)位於臨時結構描述中，例如 pg\$1temp\$1xxx，並且只能由建立它們的工作階段存取。暫時資料表會在工作階段結束時捨棄。不過，PostgreSQL 的自動清空程序看不到這些資料表，而且必須由建立它們的工作階段手動清空。嘗試從另一個工作階段清空暫時資料表沒有效果。
在異常情況下，暫時資料表存在，而沒有作用中的工作階段擁有它。如果擁有工作階段由於嚴重損毀、網路問題或類似事件意外結束，則可能無法清除暫時資料表，將其保留為「孤立」資料表。當 PostgreSQL 自動清空程序偵測到孤立的暫時資料表時，它會記錄下列訊息：
```
LOG: autovacuum: found orphan temp table \"%s\".\"%s\" in database \"%s\"
```
`postgres_get_av_diag()` 函數將暫存資料表識別為封鎖程式時，會顯示類似下列的輸出。為了讓函數正確顯示與暫時資料表相關的輸出，它需要在存在這些資料表的相同資料庫中執行。
```
blocker | Temporary table
database | my_database
blocker_identifier | pg_temp_14.ttemp
wait_event | Not applicable
autovacuum_lagging_by | 1,805,802,632
suggestion | Connect to database "my_database". Review carefully, you may consider dropping temporary table using command in suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action | {"DROP TABLE ttemp;"}
```
**建議動作**
遵循輸出 `suggestion` 欄中提供的指示，以識別和移除防止自動清空執行的暫時資料表。使用以下命令捨棄 `postgres_get_av_diag()` 報告的暫時資料表。根據 `postgres_get_av_diag()` 函數提供的輸出取代資料表名稱。
```
DROP TABLE my_temp_schema.my_temp_table;
```
下列查詢可用來識別暫時資料表：
```
SELECT
oid,
relname,
relnamespace::regnamespace,
age(relfrozenxid)
FROM
pg_class
WHERE
relpersistence = 't'
ORDER BY
age(relfrozenxid) DESC;
```
# 解決 RDS for PostgreSQL 中無法識別的清空封鎖程式
本節會探索其他可能阻止清空進行的原因。`postgres_get_av_diag()` 函數目前無法直接識別這些問題。
**Topics**
+ [無效的頁面](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Invalid_pages)
+ [索引不一致](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Index_inconsistency)
+ [異常高的交易速率](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.High_transaction_rate)
## 無效的頁面
當 PostgreSQL 在存取頁面時偵測到頁面檢查總和不相符時，會發生無效的頁面錯誤。內容無法讀取，防止自動清空凍結元組。這會有效停止清除程序。下列錯誤會寫入 PostgreSQL 的日誌：
```
WARNING: page verification failed, calculated checksum YYYYY but expected XXXX
ERROR: invalid page in block ZZZZZ of relation base/XXXXX/XXXXX
CONTEXT: automatic vacuum of table myschema.mytable
```
**判斷物件類型**
```
ERROR: invalid page in block 4305910 of relation base/16403/186752608
WARNING: page verification failed, calculated checksum 50065 but expected 60033
```
從錯誤訊息中，路徑 `base/16403/186752608` 會提供下列資訊：
+ "base" 是 PostgreSQL 資料目錄下的目錄名稱。
+ "16403" 是資料庫 OID，您可以在 `pg_database` 系統目錄中查詢。
+ "186752608" 是 `relfilenode`，可用來查詢 `pg_class` 系統目錄中的結構描述和物件名稱。
透過檢查受影響資料庫中下列查詢的輸出，您可以判斷物件類型。下列查詢會擷取 OID 186752608 的物件資訊。將 OID 取代為與您所遇到錯誤相關的 OID。
```
SELECT
relname AS object_name,
relkind AS object_type,
nspname AS schema_name
FROM
pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE
c.oid = 186752608;
```
如需詳細資訊，請參閱 PostgreSQL 文件 [https://www.postgresql.org/docs/current/catalog-pg-class.html](https://www.postgresql.org/docs/current/catalog-pg-class.html)，以了解所有支援的物件類型，如 `pg_class` 中的 `relkind` 欄所述。
**指引**
此問題最有效的解決方案取決於特定 Amazon RDS 執行個體的組態，以及受不一致頁面影響的資料類型。
**如果物件類型是索引：**
建議重建索引。
+ **使用 `CONCURRENTLY` 選項** – 在 PostgreSQL 第 12 版之前，重建索引需要獨佔資料表鎖定，以限制對資料表的存取。使用 PostgreSQL 第 12 版和更新版本時，`CONCURRENTLY` 選項允許資料列層級鎖定，以大幅改善資料表的可用性。以下是命令：
```
REINDEX INDEX ix_name CONCURRENTLY;
```
雖然 `CONCURRENTLY` 干擾性較低，但在忙碌的資料表上可能會變慢。如果可能，請考慮在低流量期間建立索引。
如需詳細資訊，請參閱 PostgreSQL [REINDEX](https://www.postgresql.org/docs/current/sql-reindex.html) 文件。
+ **使用 `INDEX_CLEANUP FALSE` 選項** – 如果索引較大且估計需要大量時間才能完成，您可以在排除索引的同時執行手動 `VACUUM FREEZE` 來解除封鎖自動清空。此功能可在 PostgreSQL 第 12 版及更新版本中使用。
略過索引可讓您略過不一致索引的清空程序，並減輕包圍問題。不過，這無法解決基本的無效頁面問題。若要完全處理和解決無效的頁面問題，您仍然需要重建索引。
**如果物件類型是具體化視觀表：**
如果具體化視觀表發生無效頁面錯誤，請登入受影響的資料庫並重新整理以解決無效頁面：
重新整理具體化視觀表：
```
REFRESH MATERIALIZED VIEW schema_name.materialized_view_name;
```
如果重新整理失敗，請嘗試重新建立：
```
DROP MATERIALIZED VIEW schema_name.materialized_view_name;
CREATE MATERIALIZED VIEW schema_name.materialized_view_name AS query;
```
重新整理或重新建立具體化視觀表會將其還原，而不會影響基礎資料表資料。
**對於所有其他物件類型：**
對於所有其他物件類型，請聯絡 AWS 支援。
## 索引不一致
邏輯上不一致的索引會防止自動清空進行。在索引的清空階段或 SQL 陳述式存取索引時，系統會記錄下列錯誤或類似錯誤。
```
ERROR: right sibling's left-link doesn't match:block 5 links to 10 instead of expected 2 in index ix_name
```
```
ERROR: failed to re-find parent key in index "XXXXXXXXXX" for deletion target page XXX
CONTEXT: while vacuuming index index_name of relation schema.table
```
**指引**
在手動 `VACUUM FREEZE` 上使用 `INDEX_CLEANUP` 重建索引或略過索引。如需如何重建索引的資訊，請參閱[如果物件類型是索引](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Invalid_pages)。
+ **使用 CONCURRENTLY 選項** – 在 PostgreSQL 第 12 版之前，重建索引需要獨佔資料表鎖定，以限制對資料表的存取。透過 PostgreSQL 第 12 版和更新版本，CONCURRENTLY 選項允許資料列層級鎖定，以大幅改善資料表的可用性。以下是命令：
```
REINDEX INDEX ix_name CONCURRENTLY;
```
雖然 CONCURRENTLY 干擾性較低，但在忙碌的資料表上可能會變慢。如果可能，請考慮在低流量期間建立索引。如需詳細資訊，請參閱 *PostgreSQL* 文件中的 [REINDEX](https://www.postgresql.org/docs/current/sql-reindex.html)。
+ **使用 INDEX\$1CLEANUP FALSE 選項** – 如果索引較大且估計需要大量時間才能完成，您可以在排除索引的同時執行手動 VACUUM FREEZE 來解除封鎖自動清空。此功能可在 PostgreSQL 第 12 版及更新版本中使用。
略過索引可讓您略過不一致索引的清空程序，並減輕包圍問題。不過，這無法解決基本的無效頁面問題。若要完全處理和解決無效的頁面問題，您仍然需要重建索引。
## 異常高的交易速率
在 PostgreSQL 中，高交易速率可能會大幅影響自動清空的效能，導致清除無效元組的速度變慢，並提高交易 ID 包圍的風險。您可以透過測量兩個時段之間的 `max(age(datfrozenxid))` 差異來監控交易速率，通常是每秒。此外，您可以使用 RDS Performance Insights 的下列計數器指標來測量交易速率 (xact\$1commit 和 xact\$1rollback 的總和)，這是交易的總數。
| 計數器 | 類型 | 單位 | 指標 |
| --- | --- | --- | --- |
| xact\$1commit | 交易 | 每秒遞交數 | db.Transactions.xact\$1commit |
| xact\$1rollback | 交易 | 每秒轉返數 | db.Transactions.xact\$1rollback |
快速增加表示交易負載很高，可能會自動清空不堪負荷，導致膨脹、鎖定爭用和潛在的效能問題。這可能會在幾個方面對自動清空程序產生負面影響：
+ **資料表活動：**正在清空的特定資料表可能遇到大量交易，因而導致延遲。
+ **系統資源**整體系統可能會超載，導致自動清空難以存取必要的資源以有效率地運作。
請考慮下列策略，以允許自動清空更有效地運作並跟上其任務：
1. 如果可能，請降低交易速率。考慮在可行的情況下批次處理或分組類似的交易。
1. 以經常更新的資料表為目標，在離峰時段內每夜、每週或每兩週手動 `VACUUM FREEZE` 操作一次。
1. 考慮擴展執行個體類別以配置更多系統資源，以處理高交易量和自動清空。
# 解決 RDS for PostgreSQL 中的清空效能問題
本節討論通常會導致降低清空效能的因素，以及如何解決這些問題。
**Topics**
+ [清空大型索引](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Large_indexes)
+ [要清空的資料表或資料庫過多](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Multiple_tables)
+ [積極清空 (以防止包圍) 正在執行](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Aggressive_vacuum)
## 清空大型索引
VACUUM 會執行循序階段：初始化、堆積掃描、索引和堆積清空、索引清除、堆積截斷和最終清除。在堆積掃描期間，程序會刪除頁面、重組並凍結頁面。完成堆積掃描後，VACUUM 會清除索引、將空白頁面傳回至作業系統，並執行最終清除任務，例如清空可用空間貼圖和更新統計資料。
當 `maintenance_work_mem` (或 `autovacuum_work_mem`) 不足以處理索引時，索引清空可能需要多次通過。在 PostgreSQL 16 和更早版本中，用於儲存無效元組的 ID 1 GB 記憶體限制通常會強制在大型索引上多次通過。PostgreSQL 17 推出 `TidStore`，可動態配置記憶體，而不是使用單一配置陣列。這可消除 1 GB 限制、更有效率地使用記憶體，並減少每個索引進行多次索引掃描的需求。
如果可用的記憶體無法同時容納整個索引處理，大型索引仍可能需要 PostgreSQL 17 中的多次通過。一般而言，較大的索引包含更多需要多次通過的無效元組。
**偵測緩慢清空操作**
`postgres_get_av_diag()` 函數可以偵測清空操作何時因為記憶體不足而緩慢執行。如需該功能的詳細資訊，請參閱[在 RDS for PostgreSQL 中安裝自動清空監控和診斷工具](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Installation.md)。
當可用的記憶體不足以在單次通過中完成索引清空時， `postgres_get_av_diag()` 函數會發出下列通知。
**`rds_tools` 1.8**
```
NOTICE: Your database is currently running aggressive vacuum to prevent wraparound and it might be slow.
```
```
NOTICE: The current setting of autovacuum_work_mem is "XXX" and might not be sufficient. Consider increasing the setting, and if necessary, scaling up the Amazon RDS instance class for more memory.
Additionally, review the possibility of manual vacuum with exclusion of indexes using (VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) table_name;).
```
**`rds_tools` 1.9**
```
NOTICE: Your database is currently running aggressive vacuum to prevent wraparound and it might be slow.
```
```
NOTICE: The current setting of autovacuum_work_mem is XX might not be sufficient. Consider increasing the setting to XXX, and if necessary, scaling up the RDS instance class for more
memory. The suggested value is an estimate based on the current number of dead tuples for the table being vacuumed, which might not fully reflect the latest state. Additionally, review the possibility of manual
vacuum with exclusion of indexes using (VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) table_name;). For more information, see
[Working with PostgreSQL autovacuum in the Amazon Amazon RDS User Guide](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum.html)
.
```
**注意**
`postgres_get_av_diag()` 函數依賴 `pg_stat_all_tables.n_dead_tup` 來估計索引清空所需的記憶體量。
當 `postgres_get_av_diag()` 函數識別由於 `autovacuum_work_mem` 不足而需要多個索引掃描的緩慢清空操作時，會產生下列訊息：
```
NOTICE: Your vacuum is performing multiple index scans due to insufficient autovacuum_work_mem:XXX for index vacuuming.
For more information, see [Working with PostgreSQL autovacuum in the Amazon Amazon RDS User Guide](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum.html).
```
**指引**
您可以使用手動 `VACUUM FREEZE` 來套用下列解決方法，以加速凍結資料表。
**增加清空的記憶體**
如 `postgres_get_av_diag()` 函數所建議，建議增加 `autovacuum_work_mem` 參數以解決執行個體層級的潛在記憶體限制。雖然 `autovacuum_work_mem` 是動態參數，但請務必注意，若要讓新的記憶體設定生效，自動清空精靈需要重新啟動其工作者。若要完成此操作：
1. 確認新的設定已就緒。
1. 終止目前正在執行自動清空的程序。
此方法可確保調整後的記憶體配置套用至新的自動清空操作。
如需更立即的結果，請考慮在工作階段中手動執行增加 `maintenance_work_mem` 設定的 `VACUUM FREEZE` 操作：
```
SET maintenance_work_mem TO '1GB';
VACUUM FREEZE VERBOSE table_name;
```
如果您使用 Amazon RDS 並發現需要額外的記憶體以支援 `maintenance_work_mem` 或 `autovacuum_work_mem` 的更高值，請考慮升級至具有更多記憶體的執行個體類別。這可提供必要的資源來增強手動和自動清空操作，進而改善整體清空和資料庫效能。
**停用 INDEX\$1CLEANUP**
PostgreSQL 第 12 版和更新版本中的手動 `VACUUM` 允許略過索引清除階段，而 PostgreSQL 第 14 版和更新版本中的緊急自動清空會根據 [https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-VACUUM-FAILSAFE-AGE](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-VACUUM-FAILSAFE-AGE) 參數自動執行此操作。
**警告**
略過索引清除可能會導致索引膨脹，並對查詢效能產生負面影響。若要緩解這種情況，請考慮在維護時段重新編製索引或清空受影響的索引。
如需處理大型索引的其他指引，請參閱 [使用大型索引管理自動清空](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.LargeIndexes.md) 上的文件。
**平行索引清空**
從 PostgreSQL 13 開始，根據預設可以使用手動 `VACUUM` 平行清空和清理索引，並為每個索引指派一個清空工作者程序。不過，對於 PostgreSQL 判斷清空操作是否符合平行執行的資格，必須符合特定條件：
+ 必須至少有兩個索引。
+ 應將 `max_parallel_maintenance_workers` 參數設定為至少 2。
+ 索引大小必須超過 `min_parallel_index_scan_size` 限制，預設為 512KB。
您可以根據 Amazon RDS 執行個體上可用的 vCPU 數目和資料表上的索引數目來調整 `max_parallel_maintenance_workers` 設定，以最佳化清空周轉時間。
如需詳細資訊，請參閱 [Amazon RDS for PostgreSQL 和 Amazon Aurora PostgreSQL 中的平行清空](https://aws.amazon.com/blogs/database/parallel-vacuuming-in-amazon-rds-for-postgresql-and-amazon-aurora-postgresql/)。
## 要清空的資料表或資料庫過多
如 PostgreSQL 的[自動清空精靈](https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM')文件所述，自動清空精靈會透過多個程序運作。這包括持久性自動清空啟動器，負責為系統中的每個資料庫啟動自動清空工作者程序。啟動器會排程這些工作者，每個資料庫大約每 `autovacuum_naptime` 秒啟動一次。
使用「N」個資料庫時，新工作者大約每 [`autovacuum_naptime`/N 秒] 開始一次。不過，並行工作者的總數會受到 `autovacuum_max_workers` 設定的限制。如果需要清空的資料庫或資料表數量超過此限制，則在工作者一變成可用時，就會立即處理下一個資料庫或資料表。
當許多大型資料表或資料庫需要同時清空時，所有可用的自動清空工作者可能會長時間佔用，進而延遲其他資料表和資料庫的維護。在交易速率較高的環境中，此瓶頸可能會快速提升，並可能導致 Amazon RDS 執行個體中的包圍清空問題。
當 `postgres_get_av_diag()` 偵測到大量資料表或資料庫時，會提供下列建議：
```
NOTICE: Your database is currently running aggressive vacuum to prevent wraparound and it might be slow.
```
```
NOTICE: The current setting of autovacuum_max_workers:3 might not be sufficient. Consider increasing the setting and, if necessary, consider scaling up the Amazon RDS instance class for more workers.
```
**指引**
**增加 autovacuum\$1max\$1workers**
為了加速清空，建議您調整 `autovacuum_max_workers` 參數，以允許更多並行自動清空工作者。如果效能瓶頸持續存在，請考慮將 Amazon RDS 執行個體擴展到具有更多 vCPU 的類別，這可以進一步改善平行處理功能。
## 積極清空 (以防止包圍) 正在執行
PostgreSQL 中資料庫 (MaximumUsedTransactionIDs) 的存留期只會在積極清空 (防止包圍) 成功完成時減少。在此清空完成之前，存留期會根據交易速率繼續增加。
當 `postgres_get_av_diag()` 函數偵測到積極的清空時，會產生下列 `NOTICE`。不過，它只會在清空作用中至少兩分鐘後觸發此輸出。
```
NOTICE: Your database is currently running aggressive vacuum to prevent wraparound, monitor autovacuum performance.
```
如需積極清空的詳細資訊，請參閱[積極清空已執行時](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.NOTICE.md)。
您可以使用下列查詢來確認積極清空是否正在進行中：
```
SELECT
a.xact_start AS start_time,
v.datname "database",
a.query,
a.wait_event,
v.pid,
v.phase,
v.relid::regclass,
pg_size_pretty(pg_relation_size(v.relid)) AS heap_size,
(
SELECT
string_agg(pg_size_pretty(pg_relation_size(i.indexrelid)) || ':' || i.indexrelid::regclass || chr(10), ', ')
FROM
pg_index i
WHERE
i.indrelid = v.relid
) AS index_sizes,
trunc(v.heap_blks_scanned * 100 / NULLIF(v.heap_blks_total, 0)) AS step1_scan_pct,
v.index_vacuum_count || '/' || (
SELECT
count(*)
FROM
pg_index i
WHERE
i.indrelid = v.relid
) AS step2_vacuum_indexes,
trunc(v.heap_blks_vacuumed * 100 / NULLIF(v.heap_blks_total, 0)) AS step3_vacuum_pct,
age(CURRENT_TIMESTAMP, a.xact_start) AS total_time_spent_sofar
FROM
pg_stat_activity a
INNER JOIN pg_stat_progress_vacuum v ON v.pid = a.pid;
```
您可以透過檢查輸出中的查詢欄來判斷是否為積極的清空 (以防止包圍)。片語「防止包圍」表示它是積極的清空。
```
query | autovacuum: VACUUM public.t3 (to prevent wraparound)
```
例如，假設您有交易存留期為 10 億的封鎖程式和需要積極清空的資料表，以防止在相同的交易存留期進行包圍。此外，有另一個交易存留期為 7.5 億的封鎖程式。在交易存留期 10 億時清除封鎖程式後，交易存留期不會立即降至 7.5 億。它將保持很高，直到需要積極清空的資料表或任何存留期超過 7.5 億的交易完成為止。在此期間，PostgreSQL 叢集的交易存留期將繼續增加。清空程序完成後，交易存留期將降至 7.5 億，但會再次開始增加，直到進一步清空完成為止。只要這些條件持續存在，此週期就會繼續，直到交易存留期最終降至由 `autovacuum_freeze_max_age` 所指定為 Amazon RDS 執行個體設定的層級為止。
# RDS for PostgreSQL 中的通知訊息說明
`postgres_get_av_diag()` 函數提供下列通知訊息：
**當存留期尚未達到監控閾值時**
用於 `postgres_get_av_diag()` 識別封鎖程式的監控閾值預設為 5 億筆交易。如果 `postgres_get_av_diag()`產生下列通知，則表示交易存留期尚未達到此閾值。
```
NOTICE: postgres_get_av_diag() checks for blockers that prevent aggressive vacuums only, it does so only after exceeding dvb_threshold which is 500,000,000 and age of this PostgreSQL cluster is currently at 2.
```
**未連線至具有最舊交易 ID 存留期的資料庫**
`postgres_get_av_diag()` 函數會在連接至具有最舊交易 ID 存留期的資料庫時，提供最準確的輸出。`postgres_get_av_diag()` 所報告之交易 ID 存留期最早的資料庫，將與您案例中的「my\$1database」不同。如果您未連線到正確的資料庫，會產生下列通知：
```
NOTICE: You are not connected to the database with the age of oldest transaction ID. Connect to my_database database and run postgres_get_av_diag() for accurate reporting.
```
連線具有最舊交易存留期的資料庫非常重要，原因如下：
+ **識別暫時資料表封鎖程式：**由於暫時資料表的中繼資料專屬於每個資料庫，因此它們通常會在建立它們的資料庫中找到。不過，如果暫存資料表剛好是最熱門的封鎖程式，並且位於具有最舊交易的資料庫中，則可能會誤導。連線到正確的資料庫可確保正確識別暫時資料表封鎖程式。
+ **診斷緩慢清空：**索引中繼資料和資料表計數資訊是資料庫特定的，且為診斷緩慢清空問題的必要項目。
**具有依存留期最舊交易的資料庫位於 rdsadmin 或 template0 資料庫**
在某些情況下，`rdsadmin`或 `template0` 資料庫可能會識別為具有最舊交易 ID 存留期的資料庫。如果發生這種情況，`postgres_get_av_diag()` 會發出下列通知：
```
NOTICE: The database with the age of oldest transaction ID is rdsadmin or template0, reach out to support if the reported blocker is in rdsadmin or template0.
```
確認列出的封鎖程式不是來自這兩個資料庫。如果報告封鎖程式出現在 `rdsadmin`或 `template0` 中，請聯絡支援，因為這些資料庫無法供使用者存取且需要介入。
`rdsadmin` 或 `template0` 資料庫不太可能包含最熱門的封鎖程式。
**當積極清空已在執行時**
`postgres_get_av_diag()` 函數旨在報告積極清空程序何時執行，但只會在清空作用中至少 1 分鐘後觸發此輸出。此刻意延遲有助於降低誤報的機會。透過等待，函數可確保僅報告有效且重要的清空，進而更準確且可靠地監控清空活動。
當 `postgres_get_av_diag()` 函數偵測到一或多個進行中的積極清空時，會產生下列通知。
```
NOTICE: Your database is currently running aggressive vacuum to prevent wraparound, monitor autovacuum performance.
```
如通知所示，繼續監控清空的效能。如需積極清空的詳細資訊，請參閱 [積極清空 (以防止包圍) 正在執行](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Performance.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Aggressive_vacuum)
**當自動清空關閉時**
如果資料庫執行個體上已停用自動清空，`postgres_get_av_diag()` 函數會產生下列通知：
```
NOTICE: Autovacuum is OFF, we strongly recommend to enable it, no restart is necessary.
```
自動清空是 RDS for PostgreSQL 資料庫執行個體的重要功能，可確保順暢的資料庫操作。它會自動移除舊的資料列版本、回收儲存空間，並防止資料表膨脹，有助於保持資料表和索引的效率，以獲得最佳效能。此外，它可以防止交易 ID 包圍，這會停止 Amazon RDS 執行個體上的交易。停用自動清空可能會導致資料庫效能和穩定性的長期下降。我們建議您隨時將其保留。如需詳細資訊，請參閱[了解 RDS for PostgreSQL 環境中的自動清空](https://aws.amazon.com/blogs/database/understanding-autovacuum-in-amazon-rds-for-postgresql-environments/)。
關閉自動清空不會停止積極的清空。這些仍會在您的資料表達到 `autovacuum_freeze_max_age` 閾值時發生。
**剩餘的交易數量非常低**
當包圍清空即將關閉時，`postgres_get_av_diag()` 函數會產生下列通知。當您的 Amazon RDS 執行個體有 1 億筆交易且不可能拒絕新交易時，就會發出此通知。
```
WARNING: Number of transactions remaining is critically low, resolve issues with autovacuum or perform manual VACUUM FREEZE before your instance stops accepting transactions.
```
您需要立即採取動作，以避免資料庫停機時間。您應該密切監控您的清空操作，並考慮在受影響的資料庫上手動啟動 `VACUUM FREEZE`，以防止交易失敗。