共用方式為

將數據大量上傳至 適用於 PostgreSQL 的 Azure 資料庫 的最佳做法 - 彈性伺服器

  • 發行項

適用範圍：適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器

本文討論在「適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器」中大量載入資料的各種方法，以及空白資料庫中初始資料載入和增量資料載入的最佳做法。

載入方法

下列數據載入方法會依最耗時到最耗時的順序排列：

  • 執行單一記錄 INSERT 命令。
  • 每個認可分成 100 到 1,000 個數據列的批次。 您可以使用交易區塊來包裝每個認可的多個記錄。
  • 使用多個資料列值執行 INSERT。
  • 執行 COPY 命令。

將數據載入資料庫中 COPY 的慣用方法是 命令。 COPY如果命令並非不可能，批次INSERT是下一個最佳方法。 使用命令進行 COPY 多線程處理最適合大量載入數據。

上傳大量數據的步驟

以下是將數據大量上傳至 適用於 PostgreSQL 的 Azure 資料庫 彈性伺服器的步驟。

步驟 1：準備您的數據

請確定您的資料已乾淨且已正確格式化資料庫。

步驟 2：選擇載入方法

根據數據的大小和複雜度，選取適當的載入方法。

步驟 3：執行載入方法

執行選擇的載入方法，將數據上傳至資料庫。

步驟 4：驗證數據

上傳之後，請確認數據已正確載入資料庫。

初始資料載入的最佳做法

以下是初始數據載入的最佳做法。

卸除索引

在進行初始數據載入之前，建議您卸除數據表中的所有索引。 載入數據之後建立索引一律更有效率。

卸除限制式

主要卸除限制式如下所述：

  • 唯一金鑰限制式

若要達到強效能，建議您在初始數據載入之前卸除唯一索引鍵條件約束，並在數據載入完成後重新建立它們。 不過，卸除唯一索引鍵限制式會取消對重複資料的保護。

  • F外部索引鍵限制式

建議您在初始數據載入之前卸除外鍵條件約束，並在數據載入完成後重新建立它們。

將 session_replication_role 參數變更為 replica 也會停用所有外部索引鍵檢查。 不過，如果變更未正確使用，可能會使數據不一致。

未記錄的資料表

在初始數據載入中使用未記錄數據表之前，請先考慮未記錄數據表的優缺點。

使用未記錄的數據表可加速數據載入。 寫入未記錄數據表的數據不會寫入預先寫入記錄。

使用未記錄資料表的缺點如下：

  • 它們並不具備損毀安全性。 在經歷當機或未正常關機後，未記錄的資料表將會自動截斷。
  • 未記錄資料表的資料無法複寫到待命伺服器。

若要建立未記錄的資料表，或將現有資料表變更為未記錄的資料表，請使用下列選項：

  • 使用下列語法建立新的未記錄資料表：

    CREATE UNLOGGED TABLE <tablename>;

  • 使用下列語法，將現有的已記錄資料表轉換為未記錄資料表：

    ALTER TABLE <tablename> SET UNLOGGED;

伺服器參數微調

  • auto vacuum': It's best to turn off 在初始數據載入期間自動清理』。 初始載入完成後，建議您在資料庫中的所有資料表上執行手動 VACUUM ANALYZE 操作，然後開啟 auto vacuum。

注意

只有在記憶體和磁碟空間充足的情況，才遵循上述建議。

  • maintenance_work_mem：在適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體上，最多可以設定為 2 GB。 maintenance_work_mem 有助於加速自動清理、索引和外鍵建立。

  • checkpoint_timeout：在適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體上，checkpoint_timeout 值可以從預設的 5 分鐘增加到最多 24 小時。 建議您在一開始載入 適用於 PostgreSQL 的 Azure 資料庫 彈性伺服器實例上的數據之前，將值增加到 1 小時。

  • checkpoint_completion_target：建議值為 0.9。

  • max_wal_size：您可以在執行初始資料載入時，設定為適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體上的允許最大值，即 64 GB。

  • wal_compression：這可以開啟。 啟用此參數時，可能會產生一些額外的 CPU 成本，以在預先寫入記錄 （WAL） 記錄和 WAL 重新執行期間解壓縮。

建議

在適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體上開始進行初始資料載入之前，建議您：

  • 停用伺服器上的高可用性。 您可以在主要伺服器上完成初始載入之後加以啟用。
  • 在初始資料載入完成之後建立讀取複本。
  • 在初始數據載入期間，將記錄最小化或全部停用（例如，停用 pgaudit、pg_stat_statements、查詢存放區）。

重新建立索引並新增限制式

假設您在初始載入之前卸除了索引和條件約束，建議您在 中使用高值 maintenance_work_mem 來建立索引並新增條件約束。 此外，從 PostgreSQL 第 11 版開始，您可以修改下列參數，以在初始資料載入之後，更快速地建立平行索引：

  • max_parallel_workers：設定系統可支援平行查詢的工作者數目上限。

  • max_parallel_maintenance_workers：控制可在 CREATE INDEX 中使用的背景工作處理序數目上限。

您也可以在工作階段層級藉由建立建議的設定，以建立索引。 以下是操作範例：

SET maintenance_work_mem = '2GB';
SET max_parallel_workers = 16;
SET max_parallel_maintenance_workers = 8;
CREATE INDEX test_index ON test_table (test_column);

增量資料載入的最佳做法

累加式數據載入的最佳做法如下所述：

分割區資料表

我們一律建議您分割大型資料表。 分割的一些優點，特別是在增量載入期間，包括：

  • 根據新的差異建立新的分割區，讓將新數據新增至數據表更有效率。
  • 維護資料表會變得更容易。 您可以在增量資料載入期間卸除分割區，以避免耗費時間來刪除大型資料表。
  • 自動數據清理只會在累加式載入期間變更或新增的數據分割上觸發，這可讓維護數據表上的統計數據變得更容易。

維護最新的資料表統計資料

監視和維護資料表統計資料對於資料庫的查詢效能很重要。 這也包括您有增量載入的情況。 PostgreSQL 會使用 autovacuum 精靈程序來清除無效 Tuple，並分析資料表以保持統計資料更新。 如需詳細資訊，請參閱 Autovacuum 監視和微調。

建立外部索引鍵限制式的索引

在子資料工作表中建立外部索引鍵的索引，在下列情況中很有幫助：

  • 父資料表中的資料更新或刪除。 在父資料表中更新或刪除資料時，會在子資料工作表上執行查閱。 您可以在子數據表上為外鍵編製索引，以加快查閱速度。
  • 查詢，您可以在其中看到父數據表和子數據表聯結在索引鍵數據行上。

識別未使用的索引

識別資料庫中未使用的索引，並加以卸除。 索引是資料載入的額外負荷。 資料表上的索引越少，資料擷取期間的效能就越好。

您可以使用兩種方式來識別未使用的索引：透過查詢存放區和索引使用量查詢。

查詢存放區

查詢存放區功能可協助識別索引，這些索引可以根據資料庫的查詢使用模式來卸除。 如需逐步指導方針，請參閱查詢存放區。

啟用伺服器上的查詢存放區之後，您可以使用下列查詢來識別可藉由連線到 azure_sys 資料庫卸除的索引。

SELECT * FROM IntelligentPerformance.DropIndexRecommendations;

索引使用量

您也可以使用下列查詢來識別未使用的索引：

SELECT
    t.schemaname,
    t.tablename,
    c.reltuples::bigint                            AS num_rows,
 pg_size_pretty(pg_relation_size(c.oid))        AS table_size,
    psai.indexrelname                              AS index_name,
 pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    CASE WHEN i.indisunique THEN 'Y' ELSE 'N' END AS "unique",
    psai.idx_scan                                  AS number_of_scans,
    psai.idx_tup_read                              AS tuples_read,
    psai.idx_tup_fetch                             AS tuples_fetched
FROM
 pg_tables t
    LEFT JOIN pg_class c ON t.tablename = c.relname
    LEFT JOIN pg_index i ON c.oid = i.indrelid
    LEFT JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid
WHERE
    t.schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;

number_of_scans、tuples_read 和 tuples_fetched 資料行表示索引 usage.number_of_scans 資料行值為零點，做為未使用的索引。

伺服器參數微調

注意

只有在記憶體和磁碟空間足夠的情況下，才遵循下列參數中的建議。

  • maintenance_work_mem：在適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體上，此參數最多可以設定為 2 GB。 maintenance_work_mem 有助於加速索引建立和外部索引鍵新增。

  • checkpoint_timeout：在適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體上，checkpoint_timeout 值可以從預設的 5 分鐘增加到 10 或 15 分鐘。 增加 checkpoint_timeout 至更顯著的值，例如 15 分鐘，可以減少 I/O 負載，但缺點是，如果發生當機，需要更長的時間才能復原。 建議您在進行變更之前仔細考慮。

  • checkpoint_completion_target：建議值為 0.9。

  • max_wal_size：此值取決於 SKU、儲存體和工作負載。 下列範例示範一種方式，可到達的正確值 max_wal_size。

在尖峰上班時間，執行下列動作以得出值：

a. 執行下列查詢，以取得目前的 WAL 記錄序號 (LSN)：

SELECT pg_current_wal_lsn ();

b. 等候 checkpoint_timeout 秒數。 執行下列查詢以取得目前的 WAL LSN：

SELECT pg_current_wal_lsn ();

c. 使用這兩個結果來檢查 GB 的差異：

SELECT round (pg_wal_lsn_diff('LSN value when running the second time','LSN value when run the first time')/1024/1024/1024,2) WAL_CHANGE_GB;
  • wal_compression：這可以開啟。 啟用此參數可能會產生額外的CPU成本，以便在WAL記錄期間壓縮，並在WAL重新執行期間解壓縮。

相關內容