使用大型索引管理自動清空 - Amazon Relational Database Service
盡快清空資料表

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

使用大型索引管理自動清空

作為其操作的一部分，自動清空會在資料表上執行時執行數個清空階段。在清除資料表之前，首先會清空其所有索引。移除多個大型索引時，此階段會耗用大量的時間和資源。因此，最佳實務是務必控制資料表上的索引數目，並清除未使用的索引。

在此程序中，首先檢查整體索引大小。然後，判斷是否有可以移除的潛在未用索引，如下列範例所示。

檢查資料表及其索引的大小

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)

在此範例中，索引的大小大於資料表。這種差異可能會導致性能問題，因為索引膨脹或未使用，這會影響自動清空以及插入操作。

檢查是否有未使用的索引

使用 pg_stat_user_indexes 檢視，您可以檢查索引與 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)
注意

這些統計資訊是從統計資訊重設時開始累計的。假設您有僅在營業季度結束時使用的索引，或僅用於特定報告的索引。自統計資訊重設以來，可能尚未使用此索引。如需詳細資訊，請參閱統計資訊函數。用來強制執行唯一性的索引不會執行掃描，也不應將其識別為未使用的索引。若要識別未使用的索引，您應該對應用程式及其查詢有深入的理解。

若要檢查上次何時重設資料庫的統計資訊，請使用 pg_stat_database

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 適用於 PostgreSQL 12 和更高版本

如果您在大型資料表中有太多索引，您的資料庫執行個體可能會接近交易 ID 包裝 （XID），也就是XID計數器包裝為零時。若保持取消核取的狀態，此情況可能會導致資料遺失。不過，您可以在不清除索引的情況下快速清空資料表。在 RDS for PostgreSQL 12 和更高版本中，您可以VACUUM搭配 INDEX_CLEANUP子句使用 。

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。如需執行手動清空凍結的相關資訊，請參閱 執行手動清理凍結。

注意

定期略過索引清除可能會導致索引膨脹，這會影響整體掃描效能。作為最佳實務，只使用上述程序來防止交易 ID 包圍。

RDS 適用於 PostgreSQL 11 及更舊版本

不過，RDS在 PostgreSQL 11 和更低版本中，允許更快完成真空的唯一方法是減少資料表上的索引數量。捨棄索引可能會影響查詢計畫。建議您先捨棄未使用的索引，然後在XID包裝非常接近時捨棄索引。在清空程序完成之後，您可以重新建立這些索引。