PostgreSQL – パーティショニングを利用したパフォーマンス改善方法
本コンテンツはPostgreSQL – インデックスを利用したパフォーマンス改善方法の続きになっています。
インデックスに関する知識がない場合、そちらから始めることを推奨します。
データベースのパフォーマンス最適化において、大量のデータを効率的に管理することは非常に重要です。
パーティショニングを活用することで、大規模なテーブルのパフォーマンスを大幅に向上させることができます。
本記事では、PostgreSQLのパーティショニングについて、その利点や設定方法について解説します。
PostgreSQLのセットアップ
パーティショニング
パーティショニングとは、論理的には一つの大きなテーブルを、物理的に複数の小さなテーブル（パーティション）に分割して管理する手法です。
パーティショニングのメリット
- インデックスサイズの縮小: 複合インデックスのカラム数が減ることで、インデックスのサイズが抑えられます。
- クエリ速度の向上: クエリが一つのパーティションの大部分にアクセスする場合、テーブル全体に分散したランダムアクセスを伴うインデックススキャンより、そのパーティションへの順次アクセスをすることでパフォーマンスを向上させることができます。
- データ管理の効率化: パーティションごとにデータを管理できるため、古いデータの削除や新しいデータの追加が容易になります。
スキーマ、テーブルの作成
データベースにスキーマを設定し、基となるテーブルを作成します。
ここでは、地理情報を管理するテーブルを例にします。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE SCHEMA test; CREATE TABLE test.geographical_data( id SERIAL, pref CHAR(1), city CHAR(1), latitude FLOAT, longitude FLOAT ); INSERT INTO test.geographical_data(pref, city, latitude, longitude) SELECT CHR(ASCII('A') + FLOOR(random() * 5)::INT) AS pref, CHR(ASCII('a') + FLOOR(random() * 26)::INT) AS city, ROUND(CAST(random() AS NUMERIC), 3) AS latitude, ROUND(CAST(random() AS NUMERIC), 3) AS longitude FROM generate_series(1, 10000000); |
以下のようなテーブルデータが作成されます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
postgres=# SELECT * FROM test.geographical_data LIMIT 10; id | pref | city | latitude | longitude ----+------+------+----------+----------- 1 | E | r | 0.248 | 0.722 2 | D | p | 0.614 | 0.874 3 | B | d | 0.908 | 0.002 4 | E | f | 0.746 | 0.219 5 | D | b | 0.875 | 0.204 6 | D | x | 0.182 | 0.12 7 | D | h | 0.235 | 0.152 8 | A | z | 0.269 | 0.981 9 | D | b | 0.947 | 0.659 10 | E | z | 0.533 | 0.426 (10 rows) |
作成されるデータのサイズ約500MB
行数1000万行
親テーブルの作成
次に、パーティショニングを適用する親テーブルを作成します。
ここでは、pref列をパーティションキーに、リストパーティショニングを使用します。
パーティショニングの3つの形式
|
1 2 3 4 5 6 7 |
CREATE TABLE test.geographical_data_partition( id SERIAL, pref CHAR(1), city CHAR(1), latitude FLOAT, longitude FLOAT ) PARTITION BY LIST (pref); |
パーティショニングテーブルの作成
上記の親テーブルに対する、パーティショニングテーブルを作成します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE TABLE test.geographical_data_partition_A PARTITION OF test.geographical_data_partition FOR VALUES IN ('A'); CREATE TABLE test.geographical_data_partition_B PARTITION OF test.geographical_data_partition FOR VALUES IN ('B'); CREATE TABLE test.geographical_data_partition_C PARTITION OF test.geographical_data_partition FOR VALUES IN ('C'); CREATE TABLE test.geographical_data_partition_D PARTITION OF test.geographical_data_partition FOR VALUES IN ('D'); CREATE TABLE test.geographical_data_partition_E PARTITION OF test.geographical_data_partition FOR VALUES IN ('E'); |
親テーブルにデータを挿入
パーティショニングの作成が完了したら、基となるテーブルtest.geographical_dataのデータを親テーブルに挿入します。
これにより、パーティションキーに対応するデータが、自動的に適切なパーティションに振り分けられます。
|
1 2 3 4 5 6 7 |
INSERT INTO test.geographical_data_partition(pref, city, latitude, longitude) SELECT pref, city, latitude, longitude FROM test.geographical_data; |
演習
- 以下の2つのクエリを実行し、合計コストや実行時間の改善率を表にまとめましょう。
クエリ1 : パーティションなし
|
1 2 3 4 5 6 7 |
EXPLAIN ANALYZE SELECT * FROM test.geographical_data WHERE pref = 'A'; |
クエリ2 : パーティションあり
|
1 2 3 4 5 6 7 |
EXPLAIN ANALYZE SELECT * FROM test.geographical_data_partition WHERE pref = 'A'; |
test.geographical_dataテーブルのpref列にインデックスを使用し、クエリ1を実行した場合の合計コストや実行時間の改善率と比較しましょう。- どの様なテーブルを設計する際にパーティショニングを使用すべきか理由とともに考えましょう。
CHALLENGE
test.geographical_data_partitionpref列にインデックスを使用したtest.geographical_data
これら2つのテーブルに下記クエリを実行した際の実行時間を確認しましょう。
|
1 2 3 4 5 6 7 |
EXPLAIN ANALYZE SELECT pref FROM test.targert_table_name WHERE pref = 'A'; |
2の結果と違いが生まれる理由はなぜでしょうか。
ヒント
インデックスのスキャン方法:インデックスオンリースキャン
