PostgreSQL 12 – いくつかの新機能のご紹介

PostgreSQL コミュニティは毎年、PostgreSQL 12 のメジャーリリースを一貫して続けています。PostgreSQL 12 は、JSON データをクエリする新しい方法、インデックスの拡張、パーティション化されたテーブルでのパフォーマンスの向上などの機能を導入し、すでに堅牢な機能の管理を簡素化しながら、新たに開発する機会を設けています。

この記事では、PostgreSQL 12 の素晴らしい新機能のいくつかを詳しく見ていきます。それらを既存の開発および運用プラクティスに組み込む方法を探っていきます。これらの機能の一部は透過的で、PostgreSQL 12 にアップグレードするだけでご利用いただけます。他の部分については、ご利用いただくには既存のアプリケーションまたはプロセスへの変更を加えていただく必要があります。これらの新機能の利点を取り上げ、さらにこのような機能を既存のアプリケーションに適応させる方法の例をご紹介します。

インデックスの改善

PostgreSQL のデフォルトのインデックスタイプは B ツリーです。B ツリーインデックスは、ほとんどのタイプのデータをプライマリキーである整数から E メールアドレスである文字列へインデックスを付けるために使用します。テーブルが大きくなると、対応するインデックスも大きくなります。B ツリーインデックスが大きくなると、データ構造のバランスを保つ必要があります。そうすることで、特定のリーフページがいっぱいになったときにページが必ず分割されるようにします。ほとんどの場合、PostgreSQL はこれらのページを中央で分割することで、新しく分割された各ページに同じ量のデータと空き領域ができるようにします。テーブルに追加されるデータがややランダムである場合は、中央で分割するのが理想的です。ただし、データに重複するインデックスエントリが多数ある場合は、途中で分割すると大量の空き領域が未使用になる可能性があります。PostgreSQL 12 は、B ツリーインデックスページを分割するロジックを変更して、重複するインデックスエントリのコンテキストを使用し、いくつかの圧縮技術を用いています。これらを改善することで、インデックスのデータに応じて、一部の B ツリーインデックスが PostgreSQL 12 で 40% も小さくなる可能性があります。

古いバージョンの PostgreSQL からアップグレードされたデータベースは、古い B ツリー形式のままです。新しい B ツリー形式を利用するには、PostgreSQL 12 でインデックスを作成する必要があります。PostgreSQL には、指定されたテーブルのすべてのインデックスを再構築する REINDEX コマンドがありますが、REINDEX コマンドは、多くの本番環境で禁止ロックを取得します。

postgres=# REINDEX TABLE events;

postgres=# SELECT locktype, transactionid, mode, granted
             FROM pg_locks WHERE relation = 'events'::regclass;
 locktype | transactionid |   mode    | granted
----------+---------------+-----------+---------
 relation |               | ShareLock | t
(1 row)

REINDEX コマンドは、テーブルに対する ShareLock を取得して、テーブルへのすべての書き込みを防ぎます。PostgreSQL 12 には新しいコマンド REINDEX CONCURRENTLY が導入され、REINDEX による重いロックなしでインデックスを再構築できます。

postgres=# REINDEX TABLE CONCURRENTLY events; 

postgres=# SELECT locktype, transactionid, mode, granted
             FROM pg_locks WHERE relation = 'events'::regclass;
locktype | transactionid |           mode           | granted
----------+---------------+--------------------------+---------
 relation |               | ShareUpdateExclusiveLock | t
(1 row)

新しい REINDEX CONCURRENTLY コマンドは、ShareUpdateExclusiveLock を取得します。これは、テーブル構造への同時変更を防止するだけです。REINDEX CONCURRENTLY のロックの軽減は、インデックスの構築時間が長くなるのを代償として実現するものですが、テーブルの読み取りと書き込みは引き続き許可され、操作中はテーブルをオンラインに保ちます。

インデックスの再構築などのメンテナンス操作を実行する際、操作の進行状況を知っていることが必要になることがよくあります。インデックスの構築は、大量の CPU リソースを消費するリソース集中型のコマンドです。限られたメンテナンスウィンドウでは、管理者は頻繁に操作を続行または停止できるように決定を行う必要があります。PostgreSQL 12 では、新しいシステムビュー pg_stat_progress_create_index が導入されました。このビューには、インデックス作成の全体的な進行状況が表示されます。これは、コマンドがいつ完了するかを推測するのに役立ちます。

postgres=# SELECT command, phase, blocks_total, blocks_done FROM pg_stat_progress_create_index;
       command        |             phase              | blocks_total | blocks_done
----------------------+--------------------------------+--------------+-------------
 REINDEX CONCURRENTLY | building index: scanning table |      1639345 |      220340
(1 row)

PostgreSQL 12 では、pg_stat_progress_create_index ビューに加えて、CLUSTER コマンドの進行状況を追跡する pg_stat_progress_cluster ビューも導入されています。

新しい JSON クエリ言語

ネイティブ JSON サポートは、JSON データ型の導入により、2012 年に PostgreSQL 9.2 に最初に追加されました。PostgreSQL は JSON をサポートする最初のリレーショナルデータベースの 1 つとして、SQL を介して JSON ドキュメントを操作するための多くの関数と演算子を作成しました。それ以来、国際標準化機構 (ISO) は、データベース内の JSON と対話するための標準的な方法である SQL/JSON パス言語をリリースしました。PostgreSQL 12 では、SQL/JSON パス言語のサポートが追加されており、開発者は JSON ドキュメントのクエリ方法を選択できます。

JSON ドキュメントのクエリ方法の違いを示すために、日付や言語などのいくつかのキーを持つイベントに関する情報を含むドキュメントについて考えてみましょう。ドキュメントの 1 つは、次のようなものかもしれません。

{
  "date": ["2000/10/01"],
  "lang": ["en"],
  "category1": ["October"],
  "description": ["The 2000 Summer Olympics close in Sydney, Australia."],
  "granularity": ["year"]
}

これらのドキュメントの多くは、次のような単純なイベントテーブルに格納できます。

CREATE TABLE events (
  id    uuid,
  event jsonb
);

PostgreSQL 12 よりも前に、2000 年の日付のすべてのイベントを英語で検索するには、次のようなクエリを使用できました。

SELECT * 
  FROM events 
WHERE event->'date'->0 >= '"2000/01/01"' 
  AND event->'date'->0 < '"2001/01/01"'
  AND event->'lang'->0 = '"en"';

このクエリは、日付と言語のキーの値を抽出し、SQL を使用して、ドキュメント内の値がクエリの条件を満たすかどうかを判断します。これは適切に機能しますが、パフォーマンスの観点から、WHERE 句の各基準に対して 1 回ずつ、値が 3 回抽出されます。

SQL/JSON パス言語の導入により、新しい jsonpath タイプが利用できるようになりました。jsonpath タイプは、複雑な JSON ドキュメントを効率的にクエリするために使用できる SQL/JSON パス式のバイナリ表現です。2000 年の間にすべてのイベントを英語で返すクエリに一致する SQL/JSON パス式は、次のようになります。

'$.date &gt;= "2000/01/01" &amp;&amp; $.date &lt; "2001/01/01" &amp;&amp; $.lang == "en"'

SQL/JSON パス言語には柔軟な構文があり、JSON ドキュメントを強力な方法でクエリできます。式は、任意のキーを再帰的に照合したり、特定のレベルで特定のキーをターゲットにしたりするように構築できます。サポート SQL/JSON パス言語の完全な構文は、PostgreSQL ドキュメントで入手できます。

PostgreSQL 12 には、jsonpath 式をデータベース内の JSON ドキュメントと比較するための 2 つの新しい組み込み関数が導入されています。ドキュメントが式全体と一致する場合、jsonb_path_match 関数は true を返します。もう 1 つの関数 jsonb_path_exists は、ドキュメントが式のいずれかの部分と一致する場合に true を返します。このクエリでは、ドキュメントに完全に一致する必要がある式を作成したため、クエリには関数 jsonb_path_match が必要です。jsonb_path_match 関数を使用してイベントテーブルをクエリする場合、クエリは次のようになります。

SELECT *
  FROM events 
 WHERE jsonb_path_match(event,
'$.date >= "2000/01/01" && $.date < "2001/01/01" && $.lang == "en"');

PostgreSQL のほとんどの機能と同様に、新しい jsonpath 関数には、クエリを簡略化するための対応する演算子があります。jsonb_path_match 関数に対応する演算子は「@@」で、jsonb_path_exists の演算子は「@?」なので、演算子を使用すると、jsonb_path_match を使用したクエリは次のようになります。

SELECT *
  FROM events 
 WHERE event @@ '$.date >= "2000/01/01" && $.date < "2001/01/01" && $.lang == "en"';

関数に対して jsonpath 演算子を使用すると、将来的にパフォーマンスが向上する可能性があります。さまざまなインデックスタイプが新しい SQL/JSON パス言語をサポートしているため、jsonpath 演算子を利用します。この機能は、新しい構文を使用してクエリを証明します。

生成列

JSON ドキュメントなどの複雑なデータを操作する場合、より高度なインデックス方法が適用されていても、アプリケーションで必要なレベルでクエリが実行されない場合があります。通常、このタイプのデータには、すべてではないにしてもほとんどのクエリで頻繁に使用されるキーがあります。このタイプのワークロードを最適化する手法は、このキーを大きなドキュメントから引き出し、専用の列に格納することです。PostgreSQL 12 より前のバージョンでは、トリガーを使用して複雑なデータから列にデータを入力するのが最善の方法でした。トリガーの欠点は、テーブルでのすべての挿入操作と更新操作で発生するパフォーマンスオーバーヘッドです。

PostgreSQL 12 では、生成列が導入され、別の列の値から列を計算できるようになりました。生成列は、テーブルから頻繁に計算する値に役立ちます。単純な数式にすることも、複雑なユーザー定義関数を参照することもできます。挿入および更新操作にはパフォーマンスのオーバーヘッドがありますが、トリガーを使用する場合よりも大幅に少なくなります。

生成列が役立つ場合の例として、日付によって頻繁に照会されるイベントテーブルを考えます。PostgreSQL には強力な日付演算機能があり、過去 30 日間などの指定された日付範囲のイベントを簡単にターゲットにできます。この機能を使用するには、JSON ドキュメントの日付情報を引き出して、実際の PostgreSQL の日付に変換する必要があります。これは、次のようなユーザー定義関数を使用して行うことができます。

CREATE FUNCTION get_date(p_event jsonb)
  RETURNS date AS
$$
  SELECT to_date(
          jsonb_path_query(
              p_event,
              '$.date[0]')::text, '\"YYYY/MM/DD\"');
$$ LANGUAGE sql 
IMMUTABLE;

これにより、次のようなクエリを実行できます。

SELECT * 
  FROM events 
 WHERE get_date(event) >= current_date - 30;

これにより、PostgreSQL の日付機能を使用できますが、クエリを実行するたびにテーブルのすべての行の日付情報を抽出する必要があるため、パフォーマンスが低下します。生成列により、クエリを大幅に高速化する行を記述しながら、パフォーマンスのペナルティを 1 回だけ支払うことができます。次のようにして、生成列を追加できます。

ALTER TABLE events 
  ADD COLUMN event_date date 
  GENERATED ALWAYS AS (get_date(event)) STORED;

これで、クエリは単純に次のようになります。

SELECT * 
  FROM events 
 WHERE event_date >= current_date - 30;

イベントデータを生成列として使用すると、通常の列のようにほとんどの場合に使用できます。アプリケーションのニーズに応じて、インデックスと制約を列に追加できます。ただし、生成列をパーティションキーとして使用できないなど、いくつかの制限があります。

パーティショニングの改善

PostgreSQL 10 は宣言的なパーティショニングを導入し、大きなテーブルをより小さく管理しやすい部分に分割できるようにしました。PostgreSQL 11 は、ハッシュパーティショニング、プライマリキーサポート、外部キーサポート、および実行時のパーティションプルーニングを追加することにより、宣言型パーティショニングを改善しました。PostgreSQL 12 は引き続きパーティション機能を増強しています。最も顕著な強化点は、パーティション分割されたテーブルに対してクエリを実行するときのパフォーマンスの向上です。一部のアプリケーションでは、多数のパーティションが必要になる場合があります。アプリケーションによっては、7 年前のパーティションを毎日必要とする場合や、顧客ごとにパーティションを必要とするため、数千のパーティションが必要になる場合があります。PostgreSQL 12 より前は、クエリに必要なパーティションを削除するアルゴリズムは非効率的でした。

この非効率的なアルゴリズムを示すために、プライマリキー trans_id によって範囲分割された 5000 のパーティションを持つ単純なトランザクションテーブルを考えます。

CREATE TABLE transactions (
  trans_id bigserial PRIMARY KEY,
  trans_date date,
  amount numeric,
  status int
) PARTITION BY RANGE (trans_id);

プライマリキーに対してクエリを実行する場合、インデックスを利用しますが、PostgreSQL 11 では、「トランザクション」テーブルに対するクエリの計画時間が非常に長くなります。簡単なクエリの実行プランはこちらです。

postgres=# EXPLAIN ANALYZE SELECT *
postgres-# FROM transactions WHERE trans_id = 96781536;
                                 QUERY PLAN
---------------------------------------------------------------------
 Append  (cost=0.15..8.18 rows=1 width=48) 
         (actual time=0.011..0.012 rows=0 loops=1)
   ->  Index Scan using transactions_96800000_pkey 
       on transactions_96800000
         (cost=0.15..8.17 rows=1 width=48) 
         (actual time=0.009..0.010 rows=0 loops=1)
         Index Cond: (trans_id = 96781536)
 Planning Time: 1758.868 ms
 Execution Time: 0.261 ms
(5 rows)

このクエリの実行時間はわずか 0.261 ミリ秒ですが、実行パスを決定するための計画時間は 1.7 秒を超えています。この長い計画時間はパーティションの数に比例するため、古いバージョンの PostgreSQL で多くのパーティションを持つことは通常理にかないません。PostgreSQL 12 は、計画アルゴリズムを変更して、多数のパーティションでの効率を大幅に向上させることで、この問題に対処しています。PostgreSQL 12 での同じクエリの実行プランはこちらです。

postgres=# EXPLAIN ANALYZE SELECT *
postgres-# FROM transactions WHERE trans_id = 96781536;
                                 QUERY PLAN
---------------------------------------------------------------------
 Index Scan using transactions_96800000_pkey on transactions_96800000   
   (cost=0.15..8.17 rows=1 width=48) 
   (actual time=0.010..0.011 rows=0 loops=1)
   Index Cond: (trans_id = 96781536)
 Planning Time: 0.112 ms
 Execution Time: 0.036 ms
(4 rows)

このクエリの計画時間は 0.112 ミリ秒に短縮され、実行時間も 0.036 ミリ秒に短縮されました。これにより、パフォーマンスが 10,000 倍以上向上します。

PostgreSQL 12 のパーティション分割パフォーマンスの向上により、パーティションの追加がより一般的になるかもしれません。PostgreSQL 12 より前は、新しいパーティションを既存のテーブルにアタッチするには、テーブル全体を完全にロックして、すべての読み取りと書き込みを防止する必要がありました。多くのアプリケーションは、これが引き起こす一時的な停止を受け入れることができません。

このストアドプロシージャは、「トランザクション」テーブルにパーティションを追加する例です。

CREATE PROCEDURE add_partition(p_from_id bigint, p_to_id bigint)
AS $$
DECLARE
  sql text;
  l_name text;
BEGIN
  IF does_partition_exist(p_from_id, p_to_id) THEN
    RAISE EXCEPTION 'Partition range already exists for % to %',
                    p_from_id, p_to_id;
  END IF;

  l_name := 'transactions_' || p_to_id;
  sql := 'CREATE TABLE ' || l_name ||
         ' (LIKE transactions INCLUDING DEFAULTS INCLUDING CONSTRAINTS)';
  EXECUTE sql;

  sql := 'ALTER TABLE transactions ATTACH PARTITION ' || l_name ||
         ' FOR VALUES FROM ' ||
         '(' || p_from_id || ') TO (' || p_to_id || ')';
  EXECUTE sql;
END
$$ LANGUAGE plpgsql;

このストアドプロシージャを PostgreSQL 11 で実行すると、pg_locks システムビューに表示される 2 つのロックがあります。

postgres=# select locktype, mode, granted 
           from pg_locks where relation = 'transactions'::regclass;
 locktype |        mode         | granted
----------+---------------------+---------
 relation | AccessShareLock     | t
 relation | AccessExclusiveLock | t
(2 rows)

1 つ目は、AccessShareLock です。このロックは一般的であり、テーブルを読み取るときに発生し、トランザクションの進行中にテーブル構造が変更されないようにします。2 番目のロックは、非常に重いロックである AccessExclusiveLock です。このロックは、トランザクションが完了してロックが解放されるまで、読み取りを含むテーブルへのすべてのアクセスを防ぎます。

PostgreSQL 12 で同じストアドプロシージャを実行すると、取得するロックが異なります。

postgres=# SELECT locktype, mode, granted 
             FROM pg_locks WHERE relation = 'transactions'::regclass;
 locktype |           mode           | granted
----------+--------------------------+---------
 relation | AccessShareLock          | t
 relation | ShareUpdateExclusiveLock | t
(2 rows)

取得するのは同じ AccessShareLock ですが、AccessExclusiveLock の代わりに、ShareUpdateExclusiveLock を取得します。ShareUpdateExlusiveLock は、AccessExclusiveLock よりも軽量で、ロックが保持されている間に読み取りと書き込みの両方が行えます。基本的には、テーブル構造への同時変更を防ぐだけです。このロック強度の低下により、ダウンタイムなしで新しいパーティションをテーブルに追加できます。

PostgreSQL のパーティショニングの機能性が向上したため、より多くのテーブルがそれを活用しています。PostgreSQL 12 より前のバージョンでは、パーティションテーブルは、アプリケーションの参照整合性を制限する外部キー制約のリファレンスになることができませんでした。PostgreSQL 12 では、外部キー制約がそれらを参照する機能が導入されています。

ALTER TABLE transaction_lines 
  ADD CONSTRAINT trans_fk FOREIGN KEY (trans_id) 
  REFERENCES transactions (trans_id);

設定パラメータ

すべてのメジャーバージョンで、PostgreSQL は新しい設定パラメータを導入し、場合によっては既存のパラメータのデフォルトを変更します。新しいパラメータの多くは、新しい PostgreSQL を制御するノブです。Amazon Relational Database Service (RDS) PostgreSQL 12 のデフォルトのパラメータグループは、新しい設定をほとんどのアプリケーションで適切に機能する値に設定しますが、ワークロードまたはビジネスルールに基づいて微調整が必要になる場合があります。Amazon RDS PostgreSQL 12 データベースインスタンスを作成する場合、次のパラメータは新しいか、PostgreSQL コミュニティのデフォルト値とは異なるため、特に注意してください。

ssl_min_protocol_version = TLSv1.2

この新しいパラメータは、データベースサーバーへの接続を許可する最小の SSL/TLS プロトコルバージョンを設定します。PostgreSQL コミュニティのデフォルトは TLSv1 ですが、RDS PostgreSQL のデフォルトはより高いレベルのセキュリティです。

jit = off

ジャストインタイム (JIT) コンパイラは、複雑な SQL 式の一部をネイティブランタイムに変換して、一部のクエリをより高速に実行できるようにする機能です。この機能は PostgreSQL 11 で最初に導入され、PostgreSQL コミュニティのデフォルトでこの機能がオンになっています。この機能は一部のユースケースでパフォーマンスの低下を引き起こす可能性があるため、Amazon RDS PostgreSQL 12 のデフォルトではこの機能をオフにしています。

log_transaction_sample_rate = 0

この新機能により、すべてのトランザクションの一部をログファイルに書き込むことができます。一部のアプリケーションでは、トランザクション率が非常に高く、すべてのステートメントのログ記録をオンにすると、ログが圧倒されます。このパラメータを設定して、ログに書き込まれるトランザクションの割合を調整できます。たとえば、このパラメータを 0.01 に設定すると、トランザクションの 1% がログに書き込まれます。

tcp_user_timeout = 0

一部の環境では、ネットワーク接続の信頼性が低いため、多くの古い接続があります。PostgreSQL 12 では、このパラメータが制御するこれらの状況に対処する新機能が導入されています。これにより、接続を終了する前に接続が確認応答を待機する時間をミリ秒単位で指定します。

plan_cache_mode = auto

PostgreSQL が準備済みステートメントを実行する場合、使用する特定のパラメータに基づくカスタムプランまたは一般的なプランのいずれかを使用できるため、計画時間を節約できます。PostgreSQL は常に、準備されたステートメントを実行した回数に基づいて、これらのオプションから自動的に選択します。PostgreSQL 12 では、この動作をこのパラメータでオーバーライドできます。

extra_float_digits = 0

このパラメータは PostgreSQL で長年使用できましたが、PostgreSQL 12 では動作が変更されました。パラメータは、浮動小数点値のテキスト出力に使用される桁数を調整します。PostgreSQL コミュニティのデフォルトでは、この値が 1 に設定されており、最も短く正確な形式で値が出力されます。この新しい動作には、結果を四捨五入する古いデフォルトの 0 よりもパフォーマンス上の利点があります。Amazon RDS PostgreSQL 12 は、バージョン間で浮動小数点の出力の一貫性を維持するデフォルトとして 0 を保持しています。

hll.force_groupagg = false

一部の拡張機能には、バージョン間で異なるパラメータがあります。拡張 postgresql-hll は、HyperLogLog データ型と、そのデータ型を使用するための対応する関数を作成します。データを hll 構造に格納すると、一部の分析ユースケースのパフォーマンスを向上させることができます。このパラメータは、ハッシュ集約の使用を無効にし、hll データ型を使用するクエリのグループ集約を強制します。グループ集約を強制すると、一部のクエリのパフォーマンスが向上します。

まとめ

このブログ記事では、PostgreSQL 12 の優れた新機能の多くを探りました。Amazon RDS for PostgreSQL 12 が利用できるようになり、PostgreSQL コミュニティの多くの印象的な機能に加えて、RDS のすべての利点をご利用いただけます。Amazon RDS PostgreSQL では、自動フェイルオーバー、バックアップとリカバリ、自動パッチなどのマネージド型データベース機能を提供しています。PostgreSQL のすでに印象的な機能に加えて、60 以上の拡張機能もご利用いただけます。

優れた新機能を備えた PostgreSQL 12 ですが、その最大の新開発が着々と進められており、将来の PostgreSQL のバージョンで陽の目を見る日を待っています。テーブルアクセスメソッド用の API を作成する新しい内部アーキテクチャは、次世代の作業の土台になります。テーブルアクセスメソッドを使用すると、PostgreSQL はカラムナなどの新しいストレージ形式をサポートできます。PostgreSQL は革新を続けており、将来のバージョンの PostgreSQL に多くの新機能が搭載される予定です。

著者について

Jim は AWS の主席データベースエンジニアです。AWS 入社前は OpenSCG 社の CTO であり、Postgres-XC のスケーラブルなパブリッククラウドソリューションである StormDB 社の CEO でもありました。Jim は EnterpriseDB 社のチーフアーキテクトであり、創設チームの一員でした。PostgreSQL コミュニティでも非常にアクティブに活動している Jim は、ニューヨークとフィラデルフィア両都市の PostgreSQL Meetup グループを共同で立ち上げ、最大規模の PostgreSQL カンファレンスを共同開催しています。また、PostgreSQL Foundation の理事も務めています。Jim は PostgreSQL および他のオープンソースカンファレンスでも定期的に講演しています。

Amazon Web Services ブログ