SELECT vs COPY PostgreSQLでパフォーマンス検証してみた！どちらを選ぶべきか解説！

SELECT vs COPY、PostgreSQLでパフォーマンス検証してみました！

PostgreSQL、SELECTコマンド、COPYコマンドの基礎知識から解説し、結局どちらを選ぶべきか結果も出しているので是非最後まで読んでみてください。

PostgreSQLとは

PostgreSQLは、信頼性の高いオープンソースリレーショナルデータベース管理システム（RDBMS）の一つです。データ管理に必要な機能を豊富に備え、大規模なデータセットを効率的に処理する能力を持っています。

その柔軟性と拡張性により、多様な業界で幅広く採用されており、データの整合性と信頼性を確保しつつ、複雑なクエリや大量のトランザクションを処理することができます。

PostgreSQLは、その堅牢性とパフォーマンス、そして活発なコミュニティサポートにより、多くの開発者やデータベース管理者から高い評価を受けています。

PostgreSQLのインストールと設定方法

PostgreSQLのインストール方法

PostgreSQLのインストールは、使用するオペレーティングシステムに応じて異なる方法で行います。今回のメインはSELECTとCOPYについてなので、詳細については割愛しますが主に以下の方法でインストールできます。

Windowsユーザーは、公式ウェブサイトからインストーラーをダウンロードして実行するのが最も簡単です。

一方、Linuxユーザーはパッケージマネージャーを使用してインストールすることができます。例えば、Ubuntuでは「sudo apt-get install postgresql」コマンドを使用します。

macOSユーザーは、Homebrewを使用して「brew install postgresql」コマンドでインストールできます。

PostgreSQLの基本的な設定方法

PostgreSQLをインストールした後、基本的な設定を行うことで、データベースの性能と安全性を最適化できます。

まず、postgresql.confファイルを編集して、メモリ使用量、接続数、ログ設定などのパラメータを調整します。次に、pg_hba.confファイルを通じて、クライアント認証を設定し、セキュリティを強化します。

例えば、リモート接続を許可するためには、pg_hba.confに適切なエントリを追加する必要があります。これらの設定は、データベースの規模や用途に応じて適切に調整することが重要です。

PostgreSQLでのデータベース作成方法

PostgreSQLでデータベースを作成するには、主に二つの方法があります。

一つ目は、コマンドラインから「createdb」コマンドを使用する方法です。例えば、「createdb mydb」と入力すると、「mydb」という名前のデータベースが作成されます。

二つ目は、SQLコマンドを使用する方法で、psqlクライアントに接続後、「CREATE DATABASE mydb;」というSQLステートメントを実行します。

どちらの方法も同じ結果をもたらしますが、状況や個人の好みに応じて選択できます。データベース作成後は、必要に応じてスキーマやテーブルを作成し、データを挿入していくことができます。

PostgreSQLの基本操作

データベース操作の基本

PostgreSQLでのデータベース操作の基本は、効率的なデータ管理の鍵となります。

データベースの作成、接続、切断、削除などの操作は、psqlコマンドラインツールやPgAdminのようなGUIツールを使用して行うことができます。

例えば、psqlを使用してデータベースに接続するには、「psql -d mydatabase -U myuser」というコマンドを実行します。また、「\l」コマンドでデータベースの一覧を表示したり、「\c database_name」でデータベースを切り替えたりすることができます。

これらの基本操作を習得することで、データベース管理の効率が大幅に向上し、より複雑な操作にも自信を持って取り組むことができるようになります。

テーブル作成とデータ挿入

PostgreSQLでのテーブル作成とデータ挿入は、データベース設計の重要な部分です。テーブルを作成するには、CREATE TABLE文を使用します。
例えば、

CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(50), email VARCHAR(100));

というSQLステートメントで、ユーザー情報を格納するテーブルを作成できます。

データの挿入には、INSERT INTO文を使用します。

INSERT INTO users (name, email) VALUES (‘Taro’, ‘[email protected]’);

のように記述します。
また、複数の行を一度に挿入する場合は、VALUES句を複数指定することができます。

テーブル設計時には、適切なデータ型の選択や制約の設定が重要で、これらによってデータの整合性と効率的なクエリ実行が保証されます。

クエリの基本

PostgreSQLでのクエリの基本は、データベースから必要な情報を効率的に取得するための重要なスキルです。

最も基本的なクエリはSELECT文で、

SELECT * FROM users WHERE age > 18;

のように、特定の条件に合致するデータを取得できます。

さらに、JOINを使用して複数のテーブルからデータを結合したり、GROUP BYで集計を行ったりすることも可能です。例えば、

SELECT department, AVG(salary) FROM employees GROUP BY department;

というクエリで、部門ごとの平均給与を算出できます。

効果的なクエリ作成には、インデックスの適切な使用や実行計画の分析など、パフォーマンスの最適化も考慮することが重要です。
基本的なクエリ操作を習得することで、複雑なデータ分析や効率的なデータ取得が可能になります。

SELECTコマンド、COPYコマンドとは

SELECTコマンドについて

SELECTコマンドは、PostgreSQLにおいてデータを取得するための最も基本的で汎用的なコマンドです。

このコマンドは、テーブルから特定の条件に合致するデータを柔軟に抽出し、必要に応じて加工することができます。例えば、

SELECT name, age FROM users WHERE age > 30 ORDER BY name;

というクエリは、30歳以上のユーザーの名前と年齢を名前順で取得します。

SELECTは、WHEREによる条件指定、JOINによる複数テーブルの結合、GROUP BYによる集計など、多様な操作と組み合わせることができる強力なツールです。

ただし、大量のデータを処理する場合、インデックスの適切な使用や実行計画の最適化が重要になります。SELECTの柔軟性と機能性は、データ分析や複雑なデータ操作において非常に有用ですが、大規模なデータ転送には必ずしも最適ではない場合があります。

COPYコマンドについて

COPYコマンドは、PostgreSQLで大量のデータを高速に転送するための特殊なコマンドです。

このコマンドは、テーブルとファイルシステム間でデータを効率的に移動させることができます。例えば、

COPY users TO ‘/tmp/users.csv’ WITH CSV HEADER;

というコマンドは、usersテーブルの内容をCSVファイルにエクスポートします。
逆に、

COPY users FROM ‘/tmp/users.csv’ WITH CSV HEADER;

でデータをインポートできます。

COPYは、バイナリ形式やテキスト形式など、様々なデータ形式をサポートしており、大規模なデータセットの転送に特に有効です。

ただし、SELECTと比較して柔軟性は低く、データの変換や条件付き転送には適していません。COPYの主な利点は、その高速性と効率性にあり、バックアップや大規模なデータ移行において重要な役割を果たします。

PostgreSQLでのSELECT vs COPYのパフォーマンス検証

準備と検証方法

以下のようにテーブルへのデータを用意します。
またDB自体の設定（postgresql.conf）への設定は行わずデフォルトで実施します。

テーブルの作成
CREATE TABLE test_data ( id SERIAL PRIMARY KEY, data TEXT );
1万件のデータ作成
INSERT INTO test_data (data) SELECT md5(random()::text) FROM generate_series(1, 10000);
10万件のデータ作成
INSERT INTO test_data (data) SELECT md5(random()::text) FROM generate_series(1, 100000);
10万件のデータ作成
INSERT INTO test_data (data) SELECT md5(random()::text) FROM generate_series(1, 100000);
100万件のデータ作成
INSERT INTO test_data (data) SELECT md5(random()::text) FROM generate_series(1, 1000000);
1000万件のデータ作成
INSERT INTO test_data (data) SELECT md5(random()::text) FROM generate_series(1, 10000000);
SELECTの実施
EXPLAIN ANALYZE SELECT * FROM test_data;
COPYの実施
time -v psql -U postgres -d main -c "\\copy (SELECT * FROM test_data) TO '/tmp/output.csv' WITH CSV HEADER"

※ 各データ件数で実行する前にテーブル作成から実施する

※ 各コマンド実施前に一度SELECTを実行しておきます。キャッシュに乗った状態での計測をするためです。

SELECTとCOPYのパフォーマンス比較

SELECTとCOPYのパフォーマンス比較は、データベース操作の効率性を向上させる上で重要な観点です。
一般的に、COPYはSELECTよりも高速にデータを転送できますが、使用状況によって適切な選択が異なります。

例えば、1000万行のデータをファイルにエクスポートする場合、COPYは数秒で完了する可能性がありますが、SELECTを使用すると数分かかる場合があります。ただし、データの一部のみを転送したり、転送中にデータを変換したりする必要がある場合は、SELECTの方が適しています。

パフォーマンスの差は、データ量、ネットワーク速度、ディスクI/O速度などの要因に大きく影響されます。したがって、具体的な使用状況に応じて、SELECTとCOPYのどちらが適しているかを慎重に検討する必要があります。

実行完了まで何秒かかったかを比較してみます。

件数	1万件	10万件	100万件	1000万件
SELECT	1.356 ms	13.674 ms	114.662 ms	861.725 ms
COPY	50 ms	79 ms	248 ms	2621 ms

PostgreSQLでのSELECT vs COPYのパフォーマンス検証結果

今回の検証の場合、1つのカラムのみのテーブルで検証を行ったためにSELECTの方が優位になっていると考えます。

本来であれば、SELECTは大量データの転送では効率が低下する傾向があります。一方、COPYは大量データの高速転送に優れていますが、データの変換や条件付き転送には適していません。

SELECTとCOPYを適切に使い分けることが重要です。パフォーマンスの最適化には、データ量、クエリの複雑さ、インデックスの使用、ハードウェアの性能など、多くの要因を考慮する必要があります。

まとめ：SELECTとCOPYどちらを選ぶべきか

SELECTの使い所

SELECTコマンドは、データベースからの柔軟なデータ取得や複雑なデータ操作が必要な場面で最も効果的です。

具体的には、特定の条件に基づいてデータを抽出する場合、複数のテーブルを結合して情報を取得する場合、またはデータに対して集計や変換を行う場合にSELECTが適しています。
例えば、

SELECT category, COUNT() FROM products GROUP BY category HAVING COUNT() > 100;

というクエリは、100個以上の製品がある各カテゴリを抽出します。
SELECTは、データ分析、レポート生成、アプリケーションのデータ取得など、幅広い用途で使用されます。また、インデックスを適切に使用することで、大規模なデータセットでも効率的なクエリ実行が可能です。

SELECTの柔軟性と豊富な機能は、複雑なデータ操作や条件付きデータ取得において非常に有用です。

COPYの使い所

COPYコマンドは、大量のデータを高速に転送する必要がある場面で最も効果的です。

特に、データベースのバックアップやリストア、大規模なデータ移行、外部システムとのデータ交換などの場面で威力を発揮します。
例えば、数百万行のデータを含むテーブル全体をCSVファイルにエクスポートする場合、

COPY large_table TO ‘/path/to/backup.csv’ WITH CSV HEADER;

というコマンドを使用することで、非常に短時間で処理を完了できます。
同様に、大量のデータを一括でテーブルにインポートする際も、COPYは高速で効率的です。COPYは、データの変換や条件付き転送には適していませんが、純粋なデータの移動や複製においては、SELECTよりも圧倒的に高速です。

したがって、大規模なETL（抽出・変換・負荷）プロセスの一部として、データの抽出や負荷フェーズでCOPYを活用することで、全体的なパフォーマンスを大幅に向上させることができます。

SELECTとCOPYどちらを選ぶべきか

SELECTとCOPYの選択は、具体的な使用状況と要件に応じて慎重に判断する必要があります。
データ操作の複雑さ、転送するデータ量、パフォーマンス要件、データの整合性などを総合的に考慮して決定します。

例えば、日次レポートの生成のように、特定の条件に基づいてデータを抽出し、集計や変換を行う必要がある場合はSELECTが適しています。

一方、月次バックアップのように、テーブル全体を高速にエクスポートする必要がある場合はCOPYが最適です。

また、大規模なデータ移行プロジェクトでは、SELECTを使用してデータを抽出・変換し、COPYを使用して変換後のデータを高速に転送するというハイブリッドアプローチも効果的です。

最終的には、操作の目的、データの特性、システムのリソース制約などを考慮し、効率性と機能性のバランスを取りながら適切なコマンドを選択することが重要です。状況によっては、SELECTとCOPYを組み合わせて使用することで、最適なパフォーマンスと柔軟性を達成できる場合もあります。