本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。
# Amazon RDS for PostgreSQL 的常用 DBA 任務
資料庫管理員 (DBA) 在管理 Amazon RDS for PostgreSQL 資料庫執行個體時執行各種任務。若您是已經熟悉 PostgreSQL 的 DBA，則需要了解在硬體上執行 PostgreSQL 和 RDS for PostgreSQL 之間的一些重要差異。例如，由於 Amazon RDS 為受管服務，因此不允許 Shell 存取資料庫執行個體。這表示您無法直接存取 `pg_hba.conf` 和其他組態檔。對於 RDS for PostgreSQL，會對與 RDS for PostgreSQL 資料庫執行個體相關聯的自訂資料庫參數群組，進行通常對內部部署執行個體的 PostgreSQL 組態檔所做的變更。如需詳細資訊，請參閱[Amazon RDS 的參數群組](USER_WorkingWithParamGroups.md)。
您也無法以您對內部部署 PostgreSQL 執行個體所做的相同方式存取日誌檔。如要進一步了解記錄，請參閱 [ RDS for PostgreSQL 資料庫日誌檔案](USER_LogAccess.Concepts.PostgreSQL.md)。
如同另一個範例，您無法存取 PostgreSQL `superuser` 帳戶。在 RDS for PostgreSQL 上，`rds_superuser` 角色是最具特權的角色，且其在設定時間授予 `postgres`。無論您是熟悉於內部部署使用 PostgreSQL 還是對 RDS for PostgreSQL 完全陌生，我們都建議您了解 `rds_superuser` 角色，及如何使用角色、使用者、群組和許可權限。如需詳細資訊，請參閱[了解 PostgreSQL 角色和許可](Appendix.PostgreSQL.CommonDBATasks.Roles.md)。
下文將介紹 RDS for PostgreSQL 的一些常用 DBA 任務。
**Topics**
+ [
# RDS for PostgreSQL 支援的定序
](PostgreSQL-Collations.md)
+ [
# 了解 PostgreSQL 角色和許可
](Appendix.PostgreSQL.CommonDBATasks.Roles.md)
+ [
# PostgreSQL 中的無效連線處理
](Appendix.PostgreSQL.CommonDBATasks.DeadConnectionHandling.md)
+ [
# 在 Amazon RDS for PostgreSQL 上使用 PostgreSQL 自動清空
](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.md)
+ [
# 在 Amazon RDS for PostgreSQL 中管理高物件計數
](PostgreSQL.HighObjectCount.md)
+ [
# 在 Amazon RDS for PostgreSQL 中管理 TOAST OID 爭用
](Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.md)
+ [
## 搭配使用 RDS for PostgreSQL 支援的記錄機制
](#Appendix.PostgreSQL.CommonDBATasks.Auditing)
+ [
# 使用 PostgreSQL 管理暫存檔案
](PostgreSQL.ManagingTempFiles.md)
+ [
## 使用 pgBadger 進行 PostgreSQL 的日誌分析
](#Appendix.PostgreSQL.CommonDBATasks.Badger)
+ [
## 使用 PgSNAPPER 監控 PostgreSQL
](#Appendix.PostgreSQL.CommonDBATasks.Snapper)
+ [
# 在 RDS for PostgreSQL 中管理自訂轉換
](PostgreSQL.CustomCasts.md)
+ [
# RDS for PostgreSQL 中平行查詢的最佳實務
](PostgreSQL.ParallelQueries.md)
+ [
# 在 RDS for PostgreSQL 資料庫執行個體上搭配使用參數
](Appendix.PostgreSQL.CommonDBATasks.Parameters.md)
# RDS for PostgreSQL 支援的定序
定序是一組規則，用來決定如何排序和比較存放在資料庫中的字元字串。定序在電腦系統中扮演著基本角色，並包含為作業系統的一部分。隨著新字元加入至語言或排序規則變更時，定序也會變更。
定序程式庫會定義定序的特定規則和演算法。PostgreSQL 內使用的最熱門定序程式庫是 GNU C (glibc)和 Unicode 國際化元件 (ICU)。依預設，RDS for PostgreSQL 會使用 glibc 定序，其中包含多位元組字元序列的 Unicode 字元排序順序。
當您在 RD for PostgreSQL 中建立新的資料庫執行個體 時，它會檢查作業系統是否有可用的定序。`CREATE DATABASE` 命令 `LC_COLLATE` 和 `LC_CTYPE` 的 PostgreSQL 參數用來指定一個定序，其作為該資料庫中的預設定序。或者，您也可以使用 `CREATE DATABASE` 中的 `LOCALE` 參數來設定這些參數。這會決定資料庫中字元字串的預設定序，以及將字元分類為字母、數字或符號的規則。您也可以選擇要在資料欄、索引或查詢上使用的定序。
RDS for PostgreSQL 取決於作業系統中的 glibc 程式庫是否支援定序。RDS for PostgreSQL 執行個體會定期使用最新版本的作業系統進行更新。這些更新有時會包含較新版本的 glibc 程式庫。較新版本的 glibc 很少會變更某些字元的排序順序或定序，這可能導致資料以不同的方式排序或產生無效的索引項目。如果您在更新期間發現定序的排序順序問題，您可能需要重建索引。
為了 glibc 更新可能造成的影響，RDS for PostgreSQL 現在包含獨立的預設定序程式庫。此定序程式庫可在 RDS for PostgreSQL 14.6、13.9、12.13、11.18、10.23 和較新的次要版本中使用。它與 glibc 2.26-59.amzn2 相容，並提供排序順序穩定性以防止出現不正確的查詢結果。
# 了解 PostgreSQL 角色和許可
當您使用 RDS for PostgreSQL 資料庫執行個體時 AWS 管理主控台，會同時建立管理員帳戶。依預設，其名稱為 `postgres`，如下列螢幕擷取畫面所示：
![\[Create database (建立資料庫) 頁面中的憑證預設登入身分為 postgres。\]](http://docs.aws.amazon.com/zh_tw/AmazonRDS/latest/UserGuide/images/default-login-identity-apg-rpg.png)
您可以選擇其他名稱，而不是接受預設值 (`postgres`)。若是如此，則選擇的名稱必須以字母開頭，且介於 1 到 16 個英數字元之間。為簡單起見，我們在本指南中使用主要使用者帳戶的預設值 (`postgres`)，來指稱主要使用者帳戶。
如果您使用 `create-db-instance` AWS CLI 而非 AWS 管理主控台，您可以在 命令中使用 `master-username` 參數來傳遞名稱，以建立名稱。如需詳細資訊，請參閱 [建立 Amazon RDS 資料庫執行個體](USER_CreateDBInstance.md)。
無論您是使用 AWS 管理主控台、 AWS CLI或 Amazon RDS API，還是使用預設`postgres`名稱或選擇不同的名稱，這個第一個資料庫使用者帳戶都是 `rds_superuser`群組的成員，並且具有 `rds_superuser` 權限。
**Topics**
+ [
# 了解 rds\$1superuser 角色
](Appendix.PostgreSQL.CommonDBATasks.Roles.rds_superuser.md)
+ [
# 控制使用者對 PostgreSQL 資料庫的存取
](Appendix.PostgreSQL.CommonDBATasks.Access.md)
+ [
# 委派和控制使用者密碼管理
](Appendix.PostgreSQL.CommonDBATasks.RestrictPasswordMgmt.md)
+ [
# 使用 SCRAM 進行 PostgreSQL 密碼加密
](PostgreSQL_Password_Encryption_configuration.md)
# 了解 rds\$1superuser 角色
於 PostgreSQL 中，*角色*可定義使用者、群組或授予群組或使用者對資料庫中各種物件的一組特定權限。`CREATE USER` 和 `CREATE GROUP` 的 PostgreSQL 命令已為更為通用的 `CREATE ROLE` 所取代，其具有可區分資料庫使用者的特定屬性。資料庫使用者可視為具有 LOGIN 權限的角色。
**注意**
`CREATE USER` 和 `CREATE GROUP` 命令仍可使用。如需詳細資訊，請參閱 PostgreSQL 文件中的[資料庫角色](https://www.postgresql.org/docs/current/user-manag.html)。
`postgres` 使用者是 RDS for PostgreSQL 資料庫執行個體上具最高權限的資料庫使用者。其具有下列 `CREATE ROLE` 陳述式所定義的特性。
```
CREATE ROLE postgres WITH LOGIN NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION VALID UNTIL 'infinity'
```
屬性 `NOSUPERUSER`、`NOREPLICATION`、`INHERIT`，和 `VALID UNTIL 'infinity'` 為 CREATE ROLE 的預設選項，除非另有指定。
根據預設，`postgres` 具有授與 `rds_superuser` 角色的權限，以及建立角色和資料庫的許可。`rds_superuser` 角色可讓 `postgres` 使用者執行下列動作：
+ 新增可與 Amazon RDS 搭配使用的擴充功能。如需詳細資訊，請參閱 [使用 Amazon RDS for PostgreSQL 支援的 PostgreSQL 功能](PostgreSQL.Concepts.General.FeatureSupport.md)
+ 建立使用者的角色，並授予使用者權限。如需詳細資訊，請參閱 PostgreSQL 文件中的 [CREATE ROLE](https://www.postgresql.org/docs/current/sql-createrole.html) 和 [GRANT](https://www.postgresql.org/docs/14/sql-grant.html)。
+ 建立資料庫。如需詳細資訊，請參閱 PostgreSQL 文件中的 [CREATE DATABASE](https://www.postgresql.org/docs/14/sql-createdatabase.html)。
+ 將 `rds_superuser` 權限授予並無這些權限的使用者角色，並視需要撤銷這些權限。建議您僅將此角色授予執行超級使用者任務的使用者。換句話說，您可以將此角色授予資料庫管理員 (DBA) 或系統管理員。
+ 對不具 `rds_replication` 角色的資料庫使用者授予 (和撤銷) `rds_superuser` 角色。
+ 對不具 `rds_password` 角色的資料庫使用者授予 (和撤銷) `rds_superuser` 角色。
+ 使用 `pg_stat_activity` 檢視，取得有關所有資料庫連線的狀態資訊。如有需要，`rds_superuser` 可使用 `pg_terminate_backend` 或 `pg_cancel_backend` 停止任何連線。
於 `CREATE ROLE postgres...` 陳述式中，您可看到 `postgres` 使用者角色明確禁止 PostgreSQL `superuser` 權限。RDS for PostgreSQL 為受管服務，因此您無法存取主機作業系統，也無法使用 PostgreSQL `superuser` 帳户進行連線。許多需要在獨立 PostgreSQL 上進行 `superuser` 存取的任務是由 Amazon RDS 自動管理。
如需有關授予權限的詳細資訊，請參閱 PostgreSQL 文件中的 [GRANT](http://www.postgresql.org/docs/current/sql-grant.html)。
`rds_superuser` 角色是 中數個*預先定義*角色的其中一個。RDS for PostgreSQL 資料庫執行個體。
**注意**
在 PostgreSQL 13 和更早版本中，*預先定義*角色稱為*預設*角色。
於下列清單中，您可以找到為新的 自動建立的一些其他預先定義角色。RDS for PostgreSQL 資料庫執行個體。預先定義的角色及其權限無法進行變更。您無法為這些預先定義角色停止、重新命名或修改權限。嘗試這麼做會造成錯誤。
+ **rds\$1password** – 可變更密碼並為資料庫使用者設定密碼約束的角色。依預設授予此角色 `rds_superuser` 角色，並可授予資料庫使用者該角色。如需詳細資訊，請參閱[控制使用者對 PostgreSQL 資料庫的存取控制使用者對 PostgreSQL 的存取](Appendix.PostgreSQL.CommonDBATasks.Access.md)。
+ 對於 14 之前的 RDS for PostgreSQL 版本，`rds_password` 角色可以變更密碼，並為資料庫使用者和具有 `rds_superuser` 角色的使用者設定密碼限制。從 RDS for PostgreSQL 第 14 版及更新版本，`rds_password` 角色可以變更密碼，並僅為資料庫使用者設定密碼限制。只有具有 `rds_superuser` 角色的使用者才能對具有 `rds_superuser` 角色的其他使用者執行這些動作。
+ **rdsadmin** – 為處理具有 `superuser` 權限的管理員將在獨立 PostgreSQL 資料庫上執行的許多管理任務而建立的角色。此角色由 RDS for PostgreSQL 在內部用於許多管理任務。
+ **rdstopmgr**— Amazon RDS 內部使用的一個角色，可支援異地同步備份部署。
+ **rds\$1reserved** – Amazon RDS 在內部用來保留資料庫連線的角色。
# 檢視角色及其權限
您可以根據 PostgreSQL 版本，使用不同的命令在 RDS for PostgreSQL 資料庫執行個體中檢視預先定義的角色及其權限。若要查看所有預先定義的角色，您可以連線至 RDS for PostgreSQL 資料庫執行個體，並使用 `psql` 執行下列命令。
**對於 `psql` 第 15 版和更早版本**
連線至 RDS for PostgreSQL 資料庫執行個體，並在 psql 中使用 `\du` 命令：
```
postgres=> \du
List of roles
Role name | Attributes | Member of
-----------------+------------------------------------------------------------+------------------------------------------------------
postgres | Create role, Create DB +| {rds_superuser}
| Password valid until infinity |
rds_ad | Cannot login | {}
rds_iam | Cannot login | {}
rds_password | Cannot login | {}
rds_replication | Cannot login | {}
rds_superuser | Cannot login | {pg_monitor,pg_signal_backend,rds_password,rds_replication}
rdsadmin | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
| Password valid until infinity |
```
**對於 `psql` 第 16 版和更新版本**
```
postgres=> \drg+
List of role grants
Role name | Member of | Options | Grantor
---------------+-----------------------------+---------------------+----------
postgres | rds_superuser | INHERIT, SET | rdsadmin
rds_superuser | pg_checkpoint | ADMIN, INHERIT, SET | rdsadmin
rds_superuser | pg_monitor | ADMIN, INHERIT, SET | rdsadmin
rds_superuser | pg_signal_backend | ADMIN, INHERIT, SET | rdsadmin
rds_superuser | pg_use_reserved_connections | ADMIN, INHERIT, SET | rdsadmin
rds_superuser | rds_password | ADMIN, INHERIT, SET | rdsadmin
rds_superuser | rds_replication | ADMIN, INHERIT, SET | rdsadmin
```
若要在沒有版本相依性的情況下檢查角色成員資格，您可以使用下列 SQL 查詢：
```
SELECT m.rolname AS "Role name", r.rolname AS "Member of"
FROM pg_catalog.pg_roles m
JOIN pg_catalog.pg_auth_members pam ON (pam.member = m.oid)
LEFT JOIN pg_catalog.pg_roles r ON (pam.roleid = r.oid)
LEFT JOIN pg_catalog.pg_roles g ON (pam.grantor = g.oid)
WHERE m.rolname !~ '^pg_'
ORDER BY 1, 2;
```
於輸出中，您可看到 `rds_superuser` 並非資料庫使用者角色 (無法登入)，但其具有許多其他角色的權限。您還可以看到資料庫使用者 `postgres` 是 `rds_superuser` 角色的成員。如前所述，`postgres` 是 Amazon RDS 主控台 **Create database (建立資料庫)** 頁面中的預設值。若選擇其他名稱，則該名稱將顯示於角色清單中。
# 控制使用者對 PostgreSQL 資料庫的存取
PostgreSQL 中的新資料庫會永遠使用資料庫 `public` 結構描述中的一組預設權限建立，允許所有資料庫使用者和角色建立物件。例如，這些權限可讓資料庫使用者連接至資料庫，並在連線時建立暫存資料表。
為了對您在 RDS for PostgreSQL 資料庫執行個體上建立之資料庫執行個體的使用者存取進行更好地控制，我們建議您撤消這些預設的 `public` 權限。完成此作業後，您可更精細地為資料庫使用者授予特定權限，如下列程序所示。
**如要設定新資料庫執行個體的角色和權限**
假設您正在一個剛建立的 RDS for PostgreSQL 資料庫執行個體上設定資料庫，以供多位研究人員使用，其皆需要資料庫的讀寫存取權。
1. 使用 `psql` (或 pgAdmin) 連接至 您的 RDS for PostgreSQL 資料庫執行個體：
```
psql --host=your-db-instance.666666666666.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```
出現提示時，輸入您的密碼。`psql` 用戶端連接並顯示預設管理連接資料庫 `postgres=>` 作為提示。
1. 如要防止資料庫使用者在 `public` 結構描述中建立物件，請執行下列動作：
```
postgres=> REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE
```
1. 接下來，您會建立新的資料庫執行個體：
```
postgres=> CREATE DATABASE lab_db;
CREATE DATABASE
```
1. 從此新資料庫上 `PUBLIC` 結構描述撤消所有權限。
```
postgres=> REVOKE ALL ON DATABASE lab_db FROM public;
REVOKE
```
1. 建立一個資料庫使用者的角色。
```
postgres=> CREATE ROLE lab_tech;
CREATE ROLE
```
1. 使具有此角色的資料庫使用者可連接至資料庫。
```
postgres=> GRANT CONNECT ON DATABASE lab_db TO lab_tech;
GRANT
```
1. 授予具 `lab_tech` 角色的所有使用者此資料庫的所有權限。
```
postgres=> GRANT ALL PRIVILEGES ON DATABASE lab_db TO lab_tech;
GRANT
```
1. 建立資料庫使用者，如下所示：
```
postgres=> CREATE ROLE lab_user1 LOGIN PASSWORD 'change_me';
CREATE ROLE
postgres=> CREATE ROLE lab_user2 LOGIN PASSWORD 'change_me';
CREATE ROLE
```
1. 授予這兩個使用者與 lab\$1tech 角色關聯的權限：
```
postgres=> GRANT lab_tech TO lab_user1;
GRANT ROLE
postgres=> GRANT lab_tech TO lab_user2;
GRANT ROLE
```
至此，`lab_user1` 和 `lab_user2` 便可連接 `lab_db` 資料庫。此範例並未遵循企業使用的最佳實務，其中可能包括建立多個資料庫執行個體、不同的結構描述，及授予有限的權限。如需更多完整資訊和其他方案，請參閱[管理 PostgreSQL 使用者和角色](https://aws.amazon.com/blogs//database/managing-postgresql-users-and-roles/)。
如需有關 PostgreSQL 資料庫中權限的詳細資訊，請參閱 PostgreSQL 文件中的 [GRANT](https://www.postgresql.org/docs/current/static/sql-grant.html) 命令。
# 委派和控制使用者密碼管理
作為 DBA，您可能想要委派管理使用者密碼。或者，您可能想要防止資料庫使用者變更其密碼或重新設定密碼約束 (例如密碼生命週期)。如要確保只有您選擇的資料庫使用者才可變更密碼設定，您可開啟受限制的密碼管理功能。啟動此功能時，只有那些已被授予 `rds_password` 角色的資料庫使用者才可管理密碼。
**注意**
如要使用受限制的密碼管理，您的 RDS for PostgreSQL 資料庫執行個體必須執行 PostgreSQL 10.6 或更新版本。
依預設，此功能為 `off`，如下列所示：
```
postgres=> SHOW rds.restrict_password_commands;
rds.restrict_password_commands
--------------------------------
off
(1 row)
```
如要開啟此功能，請使用自訂參數群組，並將 `rds.restrict_password_commands` 的設定變更為 1。請務必重新啟動您的 RDS for PostgreSQL 資料庫執行個體，以使設定生效。
啟用此功能後，下列 SQL 命令需要 `rds_password` 權限：
```
CREATE ROLE myrole WITH PASSWORD 'mypassword';
CREATE ROLE myrole WITH PASSWORD 'mypassword' VALID UNTIL '2023-01-01';
ALTER ROLE myrole WITH PASSWORD 'mypassword' VALID UNTIL '2023-01-01';
ALTER ROLE myrole WITH PASSWORD 'mypassword';
ALTER ROLE myrole VALID UNTIL '2023-01-01';
ALTER ROLE myrole RENAME TO myrole2;
```
若密碼使用 MD5 雜湊演算法，則重新命名角色 (`ALTER ROLE myrole RENAME TO newname`) 也會受到限制。
啟用此功能後，嘗試使用任何這些 SQL 命令，而不使用 `rds_password` 角色權限會產生下列錯誤：
```
ERROR: must be a member of rds_password to alter passwords
```
建議您僅對專用於密碼管理的一些角色授予 `rds_password`。若您授予不具 `rds_superuser` 權限之資料庫使用者 `rds_password` 權限，則還需要授予其 `CREATEROLE` 屬性。
確定您驗證用戶端上的密碼需求，例如到期時間和需要的複雜度。若您使用自己的用戶端公用程式進行與密碼相關的變更，則該公用程式必須為 `rds_password` 的成員並具有 `CREATE ROLE` 權限。
# 使用 SCRAM 進行 PostgreSQL 密碼加密
*Salted Challenge Response Authentication Mechanism (SCRAM)* 是 PostgreSQL 預設訊息摘要 (MD5) 演算法的替代選項，用於加密密碼。SCRAM 身分驗證機制被認為比 MD5 更安全。若要進一步了解這兩種不同的密碼保護方法，請參閱 PostgreSQL 文件中的 [Password Authentication](https://www.postgresql.org/docs/14/auth-password.html) (密碼身分驗證)。
建議您使用 SCRAM 作為 的密碼加密配置，而不是使用 MD5。RDS for PostgreSQL 資料庫執行個體。這是一種加密的挑戰回應機制，使用 scram-sha-256 演算法進行密碼身分驗證和加密。
您可能需要更新程式庫，用戶端應用程式才會支援 SCRAM。例如，42.2.0 之前的 JDBC 版本不支援 SCRAM。如需詳細資訊，請參閱 PostgreSQL JDBC 驅動程式文件中的 [PostgreSQL JDBC Driver](https://jdbc.postgresql.org/changelogs/2018-01-17-42.2.0-release/) (PostgreSQL JDBC 驅動程式)。如需其他 PostgreSQL 驅動程式和 SCRAM 支援的清單，請參閱 PostgreSQL 文件中的 [List of drivers](https://wiki.postgresql.org/wiki/List_of_drivers) (驅動程式清單)。
RDS for PostgreSQL 13.1 版和更新版本支援 scram-sha-256。這些版本亦允許您將資料庫執行個體設定為需要 SCRAM，如以下程序所述。
## 設定 RDS for PostgreSQL 資料庫執行個體以要求 SCRAM
，您可以要求 RDS for PostgreSQL 資料庫執行個體僅接受使用 scram-sha-256 演算法的密碼。
**重要**
對於 PostgreSQL 資料庫的現有 RDS 代理，如果您將資料庫驗證修改為僅使用 `SCRAM`，則該代理會變成無法使用，最多持續 60 秒。若要避免發生此問題，請執行下列其中一項：
確定資料庫同時允許 `SCRAM` 和 `MD5` 身分驗證。
若只要使用 `SCRAM` 身分驗證，請建立新代理、將應用程式流量遷移至新代理，然後刪除先前與資料庫相關聯的代理。
在對系統進行變更之前，請確保您了解完整過程，如下所示：
+ 獲得所有資料庫使用者的所有角色與密碼加密相關資訊。
+ 針對控制密碼加密的參數，再次檢查 RDS for PostgreSQL 資料庫執行個體的參數設定。
+ 如果您的 RDS for PostgreSQL 資料庫執行個體使用預設參數群組，則需要建立自訂資料庫參數群組，並將其套用到 RDS for PostgreSQL 資料庫執行個體，讓您可以在需要時修改參數。如果您的 RDS for PostgreSQL 資料庫執行個體使用自訂參數群組，則您稍後可以視需要在過程中修改必要參數。
+ 將 `password_encryption` 參數變更為 `scram-sha-256`。
+ 通知所有資料庫使用者他們必須更新密碼。針對您的 `postgres` 帳戶進行相同的動作。系統會使用 scram-sha-256 演算法加密與儲存新密碼。
+ 驗證確認使用加密類型將所有密碼加密。
+ 如果所有密碼都使用 scram-sha-256，您可以將 `rds.accepted_password_auth_method` 參數從 `md5+scram` 變更為 `scram-sha-256`。
**警告**
在您僅將 `rds.accepted_password_auth_method` 變更為 scram-sha-256 後，則任何具有 `md5` 加密密碼的使用者 (角色) 將無法連線。
### 準備好為您的 RDS for PostgreSQL 資料庫執行個體要求 SCRAM
對 RDS for PostgreSQL 資料庫執行個體進行任何變更之前，請檢查所有現有的資料庫使用者帳戶。另外，請檢查用於密碼的加密類型。您可以使用 `rds_tools` 擴充功能執行這些任務。若要查看哪些 PostgreSQL 版本支援 `rds_tools`，請參閱 [Amazon RDS for PostgreSQL 的延伸版本](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html)。
**獲得資料庫使用者 (角色) 與密碼加密方法清單**
1. 使用 `psql` 連線至 RDS for PostgreSQL 資料庫執行個體，如下所示。
```
psql --host=db-name.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```
1. 安裝 `rds_tools` 擴充功能。
```
postgres=> CREATE EXTENSION rds_tools;
CREATE EXTENSION
```
1. 取得角色和加密清單。
```
postgres=> SELECT * FROM
rds_tools.role_password_encryption_type();
```
您會看到類似下列的輸出。
```
rolname | encryption_type
----------------------+-----------------
pg_monitor |
pg_read_all_settings |
pg_read_all_stats |
pg_stat_scan_tables |
pg_signal_backend |
lab_tester | md5
user_465 | md5
postgres | md5
(8 rows)
```
### 建立自訂資料庫參數群組
**注意**
如果您的 RDS for PostgreSQL 資料庫執行個體已使用自訂參數群組，則不需要建立新的群組。
如需 Amazon RDS 的參數群組概觀，請參閱 [在 RDS for PostgreSQL 資料庫執行個體上搭配使用參數](Appendix.PostgreSQL.CommonDBATasks.Parameters.md)。
用於密碼的密碼加密類型是在某一個參數 (亦即 `password_encryption`) 中設定的。RDS for PostgreSQL 資料庫執行個體允許的加密則是在另一個參數 (亦即 `rds.accepted_password_auth_method`) 中設定的。若要從這些預設值變更，則需要您建立自訂資料庫參數群組，並套用到您的執行個體。
您也可以使用 AWS 管理主控台 或 RDS API 來建立自訂資料庫參數群組。如需詳細資訊，請參閱
您可以使用資料庫執行個體與自訂參數群組建立關聯。
**建立自訂資料庫參數群組**
1. 使用 CLI `[create-db-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-parameter-group.html) ` 命令建立自訂資料庫參數群組。此範例使用 `postgres13` 作為此自訂參數群組的來源。
對於 Linux、macOS 或 Unix：
```
aws rds create-db-parameter-group --db-parameter-group-name 'docs-lab-scram-passwords' \
--db-parameter-group-family postgres13 --description 'Custom parameter group for SCRAM'
```
在 Windows 中：
```
aws rds create-db-parameter-group --db-parameter-group-name "docs-lab-scram-passwords" ^
--db-parameter-group-family postgres13 --description "Custom DB parameter group for SCRAM"
```
1. 使用 `[modify-db-instance](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html)` CLI 命令將此自訂參數群組套用至您的 RDS for PostgreSQL 資料庫叢集。
對於 Linux、macOS 或 Unix：
```
aws rds modify-db-instance --db-instance-identifier 'your-instance-name' \
--db-parameter-group-name "docs-lab-scram-passwords
```
在 Windows 中：
```
aws rds modify-db-instance --db-instance-identifier "your-instance-name" ^
--db-parameter-group-name "docs-lab-scram-passwords
```
若要將 RDS for PostgreSQL 資料庫執行個體與自訂資料庫參數群組重新同步，您需要重新啟動叢集的主要與所有其他執行個體。為了盡量降低對使用者的影響，排程在一般維護時段期間進行此操作。
### 設定密碼加密以使用 SCRAM
RDS for PostgreSQL 資料庫執行個體使用的密碼加密機制是在資料庫參數群組的 `password_encryption` 參數中設定的。允許的值為未設定、`md5` 或 `scram-sha-256`。預設值視 RDS for PostgreSQL 版本而定，如下所示：
+ RDS for PostgreSQL 14 和更新版本 - 預設值是 `scram-sha-256`
+ RDS for PostgreSQL 13 – 預設值是 `md5`
透過將自訂資料庫參數群組連接至 RDS for PostgreSQL 資料庫執行個體，您可以修改密碼加密參數的值。
![\[接著，RDS 主控台顯示 RDS for PostgreSQL 其 password_encryption 參數的預設值。\]](http://docs.aws.amazon.com/zh_tw/AmazonRDS/latest/UserGuide/images/rpg-pwd-encryption-md5-scram-1.png)
**將密碼加密設定變更為 scram-sha-256**
+ 將密碼加密的值變更為 scram-sha-256，如下所示。變更會立即套用，因為參數是動態的，因此不需要重新啟動即可使變更生效。
對於 Linux、macOS 或 Unix：
```
aws rds modify-db-parameter-group --db-parameter-group-name \
'docs-lab-scram-passwords' --parameters 'ParameterName=password_encryption,ParameterValue=scram-sha-256,ApplyMethod=immediate'
```
在 Windows 中：
```
aws rds modify-db-parameter-group --db-parameter-group-name ^
"docs-lab-scram-passwords" --parameters "ParameterName=password_encryption,ParameterValue=scram-sha-256,ApplyMethod=immediate"
```
### 將使用者角色的密碼遷移至 SCRAM
您可以將使用者角色的密碼遷移至 SCRAM，如下說明：
**將資料庫使用者 (角色) 密碼從 MD5 遷移至 SCRAM**
1. 以管理員使用者身分 (預設使用者名稱 `postgres`) 登入，如下所示。
```
psql --host=db-name.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```
1. 使用下列命令檢查 RDS for PostgreSQL 資料庫執行個體上 `password_encryption` 參數的設定。
```
postgres=> SHOW password_encryption;
password_encryption
---------------------
md5
(1 row)
```
1. 將此參數的值變更為 scram-sha-256。如需詳細資訊，請參閱[設定密碼加密以使用 SCRAM](#PostgreSQL_Password_Encryption_configuration.configure-password-encryption)。
1. 再次檢查該值以確定它現在已設定為 `scram-sha-256`，如下所示。
```
postgres=> SHOW password_encryption;
password_encryption
---------------------
scram-sha-256
(1 row)
```
1. 通知所有資料庫使用者變更自己的密碼。務必亦為帳戶 `postgres` (具有 `rds_superuser` 權限的資料庫使用者) 變更您自己的密碼。
```
labdb=> ALTER ROLE postgres WITH LOGIN PASSWORD 'change_me';
ALTER ROLE
```
1. 對 上所有的資料庫重複此程序。RDS for PostgreSQL 資料庫執行個體。
### 變更參數為需要 SCRAM
這是程序中的最後一個步驟。在以下程序中進行變更後，仍然使用 `md5` 將密碼加密的任何使用者帳戶 (角色) 將無法登入 RDS for PostgreSQL 資料庫執行個體。
`rds.accepted_password_auth_method` 指定 RDS for PostgreSQL 資料庫執行個體在登入程序期間接受的使用者密碼加密方法。預設值為 `md5+scram`，這意味著任何一種方法都接受。在下圖中，您可以找到此參數的預設設定。
![\[顯示 rds.accepted_password_auth_method 參數其預設值與允許值的 RDS 主控台。\]](http://docs.aws.amazon.com/zh_tw/AmazonRDS/latest/UserGuide/images/pwd-encryption-md5-scram-2.png)
此參數的允許值為 `md5+scram` 或僅 `scram`。將此參數值變更為 `scram` 會使此成為一個需求。
**將參數值變更為需要對密碼進行 SCRAM 身分驗證**
1. 確認 RDS for PostgreSQL 資料庫執行個體上所有資料庫的所有資料庫使用者密碼使用 `scram-sha-256` 進行密碼加密。若要這麼做，請向 `rds_tools` 查詢角色 (使用者) 和加密類型，如下所示。
```
postgres=> SELECT * FROM rds_tools.role_password_encryption_type();
rolname | encryption_type
----------------------+-----------------
pg_monitor |
pg_read_all_settings |
pg_read_all_stats |
pg_stat_scan_tables |
pg_signal_backend |
lab_tester | scram-sha-256
user_465 | scram-sha-256
postgres | scram-sha-256
( rows)
```
1. 針對您的 中所有的資料庫執行個體重複此查詢。RDS for PostgreSQL 資料庫執行個體。
如果所有密碼都使用 scram-sha-256，您可以繼續進行。
1. 將接受的密碼身分驗證值變更為 scram-sha-256，如下所示。
對於 Linux、macOS 或 Unix：
```
aws rds modify-db-parameter-group --db-parameter-group-name 'docs-lab-scram-passwords' \
--parameters 'ParameterName=rds.accepted_password_auth_method,ParameterValue=scram,ApplyMethod=immediate'
```
在 Windows 中：
```
aws rds modify-db-parameter-group --db-parameter-group-name "docs-lab-scram-passwords" ^
--parameters "ParameterName=rds.accepted_password_auth_method,ParameterValue=scram,ApplyMethod=immediate"
```
# PostgreSQL 中的無效連線處理
即使用戶端應用程式異常捨棄或終止，資料庫工作階段仍在伺服器上保持作用中狀態時，就會發生無效連線。當用戶端處理當機或意外終止，且未正確關閉其資料庫連線或取消持續請求時，通常會發生這種情況。
當伺服器程序閒置或嘗試將資料傳送至用戶端時，PostgreSQL 可有效識別和清除無效連線。不過，對於閒置、等待用戶端輸入或主動執行查詢的工作階段而言，偵測具有挑戰性。為了處理這些案例，PostgreSQL 提供 `tcp_keepalives_*`、`tcp_user_timeout` 和 `client_connection_check_interval` 參數。
**Topics**
+ [
## 了解 TCP 保持連線
](#Appendix.PostgreSQL.CommonDBATasks.DeadConnectionHandling.Understanding)
+ [
## RDS for PostgreSQL 中的金鑰 TCP 保持連線參數
](#Appendix.PostgreSQL.CommonDBATasks.DeadConnectionHandling.Parameters)
+ [
## TCP 保持連線設定的使用案例
](#Appendix.PostgreSQL.CommonDBATasks.DeadConnectionHandling.UseCases)
+ [
## 最佳實務
](#Appendix.PostgreSQL.CommonDBATasks.DeadConnectionHandling.BestPractices)
## 了解 TCP 保持連線
TCP 保持連線是一種通訊協定層級機制，可協助維護和確認連線完整性。每個 TCP 連線都會維護控制保持連線行為的核心層級設定。當保持連線計時器過期時，系統會執行下列動作：
+ 傳送未設定資料和 ACK 旗標的探查封包。
+ 根據 TCP/IP 規格，預期來自遠端端點的回應。
+ 根據回應或缺乏回應來管理連線狀態。
## RDS for PostgreSQL 中的金鑰 TCP 保持連線參數
| 參數 | Description | 預設值 |
| --- |--- |--- |
| tcp\$1keepalives\$1idle | Specifies number of seconds of inactivity before sending keepalive message. | 300 |
| tcp\$1keepalives\$1interval | Specifies number of seconds between retransmissions of unacknowledged keepalive messages. | 30 |
| tcp\$1keepalives\$1count | Maximum lost keepalive messages before declaring connection dead | 2 |
| tcp\$1user\$1timeout | Specifies how long (in Milliseconds) unacknowledged data can remain before forcibly closing the connection. | 0 |
| client\$1connection\$1check\$1interval | Sets the interval (in Milliseconds) for checking client connection status during long-running queries. This ensures quicker detection of closed connections. | 0 |
## TCP 保持連線設定的使用案例
### 保持閒置工作階段持續運作
若要防止防火牆或路由器因閒置而終止閒置連線：
+ 設定 `tcp_keepalives_idle` 以定期傳送保持連線封包。
### 偵測無效連線
若要立即偵測無效連線：
+ 調整 `tcp_keepalives_idle`、`tcp_keepalives_interval` 和 `tcp_keepalives_count`。例如，使用 Aurora PostgreSQL 預設值時，偵測無效連線大約需要一分鐘 (2 次探查 × 30 秒）。降低這些值可以加快偵測速度。
+ 使用 `tcp_user_timeout` 指定確認的最長等待時間。
TCP 保持連線設定可協助核心偵測無效連線，但在使用通訊端之前，PostgreSQL 可能不會運作。如果工作階段執行長查詢，只有在查詢完成後才會偵測到無效連線。在 PostgreSQL 14 和更高版本中，`client_connection_check_interval` 可以在查詢執行期間定期輪詢通訊端，以加速無效連線偵測。
## 最佳實務
+ **設定合理的保持連線間隔：**調校 `tcp_user_timeout`、`tcp_keepalives_idle`、`tcp_keepalives_count` 和 `tcp_keepalives_interval`，以平衡偵測速度和資源使用。
+ **為您的環境最佳化：**使設定與網路行為、防火牆政策和工作階段需求保持一致。
+ **利用 PostgreSQL 功能：**在 PostgreSQL 14 和更高版本中使用 `client_connection_check_interval`，以進行有效的連線檢查。
# 在 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`，以防止交易失敗。
# 在 Amazon RDS for PostgreSQL 中管理高物件計數
雖然 PostgreSQL 限制是理論上的，但資料庫中具有極高的物件計數會對各種操作造成顯著的效能影響。本文件涵蓋數種常見的物件類型，當總計數過高時，可能會導致數種影響。
下表提供物件類型及其潛在影響的摘要：
**物件類型和潛在影響**
| 物件的類型 | 自動清空 | 邏輯複寫 | 主要版本升級 | pg\$1dump / pg\$1restore | 一般效能 | 執行個體重新啟動 |
| --- | --- | --- | --- | --- | --- | --- |
| [關係](#PostgreSQL.HighObjectCount.Relations) | x | | x | x | x | |
| [暫時資料表](#PostgreSQL.HighObjectCount.TempTables) | x | | | | x | |
| [未記錄的資料表](#PostgreSQL.HighObjectCount.UnloggedTables) | | x | | | | x |
| [分區](#PostgreSQL.HighObjectCount.Partitions) | | | | | x | |
| [暫存檔案](#PostgreSQL.HighObjectCount.TempFiles) | | | | | x | |
| [序列](#PostgreSQL.HighObjectCount.Sequences) | | x | | | | |
| [大型物件](#PostgreSQL.HighObjectCount.LargeObjects) | | x | x | | | |
## 關係
PostgreSQL 資料庫中的資料表數量沒有特定的硬性限制。理論限制非常高，但在資料庫設計期間需要記住其他實際限制。
**影響：自動清空落後**
與工作量相比，自動清空可能會難以跟上交易 ID 成長或資料表膨脹的情況。
**建議的動作：**調整自動清空有幾個因素，可以正確跟上指定數量的資料表和指定工作負載。如需如何判斷適當自動清空設定的建議[，請參閱使用 PostgreSQL autovacuum](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum.html) 。使用 [postgres\$1get\$1av\$1diag utility](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Functions.html)來監控交易 ID 成長的問題。
**影響：主要版本升級/pg\$1dump 和還原**
Amazon RDS 在 pg\$1upgrade 執行期間使用「--link」選項，以避免必須複製資料檔案，結構描述中繼資料仍需要還原至新版本的資料庫。即使使用平行 pg\$1restore，如果有大量關聯，這也會增加停機時間。
**影響：一般效能降低**
由於目錄大小而降低的一般效能。每個資料表及其相關聯的資料欄都會新增至 `pg_attribute`，`pg_class`以及常用於一般資料庫操作的`pg_depend`資料表。不會顯示特定的等待事件，但共用緩衝區效率會受到影響。
**建議的動作：**定期檢查這些特定資料表的資料表膨脹，並偶爾在這些特定資料表`VACUUM FULL`上執行 。請注意，在目錄資料表`VACUUM FULL`上需要`ACCESS EXCLUSIVE`鎖定，這表示在操作完成之前，其他查詢都無法存取它們。
**影響：檔案描述項耗盡**
錯誤：「檔案描述項不足：系統中開啟的檔案太多；發行並重試」。PostgreSQL 參數`max_files_per_process`會決定每個程序可以開啟的檔案數量。如果連接大量資料表的連線數量較多，則可能達到此限制。
**建議的動作：**
+ 降低 參數的值`max_files_per_process`可能有助於減輕此錯誤。每個程序和子程序 （例如平行查詢） 都可以開啟此數量的檔案，如果查詢正在聯結多個資料表，則會耗盡此限制。
+ 減少連線總數，並使用連線集區，例如 [Amazon RDS Proxy](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy.html) 或其他解決方案，例如 PgBouncer。如需進一步了解，請參閱 [PgBouncer](https://www.pgbouncer.org/) 網站。
**影響：Inode 耗盡**
錯誤：「裝置上沒有剩餘空間」。如果觀察到這種情況，表示有足夠的可用儲存空間，這是因為索引用盡。[Amazon RDS 增強型監控](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_Monitoring.OS.html)可為使用中的節點提供可見性，以及主機可用的最大數量。
**大約閾值：**[百萬](#PostgreSQL.HighObjectCount.Note)
## 暫時資料表
使用暫存資料表對於測試資料或中繼結果很有用，而且是許多資料庫引擎中常見的模式。必須了解 PostgreSQL 中大量使用的影響，以避免某些陷阱。每個暫存資料表的建立和捨棄都會將資料列新增至系統目錄資料表，當資料表膨脹時，會導致一般效能問題。
**影響：自動清空落後**
暫時資料表不會由自動清空清空，但會在交易 IDs存在期間保留，若未移除，可能會導致包裝。
**建議的動作：**暫時資料表會在建立它們的工作階段期間持續運作，或可以手動捨棄。避免使用暫存資料表長時間執行交易的最佳實務，可防止這些資料表產生最大使用的交易 ID 成長。
**影響：一般效能降低**
由於目錄大小而降低的一般效能。當工作階段持續建立和捨棄暫存資料表時，它會新增至 `pg_attribute`，`pg_class`以及常用於一般資料庫操作的`pg_depend`資料表。不會顯示特定的等待事件，但共用緩衝區效率會受到影響。
**建議的動作：**
+ 定期檢查這些特定資料表的資料表膨脹，並偶爾在這些特定資料表`VACUUM FULL`上執行 。請注意，在目錄資料表`VACUUM FULL`上需要`ACCESS EXCLUSIVE`鎖定，這表示在操作完成之前，其他查詢都無法存取它們。
+ 如果大量使用暫存資料表，在主要版本升級之前，強烈建議使用這些特定目錄資料表`VACUUM FULL`的 ，以減少停機時間。
**一般最佳實務：**
+ 使用常見的資料表表達式來產生中繼結果，以減少暫時資料表的使用。這些有時可能會使所需的查詢複雜化，但會消除上述影響。
+ 使用 `TRUNCATE`命令來清除內容，而不是執行捨棄/建立步驟，以重複使用暫存資料表。這也會消除暫時資料表造成交易 ID 成長的問題。
**大約閾值：**[數十萬](#PostgreSQL.HighObjectCount.Note)
## 未記錄的資料表
未記錄的資料表可以提供效能提升，因為它們不會產生任何 WAL 資訊。必須仔細使用它們，因為它們在資料庫損毀復原期間不會提供任何耐用性，因為它們將被截斷。這是 PostgreSQL 中的昂貴操作，因為每個未記錄的資料表都是序列截斷的。雖然對少量未記錄的資料表而言，此操作速度很快，但當它們以千為單位編號時，它可以開始在啟動期間新增顯著的延遲。
**影響：邏輯複寫**
邏輯複寫通常不包含未記錄的資料表，包括[藍/綠部署](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-green-deployments.html)，因為邏輯複寫依賴 WAL 來擷取和傳輸變更。
**影響：復原期間延長停機時間**
在涉及資料庫損毀復原的任何資料庫狀態期間，例如具有容錯移轉的異地同步備份重新啟動、Amazon RDS point-in-time復原和 Amazon RDS 主要版本升級，將發生截斷未記錄資料表的序列化操作。這可能會導致比預期高出許多的停機時間體驗。
**建議的動作：**
+ 將未記錄資料表的使用量降至最低，僅限於資料庫損毀復原操作期間可遺失的資料。
+ 將未記錄資料表的使用降至最低，因為序列截斷的目前行為可能會導致資料庫啟動需要大量時間。
**一般最佳實務：**
+ 未記錄的資料表不會安全當機。啟動涉及損毀復原point-in-time復原會在 PostgreSQL 中花費大量時間，因為這是截斷每個資料表的序列程序。相較於標準 PostgreSQL，
**大約閾值：**[數千](#PostgreSQL.HighObjectCount.Note)
## 分區
分割可以提高查詢效能並提供資料的邏輯組織。在理想情況下，會組織分割區，以便在查詢規劃和執行期間使用分割區剔除。使用太多分割區可能會對查詢效能和資料庫維護產生負面影響。應謹慎選擇如何分割資料表，因為查詢規劃和執行的效能可能會受到設計不佳的負面影響。如需分割的詳細資訊，請參閱 [PostgreSQL 文件](https://www.postgresql.org/docs/current/ddl-partitioning.html)。
**影響：一般效能降低**
有時，規劃時間額外負荷會增加，並解釋查詢的計劃會變得更加複雜，導致難以識別調校機會。對於 18 之前的 PostgreSQL 版本，具有高工作負載的許多分割區可能會導致`LWLock:LockManager`等待。
**建議動作：**判斷可讓您完成資料組織，同時提供效能查詢執行的分割區數量下限。
**影響：維護複雜性**
非常大量的分割區會導致維護問題，例如建立前和移除。自動清空會將分割區視為一般關係，且必須執行定期清除，因此需要足夠的工作者來完成任務。
**建議的動作：**
+ 請確定您預先建立分割區，以便在需要新分割區 （例如，每月型分割區） 且舊分割區推出時，不會封鎖工作負載。
+ 確保您有足夠的自動清空工作者，可執行所有分割區的正常清除維護。
**大約閾值：**[數百](#PostgreSQL.HighObjectCount.Note)
## 暫存檔案
與上述的暫存資料表不同，當複雜的查詢可能同時執行數個排序或雜湊操作時，PostgreSQL 會建立暫存檔案，每個操作會使用執行個體記憶體將結果儲存到 `work_mem` 參數中指定的值。當執行個體記憶體不足時，會建立暫存檔來儲存結果。如需[暫存](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.ManagingTempFiles.html)檔案的詳細資訊，請參閱管理暫存檔案如果您的工作負載產生大量這些檔案，可能會有數種影響。
**影響：檔案描述項耗盡**
錯誤：「檔案描述項不足：系統中開啟的檔案太多；發行並重試」。PostgreSQL 參數`max_files_per_process`會決定每個程序可以開啟的檔案數量。如果連接大量資料表的連線數量很高，則可能達到此限制。
**建議的動作：**
+ 降低 參數的值`max_files_per_process`可能有助於緩解此錯誤。每個程序和子程序 （例如平行查詢） 都可以開啟此數量的檔案，如果查詢正在聯結多個資料表，則會耗盡此限制。
+ 減少連線的整體數量，並使用連線集區器，例如 [Amazon RDS Proxy](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy.html) 或其他解決方案，例如 PgBouncer。如需進一步了解，請參閱 [PgBouncer](https://www.pgbouncer.org/) 網站。
**影響：Inode 耗盡**
錯誤：「裝置上沒有剩餘空間」。如果觀察到這種情況，表示有足夠的可用儲存空間，這是因為索引用盡。[Amazon RDS 增強型監控](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_Monitoring.OS.html)可為使用中的節點提供可見性，以及主機可用的最大數量。
**一般最佳實務：**
+ 使用 [Performance Insights](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.html)監控您的暫存檔案用量。
+ 調校正在產生重要暫存檔案的查詢，以查看是否可以減少暫存檔案的總數。
**大約閾值：**[數千](#PostgreSQL.HighObjectCount.Note)
## 序列
序列是用於在 PostgreSQL 中自動遞增資料欄的基礎物件，可為資料提供唯一性和金鑰。這些可用於個別資料表，在正常操作期間不會產生任何後果，但邏輯複寫除外。
在 PostgreSQL 中，邏輯複寫目前不會將序列的目前值複寫至任何訂閱者。若要進一步了解，請參閱 [ PostgreSQL 文件中的限制頁面](https://www.postgresql.org/docs/current/logical-replication-restrictions.html)。
**影響：延長切換時間**
如果您計劃將 [Amazon RDS 藍/綠部署](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-green-deployments.html)用於任何類型的組態變更或升級，請務必了解大量序列對切換的影響。切換的最後一個階段之一將同步序列的目前值，如果有數千個，這將增加整體切換時間。
**建議的動作：**如果您的資料庫工作負載允許使用共用 UUID 而非sequence-per-table的方法，這會在切換期間減少同步步驟。
**大約閾值：**[數千](#PostgreSQL.HighObjectCount.Note)
## 大型物件
大型物件存放在名為 pg\$1largeobject 的單一系統資料表中。每個大型物件在系統資料表 pg\$1largeobject\$1metadata 中也有一個項目。這些物件的建立、修改和清除與標準關係截然不同。大型物件不是由自動清空處理，必須透過稱為 vacuumlo 的個別程序定期清理。如需管理大型物件的範例，請參閱使用 lo 模組管理大型物件。
**影響：邏輯複寫**
在邏輯複寫期間，目前不會在 PostgreSQL 中複寫大型物件。若要進一步了解，請參閱 [ PostgreSQL 文件中的限制頁面](https://www.postgresql.org/docs/current/logical-replication-restrictions.html)。在[藍色/綠色](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-green-deployments.html)組態中，這表示藍色環境中的大型物件不會複寫至綠色環境。
**影響：主要版本升級**
如果有數百萬個大型物件，且執行個體無法在升級期間處理它們，則升級可能會耗盡記憶體並失敗。PostgreSQL 主要版本升級程序包含兩個大階段：透過 pg\$1dump 傾印結構描述，並透過 pg\$1restore 還原結構描述。如果您的資料庫有數百萬個大型物件，您需要確保您的執行個體在升級期間有足夠的記憶體來處理 pg\$1dump 和 pg\$1restore，並將其擴展到更大的執行個體類型。
**一般最佳實務：**
+ 定期使用 vacuumlo 公用程式移除您可能擁有的任何孤立大型物件。
+ 考慮使用 BYTEA 資料類型將大型物件存放在資料庫中。
**大約閾值：**[百萬](#PostgreSQL.HighObjectCount.Note)
## 大約閾值
本主題中提到的大約閾值僅用於提供特定資源可擴展程度的估計。它們代表一般範圍，其中描述的影響變得更有可能，但實際行為取決於您的特定工作負載、執行個體大小和組態。雖然可能超過這些預估值，但必須遵守注意和維護，以避免列出的影響。
# 在 Amazon RDS for PostgreSQL 中管理 TOAST OID 爭用
TOAST （超大型屬性儲存技術） 是一種 PostgreSQL 功能，旨在處理超過一般 8KB 資料庫區塊大小的大型資料值。PostgreSQL 不允許實體資料列跨越多個區塊。區塊大小可做為資料列大小的上限。TOAST 透過將大型欄位值分割為較小的區塊來克服此限制。它將它們分別存放在連結到主資料表的專用 TOAST 資料表中。如需詳細資訊，請參閱 [PostgreSQL TOAST 儲存機制和實作文件](https://www.postgresql.org/docs/current/storage-toast.html)。
**Topics**
+ [
## 了解 TOAST 操作
](#Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.HowWorks)
+ [
## 識別效能挑戰
](#Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.PerformanceChallenges)
+ [
## 建議
](#Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.Recommendations)
+ [
## 監控
](#Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.Monitoring)
## 了解 TOAST 操作
TOAST 會執行壓縮，並離線存放大型欄位值。TOAST 會將唯一的 OID （物件識別符） 指派給儲存在 TOAST 資料表中的每個超大型資料區塊。主資料表會將 TOAST 值 ID 和關係 ID 存放在頁面上，以參考 TOAST 資料表中對應的資料列。這可讓 PostgreSQL 有效率地尋找和管理這些 TOAST 區塊。不過，隨著 TOAST 資料表的成長，系統可能會耗盡可用的 OIDs，導致效能降低和因 OID 耗盡而可能停機。
### TOAST 中的物件識別符
物件識別符 (OID) 是 PostgreSQL 用於參考資料庫物件的全系統唯一識別符，例如資料表、索引和函數。這些識別符在 PostgreSQL 的內部操作中扮演重要角色，可讓資料庫有效率地尋找和管理物件。
對於具有合格資料集的資料表，PostgreSQL 會指派 OIDs，以唯一識別存放在相關聯 TOAST 資料表中的每個超大型資料區塊。系統會將每個區塊與 建立關聯`chunk_id`，這有助於 PostgreSQL 在 TOAST 資料表中有效率地組織和尋找這些區塊。
## 識別效能挑戰
PostgreSQL 的 OID 管理倚賴全域 32 位元計數器，以便在產生 40 億個唯一值之後進行包裝。當資料庫叢集共用此計數器時，OID 配置在 TOAST 操作期間涉及兩個步驟：
+ 用於**配置的全域計數器** – 全域計數器會跨叢集指派新的 OID。
+ **本機搜尋衝突** – TOAST 資料表可確保新的 OID 不會與該特定資料表中已使用的現有 OIDs 衝突。
發生下列情況時，可能會發生效能降低：
+ TOAST 資料表具有高分段或密集的 OID 用量，導致指派 OID 時發生延遲。
+ 系統經常在具有大量使用 TOAST 的高資料流失或寬資料表的環境中配置和重複使用 OIDs。
如需詳細資訊，請參閱 [PostgreSQL TOAST 資料表大小限制和 OID 配置文件](https://wiki.postgresql.org/wiki/TOAST#Total_table_size_limit)：
全域計數器會產生 OIDs 並包圍每 40 億個值，因此系統偶爾會再次產生已使用的值。PostgreSQL 偵測到 ，並使用下一個 OID 再次嘗試。如果使用過的 OID 值執行非常長，且在 TOAST 資料表中沒有間隙，則可能會發生緩慢的 INSERT。隨著 OID 空間填滿，這些挑戰變得更加明顯，導致插入和更新速度變慢。
### 識別問題
+ 簡單`INSERT`陳述式會以不一致且隨機的方式，花費比平常更長的時間。
+ 只有涉及 TOAST 操作的 `INSERT`和 `UPDATE`陳述式才會發生延遲。
+ 當系統難以在 TOAST 資料表中尋找可用的 OIDs時，以下日誌項目會出現在 PostgreSQL 日誌中：
```
LOG: still searching for an unused OID in relation "pg_toast_20815"
DETAIL: OID candidates have been checked 1000000 times, but no unused OID has been found yet.
```
+ Performance Insights 指出與 `LWLock:buffer_io`和`LWLock:OidGenLock`等待事件相關聯的大量平均作用中工作階段 (AAS)。
您可以執行下列 SQL 查詢，以識別具有等待事件的長期執行 INSERT 交易：
```
SELECT
datname AS database_name,
usename AS database_user,
pid,
now() - pg_stat_activity.xact_start AS transaction_duration,
concat(wait_event_type, ':', wait_event) AS wait_event,
substr(query, 1, 30) AS TRANSACTION,
state
FROM
pg_stat_activity
WHERE (now() - pg_stat_activity.xact_start) > INTERVAL '60 seconds'
AND state IN ('active', 'idle in transaction', 'idle in transaction (aborted)', 'fastpath function call', 'disabled')
AND pid <> pg_backend_pid()
AND lower(query) LIKE '%insert%'
ORDER BY
transaction_duration DESC;
```
顯示具有延長等待時間的 INSERT 操作的查詢結果範例：
```
database_name | database_user | pid | transaction_duration | wait_event | transaction | state
---------------+-----------------+-------+----------------------+---------------------+--------------------------------+--------
postgres | db_admin_user| 70965 | 00:10:19.484061 | LWLock:buffer_io | INSERT INTO "products" (......... | active
postgres | db_admin_user| 69878 | 00:06:14.976037 | LWLock:buffer_io | INSERT INTO "products" (......... | active
postgres | db_admin_user| 68937 | 00:05:13.942847 | : | INSERT INTO "products" (......... | active
```
### 隔離問題
+ **測試小型插入** – 插入小於`toast_tuple_target`閾值的記錄。請記住，壓縮會在 TOAST 儲存體之前套用。如果此操作沒有效能問題，則問題與 TOAST 操作有關。
+ **測試新資料表** – 建立具有相同結構的新資料表，並插入大於 的記錄`toast_tuple_target`。如果這樣做沒有問題，問題會本地化為原始資料表的 OID 配置。
## 建議
下列方法有助於解決 TOAST OID 爭用問題。
+ **資料清除和封存** – 檢閱和刪除任何過時或不必要的資料，以釋出 OIDs 以供日後使用，或封存資料。考量下列限制：
+ 可擴展性有限，因為未來可能不一定可以進行清理。
+ 可能長時間執行的 VACUUM 操作，以移除產生的無效元組。
+ **寫入新資料表** – 為未來的插入建立新的資料表，並使用`UNION ALL`檢視來合併查詢的舊資料和新資料。此檢視會顯示舊資料表和新資料表的合併資料，允許查詢以單一資料表的形式存取它們。考量下列限制：
+ 舊資料表的更新仍可能導致 OID 耗盡。
+ **分割區或碎片** – 分割資料表或碎片資料，以獲得更佳的可擴展性和效能。考量下列限制：
+ 查詢邏輯和維護的複雜性更高，可能需要應用程式變更才能正確處理分割的資料。
## 監控
### 使用系統資料表
您可以使用 PostgreSQL 的系統資料表來監控 OID 用量的成長。
**警告**
根據 TOAST 資料表中的 OIDs 數量，可能需要一些時間才能完成。建議您在非上班時間排定監控，以將影響降至最低。
下列匿名區塊會計算每個 TOAST 資料表中使用的不同 OIDs 數目，並顯示父資料表資訊：
```
DO $$
DECLARE
r record;
o bigint;
parent_table text;
parent_schema text;
BEGIN
SET LOCAL client_min_messages TO notice;
FOR r IN
SELECT
c.oid,
c.oid::regclass AS toast_table
FROM
pg_class c
WHERE
c.relkind = 't'
AND c.relowner != 10 LOOP
-- Fetch the number of distinct used OIDs (chunk IDs) from the TOAST table
EXECUTE 'SELECT COUNT(DISTINCT chunk_id) FROM ' || r.toast_table INTO o;
-- If there are used OIDs, find the associated parent table and its schema
IF o <> 0 THEN
SELECT
n.nspname,
c.relname INTO parent_schema,
parent_table
FROM
pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE
c.reltoastrelid = r.oid;
-- Raise a concise NOTICE message
RAISE NOTICE 'Parent schema: % | Parent table: % | Toast table: % | Number of used OIDs: %', parent_schema, parent_table, r.toast_table, TO_CHAR(o, 'FM9,999,999,999,999');
END IF;
END LOOP;
END
$$;
```
依 TOAST 資料表顯示 OID 用量統計資料的範例輸出：
```
NOTICE: Parent schema: public | Parent table: my_table | Toast table: pg_toast.pg_toast_16559 | Number of used OIDs: 45,623,317
NOTICE: Parent schema: public | Parent table: my_table1 | Toast table: pg_toast.pg_toast_45639925 | Number of used OIDs: 10,000
NOTICE: Parent schema: public | Parent table: my_table2 | Toast table: pg_toast.pg_toast_45649931 | Number of used OIDs: 1,000,000
DO
```
下列匿名區塊會擷取每個非空白 TOAST 資料表指派的 OID 上限：
```
DO $$
DECLARE
r record;
o bigint;
parent_table text;
parent_schema text;
BEGIN
SET LOCAL client_min_messages TO notice;
FOR r IN
SELECT
c.oid,
c.oid::regclass AS toast_table
FROM
pg_class c
WHERE
c.relkind = 't'
AND c.relowner != 10 LOOP
-- Fetch the max(chunk_id) from the TOAST table
EXECUTE 'SELECT max(chunk_id) FROM ' || r.toast_table INTO o;
-- If there's at least one TOASTed chunk, find the associated parent table and its schema
IF o IS NOT NULL THEN
SELECT
n.nspname,
c.relname INTO parent_schema,
parent_table
FROM
pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE
c.reltoastrelid = r.oid;
-- Raise a concise NOTICE message
RAISE NOTICE 'Parent schema: % | Parent table: % | Toast table: % | Max chunk_id: %', parent_schema, parent_table, r.toast_table, TO_CHAR(o, 'FM9,999,999,999,999');
END IF;
END LOOP;
END
$$;
```
顯示 TOAST 資料表最大區塊 IDs的範例輸出：
```
NOTICE: Parent schema: public | Parent table: my_table | Toast table: pg_toast.pg_toast_16559 | Max chunk_id: 45,639,907
NOTICE: Parent schema: public | Parent table: my_table1 | Toast table: pg_toast.pg_toast_45639925 | Max chunk_id: 45,649,929
NOTICE: Parent schema: public | Parent table: my_table2 | Toast table: pg_toast.pg_toast_45649931 | Max chunk_id: 46,649,935
DO
```
### 使用 Performance Insights
在需要指派新物件識別符 (OIDs) 的操作期間，等待事件`LWLock:buffer_io`和 `LWLock:OidGenLock`會出現在績效詳情中。這些事件的高平均作用中工作階段 (AAS) 通常會在 OID 指派和資源管理期間指向爭用。這在具有高資料流失、大量大數據用量或頻繁建立物件的環境中特別常見。
#### LWLock：buffer\$1io
`LWLock:buffer_io` 是當 PostgreSQL 工作階段正在等待共用緩衝區上的 I/O 操作完成時發生的等待事件。當資料庫將資料從磁碟讀取到記憶體，或將修改過的頁面從記憶體寫入磁碟時，通常會發生這種情況。`BufferIO` 等待事件會防止多個程序在 I/O 操作進行時存取或修改相同的緩衝區，以確保一致性。此等待事件的頻繁出現可能表示資料庫工作負載中的磁碟瓶頸或過多的 I/O 活動。
在 TOAST 操作期間：
+ PostgreSQL 會為大型物件配置 OIDs，並透過掃描 TOAST 資料表的索引來確保其唯一性。
+ 大型 TOAST 索引可能需要存取多個頁面來驗證 OID 唯一性。這會導致磁碟 I/O 增加，特別是當緩衝集區無法快取所有必要的頁面時。
索引的大小會直接影響在這些操作期間需要存取的緩衝區頁面數目。即使索引未膨脹，其透薄大小也會增加緩衝區 I/O，尤其是在高並行或高流失環境中。如需詳細資訊，請參閱 [LWLock：BufferIO 等待事件疑難排解指南](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/apg-waits.lwlockbufferio.html)。
#### LWLock:OidGenLock
`OidGenLock` 是當 PostgreSQL 工作階段正在等待配置新的物件識別符 (OID) 時發生的等待事件。此鎖定可確保循序且安全地產生 OIDs，一次只允許一個程序產生 OIDs。
在 TOAST 操作期間：
+ **TOAST 資料表中區塊的 OID 配置** – PostgreSQL 會將 OIDs 指派給 TOAST 資料表中的區塊。每個 OID 都必須是唯一的，以防止系統目錄中的衝突。
+ **高並行** – 由於對 OID 產生器的存取是循序的，因此當多個工作階段同時建立需要 OIDs物件時，`OidGenLock`可能會發生 的爭用。這會增加工作階段等待 OID 配置完成的可能性。
+ **相依於系統目錄存取** – 配置 OIDs 需要更新共用系統目錄資料表，例如 `pg_class`和 `pg_type`。如果這些資料表遇到繁重的活動 （由於頻繁的 DDL 操作），可能會增加 的鎖定爭用`OidGenLock`。
+ **高 OID 分配需求** – 具有大型資料記錄的 TOAST 繁重工作負載需要持續的 OID 分配，從而增加爭用。
增加 OID 爭用的其他因素：
+ **頻繁建立物件** – 經常建立和捨棄物件的工作負載，例如暫存資料表，在全域 OID 計數器上擴增爭用。
+ **全域計數器鎖定** – 全域 OID 計數器會循序存取，以確保唯一性，在高並行環境中建立單一爭用點。
## 搭配使用 RDS for PostgreSQL 支援的記錄機制
您可以設定數個參數、擴充功能和其他可設定項目來記錄在 PostgreSQL 資料庫執行個體上發生的活動。這些索引標籤包括以下項目：
+ `log_statement` 參數可用來記錄 PostgreSQL 資料庫中的使用者活動。若要進一步了解 RDS for PostgreSQL 記錄和如何監控日誌，請參閱 [ RDS for PostgreSQL 資料庫日誌檔案](USER_LogAccess.Concepts.PostgreSQL.md)。
+ `rds.force_admin_logging_level` 參數可記錄 Amazon RDS 內部使用者 (rdsadmin) 在資料庫中對資料庫執行個體執行的動作。其會將輸出寫入 PostgreSQL 錯誤日誌。允許的值為：`disabled`、`debug5`、`debug4`、`debug3`、`debug2`、`debug1`、`info`、`notice`、`warning`、`error`、log、`fatal` 和 `panic`。預設值為 `disabled`。
+ `rds.force_autovacuum_logging_level` 參數可設定為在 PostgreSQL 錯誤日誌中擷取各種自動資料清理作業。如需詳細資訊，請參閱[記錄清理和自動資料清理活動](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.Logging.md)。
+ PostgreSQL Audit (pgAudit) 擴充功能可安裝並設定為擷取工作階段層級或物件層級的活動。如需詳細資訊，請參閱[使用 PgAudit 記錄資料庫活動](Appendix.PostgreSQL.CommonDBATasks.pgaudit.md)。
+ `log_fdw` 擴充功能可讓您使用 SQL 存取資料庫引擎日誌。如需詳細資訊，請參閱[使用 log\$1fdw 擴充功能存取使用 SQL 的資料庫日誌](CHAP_PostgreSQL.Extensions.log_fdw.md)。
+ `pg_stat_statements` 程式庫被指定為 RDS for PostgreSQL 第 10 版及更高版本中 `shared_preload_libraries` 參數的預設值。您可以使用這個程式庫來分析正在執行的查詢。請確定已在資料庫參數群組中設定 `pg_stat_statements`。如需使用此程式庫提供的資訊監控 RDS for PostgreSQL 資料庫執行個體的詳細資訊，請參閱 [RDS PostgreSQL 的 SQL 統計資料](USER_PerfInsights.UsingDashboard.AnalyzeDBLoad.AdditionalMetrics.PostgreSQL.md)。
+ `log_hostname` 參數會將每個用戶端連線的主機名稱擷取至日誌檔。對於 RDS for PostgreSQL 第 12 版及更新版本，此參數預設會設為 `off`。如果您將其開啟，請務必監控工作階段連線時間。開啟時，服務會使用網域名稱系統 (DNS) 反向查詢請求，來取得進行連線的用戶端主機名稱，並將其新增至 PostgreSQL 日誌。這會在工作階段連線期間產生明顯的影響。建議您開啟此參數，僅用於疑難排解目的。
一般來說，記錄是為了讓 DBA 監控情況、調校效能和排除故障。許多日誌都會自動上傳到 Amazon CloudWatch 或 Performance Insights。日誌會在這些地方進行排序和分組，以便為您的資料庫執行個體提供完整的指標。若要進一步了解 Amazon RDS 監控和指標，請參閱 [監控 Amazon RDS 執行個體中的指標](CHAP_Monitoring.md)。
# 使用 PostgreSQL 管理暫存檔案
在 PostgreSQL 中，複雜查詢可能會同時執行數個排序或雜湊操作，每個操作都使用執行個體記憶體來儲存結果，直到 [https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM) 參數中指定的值為止。當執行個體記憶體不足時，會建立暫存檔來儲存結果。這些檔案會寫入磁碟以完成查詢執行。稍後，這些檔案會在查詢完成後自動移除。在 RDS for PostgreSQL 中，這些檔案會儲存在資料磁碟區上的 Amazon EBS 中。如需詳細資訊，請參閱 [Amazon RDS 資料庫執行個體儲存體](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html)。您可以監控在 CloudWatch 中發佈的 `FreeStorageSpace` 指標，藉此確保資料庫執行個體有充足的可用儲存空間。如需詳細資訊，請參閱 [https://repost.aws/knowledge-center/storage-full-rds-cloudwatch-alarm](https://repost.aws/knowledge-center/storage-full-rds-cloudwatch-alarm)。
我們建議針對涉及多個並行查詢的工作負載使用 Amazon RDS Optimized Read 執行個體，以增加暫存檔案的使用量。這些執行個體會使用本機非揮發性記憶體快速 (NVMe) 為主的固態硬碟 (SSD) 區塊層級儲存體來放置暫存檔案。如需詳細資訊，請參閱 [使用 Amazon RDS Optimized Reads 改善 RDS for PostgreSQL 的查詢效能](USER_PostgreSQL.optimizedreads.md)。
您可以使用以下參數和函數來管理執行個體中的暫存檔案。
+ **[https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-DISK](https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-DISK)** – 此參數會取消任何超過 temp\$1file 大小的查詢 (以 KB 為單位)。此限制可防止任何查詢無休止地執行，並消耗含有暫存檔的磁碟空間。您可以使用 `log_temp_files` 參數的結果來估計值。最佳實務是檢查工作負載行為並根據估計值設定限制。以下範例顯示查詢超過限制時的取消方式。
```
postgres=>select * from pgbench_accounts, pg_class, big_table;
```
```
ERROR: temporary file size exceeds temp_file_limit (64kB)
```
+ **[https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-TEMP-FILES](https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-TEMP-FILES)** – 此參數會在移除工作階段的暫存檔案時，將訊息傳送至 postgresql.log。這個參數會在查詢順利完成後產生日誌。因此，它可能無助於疑難排解使用中、長時間執行的查詢。
下列範例顯示，當查詢順利完成時，這些項目會在清理暫存檔時記錄在 postgresql.log 檔案中。
```
2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp31236.5", size 140353536
2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:STATEMENT: select a.aid from pgbench_accounts a, pgbench_accounts b where a.bid=b.bid order by a.bid limit 10;
2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp31236.4", size 180428800
2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:STATEMENT: select a.aid from pgbench_accounts a, pgbench_accounts b where a.bid=b.bid order by a.bid limit 10;
```
+ **[https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-GENFILE](https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-GENFILE)** – 此功能可從 RDS for PostgreSQL 13 及以上版本使用，提供目前暫存檔案使用情況的可見性。完成的查詢不會出現在函數的結果中。在下列範例中，您可以檢視此函數的結果。
```
postgres=>select * from pg_ls_tmpdir();
```
```
name | size | modification
-----------------+------------+------------------------
pgsql_tmp8355.1 | 1072250880 | 2023-02-06 22:54:56+00
pgsql_tmp8351.0 | 1072250880 | 2023-02-06 22:54:43+00
pgsql_tmp8327.0 | 1072250880 | 2023-02-06 22:54:56+00
pgsql_tmp8351.1 | 703168512 | 2023-02-06 22:54:56+00
pgsql_tmp8355.0 | 1072250880 | 2023-02-06 22:54:00+00
pgsql_tmp8328.1 | 835031040 | 2023-02-06 22:54:56+00
pgsql_tmp8328.0 | 1072250880 | 2023-02-06 22:54:40+00
(7 rows)
```
```
postgres=>select query from pg_stat_activity where pid = 8355;
query
----------------------------------------------------------------------------------------
select a.aid from pgbench_accounts a, pgbench_accounts b where a.bid=b.bid order by a.bid
(1 row)
```
檔案名稱包含產生暫存檔之工作階段的處理 ID (PID)。較進階的查詢 (例如下列範例) 會針對每個 PID 執行暫存檔案的總和。
```
postgres=>select replace(left(name, strpos(name, '.')-1),'pgsql_tmp','') as pid, count(*), sum(size) from pg_ls_tmpdir() group by pid;
```
```
pid | count | sum
------+-------------------
8355 | 2 | 2144501760
8351 | 2 | 2090770432
8327 | 1 | 1072250880
8328 | 2 | 2144501760
(4 rows)
```
+ **`[ pg\$1stat\$1statements](https://www.postgresql.org/docs/current/pgstatstatements.html)`** – 如果您啟動 pg\$1stat\$1statements 參數，則可以檢視每次呼叫的平均暫存檔案使用量。您可以識別查詢的 query\$1id，並使用它來檢查暫存檔的使用情況，如以下範例所示。
```
postgres=>select queryid from pg_stat_statements where query like 'select a.aid from pgbench%';
```
```
queryid
----------------------
-7170349228837045701
(1 row)
```
```
postgres=>select queryid, substr(query,1,25), calls, temp_blks_read/calls temp_blks_read_per_call, temp_blks_written/calls temp_blks_written_per_call from pg_stat_statements where queryid = -7170349228837045701;
```
```
queryid | substr | calls | temp_blks_read_per_call | temp_blks_written_per_call
----------------------+---------------------------+-------+-------------------------+----------------------------
-7170349228837045701 | select a.aid from pgbench | 50 | 239226 | 388678
(1 row)
```
+ **`[Performance Insights](https://aws.amazon.com/rds/performance-insights/)`** – 在績效詳情儀表板中，您可以透過開啟指標 **temp\$1bytes** 和 **temp\$1files** 來檢視暫存檔的使用情況。然後，您可以查看這兩個指標的平均值，並查看它們如何對應至查詢工作負載。績效詳情中的檢視不會明確顯示產生暫存檔的查詢。不過，當您將績效詳情與為 `pg_ls_tmpdir` 顯示的查詢結合使用時，您可以疑難排解、分析及判斷查詢工作負載中的變更。
如需如何使用 Performance Insights 來分析指標和查詢的詳細資訊，請參閱 [使用績效詳情儀表板來分析指標](USER_PerfInsights.UsingDashboard.md)。
如需使用 Performance Insights 檢視暫存檔案使用量的範例，請參閱 [使用 Performance Insights 來檢視暫存檔使用情況](PostgreSQL.ManagingTempFiles.Example.md)
# 使用 Performance Insights 來檢視暫存檔使用情況
您可以使用 Performance Insights，透過開啟指標 **temp\$1bytes** 和 **temp\$1files** 來檢視暫存檔的使用情況。Performance Insights 中的檢視不會顯示產生暫存檔的特定查詢，但是，當您將 Performance Insights 與針對 `pg_ls_tmpdir` 顯示的查詢結合時，您可以疑難排解、分析和判斷查詢工作負載中的變更。
1. 在 [績效詳情] 儀表板中選擇**管理指標**。
1. 選擇**資料庫指標**，並選取 **temp\$1bytes** 和 **temp\$1files** 指標，如下方影像所示。
![\[指標會顯示在圖形中。\]](http://docs.aws.amazon.com/zh_tw/AmazonRDS/latest/UserGuide/images/rpg_mantempfiles_metrics.png)
1. 在**最高 SQL**索引標籤中，選擇**偏好設定**圖示。
1. 在**偏好設定**視窗中，開啟**最高 SQL**索引標籤中顯示的下列統計資料，然後選擇**繼續**。
+ Temp writes/sec
+ Temp reads/sec
+ Tmp blk write/call
+ Tmp blk read/call
1. 暫存檔在與針對 `pg_ls_tmpdir` 顯示的查詢組合時會被劃分，如以下範例所示。
![\[顯示暫存檔使用情況的查詢。\]](http://docs.aws.amazon.com/zh_tw/AmazonRDS/latest/UserGuide/images/rpg_mantempfiles_query.png)
當工作負載中最常用的查詢經常建立暫存檔案時，就會發生 `IO:BufFileRead` 和 `IO:BufFileWrite` 事件。您可以使用 Performance Insights，透過檢閱「資料庫負載」和「最高 SQL」區段中的「平均作用中工作階段」(AAS)，找出最常在 `IO:BufFileRead` 和 `IO:BufFileWrite` 上等待的查詢。
![\[圖中的 IO:BufFileRead 和 IO:BufFileWrite。\]](http://docs.aws.amazon.com/zh_tw/AmazonRDS/latest/UserGuide/images/perfinsights_IOBufFile.png)
如需如何使用 Performance Insights 來分析各等待事件的最常用查詢和負載的詳細資訊，請參閱 [最高 SQL 索引標籤概觀](USER_PerfInsights.UsingDashboard.AnalyzeDBLoad.AdditionalMetrics.md#USER_PerfInsights.UsingDashboard.Components.AvgActiveSessions.TopLoadItemsTable.TopSQL)。您應找出並調整造成暫存檔案使用量及相關等待事件增加的查詢。如需這些等待事件和修補的詳細資訊，請參閱 [IO:BufFileRead 和 IO:BufFileWrite](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/wait-event.iobuffile.html)。
**注意**
[https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM) 參數可控制排序操作何時用完記憶體，以及將結果寫入暫存檔。我們建議您不要將此參數的設定變更為高於預設值，因為它會允許每個資料庫工作階段耗用更多記憶體。此外，執行複雜聯結和排序的單一工作階段可以執行平行操作，其中每個操作都會耗用記憶體。
最佳實務是，當您有具有多個聯結和排序的大型報表時，請使用 `SET work_mem` 命令在工作階段層級設定此參數。然後，變更僅套用於目前工作階段，不會全域變更該值。
## 使用 pgBadger 進行 PostgreSQL 的日誌分析
您可以使用 [pgbadger](http://dalibo.github.io/pgbadger/) 等日誌分析器來分析 PostgreSQL 日誌。pgBadger 文件會陳述 %l 模式 (工作階段或程序的日誌行) 應該是字首的一部分。不過，如果您將目前的 RDS `log_line_prefix` 當作參數提供給 pgBadger，它應該仍會產生報告。
例如，下列命令會使用 pgbadger 正確地格式化日期為 2014-02-04 的 Amazon RDS for PostgreSQL 日誌檔案。
```
./pgbadger -f stderr -p '%t:%r:%u@%d:[%p]:' postgresql.log.2014-02-04-00
```
## 使用 PgSNAPPER 監控 PostgreSQL
您可以使用 PgSNapper 協助定期收集與 Amazon RDS for PostgreSQL 效能相關的統計資訊和指標。如需詳細資訊，請參閱[使用 PGSnapper 監控 Amazon RDS for PostgreSQL 效能](https://aws.amazon.com/blogs/database/monitor-amazon-rds-for-postgresql-and-amazon-aurora-postgresql-performance-using-pgsnapper/)
# 在 RDS for PostgreSQL 中管理自訂轉換
PostgreSQL **中的類型轉換**是將值從一個資料類型轉換為另一個資料類型的程序。PostgreSQL 提供許多常見轉換的內建轉換，但您也可以建立自訂轉換來定義特定類型轉換應如何運作。
轉換指定如何執行從一個資料類型到另一個資料類型的轉換。例如，將文字轉換為`'123'`整數 `123`，或將數值`45.67`轉換為文字 `'45.67'`。
如需 PostgreSQL 轉換概念和語法的完整資訊，請參閱 [PostgreSQL CREATE CAST 文件](https://www.postgresql.org/docs/current/sql-createcast.html)。
從 RDS for PostgreSQL 13.23、14.20、15.15、16.11、17.7 和 18.1 版開始，您可以使用 rds\$1casts 擴充功能為內建類型安裝額外的轉換，同時仍然能夠為自訂類型建立自己的轉換。
**Topics**
+ [
## 安裝和使用 rds\$1casts 延伸模組
](#PostgreSQL.CustomCasts.Installing)
+ [
## 支援的轉換
](#PostgreSQL.CustomCasts.Supported)
+ [
## 建立或捨棄轉換
](#PostgreSQL.CustomCasts.Creating)
+ [
## 使用適當的內容策略建立自訂轉換
](#PostgreSQL.CustomCasts.BestPractices)
## 安裝和使用 rds\$1casts 延伸模組
若要建立`rds_casts`擴充功能，請將 RDS for 執行個體，`rds_superuser`並執行下列命令： PostgreSQL
```
CREATE EXTENSION IF NOT EXISTS rds_casts;
```
## 支援的轉換
在您要使用自訂轉換的每個資料庫中建立延伸。建立擴充功能後，請使用下列命令來檢視所有可用的轉換：
```
SELECT * FROM rds_casts.list_supported_casts();
```
此函數會列出可用的轉換組合 （來源類型、目標類型、強制內容和轉換函數）。例如，如果您想要`text`將 建立`numeric`為 `implicit` 轉換。您可以使用下列查詢來尋找轉換是否可供建立：
```
SELECT * FROM rds_casts.list_supported_casts()
WHERE source_type = 'text' AND target_type = 'numeric';
id | source_type | target_type | qualified_function | coercion_context
----+-------------+-------------+--------------------------------------+------------------
10 | text | numeric | rds_casts.rds_text_to_numeric_custom | implicit
11 | text | numeric | rds_casts.rds_text_to_numeric_custom | assignment
13 | text | numeric | rds_casts.rds_text_to_numeric_custom | explicit
20 | text | numeric | rds_casts.rds_text_to_numeric_inout | implicit
21 | text | numeric | rds_casts.rds_text_to_numeric_inout | assignment
23 | text | numeric | rds_casts.rds_text_to_numeric_inout | explicit
```
rds\$1casts 延伸提供每種轉換函數的兩種類型：
+ *\$1inout 函數* - 使用 PostgreSQL 的標準 I/O 轉換機制，其行為與使用 INOUT 方法建立的轉換完全相同
+ *\$1custom 函數* - 提供處理邊緣案例的增強型轉換邏輯，例如將空字串轉換為 NULL 值以避免轉換錯誤
這些`inout`函數會複寫 PostgreSQL 的原生轉換行為，而`custom`函數會透過處理標準 INOUT 轉換無法容納的案例來擴展此功能，例如將空字串轉換為整數。
## 建立或捨棄轉換
您可以使用兩種方法建立和捨棄支援的轉換：
### 投射建立
**方法 1：使用原生 CREATE CAST 命令**
```
CREATE CAST (text AS numeric)
WITH FUNCTION rds_casts.rds_text_to_numeric_custom
AS IMPLICIT;
```
**方法 2：使用 rds\$1casts.create\$1cast 函數**
```
SELECT rds_casts.create_cast(10);
```
`create_cast` 函數會從`list_supported_casts()`輸出取得 ID。此方法更簡單，並確保您使用正確的函數和內容組合。此 ID 保證在不同 Postgres 版本中保持不變。
若要確認已成功建立轉換，請查詢 pg\$1cast 系統目錄：
```
SELECT oid, castsource::regtype, casttarget::regtype, castfunc::regproc, castcontext, castmethod
FROM pg_cast
WHERE castsource = 'text'::regtype AND casttarget = 'numeric'::regtype;
oid | castsource | casttarget | castfunc | castcontext | castmethod
--------+------------+------------+--------------------------------------+-------------+------------
356372 | text | numeric | rds_casts.rds_text_to_numeric_custom | i | f
```
`castcontext` 資料欄顯示：`e`適用於 EXPLICIT、`a`適用於 ASSIGNMENT 或`i`適用於 IMPLICIT。
### 捨棄轉換
**方法 1：使用 DROP CAST 命令**
```
DROP CAST IF EXISTS (text AS numeric);
```
**方法 2：使用 rds\$1casts.drop\$1cast 函數**
```
SELECT rds_casts.drop_cast(10);
```
`drop_cast` 函數會採用與建立投射時相同的 ID。此方法可確保您捨棄使用對應 ID 建立的確切轉換。
## 使用適當的內容策略建立自訂轉換
為整數類型建立多個轉換時，如果所有轉換都建立為 IMPLICIT，則可能會發生運算子模棱兩可錯誤。下列範例透過建立從文字到不同整數寬度的兩個隱含轉換來示範此問題：
```
-- Creating multiple IMPLICIT casts causes ambiguity
postgres=> CREATE CAST (text AS int4) WITH FUNCTION rds_casts.rds_text_to_int4_custom(text) AS IMPLICIT;
CREATE CAST
postgres=> CREATE CAST (text AS int8) WITH FUNCTION rds_casts.rds_text_to_int8_custom(text) AS IMPLICIT;
CREATE CAST
postgres=> CREATE TABLE test_cast(col int);
CREATE TABLE
postgres=> INSERT INTO test_cast VALUES ('123'::text);
INSERT 0 1
postgres=> SELECT * FROM test_cast WHERE col='123'::text;
ERROR: operator is not unique: integer = text
LINE 1: SELECT * FROM test_cast WHERE col='123'::text;
^
HINT: Could not choose a best candidate operator. You might need to add explicit type casts.
```
發生錯誤是因為 PostgreSQL 無法判斷在比較整數資料欄與文字值時要使用的隱含轉換。int4 和 int8 隱含轉換都是有效的候選項目，可建立模棱兩可。
若要避免此運算子模棱兩可的情況，請針對較小的整數寬度使用 ASSIGNMENT 內容，針對較大的整數寬度使用 IMPLICIT 內容：
```
-- Use ASSIGNMENT for smaller integer widths
CREATE CAST (text AS int2)
WITH FUNCTION rds_casts.rds_text_to_int2_custom(text)
AS ASSIGNMENT;
CREATE CAST (text AS int4)
WITH FUNCTION rds_casts.rds_text_to_int4_custom(text)
AS ASSIGNMENT;
-- Use IMPLICIT for larger integer widths
CREATE CAST (text AS int8)
WITH FUNCTION rds_casts.rds_text_to_int8_custom(text)
AS IMPLICIT;
postgres=> INSERT INTO test_cast VALUES ('123'::text);
INSERT 0 1
postgres=> SELECT * FROM test_cast WHERE col='123'::text;
col
-----
123
(1 row)
```
透過此策略，只有 int8 轉換是隱含的，因此 PostgreSQL 可以明確判斷要使用的轉換。
# RDS for PostgreSQL 中平行查詢的最佳實務
平行查詢執行是 PostgreSQL 的一項功能，可讓單一 SQL 查詢分割為由多個背景工作者程序同時處理的較小任務。PostgreSQL 可以跨多個 CPU 核心分配部分的查詢，例如掃描、聯結、彙總或排序，而不是完全在單一後端程序中執行查詢。*領導者程序*會協調此執行，並從*平行工作者*收集結果。
不過，對於大多數生產工作負載，特別是高並行 OLTP 系統，我們建議停用自動平行查詢執行。雖然平行處理可以加速分析或報告工作負載中大型資料集的查詢，但它帶來的重大風險通常超過忙碌生產環境中的優勢。
平行執行也會帶來大量的額外負荷。每個平行工作者都是完整的 PostgreSQL 後端程序，這需要程序偽造 （複製記憶體結構和初始化程序狀態） 和身分驗證 （從您的`max_connections`限制使用連線插槽）。每個工作者也會使用自己的記憶體，包括`work_mem`用於排序和雜湊操作，每個查詢有多個工作者，記憶體用量會快速增加 （例如，4 個工作者 × 64MB `work_mem` = 每個查詢 256MB)。因此，平行查詢可能會耗用比單一程序查詢更多的系統資源。如果未正確調校， 它們可能會導致 CPU 飽和 （多個工作者壓倒可用的處理容量）， 增加內容切換 （作業系統經常在許多工作者程序之間切換， 新增額外負荷並降低輸送量）、 或 連線耗盡 （因為每個平行工作者都會使用連線插槽， 具有 4 個工作者的單一查詢總共使用 5 個連線， 1 個領導者 \$1 4 個工作者， 可在高並行下快速耗盡連線集區， 防止新的用戶端連線並導致應用程式失敗）。在高並行工作負載中，這些問題特別嚴重，其中多個查詢可能會同時嘗試平行執行。
PostgreSQL 會根據成本估算決定是否使用平行處理。在某些情況下，規劃器可能會自動切換到平行計劃，即使實際上並不理想，它看起來更便宜。如果索引統計資料已過期，或者如果膨脹使循序掃描看起來比索引查詢更具吸引力，則可能會發生這種情況。由於這種行為，自動平行計畫有時可能會在查詢效能或系統穩定性中引入迴歸。
若要從 RDS for PostgreSQL 的平行查詢中獲得最大效益，請務必根據您的工作負載進行測試和調校、監控系統影響，以及停用自動平行計劃選擇，以有利於查詢層級控制。
## 組態參數
PostgreSQL 使用數個參數來控制平行查詢的行為和可用性。了解和調校這些對於實現可預測效能至關重要：
| 參數 | 描述 | 預設 |
| --- | --- | --- |
| max\$1parallel\$1workers | 可以執行的背景工作者程序總數上限 | GREATEST(\$1DBInstanceVCPU/2,8) |
| max\$1parallel\$1workers\$1per\$1gather | 每個查詢計劃節點的工作者數量上限 （例如，每個 Gather) | 2 |
| parallel\$1setup\$1cost | 新增啟動平行查詢基礎設施的規劃器成本 | 1000 |
| parallel\$1tuple\$1cost | 在平行模式下處理的每元組成本 （影響規劃器決策） | 0.1 |
| force\$1parallel\$1mode | 強制規劃器測試平行計劃 (off、on、regress) | off |
### 重要考量事項
+ `max_parallel_workers` 控制平行工作者的總集區。如果設定過低，某些查詢可能會回復到序列執行。
+ `max_parallel_workers_per_gather` 會影響單一查詢可以使用的工作者數量。較高的值會增加並行，但也會增加資源用量。
+ `parallel_setup_cost` 和 `parallel_tuple_cost`會影響規劃器的成本模型。降低這些值可能會讓平行計劃更容易被選擇。
+ `force_parallel_mode` 適用於測試，但除非必要，否則不應用於生產環境。
**注意**
參數的預設值`max_parallel_workers`會根據執行個體大小使用公式 動態計算`GREATEST($DBInstanceVCPU/2, 8)`。這表示當您將 DB 執行個體擴展到具有更多 vCPUs較大運算大小時，可用的平行工作者數量上限會自動增加。因此，先前連續執行或具有有限平行處理的查詢可能會在擴展操作後突然利用更多平行工作者，這可能會導致連線用量、CPU 使用率和記憶體耗用量意外增加。請務必監控任何運算擴展事件之後的平行查詢行為`max_parallel_workers_per_gather`，並視需要調整以維持可預測的資源用量。
## 識別平行查詢用量
查詢可能會根據資料分佈或統計資料轉換為平行計畫。例如：
```
SELECT count(*) FROM customers WHERE last_login < now() - interval '6 months';
```
此查詢可能會針對最近資料使用 索引，但切換到針對歷史資料的平行循序掃描。
您可以透過載入 `auto_explain`模組來記錄查詢執行計畫。若要進一步了解，請參閱 AWS 知識中心的[記錄查詢的執行計畫](https://aws.amazon.com/premiumsupport/knowledge-center/rds-postgresql-tune-query-performance/#)。
您可以監控 [CloudWatch Database Insights](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/Database-Insights-Database-Instance-Dashboard.html) 的平行查詢相關等待事件。若要進一步了解平行查詢相關的等待事件，請執行 [IPC：平行等待事件](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/apg-ipc-parallel.html)
從 PostgreSQL 第 18 版，您可以使用 [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)和 中的新資料欄來監控平行工作者活動[https://www.postgresql.org/docs/current/pgstatstatements.html](https://www.postgresql.org/docs/current/pgstatstatements.html)：
+ `parallel_workers_to_launch`：計劃啟動的平行工作者數量
+ `parallel_workers_launched`：實際啟動的平行工作者數量
這些指標有助於識別計劃和實際平行處理之間的差異，這可能表示資源限制或組態問題。使用下列查詢來監控平行執行：
對於資料庫層級平行工作者指標：
```
SELECT datname, parallel_workers_to_launch, parallel_workers_launched
FROM pg_stat_database
WHERE datname = current_database();
```
對於查詢層級平行工作者指標
```
SELECT query, parallel_workers_to_launch, parallel_workers_launched
FROM pg_stat_statements
ORDER BY parallel_workers_launched;
```
## 如何控制平行處理
有多種方法可以控制查詢平行處理，每個都針對不同的案例和需求而設計。
若要全域停用自動平行處理，請[修改參數群組](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.Modifying.html)以設定：
```
max_parallel_workers_per_gather = 0;
```
對於持久性的使用者特定設定，ALTER ROLE 命令提供設定參數的方法，這些參數將套用至特定使用者未來的所有工作階段。
例如：
`ALTER ROLE username SET max_parallel_workers_per_gather = 4;` 確保每次此使用者連線到資料庫時，其工作階段都會在需要時使用此平行工作者設定。
您可以使用 SET 命令來達成工作階段層級控制，該命令會修改目前資料庫工作階段期間的參數。當您需要暫時調整設定，而不會影響其他使用者或未來的工作階段時，這特別有用。一旦設定，這些參數會保持有效，直到明確重設或工作階段結束為止。命令非常簡單：
```
SET max_parallel_workers_per_gather = 4;
-- Run your queries
RESET max_parallel_workers_per_gather;
```
對於更精細的控制，SET LOCAL 可讓您修改單一交易的參數。當您需要調整交易中特定查詢集的設定時，這很理想，之後設定會自動回復到其先前的值。此方法有助於防止對相同工作階段中的其他操作產生意外影響。
## 診斷平行查詢行為
使用 `EXPLAIN (ANALYZE, VERBOSE)`確認查詢是否使用平行執行：
+ 尋找節點，例如 `Gather`、 `Gather Merge`或 `Parallel Seq Scan`。
+ 比較具有和沒有平行處理的計劃。
若要暫時停用平行處理以進行比較：
```
SET max_parallel_workers_per_gather = 0;
EXPLAIN ANALYZE ;
RESET max_parallel_workers_per_gather;
```
# 在 RDS for PostgreSQL 資料庫執行個體上搭配使用參數
某些情況下，您可能會在沒有指定自訂參數群組的狀況下建立一個 RDS for PostgreSQL 資料庫執行個體。如果是這樣，系統會使用您所選擇 PostgreSQL 版本的預設參數群組來建立資料庫執行個體。例如，假設您使用 PostgreSQL 13.3 建立 RDS for PostgreSQL 資料庫執行個體。在這種情況下，系統會使用 PostgreSQL 第 13 版參數群組 (`default.postgres13`) 中的值來建立資料庫執行個體。
您也可以建立自己的自訂資料庫參數群組。如果要從預設值中修改 RDS for PostgreSQL 資料庫執行個體的任何設定，則需要執行此操作。若要了解作法，請參閱[Amazon RDS 的參數群組](USER_WorkingWithParamGroups.md)。
您可以使用多種不同的方式追蹤 RDS for PostgreSQL 資料庫執行個體上的設定。您可以使用 AWS 管理主控台、 AWS CLI或 Amazon RDS API。您也可以查詢執行個體 PostgreSQL `pg_settings` 資料表中的值，如下所示。
```
SELECT name, setting, boot_val, reset_val, unit
FROM pg_settings
ORDER BY name;
```
若要進一步了解此查詢傳回的值，請參閱 PostgreSQL 說明文件中的 [https://www.postgresql.org/docs/current/view-pg-settings.html](https://www.postgresql.org/docs/current/view-pg-settings.html)。
為 RDS for PostgreSQL 資料庫執行個體更改 `max_connections` 和 `shared_buffers` 的設定時請格外小心。例如，假設您修改 `max_connections` 或 `shared_buffers` 的設定並且使用的值對於實際工作負載而言過高。這種情況下，RDS for PostgreSQL 資料庫執行個體將無法啟動。如果發生這種情況，`postgres.log` 中會顯示錯誤，如下所示。
```
2018-09-18 21:13:15 UTC::@:[8097]:FATAL: could not map anonymous shared memory: Cannot allocate memory
2018-09-18 21:13:15 UTC::@:[8097]:HINT: This error usually means that PostgreSQL's request for a shared memory segment
exceeded available memory or swap space. To reduce the request size (currently 3514134274048 bytes), reduce
PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.
```
但是，您無法更改預設 RDS for PostgreSQL 資料庫參數群組中包含的任何設定值。若要變更任何參數的設定，請首先建立自訂資料庫參數群組。然後變更該自訂資料群組中的設定，再將自訂參數群組套用於 RDS for PostgreSQL 資料庫執行個體。如需詳細資訊，請參閱 [Amazon RDS 的參數群組](USER_WorkingWithParamGroups.md)。
RDS for PostgreSQL 中的參數有兩種類型。
+ **靜態參數** – 靜態參數變更後需重新啟動 RDS for PostgreSQL 資料庫執行個體，新值才會生效。
+ **動態參數** – 動態參數的設定變更後不需要重新啟動。
**注意**
如果 RDS for PostgreSQL 資料庫執行個體使用自己的自訂資料庫參數群組，您可以變更正在執行中的資料庫執行個體上的動態參數值。您可以使用 AWS 管理主控台、 AWS CLI或 Amazon RDS API 來執行此操作。
如果您擁有權限，也可以使用 `ALTER DATABASE`、`ALTER ROLE` 和 `SET` 命令來變更參數值。
## RDS for PostgreSQL DB 資料庫執行個體參數清單
下表列出 RDS for PostgreSQL 資料庫執行個體中的一些 (但並非全部) 可用參數。若要檢視所有可用的參數，請使用 [describe-db-parameters](https://docs.aws.amazon.com/cli/latest/reference/rds/describe-db-parameters.html) AWS CLI 命令。例如，若要取得預設參數群組中 RDS for PostgreSQL 第 13 版可用的所有參數清單，請執行下列命令。
```
aws rds describe-db-parameters --db-parameter-group-name default.postgres13
```
您也可以使用主控台。從 Amazon RDS 功能表中選擇 **Parameter groups** (參數群組)，然後從 AWS 區域中可用的參數群組中選擇參數群組。
| 參數名稱 | Apply\$1Type | 描述 |
| --- | --- | --- |
| `application_name` | 動態 | 設定要在統計資訊和日誌中報告的應用程式名稱。 |
| `archive_command` | 動態 | 設定將呼叫的 shell 命令來封存 WAL 檔案。 |
| `array_nulls` | 動態 | 能夠在陣列中輸入 NULL 元素。 |
| `authentication_timeout` | 動態 | 設定要完成用戶端驗證的最大允許時間。 |
| `autovacuum` | 動態 | 啟動自動資料清理子程序。 |
| `autovacuum_analyze_scale_factor` | 動態 | 在分析之前的 tuple 插入、更新或刪除數目 (成為 reltuple 的一小部分)。 |
| `autovacuum_analyze_threshold` | 動態 | 在分析之前的 tuple 插入、更新或刪除數目下限。 |
| `autovacuum_freeze_max_age` | 靜態 | 要自動資料清理資料表以防止交易 ID 包圍的存留期。 |
| `autovacuum_naptime` | 動態 | 自動資料清理執行之間的休眠時間。 |
| `autovacuum_max_workers` | 靜態 | 設定同時執行自動清空工作者程序的數目上限。 |
| `autovacuum_vacuum_cost_delay` | 動態 | 自動資料清理的清理成本延遲 (以毫秒為單位)。 |
| `autovacuum_vacuum_cost_limit` | 動態 | 自動資料清理在小憩前可用的清理成本金額。 |
| `autovacuum_vacuum_scale_factor` | 動態 | 在清理之前的 tuple 更新或刪除數目 (成為 reltuple 的一小部分)。 |
| `autovacuum_vacuum_threshold` | 動態 | 在清理之前的 tuple 更新或刪除數目下限。 |
| `backslash_quote` | 動態 | 設定在字串常值中是否允許反斜線 (\$1)。 |
| `bgwriter_delay` | 動態 | 回合之間的背景寫入器休眠時間。 |
| `bgwriter_lru_maxpages` | 動態 | 每回合要清空之 LRU 頁面的背景寫入器數目上限。 |
| `bgwriter_lru_multiplier` | 動態 | 每回合要釋放之平均緩衝區使用量的倍數。 |
| `bytea_output` | 動態 | 設定位元組的輸出格式。 |
| `check_function_bodies` | 動態 | 在 CREATE FUNCTION 期間檢查函數本文。 |
| `checkpoint_completion_target` | 動態 | 在檢查點期間用於清空已變更緩衝區的時間 (成為檢查點間隔的一小部分)。 |
| `checkpoint_segments` | 動態 | 設定日誌區段中自動預寫入日誌 WAL 檢查點之間的最大距離。 |
| `checkpoint_timeout` | 動態 | 設定自動 WAL 檢查點之間的最長時間。 |
| `checkpoint_warning` | 動態 | 啟用檢查點區段的填入頻率高於此值時的警告。 |
| `client_connection_check_interval` | 動態 | 設定執行查詢時檢查是否中斷連線的時間間隔。 |
| `client_encoding` | 動態 | 設定用戶端的字元集編碼。 |
| `client_min_messages` | 動態 | 設定傳送給用戶端的訊息層級。 |
| `commit_delay` | 動態 | 設定在交易認可與將 WAL 清空至磁碟之間的延遲 (以毫秒為單位)。 |
| `commit_siblings` | 動態 | 設定在執行 commit\$1delay 之前同時開啟的交易數目下限。 |
| `constraint_exclusion` | 動態 | 讓規劃器能夠使用限制條件來最佳化查詢。 |
| `cpu_index_tuple_cost` | 動態 | 設定規劃器對於在索引掃描期間處理每個索引項目的成本估算。 |
| `cpu_operator_cost` | 動態 | 設定規劃器對於處理每個運算子或函數呼叫的成本估算。 |
| `cpu_tuple_cost` | 動態 | 設定規劃器對於處理每個 tuple (資料列) 的成本估算。 |
| `cursor_tuple_fraction` | 動態 | 設定規劃器對於將擷取之游標的資料列部分估算。 |
| `datestyle` | 動態 | 設定日期和時間值的顯示格式。 |
| `deadlock_timeout` | 動態 | 設定在檢查死鎖前等待鎖定的時間。 |
| `debug_pretty_print` | 動態 | 將剖析和計劃樹狀顯示縮排。 |
| `debug_print_parse` | 動態 | 記錄每項查詢的剖析樹狀。 |
| `debug_print_plan` | 動態 | 記錄每項查詢的執行計劃。 |
| `debug_print_rewritten` | 動態 | 記錄每項查詢的重寫剖析樹狀。 |
| `default_statistics_target` | 動態 | 設定預設統計資訊目標。 |
| `default_tablespace` | 動態 | 設定要在其中建立資料表和索引的預設資料表空間。 |
| `default_transaction_deferrable` | 動態 | 設定新交易的預設可延遲狀態。 |
| `default_transaction_isolation` | 動態 | 設定每項新交易的交易隔離層級。 |
| `default_transaction_read_only` | 動態 | 設定新交易的預設唯讀狀態。 |
| `default_with_oids` | 動態 | 依預設建立內含物件 ID (OID) 的新資料表。 |
| `effective_cache_size` | 動態 | 設定規劃器對於磁碟快取大小的假設。 |
| `effective_io_concurrency` | 動態 | 磁碟子系統可以有效處理的同時要求數目。 |
| `enable_bitmapscan` | 動態 | 讓規劃器能夠使用點陣圖掃描計劃。 |
| `enable_hashagg` | 動態 | 讓規劃器能夠使用雜湊彙整計劃。 |
| `enable_hashjoin` | 動態 | 讓規劃器能夠使用雜湊聯結計劃。 |
| `enable_indexscan` | 動態 | 讓規劃器能夠使用索引掃描計劃。 |
| `enable_material` | 動態 | 讓規劃器能夠使用實體化。 |
| `enable_mergejoin` | 動態 | 讓規劃器能夠使用合併聯結計劃。 |
| `enable_nestloop` | 動態 | 讓規劃器能夠使用巢狀迴圈聯結計劃。 |
| `enable_seqscan` | 動態 | 讓規劃器能夠使用循序掃描計劃。 |
| `enable_sort` | 動態 | 讓規劃器能夠使用明確排序步驟。 |
| `enable_tidscan` | 動態 | 讓規劃器能夠使用 TID 掃描計劃。 |
| `escape_string_warning` | 動態 | 一般字串常值中反斜線 (\$1) 逸出的相關警告。 |
| `extra_float_digits` | 動態 | 設定針對浮點值顯示的位數。 |
| `from_collapse_limit` | 動態 | 設定 FROM-list 大小，超過此大小就不會收合子查詢。 |
| `fsync` | 動態 | 強制將更新同步至磁碟。 |
| `full_page_writes` | 動態 | 在檢查點後第一次修改時，將完整頁面寫入至 WAL。 |
| `geqo` | 動態 | 啟用基因查詢最佳化。 |
| `geqo_effort` | 動態 | GEQO：作業是用於設定其他 GEQO 參數的預設值。 |
| `geqo_generations` | 動態 | GEQO：演算法的反覆運算次數。 |
| `geqo_pool_size` | 動態 | GEQO：人口中的個體數目。 |
| `geqo_seed` | 動態 | GEQO：隨機路徑選取的種子。 |
| `geqo_selection_bias` | 動態 | GEQO：人口中的選擇壓力。 |
| `geqo_threshold` | 動態 | 設定 FROM 項目的閾值，超出此閾值時就會使用 GEQO。 |
| `gin_fuzzy_search_limit` | 動態 | 設定 GIN 確切搜尋所允許的結果上限。 |
| `hot_standby_feedback` | 動態 | 決定熱待命是否將意見回饋訊息傳送給主要或上游待命。 |
| `intervalstyle` | 動態 | 設定間隔值的顯示格式。 |
| `join_collapse_limit` | 動態 | 設定 FROM-list 大小，超過此大小就不會將 JOIN 結構扁平化。 |
| `lc_messages` | 動態 | 設定用來顯示訊息的語言。 |
| `lc_monetary` | 動態 | 設定用於格式化貨幣金額的地區設定。 |
| `lc_numeric` | 動態 | 設定用於格式化數字的地區設定。 |
| `lc_time` | 動態 | 設定用於格式化日期和時間值的地區設定。 |
| `log_autovacuum_min_duration` | 動態 | 設定執行時間下限，超出此時間就會記錄自動資料清理動作。 |
| `log_checkpoints` | 動態 | 記錄每個檢查點。 |
| `log_connections` | 動態 | 記錄每個成功連線。 |
| `log_disconnections` | 動態 | 記錄工作階段的結尾，包括持續時間。 |
| `log_duration` | 動態 | 記錄每個已完成 SQL 陳述式的持續時間。 |
| `log_error_verbosity` | 動態 | 設定已記錄訊息的詳細資訊。 |
| `log_executor_stats` | 動態 | 將執行器效能統計資訊寫入至伺服器日誌。 |
| `log_filename` | 動態 | 設定日誌檔案的檔案名稱樣式。 |
| `log_file_mode` | 動態 | 設定日誌檔案的許可。預設值為 0644。 |
| `log_hostname` | 動態 | 在連線日誌中記錄主機名稱。從 PostgreSQL 12 及更新版本開始，此參數預設為 'off'。開啟時，連線會使用 DNS 反向查詢來取得擷取至連線日誌的主機名稱。如果開啟此參數，您應該監控它對建立連線所需時間的影響。 |
| `log_line_prefix ` | 動態 | 控制每個日誌行前綴的資訊。 |
| `log_lock_waits` | 動態 | 記錄長鎖定等待。 |
| `log_min_duration_statement` | 動態 | 設定執行時間下限，超出此時間就會記錄陳述式。 |
| `log_min_error_statement` | 動態 | 導致所有陳述式在這個層級或以上產生要記錄的錯誤。 |
| `log_min_messages` | 動態 | 設定所記錄的訊息層級。 |
| `log_parser_stats` | 動態 | 將剖析器效能統計資訊寫入至伺服器日誌。 |
| `log_planner_stats` | 動態 | 將規劃器效能統計資訊寫入至伺服器日誌。 |
| `log_rotation_age` | 動態 | 自動日誌檔案輪換將在 N 分鐘後發生。 |
| `log_rotation_size` | 動態 | 自動日誌檔案輪換將在 N KB 後發生。 |
| `log_statement` | 動態 | 設定已記錄的陳述式類型。 |
| `log_statement_stats` | 動態 | 將累積效能統計資訊寫入至伺服器日誌。 |
| `log_temp_files` | 動態 | 記錄大於此 KB 數的暫時檔案使用。 |
| `log_timezone` | 動態 | 設定要在日誌訊息中使用的時區。 |
| `log_truncate_on_rotation` | 動態 | 在日誌輪換期間截斷名稱相同的現有日誌檔案。 |
| `logging_collector` | 靜態 | 開始子程序，將 stderr 輸出和/或 csvlog 擷取到日誌檔案中。 |
| `maintenance_work_mem` | 動態 | 設定要用於維護作業的記憶體上限。 |
| `max_connections` | 靜態 | 設定同時連線的數目上限。 |
| `max_files_per_process` | 靜態 | 設定每個伺服器程序的同時開啟檔案數目上限。 |
| `max_locks_per_transaction` | 靜態 | 設定每項交易的鎖定數目上限。 |
| `max_pred_locks_per_transaction` | 靜態 | 設定每項交易的述詞鎖定數目上限。 |
| `max_prepared_transactions` | 靜態 | 設定同時備妥交易的數目上限。 |
| `max_stack_depth` | 動態 | 設定堆疊深度上限 (以 KB 為單位)。 |
| `max_standby_archive_delay` | 動態 | 當熱待命伺服器正在處理已封存的 WAL 資料時，設定在取消查詢前的延遲上限。 |
| `max_standby_streaming_delay` | 動態 | 當熱待命伺服器正在處理已串流的 WAL 資料時，設定在取消查詢前的延遲上限。 |
| max\$1wal\$1size | 動態 | 設定觸發檢查點的 WAL 大小 (MB)。[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_tw/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Parameters.html) 在 Amazon RDS for PostgreSQL 資料庫執行個體上使用下列命令，以查看其現行值： SHOW max_wal_size;
|
| min\$1wal\$1size | 動態 | 設定 WAL 縮減到最小的大小。Postgre SQL 9.6 版和更早的版本， min\$1wal\$1size 單位是 16 MB。Postgre SQL 10 版和更新版本， min\$1wal\$1size 單位是 1 MB。 |
| `quote_all_identifiers` | 動態 | 在產生 SQL 片段時，將引號 (") 新增至所有識別符。 |
| `random_page_cost` | 動態 | 設定規劃器對於非循序擷取磁碟頁面的成本估算。除非開啟查詢計劃管理 (QPM)，否則此參數並無任何值。開啟 QPM 時，此參數的預設值為 4。 |
| rds.adaptive\$1autovacuum | 動態 | 當交易 ID 閥值超過時，隨時自動調整自動資料清理的參數。 |
| rds.force\$1ssl | 動態 | 需要使用 SSL 連線。對於 RDS for PostgreSQL 第 15 版，預設值會設定為 1 (開啟)。所有其他 RDS for PostgreSQL 主要版本 14 及更舊版本都會將預設值設定為 0 (關閉)。 |
| `rds.local_volume_spill_enabled` | 靜態 | 允許將邏輯溢出檔案寫入本機磁碟區。 |
| `rds.log_retention_period` | 動態 | 設定日誌保留期，像是 Amazon RDS 超過 n 分鐘時刪除 PostgreSQL 日誌。 |
| rds.rds\$1superuser\$1reserved\$1connections | 靜態 | 設定為 rds\$1superusers 保留的連線槽數。此參數僅適用於第 15 版和更舊版本。如需詳細資訊，請參閱 PostgreSQL 文件 [reserved\$1connections](https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-RESERVED-CONNECTIONS)。 |
| `rds.replica_identity_full` | 動態 | 當您將此參數設定為 `on` 時，它會將所有資料庫資料表的複本身分設定覆寫為 `FULL`。這表示無論 `REPLICA IDENTITY FULL` 設定為何，所有資料欄值都會寫入預寫日誌 (WAL)。 由於額外的 WAL 記錄，開啟此參數可能會增加資料庫執行個體 IOPS。 |
| rds.restrict\$1password\$1commands | 靜態 | 限制可以管理具有 rds\$1password 角色之使用者密碼的人員。將此參數設為 1 可啟用密碼限制。預設為 0。 |
| `search_path` | 動態 | 針對不符合結構描述的名稱設定結構描述搜尋順序。 |
| `seq_page_cost` | 動態 | 設定規劃器對於循序擷取磁碟頁面的成本估算。 |
| `session_replication_role` | 動態 | 設定觸發器和重寫規則的工作階段行為。 |
| `shared_buffers` | 靜態 | 設定伺服器所用的共享記憶體緩衝區數目。 |
| `shared_preload_libraries ` | 靜態 | 列出要預先載入至 RDS for PostgreSQL 資料庫執行個體的共用程式庫。支援的值包括 auto\$1explain、orafce、pgaudit、pglogical、pg\$1bigm、pg\$1cron、pg\$1hint\$1plan、pg\$1prewarm、pg\$1similarity、pg\$1stat\$1statements、pg\$1tle, pg\$1transport、plprofiler 及 plrust。 |
| `ssl` | 動態 | 啟用 SSL 連線。 |
| `sql_inheritance` | 動態 | 導致子資料表依預設包含在各種命令中。 |
| `ssl_renegotiation_limit` | 動態 | 設定在重新商談加密金鑰前要傳送和接收的流量。 |
| `standard_conforming_strings` | 動態 | 導致 ... 字串逐字地處理反斜線。 |
| `statement_timeout` | 動態 | 設定任何陳述式允許的持續時間上限。 |
| `synchronize_seqscans` | 動態 | 啟用已同步的循序掃描。 |
| `synchronous_commit` | 動態 | 設定目前交易的同步層級。 |
| `tcp_keepalives_count` | 動態 | TCP 保持連線重新傳輸的數量上限 |
| `tcp_keepalives_idle` | 動態 | 發出 TCP 保持連線之間的時間。 |
| `tcp_keepalives_interval` | 動態 | TCP 保持連線重新傳輸之間的時間。 |
| `temp_buffers` | 動態 | 設定每個工作階段所用的暫時緩衝區數目上限。 |
| temp\$1file\$1limit | 動態 | 設定暫時檔案可以增長的大小上限 (以 KB 為單位)。 |
| `temp_tablespaces` | 動態 | 設定要用於暫存資料表和排序檔案的資料表空間。 |
| `timezone` | 動態 | 設定可供顯示和解譯時間戳記的時區。 網際網路號碼分配局 (IANA) 每年會在 [https://www.iana.org/time-zones](https://www.iana.org/time-zones) 發布數次新時區。每次 RDS 發布 PostgreSQL 的新次要維護版本時，它都會在發布時隨附最新的時區資料。當您使用最新的 RDS for PostgreSQL 版本時，您會有來自 RDS 的最新時區資料。為確保您的資料庫執行個體擁有最新的時區資料，建議您升級至更高的資料庫引擎版本。您無法手動修改 PostgreSQL 資料庫執行個體中的時區表。RDS 不會修改或重設執行中資料庫執行個體的時區資料。只有在您執行資料庫引擎版本升級時，才會安裝新的時區資料。 |
| `track_activities` | 動態 | 收集有關執行命令的資訊。 |
| `track_activity_query_size` | 靜態 | 設定為 pg\$1stat\$1activity.current\$1query 保留的大小 (以位元組為單位)。 |
| `track_counts` | 動態 | 收集資料庫活動的統計資訊。 |
| `track_functions` | 動態 | 收集資料庫活動的功能層級統計資訊。 |
| `track_io_timing` | 動態 | 收集資料庫輸入/輸出活動的計時統計資訊。 |
| `transaction_deferrable` | 動態 | 指出是否延遲唯讀可序列化交易，直到可加以啟動為止 (沒有可能的序列化失敗)。 |
| `transaction_isolation` | 動態 | 設定目前交易的隔離層級。 |
| `transaction_read_only` | 動態 | 設定目前交易的唯讀狀態。 |
| `transform_null_equals` | 動態 | 將 expr=NULL 視為 expr IS NULL。 |
| `update_process_title` | 動態 | 更新程序標題以顯示作用中的 SQL 命令。 |
| `vacuum_cost_delay` | 動態 | 清理成本延遲 (以毫秒為單位)。 |
| `vacuum_cost_limit` | 動態 | 在小憩前可用的清理成本金額。 |
| `vacuum_cost_page_dirty` | 動態 | 清理所變更之頁面的清理成本。 |
| `vacuum_cost_page_hit` | 動態 | 在緩衝區快取中找到之頁面的清理成本。 |
| `vacuum_cost_page_miss` | 動態 | 在緩衝區快取中找不到之頁面的清理成本。 |
| `vacuum_defer_cleanup_age` | 動態 | 應延遲清理和熱清理 (如果有的話) 的交易數目。 |
| `vacuum_freeze_min_age` | 動態 | 清理應凍結資料表列的存留期下限。 |
| `vacuum_freeze_table_age` | 動態 | 清理應掃描整個資料表以凍結 tuple 的存留期。 |
| `wal_buffers` | 靜態 | 為 WAL 設定共享記憶體中的磁碟分頁緩衝區數目。 |
| `wal_writer_delay` | 動態 | WAL 清空之間的 WAL 寫入器休眠時間。 |
| `work_mem` | 動態 | 設定要用於查詢工作空間的記憶體上限。 |
| `xmlbinary` | 動態 | 設定有多少二進位值要在 XML 中編碼。 |
| `xmloption` | 動態 | 設定要將明確剖析和序列化作業中的 XML 資料視為文件或內容片段。 |
Amazon RDS 會對所有參數使用預設 PostgreSQL 單位。下表顯示每個參數的 PostgreSQL 預設單位。
| 參數名稱 | 單位 |
| --- | --- |
| `archive_timeout` | s |
| `authentication_timeout` | s |
| `autovacuum_naptime` | s |
| `autovacuum_vacuum_cost_delay` | ms |
| `bgwriter_delay` | ms |
| `checkpoint_timeout` | s |
| `checkpoint_warning` | s |
| `deadlock_timeout` | ms |
| `effective_cache_size` | 8 KB |
| `lock_timeout` | ms |
| `log_autovacuum_min_duration` | ms |
| `log_min_duration_statement` | ms |
| `log_rotation_age` | 分鐘 |
| `log_rotation_size` | KB |
| `log_temp_files` | KB |
| `maintenance_work_mem` | KB |
| `max_stack_depth` | KB |
| `max_standby_archive_delay` | ms |
| `max_standby_streaming_delay` | ms |
| `post_auth_delay` | s |
| `pre_auth_delay` | s |
| `segment_size` | 8 KB |
| `shared_buffers` | 8 KB |
| `statement_timeout` | ms |
| `ssl_renegotiation_limit` | KB |
| `tcp_keepalives_idle` | s |
| `tcp_keepalives_interval` | s |
| `temp_file_limit` | KB |
| `work_mem` | KB |
| `temp_buffers` | 8 KB |
| `vacuum_cost_delay` | ms |
| `wal_buffers` | 8 KB |
| `wal_receiver_timeout` | ms |
| `wal_segment_size` | B |
| `wal_sender_timeout` | ms |
| `wal_writer_delay` | ms |
| `wal_receiver_status_interval` | s |