まえがき
COPYコマンドはPostgreSQL特有の機能で、ファイルとテーブル間でデータを移動する際に使用します。COPY FROMはファイルからテーブルにデータを入力するコマンドで、COPY TOはDBからファイルにデータを出力するコマンドです。
特にCOPY FROMコマンドは大量の行をロードすることに長けているため、INSERTコマンドを使用したファイルのロードより高速にデータの取り込みが可能です。
COPYコマンドはPostgreSQL 15, 16, 17 で機能追加や性能改善がありました。本稿ではそれらの改善内容の紹介と、簡単な検証を実施します。
連載について
本連載はPostgreSQLの比較的新しいバージョンで導入、改善された機能に注目し、その紹介と簡単な検証をする連載です。
RDBは枯れた技術と評されることが多いですが、PostgreSQLは1年ごとにメジャーアップデートがあり、その度に多くの機能追加や改善が施されています。
本連載では膨大な改善の中から特定の機能に着目し、各メジャーバージョンでどのような改善が導入されたかにフォーカスします。
#1: https://zenn.dev/forcia_tech/articles/postgres_improve_01
検証について
検証はAWS EC2(c6a.large)上に構築したUbuntu 24.04上で実施しました。EBSはgp3(3000 IOPS, 125スループット)のものをアタッチしています。
検証に用いたPostgreSQLのバージョンと、各バージョンのサーバーのポート(以後の本文中のコマンド内で記載のポート)は以下の通りです。15, 16の並列COPY検証のため、これらのバージョンのみpgdataをxfsにマウントしたサーバーも起動しています。
14.14: 5432
15.12: 5433(ext4), 5436(xfs)
16.8: 5434(ext4), 5437(xfs)
17.4: 5435
また、計測ツールとしてhyperfine、コマンドの並列実行にGNU parallelを使用しています。
\copy (psqlコマンド) の改善(PostgreSQL 15)
\copyコマンドはPostgreSQLではなく、psql(クライアント)側のコマンドです。\copyが実行する内容はCOPYと同等ですが、COPYはサーバーが直接ファイルとやりとりをするのに対し、\copyはpsqlを経由してファイルとやりとりをします。そのため、\copyで参照するファイルはpsqlクライアントからアクセスできる必要があります。
PostgreSQL 15 では、\copyコマンドで行ごとに出力されていたCopyDataメッセージ[1]が8KBごとになりました。これにより、特に縦長なデータのロード時に送られるメッセージ数が削減され、性能向上が期待できそうです。
SRA OSSの検証報告 を参考に、以下の手順で検証を実施します。
準備
// int型のみのテーブル(1000万行)を用意し、ファイルに出力
postgres=# CREATE TABLE t_narrow (id int);
postgres=# INSERT INTO t_narrow SELECT generate_series(1, 10000000);
postgres=# \copy t_narrow TO 't_narrow.dat';
// psqlでcopy_fromコマンドを実行するsqlを作成
$ cat copy_from.sql
\copy t_narrow FROM 't_narrow.dat';
hyperfineを用いて計測したところ、以下のような結果となりました。
14
hyperfine "/usr/lib/postgresql/14/bin/psql -p 5432 postgres -f copy_from.sql"
Benchmark 1: /usr/lib/postgresql/14/bin/psql -p 5432 postgres -f copy_from.sql
Time (mean ± σ): 2.934 s ± 0.026 s [User: 0.637 s, System: 0.082 s]
Range (min … max): 2.907 s … 2.984 s 10 runs
15
hyperfine "/usr/lib/postgresql/15/bin/psql -p 5433 postgres -f copy_from.sql"
Benchmark 1: /usr/lib/postgresql/15/bin/psql -p 5433 postgres -f copy_from.sql
Time (mean ± σ): 2.338 s ± 0.225 s [User: 0.346 s, System: 0.054 s]
Range (min … max): 2.244 s … 2.977 s 10 runs
明らかに15の方が高速に処理できることがわかりました。
こちらはpsqlクライアントに対する改善のため、サーバーのバージョンが古くてもpsqlのバージョンを上げれば同様の結果が得られます。
psqlは15, サーバーは14
hyperfine "/usr/lib/postgresql/15/bin/psql -p 5432 postgres -f copy_from.sql"
Benchmark 1: /usr/lib/postgresql/15/bin/psql -p 5432 postgres -f copy_from.sql
Time (mean ± σ): 2.372 s ± 0.056 s [User: 0.339 s, System: 0.061 s]
Range (min … max): 2.304 s … 2.498 s 10 runs
ページ追加改善に伴う同時COPY性能の改善(PostgreSQL 16)
PostgreSQL 16 では、複数のページをまとめて追加できるようになる改善がなされました。これにより、一度に多くのデータを投入したり、同時にページを追加するような操作における性能向上が期待できそうです。
この改善恩恵を受けられる操作としては、同一テーブルに対して並列でCOPYを実行するようなケースが挙げられます。
SRA OSSの検証報告 によると、検証時点でのバージョン(16beta2)では他の変更の影響もあり、ファイルシステムによっては期待通りの性能向上が見られなかった(むしろ悪化することもある)ようでした。また、リリースバージョンではCOPY関連の追加修正があるようだったので、執筆時点での最新バージョン同士で同様の比較を試みます。
準備
// ランダムなテキストを持つテーブル(1万行)を用意し、ファイルに出力
postgres=# CREATE UNLOGGED TABLE t_copy1 (n int, c1 text, c2 text, c3 text);
postgres=# INSERT INTO t_copy1 SELECT g, md5(g::text), md5(g::text), md5(g::text) FROM generate_series(1, 10000) g;
postgres=# COPY t_copy1 TO '/tmp/t_copy1.dat';
// psqlでcopy_fromコマンドを実行するsqlと、テーブルをtruncateするsqlを作成
$ cat copy_from.sql
COPY t_copy1 FROM '/tmp/t_copy1.dat';
$ cat truncate.sql
TRUNCATE t_copy1;
上記では1万行のdatファイルを作成していますが、同様の手順で100万行のdatファイルも作成しました。
並列実行はparallelコマンドとhyperfineを組み合わせて実施しました。1万行のdatファイルは32並列、100万行のdatファイルは8並列で検証しています[2]。
結果は以下のような結果となりました。
15
// 1万行 × 32並列
hyperfine "seq 1 32 | parallel /usr/lib/postgresql/15/bin/psql -p 5433 -U ubuntu postgres -f copy_from.sql" -p "/usr/lib/postgresql/15/bin/psql -p 5433 -U ubuntu postgres -f truncate.sql"
Benchmark 1: seq 1 32 | parallel /usr/lib/postgresql/15/bin/psql -p 5433 -U ubuntu postgres -f copy_from.sql
Time (mean ± σ): 580.5 ms ± 17.0 ms [User: 186.0 ms, System: 277.6 ms]
Range (min … max): 569.0 ms … 624.3 ms 10 runs
// 100万行 × 8並列
hyperfine "seq 1 8 | parallel /usr/lib/postgresql/15/bin/psql -p 5433 -U ubuntu postgres -f copy_from.sql" -p "/usr/lib/postgresql/15/bin/psql -p 5433 -U ubuntu postgres -f truncate.sql"
Benchmark 1: seq 1 8 | parallel /usr/lib/postgresql/15/bin/psql -p 5433 -U ubuntu postgres -f copy_from.sql
Time (mean ± σ): 5.375 s ± 0.111 s [User: 0.101 s, System: 0.098 s]
Range (min … max): 5.274 s … 5.557 s 10 runs
16
// 1万行 × 32並列
hyperfine "seq 1 32 | parallel /usr/lib/postgresql/16/bin/psql -p 5434 -U ubuntu postgres -f copy_from.sql"
Benchmark 1: seq 1 32 | parallel /usr/lib/postgresql/16/bin/psql -p 5434 -U ubuntu postgres -f copy_from.sql
Time (mean ± σ): 549.0 ms ± 3.0 ms [User: 196.3 ms, System: 264.6 ms]
Range (min … max): 546.7 ms … 554.9 ms 10 runs
// 100万行 × 8並列
hyperfine "seq 1 8 | parallel /usr/lib/postgresql/16/bin/psql -p 5434 -U ubuntu postgres -f copy_from.sql" -p "/usr/lib/postgresql/16/bin/psql -p 5434 -U ubuntu postgres -f truncate.sql"
Benchmark 1: seq 1 8 | parallel /usr/lib/postgresql/16/bin/psql -p 5434 -U ubuntu postgres -f copy_from.sql
Time (mean ± σ): 5.044 s ± 0.163 s [User: 0.104 s, System: 0.097 s]
Range (min … max): 4.876 s … 5.311 s 10 runs
pgdataをxfs上にマウントしたサーバー群に対しても同じ計測を実施したところ、以下のような結果となりました。
15
// 1万行 × 32並列
hyperfine "seq 1 32 | parallel /usr/lib/postgresql/15/bin/psql -p 5436 -U ubuntu postgres -f copy_from.sql" -p "/usr/lib/postgresql/15/bin/psql -p 5436 -U ubuntu postgres -f truncate.sql"
Benchmark 1: seq 1 32 | parallel /usr/lib/postgresql/15/bin/psql -p 5436 -U ubuntu postgres -f copy_from.sql
Time (mean ± σ): 576.8 ms ± 3.4 ms [User: 186.3 ms, System: 289.0 ms]
Range (min … max): 572.2 ms … 582.5 ms 10 runs
// 100万行 × 8並列
hyperfine "seq 1 8 | parallel /usr/lib/postgresql/15/bin/psql -p 5436 -U ubuntu postgres -f copy_from.sql" -p "/usr/lib/postgresql/15/bin/psql -p 5436 -U ubuntu postgres -f truncate.sql"
Benchmark 1: seq 1 8 | parallel /usr/lib/postgresql/15/bin/psql -p 5436 -U ubuntu postgres -f copy_from.sql
Time (mean ± σ): 5.140 s ± 0.033 s [User: 0.104 s, System: 0.099 s]
Range (min … max): 5.107 s … 5.189 s 10 runs
16
// 1万行 × 32並列
hyperfine "seq 1 32 | parallel /usr/lib/postgresql/16/bin/psql -p 5437 -U ubuntu postgres -f copy_from.sql" -p "/usr/lib/postgresql/16/bin/psql -p 5437 -U ubuntu postgres -f truncate.sql"
Benchmark 1: seq 1 32 | parallel /usr/lib/postgresql/16/bin/psql -p 5437 -U ubuntu postgres -f copy_from.sql
Time (mean ± σ): 577.9 ms ± 18.8 ms [User: 194.0 ms, System: 282.0 ms]
Range (min … max): 564.6 ms … 628.8 ms 10 runs
// 100万行 × 8並列
hyperfine "seq 1 8 | parallel /usr/lib/postgresql/16/bin/psql -p 5437 -U ubuntu postgres -f copy_from.sql" -p "/usr/lib/postgresql/16/bin/psql -p 5437 -U ubuntu postgres -f truncate.sql"
Benchmark 1: seq 1 8 | parallel /usr/lib/postgresql/16/bin/psql -p 5437 -U ubuntu postgres -f copy_from.sql
Time (mean ± σ): 4.961 s ± 0.087 s [User: 0.104 s, System: 0.097 s]
Range (min … max): 4.886 s … 5.198 s 10 runs
ext4の場合、小さめのファイルを高い並列度で読み込む場合にも多少の性能向上が見られましたが、xfsではほとんど差が見られない、という結果になりました。
一方で、大きめのファイルをある程度の並列度で読み込む場合には、ext4では5%程度、xfsでは3%程度postgres 16の方が速いという結果が得られました。
確かに改善はしているようですが、本計測の条件下では劇的な性能向上というわけではなさそうな印象を受けました。
ON_ERRORオプションの追加(PostgreSQL 17)
性能に直結する改善ではありませんが、 PostgreSQL 17 ではON_ERRORというオプションが追加されました。
従来のCOPYコマンドはデータ不正があった場合その時点で全体の処理が失敗してしまいますが、ON_ERROR ignoreを指定することで不正な行を無視して後続処理を続けられるようになりました。
準備
// 事前に以下のコマンドでテーブルを作成(textカラムはchar(1)のため、2文字以上のデータは不正となる)
postgres=# create table test (id int, text char(1));
// COPY用に以下のようなファイルを作成
$ cat /tmp/test.dat
1 a
2 b
3 cc
4 d
検証
// postgres 16 へのcopyは失敗
$ psql -p 5434 postgres -c "copy test from '/tmp/test.dat'"
ERROR: value too long for type character(1)
CONTEXT: COPY test, line 3, column text: "cc"
// オプションがない場合、 postgres 17 へのcopyも失敗
$ psql -p 5435 postgres -c "copy test from '/tmp/test.dat'"
ERROR: value too long for type character(1)
CONTEXT: COPY test, line 3, column text: "cc"
// オプションがある場合、 postgres 17 へのcopyは不正行以外が成功
$ psql -p 5435 postgres -c "copy test from '/tmp/test.dat' (ON_ERROR ignore)"
NOTICE: 1 row was skipped due to data type incompatibility
COPY 3
フォルシアでは検索テーブル構築時に外部データの取り込みを頻繁に行いますが、データ不正でエラーになることは珍しくありません。プロジェクトによってはpg_bulkloadのような不正行を除外できる仕組みを使って回避しているケースもありますが、ネイティブのPostgreSQLでデータ不正によるバッチエラーが回避できるというのはかなり有用ではないかと思います。
副次的な改善
COPYコマンドに対する直接的な改善ではないものの、間接的にCOPY機能を改善するものもいくつかありますので、内容だけ簡単に紹介します。
SIMD対応などによる副次的な改善(PostgreSQL 16)
PostgreSQL 16 ではSIMDサポートが追加され、ASCIIやJSONの文字列検査の処理速度が向上しました。これに伴い、これらの型を含むテーブルに対するCOPYコマンドの性能向上が期待できそうです。
ストリームI/Oなどによる副次的な改善(PostgreSQL 17)
PostgreSQL 17 では、ストリームI/Oの利用によるseq_scanのディスクアクセス性能の向上や、サーバー・クライアント間で通信が発生するような処理の改善が施されています。これに伴い、大量のデータを出力するようなCOPY TOコマンドの性能向上が期待できそうです。
まとめ
今回検証した内容に関しては、いずれも劇的な変化ではないものの確実に性能向上していることがわかりました。
COPYコマンドはフォルシアでは頻繁に使用するコマンドですが、このような基本的なコマンドでもバージョンを重ねるごとに改善されているというのは、ある意味で驚きを感じました。
また、内部処理の改善の結果が副次的にCOPYコマンドの性能改善に繋がるケースや、逆に変更が悪影響をもたらすケースもあるという点から、改めてパフォーマンス計測結果を解釈することの難しさを実感しました。
この記事を書いた人
吉田 侑弥
だらだらと検証結果を書き連ねていますが、どう考えても不正データの除外が一番嬉しいです。
-
参考: https://www.postgresql.jp/document/15/html/protocol-flow.html#PROTOCOL-COPY ↩︎
-
検証に使用したマシンが2コアのため、過剰だったかもしれません。。 ↩︎
Discussion