使用 lo 模組管理大型物件 - Amazon Aurora
安裝 lo 擴充功能使用 lo_manage 觸發程序函數使用 移除孤立的大型物件 vacuumlo了解 vacuumlo 的運作方式改善vacuumlo效能大型物件的考量事項

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

使用 lo 模組管理大型物件

lo 模組 （擴充功能） 適用於透過 JDBC或 ODBC驅動程式使用 PostgreSQL 資料庫的資料庫使用者和開發人員。JDBC 和 都ODBC預期資料庫會在參考大型物件變更時處理刪除。不過，PostgreSQL 無法以這種方式運作。PostgreSQL 不會假設物件在參考變更時應該刪除。結果是物件會保留在磁碟上，未被參照。lo 擴充功能包含一個功能，用於在參照變更時視需要觸發該功能以刪除物件。

提示

若要判斷資料庫是否可從 lo 延伸模組中獲益，請使用 vacuumlo公用程式來檢查孤立的大型物件。若要在不採取任何動作的情況下取得孤立大型物件的計數，請以 -n 選項執行該公用程式 (無操作)。若要了解如何操作，請參閱下文中的 vacuumlo utility。

lo 模組適用於 Aurora PostgreSQL 13.7、12.11、11.16、10.21 和更新次要版本。

若要安裝此模組 (擴充功能)，您需要 rds_superuser 權限。安裝 lo 擴充功能會將下列項目新增至資料庫：

  • lo – 這是大型物件 (lo) 資料類型，可用於二進位大型物件 (BLOBs) 和其他大型物件。lo 資料類型是 oid 資料類型的領域。換言之，它是具有選用限制的物件識別碼。如需詳細資訊，請參閱 PostgreSQL 文件中的物件識別符。簡而言之，您可以使用 lo資料類型來區分資料庫資料欄，其中存放大型物件參考與其他物件識別符 (OIDs)。

  • lo_manage – 這是一個函數，您可以在包含大型物件參照的資料表欄上的觸發程序中使用此函數。只要您刪除或修改參照大型物件的值時，觸發程序都會取消該物件 (lo_unlink) 與其參照的連結。只有在資料欄是對該大型物件的唯一資料庫參照時，才對該資料欄使用觸發程序。

如需大型物件模組的詳細資訊，請參閱 PostgreSQL 文件中的 lo。

安裝 lo 擴充功能

安裝 lo 擴充功能之前，請確定您已具備 rds_superuser 權限。

安裝擴充功能

  1. 使用 psql連線到 Aurora PostgreSQL 資料庫叢集的主要資料庫執行個體。

    psql --host=your-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password

    出現提示時，輸入您的密碼。psql 用戶端連接並顯示預設管理連接資料庫 postgres=> 作為提示。

  2. 安裝擴充功能，如下所示。

    postgres=> CREATE EXTENSION lo;
CREATE EXTENSION

您現在可以使用 lo 資料類型來定義資料表中的資料欄。例如，您可以建立一個資料表 (images)，其中包含點陣影像資料。您可以將 lo 資料類型用於資料欄 raster，如下列建立資料表的範例所示。

postgres=> CREATE TABLE images (image_name text, raster lo);

使用 lo_Manage 觸發程序函數刪除物件

您可以將 lo_manage 函數用於當更新或刪除 lo 時所要清理 lo 或其他大型物件欄中的觸發程序。

在參照大型物件的資料欄上設定觸發程序

  • 執行以下任意一項：

    • 使用引數的資料欄名稱，在每個資料欄上建立 BEFORE UPDATE OR DELETE觸發，以包含對大型物件的唯一參考。

      postgres=> CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON images
    FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);

    • 僅正在更新資料欄時套用觸發程序。

      postgres=> CREATE TRIGGER t_raster BEFORE UPDATE OF images
    FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);

lo_manage 觸發程序函數只有在插入或刪除資料欄資料 (視您定義觸發程序的方式而定) 的背景下才會運作。當您執行 DROP 或 TRUNCATE 操作時則不會起作用。這意味著您應先將任何資料表中的物件資料欄刪除,再捨棄資料表，以免產生孤立物件。

例如，假設您想要捨棄包含 images 資料表的資料庫。您如下所示刪除資料欄。

postgres=> DELETE FROM images COLUMN raster

假設在該資料欄上定義 lo_manage 函數來處理刪除，現在您可以放心地捨棄該資料表。

使用 移除孤立的大型物件 vacuumlo

vacuumlo 公用程式會從資料庫識別和移除孤立的大型物件。此公用程式自 PostgreSQL 9.1.24 起可供使用。如果您的資料庫使用者定期使用大型物件，建議您vacuumlo偶爾執行 來清除孤立大型物件。

在安裝 lo 延伸模組之前，您可以使用 vacuumlo來評估 Aurora PostgreSQL 資料庫叢集是否可受益。若要這麼做，請將 vacuumlo 搭配 -n 選項 (無操作) 使用，以顯示要刪除的內容，如下所示：

$ vacuumlo -v -n -h your-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com -p 5433 -U postgres docs-lab-spatial-db
Password:*****
Connected to database "docs-lab-spatial-db"
Test run: no large objects will be removed!
Would remove 0 large objects from database "docs-lab-spatial-db".

如輸出結果所示，孤立大型對象不是此特定資料庫的問題。

如需此公用程式的詳細資訊，請參閱 PostgreSQL 文件vacuumlo中的 。

了解 vacuumlo 的運作方式

vacuumlo 命令會從 PostgreSQL 資料庫移除孤立的大型物件 (LOs)，而不會影響使用者資料表或與之衝突。

命令的運作方式如下：

  1. vacuumlo 首先建立包含資料庫中大型物件之所有物件 IDs(OIDs) 的暫存資料表。

  2. vacuumlo 然後掃描資料庫中使用資料類型 oid或 的每個資料欄lo。如果 OID在這些資料欄中vacuumlo找到相符項目，則會OID從暫時資料表中移除 。 只會vacuumlo根據這些類型檢查特別命名為 oid或 的資料欄lo，而非網域。

  3. 暫時資料表中的其餘項目代表孤立的 LOs，vacuumlo然後安全地移除。

改善vacuumlo效能

您可以使用 -l選項來vacuumlo增加批次大小，進而改善 的效能。這可讓 LOs 一次vacuumlo處理更多 。

如果您的系統有足夠的記憶體，而且您可以將暫存資料表完全容納在記憶體中，增加資料庫層級temp_buffers的設定可能會改善效能。這可讓資料表完全位於記憶體中，進而提升整體效能。

下列查詢預估暫存資料表的大小：

SELECT
    pg_size_pretty(SUM(pg_column_size(oid))) estimated_lo_temp_table_size
FROM
    pg_largeobject_metadata;

大型物件的考量事項

以下是使用大型物件時需要注意的一些重要考量：

  • Vacuumlo 是唯一的解決方案，因為目前沒有其他方法可移除孤立的 LOs。

  • 例如 pglogical、原生邏輯複寫 AWS DMS 和使用複寫技術的工具不支援複寫大型物件。

  • 設計資料庫結構描述時，請盡可能避免使用大型物件，並考慮bytea改用其他資料類型。

  • 至少每週vacuumlo定期執行一次，以防止孤立的 發生問題LOs。

  • 在存放大型物件的資料表上使用具有 lo_manage函數的觸發，以協助防止孤立LOs的建立。