Zenn
☠️

PostgreSQL について知っておきたかったこと

2025/03/11に公開
PostgreSQL
DB
tech

はじめに

私はここ10年弱、Webアプリケーションの開発に携わってきました。その間、様々なシステムやツールの使い方を学ぶ必要がありました。そうした学習の過程で、公式ドキュメントが最も役立つことが多いと気づきました。

しかし、PostgreSQLは例外でした。PostgreSQLの公式ドキュメントが優れていないからではなく（むしろ素晴らしいです！）、単に膨大すぎるからです。執筆時点での最新バージョン（17）のドキュメントは、標準的なPDFとして米国レターサイズの用紙に印刷すると、なんと3,200ページにも及びます。これは若手エンジニアが最初から最後まで読み通せるようなものではありません。

そこで、PostgreSQLデータベースを使う前に誰かに教えてほしかった情報をまとめてみようと思います。私と同じような道を歩むエンジニアの助けになれば幸いです。

なお、この記事で紹介する内容の多くは他のSQLデータベース管理システム（DBMS）や一般的なデータベースにも当てはまるかもしれませんが、私は他のシステムに詳しくないため、何が共通していて何が異なるのかは断言できません。

特別な理由がない限り、データを正規化しよう

データベース正規化とは、データベーススキーマから重複や冗長なデータを取り除くプロセスです。例えば、ユーザーがドキュメントをアップロードし、他のユーザーがそれを閲覧したときにメール通知を受け取れるサイトがあるとします。documentsテーブルにuser_emailカラムを持たせるべきではありません。ユーザーがメールアドレスを変更したとき、アップロードした何百ものドキュメントの行を更新する必要があるからです。代わりに、documentsの各行が別のテーブル（例：users）の行を外部キー（例：user_id）で参照するようにします。

「データベース正規化」をオンラインで検索すると、「第1正規形」などについての多くの情報が見つかります。それぞれの「正規形」について詳しく知る必要はありませんが、一般的なプロセスを理解しておくと、より保守しやすいデータベーススキーマを設計できるでしょう。

冗長なデータを持つ（つまりスキーマを非正規化する）ことが意味を持つ場合もあります。典型的には、特定のデータの読み取りを高速化するため、毎回再計算しなくて済むようにする場合です。例えば、ベーカリーの従業員シフトを管理するアプリケーションがあるとします。ユーザーは今年これまでに働いた時間数を確認したいかもしれません。これを計算するには、各シフトの期間を求め、それらをすべて合計する必要があります。この値を定期的に、または労働時間数が変わるたびに計算しておくと良いでしょう。このデータはPostgreSQLデータベース内、または外部（例：Redisのようなキャッシュレイヤー）で非正規化できます。ただし、非正規化データにはほぼ常にコストが伴います。それがデータの不整合の可能性であれ、書き込み複雑性の増加であれです。

PostgreSQL開発者からのアドバイスに従おう

PostgreSQL公式Wikiには、「これはやめましょう」と題された大きなリストがあります。すべての項目を理解できなくても大丈夫です！理解できない項目はおそらくその間違いを犯すこともないでしょう。特に注目に値するいくつかの提案：

  1. テキスト保存にはすべてtext型を使う
  2. タイムスタンプ保存にはすべてtimestampz/time with time zone型を使う
  3. テーブル名はスネークケース（snake_case）で命名する

SQLの一般的な特異性について知っておこう

小指を守る：SQLを大文字で書く必要はない

ほとんどのドキュメントやチュートリアルでは、SQLは次のように書かれています：

SELECT * FROM my_table WHERE x = 1 AND y > 2 LIMIT 10;

SQLキーワードの大文字・小文字は区別されません。つまり、上記のスニペットは以下と同じです：

select * from my_table where x = 1 and y > 2 limit 10;

あるいはこれも同じです：

SELECT * from my_table WHERE x = 1 and y > 2 LIMIT 10;

これはPostgreSQLに限った話ではありません。小指に優しくしましょう。

NULLは特殊

他のプログラミング言語のnullやnil値に馴染みがあるかもしれませんが、SQLのNULLはそれらとは異なります。NULLはより正確には「不明」を表します。例えば、NULL = NULLはNULLを返します（ある未知の値が別の未知の値と等しいかどうかは不明であるため！）。これは=だけでなくほとんどの演算子に当てはまります：比較の一方がNULLなら、結果はNULLになります。

NULLと比較してNULLにならない演算子がいくつかあります：

演算 説明
x IS NULL xがNULLならtrue、そうでなければfalse
x IS NOT NULL xがNULLでなければtrue、そうでなければfalse
x IS NOT DISTINCT FROM y x = yと同じだが、NULLを通常の値として扱う
x IS DISTINCT FROM y x != y/x <> yと同じだが、NULLを通常の値として扱う

WHERE節は条件がtrueと評価された場合のみマッチします。つまり、SELECT * FROM users WHERE title != 'manager'というクエリは、titleがNULLの行を返しません。なぜならNULL != 'manager'はNULLであり、trueではないからです。

NULLを扱う際に便利なもう一つの関数はCOALESCEです：COALESCEは任意の数の引数を取り、NULLでない最初の引数を返します：

COALESCE(NULL, 5, 10) = 5
COALESCE(2, NULL, 9) = 2
COALESCE(NULL, NULL) IS NULL

psqlをより便利にしよう

読みにくい出力を修正する

多くのカラムを持つテーブルや、長い値を含むカラムに対してクエリを実行すると、出力がほとんど読めなくなることがありませんか？おそらくページャーが有効になっていないからです。ターミナルページャーを使うと、より大きなキャンバス上でビューポートをスクロールしながらテキストファイル（またはpsqlの場合はテーブル）を表示できます。ページャーがなければ、スペースが尽きたところで改行して単にテキストをターミナルにダンプするだけです。

lessはUnix系システムで利用できる優れたページャーです。環境変数を~/.bashrc/~/.zshrcなどに設定することで、これをページャーとして使用できます：

# `-S`オプションを使うと、長い行を折り返さずに切り詰めます
export PAGER='less -S'

ときには、表が正しくフォーマットされていても、特に多くのカラムを持つテーブルでは特に役に立たないことがあります。その場合、psqlセッションで\pset expanded（または\xというショートカット）を使って「展開モード」に切り替えられます。これをデフォルトにしたい場合は、ホームディレクトリに.psqlrcファイル（つまり~/.psqlrc）を作成し、\xを追加します。psqlセッションを開始するたびに、そのファイル内のすべてのコマンドが最初に実行されます。

曖昧なNULL値を明確にする

出力において値がNULLであることを知るのはとても重要ですが、デフォルト設定ではそれがはっきりしないことがあります。psqlにNULLを参照するときに出力する文字列を指定できます。私は以下のように[NULL]に設定しています：

\pset null '[NULL]'

どんなUnicode文字列でも使えます！ハロウィンシーズンは過ぎましたが、友人のSteven Harmanのように「👻」に設定することもできます。

これもデフォルトにしたい場合は、ホームディレクトリに.psqlrcファイルを作成し、\pset null '[NULL]'を追加します。psqlセッションを開始するたびに、そのファイル内のすべてのコマンドが最初に実行されます。

自動補完を活用する

psqlは、多くのインタラクティブコンソールと同様に、自動補完をサポートしています。SQLは比較的厳格で構造化された言語なので便利です。ほとんどのキーワードやテーブル名を入力し始めてTabキーを押すと、psqlが残りを補完してくれます：

-- "SEL"と入力し始める
SEL
-- ^ `Tab`キーを押す
SELECT

バックスラッシュショートカットを活用する

psqlには、情報の検索、コマンドライン編集などのための多くの便利なショートカットコマンドがあります。

コマンド 機能
\? すべてのショートカットを一覧表示
\d リレーション（テーブルとシーケンス）とその所有者の一覧を表示
\d+ \dと同じだが、サイズなどのメタデータも含む
\d table_name テーブルのスキーマ（カラムのリスト、型、NULL許容性、デフォルト値を含む）および任意のインデックスや外部キー制約を表示
\e デフォルトエディタ（$EDITOR環境変数で設定）を開いてクエリを編集
\h SQL_KEYWORD SQL_KEYWORDの構文とドキュメントへのリンクを取得

これらは多数あり、上記の表はほんの一部です。

CSVにコピーする

クエリの結果をExcelなどで使いたい他の人と共有したいことがあります。PostgreSQLでは、任意のクエリの出力をローカルマシン上のCSVにコピーするのが非常に簡単です：

\copy (select * from some_table) to 'my_file.csv' CSV

すべてのカラム名を含む初期行を含めたい場合は、HEADERオプションを追加できます：

\copy (select * from some_table) to 'my_file.csv' CSV HEADER

より詳細な情報（CSVからデータを挿入する方法など）については、このコマンドに関するドキュメントをご覧ください。

カラムの省略表記とエイリアスを使用する

psqlでSELECT文を実行するとき、ASキーワードを使って出力の各カラムに好きな名前（「エイリアス」）を付けられます：

SELECT vendor, COUNT(*) AS number_of_backpacks FROM backpacks GROUP BY vendor ORDER BY number_of_backpacks DESC;

これは出力のカラム名も変更します。

さらに、GROUP BYとORDER BYにも便利な省略表記があります：SELECTの後に表示される順番でカラムを参照できるのです。そのため、前述のクエリは以下のように書けます：

SELECT vendor, COUNT(*) AS number_of_backpacks FROM backpacks GROUP BY 1 ORDER BY 2 DESC;

便利ですが、本番環境に送るクエリにはこれを含めないでください - 将来の自分に感謝されるでしょう！

インデックスを追加しても効果がない場合がある（特に誤設定の場合）

インデックスとは？

インデックスは、データの検索を支援するためのデータ構造で、テーブルの行を様々なフィールドで「ショートカットディレクトリ」として維持する責任をPostgreSQLに与えます。最も一般的なタイプはB-treeインデックスで、これは完全一致条件（例：WHERE a = 3）と範囲条件（例：WHERE a > 5）の両方に対応する検索ツリーの一種です。

しかし、特定のインデックスを使うようPostgreSQLに指示することはできません。関連データを見つけるために単にテーブルを上から下まで読む（「逐次スキャン」または「seq. scan」- 「シークスキャン」と発音 - と短縮されます）よりも速いと予測する必要があります。PostgreSQLがクエリをどのように実行するかの計画を確認するには、SELECT ... FROM ...の前にEXPLAINを追加します。これにより「クエリプラン」が表示されます：PostgreSQLがデータを見つける方法と、各タスクにかかる作業量の見積もりです。これらのクエリプランの出力を読むための多くの良いガイドがあります。thoughtbotによるこのガイドやpganalyzeによるこのガイドなどです。公式ドキュメントも良いリファレンスです（ただし初心者には少し圧倒的かもしれません）。クエリプランの分析には、このツールが非常に役立つことがよくあります。

行数の少ないテーブルにはインデックスはあまり役立たない

これは特にローカルデータベースでの開発時に重要です。ローカルデータベースには数百万行のデータはないでしょう。PostgreSQLは、わずか100行程度を処理する場合、インデックスを使用するよりも逐次スキャンを実行する方が速いと判断する場合があります。

複数カラムにインデックスを付ける場合、順序が重要

PostgreSQLは複数カラムインデックスをサポートしています。これは予想通りの動作をします：カラムaとbにインデックスを次のように作成するとします：

CREATE INDEX CONCURRENTLY ON tbl (a, b);

すると、以下のようなWHERE節：

SELECT * FROM tbl WHERE a = 1 AND b = 2;

は、aとbに別々のインデックスを作成した場合よりも高速になります。これは、1つの複数カラムインデックスでは、PostgreSQLは検索クエリの制約を効率的に組み合わせることができるB-treeを1つだけ走査すればよいからです。

このインデックスは、aだけをフィルタリングするクエリも、a単独のインデックスと同じくらい高速化します。

では、SELECT * FROM tbl WHERE b = 5;のようなクエリはどうでしょうか？これも高速化されるでしょうか？可能ですが、できる限り高速にはなりません。上記のインデックスは、b単独のインデックスの必要性を排除するわけではありません。なぜなら、インデックス内のB-treeは最初にaでキー付けされ、次にbでキー付けされるからです。したがって、インデックス内のすべてのaの値を走査して、インデックス内のすべてのbの値を見つける必要があります。クエリで任意のカラムの組み合わせを使用する必要がある場合は、両方(a, b)とb単独のインデックスを持ちたいことが多いです。ただし、必要に応じて、aとbに別々のインデックスを使用することもできます。

プレフィックスマッチを行う場合はtext_pattern_opsを使用する

データベースにマテリアライズドパスアプローチを使用してディレクトリの階層システムを保存しているとします（各行の祖先IDのリストを各行に保存する）。アプリケーションの一部では、すべての子孫ディレクトリを取得する必要があります。そのため、カラムが特定の共通プレフィックスに一致するすべての行を見つけるクエリが必要です：

-- %はワイルドカード：この`WHERE`節は、`path`が'/1/2/3/'で始まる`directories`を要求しています
SELECT * FROM directories WHERE path LIKE '/1/2/3/%'

処理を高速にするために、directoriesのpathカラムにインデックスを追加します：

CREATE INDEX CONCURRENTLY ON directories (path);

残念ながら、これは使用されない可能性があります：ほとんどのタイプのインデックス（上記のCREATE INDEX文で暗黙的に作成されるデフォルトのB-treeインデックスを含む）は、動作するために値の順序に依存しています。このような種類のプレフィックスマッチングやパターンマッチング全般に対応するために、インデックスを定義するときに異なる「演算子クラス」を指定する必要があります：

CREATE INDEX CONCURRENTLY ON directories (path text_pattern_ops);

長時間保持されたロックはアプリを壊す可能性がある（ACCESS SHAREでも）

ロックとは？

「ロック」または「ミューテックス」（「相互排除」の略）は、一度に一つのクライアントだけが危険な操作を実行できるようにします。これは多くの場所で見られる概念ですが、PostgreSQLを含む任意のデータベースでは特に重要です。なぜなら、個々のエンティティ（行、テーブル、ビューなど）の更新は完全に成功するか、完全に失敗する必要があるからです。操作が部分的にしか成功しない可能性がある一つの方法は、2つの異なるクライアント/プロセスが同時に実行しようとした場合です。その結果、任意の操作は関連するエンティティに対する「ロック」を取得する必要があります。

PostgreSQLでのロックの仕組み

PostgreSQLでは、テーブルに対していくつかの異なるロックレベルがあり、より制限的なものとそうでないものがあります。以下に、制限の少ないものから多いものへの順に示します：

ロックモード 例となるステートメント
ACCESS SHARE SELECT
ROW SHARE SELECT ... FOR UPDATE
ROW EXCLUSIVE UPDATE, DELETE, INSERT
SHARE UPDATE EXCLUSIVE CREATE INDEX CONCURRENTLY
SHARE CREATE INDEX （CONCURRENTLYなし）
ACCESS EXCLUSIVE 多くの形式のALTER TABLEとALTER INDEX

そして、これらが衝突する方法です（Xは衝突を意味します）：

（衝突表の詳細は略）

例えば、単一のテーブルに対して次のような状況を考えてみましょう：

クライアント1が実行中 クライアント2が実行したい クライアント2は開始できるか？
UPDATE SELECT ✅ はい
UPDATE CREATE INDEX CONCURRENTLY ✅ はい
SELECT CREATE INDEX ✅ はい
SELECT ALTER TABLE 🚫 いいえ、待機が必要
ALTER TABLE SELECT 🚫 いいえ、待機が必要

すべてのこの情報の完全なリストについては、公式ドキュメントを参照してください。このガイドも、操作ごとに何が衝突するかを確認するための優れたリファレンスです（通常、ロックレベルではなく操作を考えるでしょう）。

これが問題を引き起こす方法

前のセクションでは、あるクライアントがALTER TABLE文を実行していると、SELECTの実行がブロックされる可能性があることを指摘しました。ALTER TABLE文が長時間かかる場合、これは予想通り悪い状況になり得ます。コアテーブル（例えばusers、Webアプリのすべてのリクエストが参照する可能性があるもの）を更新している場合、そのテーブルから読み取るすべてのSELECT文は待機状態になります。もちろん、タイムアウトする前に - アプリが503を返す原因になります。

遅いALTER TABLE文の一般的な例：

  • 非定数デフォルト値を持つカラムの追加
    • 私の経験では、これが遅さの最も一般的な原因です
  • カラムの型の変更
  • 一意性制約の追加

では、頻繁に使用されるテーブルに新しいカラムを追加するとします。ALTER TABLE文で何も愚かなことはしていません。新しいカラムを追加していますが、変数デフォルトはありません。これでもアプリが壊れる可能性があります。

そのALTER TABLE文は高速です...ロックを取得すれば。しかし、何年も前に内部ダッシュボードを作成し、そのテーブルに対して定期的なクエリを実行するとします。時間が経つにつれて、そのクエリはどんどん遅くなりました。かつてはミリ秒だったものが、今では数分かかります。通常はこれで問題ありません - 結局のところ、単なるSELECT文です。しかし、そのクエリの実行中にALTER TABLE文が実行されると、待機する必要があります。

それはあまり驚くべきことではないかもしれませんが、これは少し驚くかもしれません：そのテーブルをクエリする後続のステートメントも待機する必要があります。これは、PostgreSQLのロックがキューを形成するからです。

（図の説明は略）

この正確なシナリオが発生した素晴らしい記事については、こちらをご覧ください。

長時間実行されるトランザクションも同様に悪影響

トランザクションに馴染みがなければ、それはデータベースステートメントのシリーズをグループ化して全体で成功するか失敗するか（専門用語では「アトミック」）にする方法です。トランザクションを開始すると（もちろんBEGINで）、変更を隠しています。他のクライアントはあなたの変更を見ることができません。トランザクションを完了する（COMMITで）と、それらの変更をデータベースの残りの部分に「公開」します。トランザクションは抽象的にロックに似ています：他のクライアントがあなたの作業を妨げるのを避けることができます。

トランザクションを必要とする典型的な例は、ある銀行口座から別の口座にお金を移すことです。一つの口座の残高を減らし、もう一つの口座の残高を増やしたいでしょう。データベースがダウンするか、または元の口座の残高が途中でマイナスになる場合、操作全体をキャンセルしたいと思うでしょう。トランザクションはそれを可能にします。

しかし、トランザクションを長時間実行しておくと、容易に問題を引き起こす可能性があります。これは、トランザクションがロックを取得すると、トランザクションがコミットされるまでそれを保持するからです。例えば、クライアント1がpsqlを開いて次のように書いたとします：

BEGIN;
SELECT * FROM backpacks WHERE id = 2;
UPDATE backpacks SET content_count = 3 WHERE id = 2;
SELECT count(*) FROM backpacks;
-- ...

おっと！クライアント1は席を離れてしまいました：誰かがカップケーキを持ってきたのです！クライアント1は効果的にid = 2の行の更新を「完了」していますが、まだロックを持っています。別のクライアントがこの行を削除したい場合、次のように実行します：

DELETE FROM backpacks WHERE id = 2;
-- ...
-- ?

しかし、これは単に停止します。クライアント1が戻ってトランザクションをコミットするまで、何も削除されません。

これがクライアントが必要以上に長くロックを保持し、他のクライアントがデータベースに対するクエリや更新を正常に行うのを妨げるような様々なシナリオにつながる可能性があることは想像できるでしょう。

JSONBは鋭いナイフ

PostgreSQLには非常に強力な機能があります：クエリ可能で効率的にシリアライズされたJSONを行の値として保存できます。多くの点で、新しいサービスを起動したり、2つの異なるデータストア間で調整したりすることなく、ドキュメント指向データベース（例えばMongoDB）のすべての強みをPostgreSQLに持たせます。

しかし、不適切に使用すると欠点もあります。

JSONBは通常のカラムよりも遅くなる可能性がある

JSONBは非常に柔軟ですが、PostgreSQLはJSONBカラムの統計情報を追跡しないため、単一のJSONBカラムに対する同等のクエリが、通常のカラムのセットに対するものよりも大幅に遅くなる可能性があります。この優れたブログ記事では、2000倍も遅くなる例が示されています！

JSONBは標準テーブルスキーマほど自己文書化されていない

JSONBカラムには基本的に何でも含めることができます - これがそれほど強力な主な理由の一つです！しかし、それはまた、その構造についてほとんど保証がないことも意味します。通常のテーブルでは、スキーマを調べてクエリが何を返すかを確認できます。キーはキャメルケース（camelCase）で書かれるのか、スネークケース（snake_case）で書かれるのか？状態は真偽値true/falseで記述されるのか、それともyes/maybe/noのような列挙型で記述されるのか？JSONBの場合、PostgreSQLデータが通常持っている静的型付けがないため、わかりません。

JSONBのPostgresQL型は扱いにくい

backpacksというテーブルがあり、そこにJSONBカラムdataがあり、brandフィールドがあるとします。90年代初頭の美学が好きなので、JanSportのbackpacksを見つけたいとします。そこで次のようなクエリを書きます：

-- 警告：動作しません！
select * from backpacks where data['brand'] = 'JanSport';

すると次のようなエラーが返ってきます：

ERROR:  invalid input syntax for type json
LINE 1: select * from backpacks where data['brand'] = 'JanSport';
                                                      ^
DETAIL:  Token "JanSport" is invalid.
CONTEXT:  JSON data, line 1: JanSport

どうしたのでしょう？PostgreSQLは比較の右辺型が左辺型と一致することを期待しています。つまり、正しくフォーマットされたJSONドキュメントであること - したがって、JSONオブジェクト、配列、文字列、数値、真偽値、またはnullである必要があります。これらの型はどれもbooleanやintegerなどのPostgreSQL型とは関係がないことに注意してください。そしてSQLのNULLはJSONBのnullとは非常に異なる動作をします。後者はより通常の型のように振る舞います。このクエリを正しく書くには、PostgreSQLがある程度の型変換を行えるようにする必要があります。

select * from backpacks where data['brand'] = '"JanSport"';
-- ^ これは実際には以下と同等です（PostgreSQLは左辺が`jsonb`であることを知っているため）
select * from backpacks where data['brand'] = '"JanSport"'::jsonb;
-- あるいは、左辺をPostgreSQLの`text`に変換することもできます：
select * from backpacks where data->>'brand' = 'JanSport';

シングルクォート内のダブルクォートに注意してください。JanSportだけでは有効なJSONではありません。

さらに、JSONBに特化した多くの演算子と関数があり、それらをすべて一度に覚えるのは難しいです。

おわりに

この情報が役立つことを願っています。この記事へのフィードバックやコメントがあれば、ほとんどのサイトでhibachrachとして私を見つけることができます。

この記事の日本語翻訳は、原文の意味を正確に伝えることを目指しています。技術的な詳細や用語に関して質問がある場合は、原文を参照するか、PostgreSQLの公式ドキュメントをご確認ください。

Discussion