PG16:Fast COPY FROM command for the foreign tables (修正版)

はじめに

にゃーん。趣味でポスグレをやっている者だ。

この記事はPostgreSQL 16 全部ぬこ Advent Calendar 2022 22日目の記事です。
今回はPostgreSQL 16のCOPYコマンドの性能改善項目について書いてみました。

ソースを最新化(commit 07eef53955ea9885dd3e4701b7eaa34ccdf2ae02)し、かつCREATE FOREIGN TABLEのoptionにbatch_size '1000'を追加して再測定しました。

概要

項目	内容
タイトル	Fast COPY FROM command for the foreign tables
Topic	Performance
Last Modified	2022-10-31
ステータス	commited
概要	外部テーブルに対するCOPY高速化

変更内容

発端となったメールを見ると、パーティションテーブル＋外部テーブルの構成に対してCOPYコマンドを実施するときの性能が向上する、というように読めます。

2022-12-23追記：篠田さんのツイートおよび、本機能の開発に携わっている藤田さん＠NTTからの指摘で、CREATE FOREIGN TABLEのoptionsにbatch_sizeオプションに'1000'を追加した環境を再構築して測定しました。

検証

手元ですぐ使える環境でCOPY処理時間を測定してみます。

環境

• EC2インスタンス：t2.medium
• OS：AM2 Linux

テーブル構成/測定方法

pgbench_accountsテーブルをパーティション親テーブルとして定義し、子外部テーブルをハッシュパーティションとして分割する構成にしました。
外部テーブル接続先の実テーブルはUNLOGGED/LOGGEDの両方のパターンを試してみます。
PostgreSQL 15/devel版で上記のテーブル群を構築し、パーティション親テーブルに対して100万件のデータをCOPYでロードします。

測定結果

各バージョン/各構成で3回測定し、その平均をとってみました。

こうかはばつぐんだ！
batch_sizeオプションの値を変化させると、どう処理時間が変わっていくのかも調べてみると面白いかもしれません。

おわりに

パーティション＋外部テーブル構成でのCOPYの高速化の効果を試してみましたが、batch_sizeオプションの恩恵をこの構成でも受けられる、という結果がわかりました。
指摘していただいた、篠田さん、藤田さん＠NTTのお二方に感謝です。

参考（DDL例）

以下のDDLでテーブルを構築しました。

CREATE EXTENSION postgres_fdw;

-- remote unlogged tables
-- CREATE UNLOGGED TABLE a1(aid integer , bid integer, abalance integer, filler character(84));
-- CREATE UNLOGGED TABLE a2(aid integer , bid integer, abalance integer, filler character(84));
-- CREATE UNLOGGED TABLE a3(aid integer , bid integer, abalance integer, filler character(84));
CREATE TABLE a1(aid integer primary key, bid integer, abalance integer, filler character(84));
CREATE TABLE a2(aid integer primary key, bid integer, abalance integer, filler character(84));
CREATE TABLE a3(aid integer primary key, bid integer, abalance integer, filler character(84));

-- local parent table
CREATE TABLE pgbench_accounts (aid integer, bid integer, abalance integer, filler character(84))
  PARTITION BY HASH (aid);

CREATE SERVER loopback foreign data wrapper postgres_fdw options (port '16001', dbname 'testdb');
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;

-- local child foreign tables
CREATE FOREIGN TABLE f_a1
  PARTITION OF pgbench_accounts
  FOR VALUES WITH (MODULUS 3, REMAINDER 0)
  SERVER loopback
  OPTIONS (table_name 'a1', batch_size '1000');

CREATE FOREIGN TABLE f_a2
  PARTITION OF pgbench_accounts
  FOR VALUES WITH (MODULUS 3, REMAINDER 1)
  SERVER loopback
  OPTIONS (table_name 'a2', batch_size '1000');

CREATE FOREIGN TABLE f_a3
  PARTITION OF pgbench_accounts
  FOR VALUES WITH (MODULUS 3, REMAINDER 2)
  SERVER loopback
  OPTIONS (table_name 'a3', batch_size '1000');