Postgresについて誰かが教えてくれたらよかったこと

 (challahscript.com)
7 ポイント 投稿者 GN⁺ 2024-11-13 | 2件のコメント | WhatsAppで共有
  • Postgresの公式ドキュメントは優れているが、Postgres 17のPDFは3,200ページにも及ぶため、初心者が実務に入る前にスキーマ設計、SQLの挙動、運用上の落とし穴をすべてドキュメントだけで身につけるのは難しい
  • 特別な理由がなければデータは正規化し、読み取り性能のために重複データを置く非正規化には、不整合と書き込みの複雑化というコストを受け入れる必要がある
  • SQLキーワードは大文字小文字を区別しないが、**NULLは「不明」**に近く、一般的な言語のnullのように比較すると予想と異なる結果になる
  • psqlはpager、\x、.psqlrc、\pset null、補完、バックスラッシュコマンド、\copyをうまく使うだけでも、出力の可読性、探索、CSVエクスポートが大きく楽になる
  • インデックス、ロック、トランザクション、JSONBは強力だが、クエリ計画と運用上の制約を知らないと性能低下や可用性問題につながり得る

膨大な公式ドキュメントの前に知っておきたい文脈

  • Postgresの公式ドキュメントは、現行バージョン17ではUS LetterのPDFで出力すると3,200ページ、A4で出力すると3,024ページになる
  • Postgresを使う前に知っておくとよい実務知識は多く、その一部は他のSQL DBMSにも適用できる可能性があるが、適用範囲が常に明確とは限らない

データは基本的に正規化する

  • 正規化とは、データベーススキーマから重複したデータや不要なデータを取り除くプロセスである
  • documentsテーブルにuser_emailを直接保存すると、ユーザーがメールアドレスを変更したとき、そのユーザーのすべての文書行を更新しなければならない
    • 代わりに、documentsの各行がusersのような別テーブルの行をuser_id外部キーで参照するようにできる
  • “1st normal form”のような各正規形をすべて暗記する必要はないが、一般的な正規化プロセスは、より保守しやすいスキーマにつながり得る
  • 非正規化とは、特定のデータを毎回再計算せず高速に読み取るために、重複データを持たせる方法である
    • 従業員のシフト勤務アプリでは、今年の累積勤務時間を毎回すべてのshift durationの合計として計算するのではなく、定期的に、または勤務時間の変更時に計算して保存できる
    • このデータはPostgres内に置くことも、Redisのようなキャッシュ層に置くこともできる
  • 非正規化にはほぼ必ずコストが伴い、代表的なコストはデータ不整合の可能性と書き込み複雑性の増加である

Postgresプロジェクトによる「やってはいけない」助言

  • 公式Postgres Wikiには“Don’t do this”の一覧がある
  • すべての項目を理解できなくても問題はなく、理解できない項目については、その失敗をする可能性も低い
  • 特に次の助言は覚えておく価値がある

SQLで混乱しやすい挙動

  • SQLキーワードは大文字である必要はない

    • 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;
SELECT * from my_table WHERE x = 1 and y > 2 LIMIT 10;
    • この性質はPostgresだけに限らない

  • NULLは一般的な言語のnull/nilとは異なる

    • SQLのNULLは、一般的なプログラミング言語のnullやnilよりも「不明」に近い
    • NULL = NULLはtrueではなくNULLを返す
    • 片方がNULLである比較は、ほとんどの場合、結果もNULLになる
    • NULLの比較には次の演算を使う必要がある
      • x IS NULL: xがNULLならtrue
      • x IS NOT NULL: xがNULLでなければtrue
      • 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だからである
    • COALESCEは複数の引数のうち、最初のNULLでない値を返す
    COALESCE(NULL, 5, 10) = 5
COALESCE(2, NULL, 9) = 2
COALESCE(NULL, NULL) IS NULL

psqlをもっと便利に使う

  • 出力の可読性を改善する

    • カラムが多い、または値が長いテーブルを照会したときに出力が読みにくいなら、pagerがオフになっている可能性がある
    • ターミナルpagerは、大きなテキストやpsqlのテーブルをviewportでスクロールして見られるようにしてくれる
    • カラムの多いテーブルでは、\pset expandedまたは\xでexpanded modeを有効にできる
    • デフォルトで使いたい場合は、ホームディレクトリの~/.psqlrcに\xを追加すればよい

  • NULL出力を明確にする

    • デフォルト設定では、出力上でNULLかどうかを明確に示さない
    • psqlではNULLの表示文字列を指定できる
    \pset null '[NULL]'
    • Unicode文字列も可能で、デフォルトで使うには~/.psqlrcに同じコマンドを追加すればよい

  • 補完とバックスラッシュコマンドを活用する

    • psqlは対話型コンソールのように補完をサポートする
    • キーワードやテーブル名の一部を入力してからTabを押すと、残りを補完できる
    • 便利なバックスラッシュコマンドは次のとおり
      • \?: すべてのshortcut一覧
      • \d: relation、つまりテーブルとシーケンスの一覧および所有者を表示
      • \d+: \dにサイズと一部のメタデータを追加
      • \d table_name: テーブルスキーマ、カラム型、nullableかどうか、デフォルト値、インデックス、外部キー制約を表示
      • \e: $EDITOR環境変数に設定されたデフォルトエディタでクエリを編集
      • \h SQL_KEYWORD: そのSQLキーワードの構文とドキュメントリンクを表示

  • CSVエクスポートとSELECTの別名

    • \copyでクエリ結果をCSVとして保存できる
    \copy (select * from some_table) to 'my_file.csv' CSV
    • カラム名を1行目に含めるにはHEADERオプションを追加する
    \copy (select * from some_table) to 'my_file.csv' CSV HEADER
    • \copyは、より標準的なCOPY文で必要になる昇格権限を避けられる
    • 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;
    • この省略形は便利だが、本番にデプロイされるクエリには入れないほうがよい

インデックスは追加すれば常に使われるわけではない

  • インデックスとクエリ計画

    • インデックスは、テーブル行を特定フィールドに基づいて探すためのショートカットディレクトリとして機能するデータ構造である
    • 最も一般的なインデックスはB-treeで、WHERE a = 3のような正確な等価条件と、WHERE a > 5のような範囲条件で機能する
    • Postgresに特定のインデックスを使うよう直接指示することはできない
    • Postgresは各テーブルについて保持している統計情報を基に、インデックスがテーブルを最初から最後まで読むsequential scanより速いかどうかを予測する
    • SELECT ... FROM ...の前にEXPLAINを付けると、Postgresがクエリをどのように実行するかについてのクエリ計画を見られる
    • クエリ計画を読むときは、thoughtbotのEXPLAIN ANALYZEガイド、pganalyzeドキュメント、公式ドキュメント、explain.depesz.comを参考にできる

  • 小さなテーブルと複合カラムインデックス

    • ローカル開発DBのように行数の少ないテーブルでは、インデックスが大きな助けにならないことがある
    • 100行程度なら、Postgresがインデックスよりsequential scanのほうが速いと判断する可能性がある
    • Postgresは複合カラムインデックスをサポートする
    CREATE INDEX CONCURRENTLY ON tbl (a, b);
    • WHERE a = 1 AND b = 2のような条件は、aとbにそれぞれ別々のインデックスを置く場合より速くなり得る
    • 1つのB-treeをたどりながら検索条件を効率的に組み合わせられるためである
    • (a, b)インデックスは、aだけでフィルタするクエリもa単独インデックスと同じくらい速くする
    • WHERE b = 5のようなクエリは速くなる場合もあるが、最善ではないことがある
      • インデックスがまずa、次にbでキー付けされているため、すべてのa値をたどってb値を探す必要がある
    • 複数カラムの組み合わせでクエリする必要があるなら、(a, b)とb単独インデックスを併用することが多い
    • 必要に応じて、a、bそれぞれの単独インデックスに依存することもできる

  • prefix matchにはtext_pattern_opsを使う

    • materialized path方式で階層型ディレクトリを保存し、特定のprefixで始まるすべてのdescendantを探す必要があるかもしれない
    SELECT * FROM directories WHERE path LIKE '/1/2/3/%'
    • pathカラムにデフォルトのB-treeインデックスを作っても、このクエリでは使われない可能性がある
    CREATE INDEX CONCURRENTLY ON directories (path);
    • prefix matchやpattern matchに必要な文字単位のソートを可能にするには、operator classを指定する必要がある
    CREATE INDEX CONCURRENTLY ON directories (path text_pattern_ops);

ロックとトランザクションが生む運用問題

  • Postgresのロック

    • ロック(lock)またはmutexは、危険な操作を一度に1つのクライアントだけが実行できるようにする仕組みである
    • データベースでrow、table、viewのようなオブジェクトを更新する場合、全体が成功するか全体が失敗する必要があり、同時操作によって一部だけ成功する状況を防ぐため、関連オブジェクトのロックを取得する
    • Postgresのテーブルロックレベルは、制限の弱いものから強いものまで複数段階ある
      • 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
    • 1つのテーブルで次の動作は可能、または待機が必要になる
      • UPDATE中のSELECT: 可能
      • UPDATE中のCREATE INDEX CONCURRENTLY: 可能
      • SELECT中のCREATE INDEX: 可能
      • SELECT中のALTER TABLE: 通常は待機
      • ALTER TABLE中のSELECT: 通常は待機
    • 一部のALTER TABLE形式はより弱いロックを要求する場合があり、全体の情報は公式の明示的ロックドキュメントとoperation別ロック競合ガイドで確認できる

  • 遅いALTER TABLEとロック待ち行列

    • ALTER TABLEが長時間かかると、同じテーブルを読むSELECTもブロックされる可能性がある
    • Webアプリのすべてのリクエストが参照するusersのような中核テーブルなら、リクエストが待機してtimeoutし、503を返すことがある
    • 遅いALTER TABLEの一般的な原因は次のとおり
      • non-constant defaultのあるカラム追加
      • カラム型の変更
      • uniqueness constraintの追加
    • Postgres 11以降では、カラム追加時にすべてのdefaultが遅くなる問題は修正されており、non-constant defaultが問題になり得る
    • ALTER TABLE自体が高速な操作であっても、ロックを得るまでは実行されない
      • 以前からある社内ダッシュボードの遅いSELECTが実行中なら、ALTER TABLEは待つ必要がある
    • Postgresのロックは待ち行列を作るため、待機中のALTER TABLEの後に入ってきた同じテーブルへの後続クエリも待つ可能性がある
    • 同じシナリオはMigrations and exclusive locksでさらに確認できる

  • 長時間トランザクションも危険

    • トランザクションは複数のデータベース文をall-or-nothingでまとめる方法で、BEGINで始まりCOMMITで終わる
    • トランザクション中の変更は他のクライアントには見えず、COMMIT時にデータベースへ公開される
    • 送金のように、ある口座残高の減少と別の口座残高の増加が一緒に成功するか一緒に取り消されるべき操作に向いている
    • トランザクションがロックを取得すると、COMMITまでロックを保持する
    • BEGIN後に特定のrowをUPDATEして席を離れると、他のクライアントによるそのrowのDELETEは、トランザクションがcommitされるまで止まったままになる
    • 必要以上に長く開かれたトランザクションは、他のクライアントのクエリや更新をブロックする可能性がある

JSONBは鋭利な道具

  • JSONBの性能とスキーマ問題

    • JSONBは柔軟だが、誤って使うとデメリットが大きい
    • PostgresはJSONBカラムの統計を追跡しないため、単一のJSONBカラムに対する等価クエリは、通常カラム群に対するクエリよりはるかに遅くなる可能性がある
    • ある事例では、JSONBのために2000倍遅くなる例を見ることができる
    • JSONBカラムには事実上何でも入れられるため強力だが、構造に関する保証は少ない
    • 通常のテーブルはスキーマを見てクエリ結果を予測できるが、JSONBではkey名がcamelCaseなのかsnake_caseなのか、状態がbooleanなのかenumなのか確実ではない
    • 通常のPostgresデータが持つ静的型の性質は、JSONBには同じ形では適用されない

  • JSONB型比較のぎこちなさ

    • backpacksテーブルのJSONBカラムdataで、brandフィールドがJanSportの行を探そうとするとき、次のクエリは動作しない
    select * from backpacks where data['brand'] = 'JanSport';
    • Postgresは比較の右側の型が左側の型と一致することを期待し、右側は正しいJSON文書でなければならない
    • JSON文書はオブジェクト、配列、文字列、数値、boolean、nullでなければならないため、JanSport単独は有効なJSONではない
    • 正しいクエリは、JSON文字列として比較するか、左側をPostgresのtextに変換する方法である
    select * from backpacks where data['brand'] = '"JanSport"';

select * from backpacks where data['brand'] = '"JanSport"'::jsonb;

select * from backpacks where data->>'brand' = 'JanSport';
    • SQLのNULLとJSONBのnullは異なる挙動をする
      • 'null'::jsonb = 'null'::jsonbはtrueだが、NULL = NULLはNULLである
    • JSONBには専用の演算子と関数が多く、一度に覚えるのは難しい
    • PostgresにはJSON値をテキストとして保存するJSONと、効率的なバイナリ形式に変換するJSONBの両方がある
    • JSONBにはインデックス作成が可能といった利点があり、JSON形式は特殊な場合と見なせる

関連記事

2件のコメント

 
bbulbum 2024-11-19

やってはいけないこと、いつか一度読んでみたいです
 
GN⁺ 2024-11-13
Hacker News の意見

  • PostgreSQL は基本的に 大文字小文字を区別するが、SQL キーワードを大文字で書くのは、たいてい視覚的なパターンマッチングによって可読性を高めるための工夫である
    必須ではないものの、他人のクエリをデバッグしなければならないなら、prettifier にかけて細かな構文上の見た目に引っかからず、定義を素早く見渡すだろう
    他の言語でコードを整えるのと同じく、一貫したインデントのような視覚的構造は、当たり前の部分を理解する時間を減らし、重要な点に集中させてくれる
    ただし actuallyUsingCaseInIdentifiers のように識別子で実際に大文字小文字を混在させるのは本当に嫌いで、CLI で確認するために 二重引用符が必要なカラムは見たくない

    • 大文字の識別子は差し替え可能なブロックのように見え、小文字が持つ 単語の形より読む速度を落とす
    • SQL を対話的に扱うとき、この区別を知っているのはかなり役に立つ
      誰にも見られない一時的なクエリを素早く打って捨てるなら大文字小文字は気にしないが、リポジトリにコミットされる SQL ではコマンドを ALL CAPSで書く
    • 大文字は白黒画面での 構文ハイライトの役割だったと理解している
      カラーがある今ではもう不要だが、古い記憶なので根拠資料はない
    • PostgreSQL は識別子を小文字に畳み込む一方、標準では大文字に畳み込むため、大文字小文字の扱いでは標準に反している
      それでも引用符付き識別子と引用符なし識別子を混ぜるべきではないし、内部構造の参照もおおむね標準化されていないので、大きな意味はない
    • SQL 用の prettifier やリンターのおすすめが気になる

  • PostgreSQL Wiki の「don’t do this」項目は初めて見たが、かなり有用だ: https://wiki.postgresql.org/wiki/Don%27t_Do_This

    • こうした機能がそれほど陥りやすい罠なら、なぜ 廃止予定にしないのか気になる
      たとえば新しいスキーマではテーブル継承のような機能を無効化し、再度有効にするには意図的に複雑な設定を要求するほうがよさそうに見える
    • SQL Anti-patterns を思い出す。データベースを扱う人なら全員読むべき本だと思う
    • MySQL 側で身につけた習慣をいくつか見直すきっかけになった

  • ここに出ている多くの内容は PostgreSQL だけに当てはまるものではない
    NULL の奇妙な挙動、インデックスカラムの順序などがそうで、特に NULL とインデックス/ユニーク制約の相互作用は MySQL でも直感的ではない
    たとえば email は NULL 不可、username は NULL 可のユーザーテーブルに (email, username) のユニーク制約を付けると、username が NULL の同じ email を複数回入れられる。NULL は他の NULL と等しくないためである

    • 参考までに、PostgreSQL 15 以降では、制約とユニークインデックスで NULLS [NOT] DISTINCT によりこの挙動に影響を与えられる
      https://www.postgresql.org/docs/devel/sql-createtable.html#S...
    • このデフォルトは実用上妥当だと思う
      逆の挙動が必要なユースケースはずっと少ない

  • 「よい理由がなければデータを正規化せよ」とだけ言って済ませるのはよくない
    著者がリンクしたページにも 正規形が非正規形まで含めて 11 種類も出てくるが、ほとんどの人はそれが何かも知らず、そのうち 7 種類は使うこともない
    より高い正規形を探し回らせるべきではない

    • それでも著者はおおむね何を意味するのかを説明する段落を付けており、その方向性は正しいと思う
      最近移ったプロジェクトでも、こうした問題をいくつか直さなければならなかったが、データを重複させる理由はほとんどない
    • この記事が初心者向けなら、確信がないときの答えはほぼ常に 第3正規形である
    • 一般的なルールは、できるだけ正規化したうえで、必要な性能が出るまで 非正規化することだ

  • 最初のヒントは、毎日 VACUUM せよというものだ
    始めた当初これを知らず、reddit のデータベースにまったく VACUUM をしていなかった。そしてある日やむを得ず実行したところ、終わるのを待つあいだ reddit はほぼ丸一日落ちていた

    • autovacuum がなかったのだろう
      reddit の規模なら トランザクション IDが先に枯渇しなかったのが驚きだ

  • 開発者には正規化をもっと気にして、何でもかんでも JSONB カラムに押し込むのをやめてほしい

    • データベースが構造化された JSON を保存できるようになるずっと前から、ジュニア開発者たちは適切な正規化の度合いをめぐって激しく机上の議論をしていた
      より経験豊富な開発者は、正解はキーを除けば何も重複させず、本当にやむを得ない場合にだけ非正規化することだと知っていた
      その後 Mongo のようなデータベースが登場し、正規化が難しい、あるいは意味をなさない「データベースっぽいもの」を提供して、そうしたジュニアを焚きつけた。その結果、ひどいデータベース設計と保守不能なガラクタの塔が一時的に繁栄した
      いまや振り子は戻り、正規化されたデータベースの利点が再発見されたが、JSON カラムはいまだに悪い慣行が育つ抜け道になっている
    • JSONB カラムを使う理由は二つある
      第一に、JSON を保存するためだ。Web サーバーがサードパーティ API を呼び出すとき、元の API レスポンスをJSONB カラムに保存してからそこで処理すれば、その API に由来する問題をデバッグするときに監査可能な記録が残る
      第二に、直和型（sum type）を保存するためだ。SQL が直和型をサポートしていないことは、SQL データベースでデータをモデリングするうえで最大の欠陥と言える
      回避策はいろいろあり、「単に JSONB カラムに入れてアプリケーションで検証する」のもその一つだが、どの回避策も特に優れているわけではない
    • 正規化を気にしていても、結局雑多な JSONB の引き出しができることは多い
      JSONB 内の値を別カラムに引き上げず、その中でひどいクエリを書いているのでなければ、それ自体が大きな問題だとは思わない
    • 最近こうしたツールを使う開発者の多くは、実質的に自分たちだけのデータベース管理システムを作り、永続化だけを別の DBMS に任せている
      永続化の要件さえうまく満たせれば、良い設計を考える強い圧力がないためだ
      DBMS の上にさらに DBMS を作るのが正しいのかは疑問だが、いずれにせよ現状はそうなっている
    • この方式がうまくいくには、スキーマ変更をロールバックできる能力まで含めたスキーママイグレーション手順が必要だ
      新しいカラムが性能を壊したり問題を起こしたりしたら、戻せなければならない
      CLI ツールが関係しているなら、どの程度のダウンタイムを許容できるのか、会社全体で同期したバージョンアップが可能なのか、それとも当面は旧・新スキーマの両方をサポートするのかまで扱う必要がある
      データベースがチームの主力製品の一部でないなら、こうしたものがすべて抜け落ちているかもしれない

  • 初心者を助けるためにこの記事を書いた: https://tomcam.github.io/postgres/

  • 記事は本当に良くて、PostgreSQL のドキュメントが3200ページもあるとは知らなかった
    しばらく使ってきて必要に応じて学んでいるが、公式ドキュメントもかなり気に入っているし、特定のトピックが必要になったときに関連記事を読むのも好きだ
    著者が https://challahscript.com/what_i_wish_someone_told_me_about_... に、(b, a) カラムインデックスは b だけで検索するときにうまく機能すると付け加えると、読者の役に立つと思う
    a だけで検索する話をしている箇所である程度示唆されているが、もう少し明示しても悪くない
    JSON/JSONB の部分はほとんど使っていないので、あまり見ていない

  • 実際の現場で見た滑稽な SQL の数々を思い出すと、Codd の論文を読んでリレーショナルモデルとは何かを理解するところから始めるとよいと思う
    11ページしかなく、それを読むだけでもこの世の苦しみは減るはずだ

  • この記事のほぼすべては、MySQL のような他のMVCC データベースにも当てはまる
    細部は違うかもしれないが、MySQL も長いトランザクションに悩まされ、ALTER 中にメタデータロックを取るなど、似たような面白い問題がある