- Postgres をより生産的かつ安全に使うのに役立つ実践的なパターンをまとめた記事
- それぞれのパターンは小さいものの、積み重なると大きな違いを生む
UUID主キーを使う
- UUID はランダムであるため、ソートやインデックス性能の面で不利な点がある
- 数値 ID よりも多くの領域を消費する
- ただし、次のような利点がある
- DB に接続しなくても UUID を生成できる
- 外部に安全に公開できる
gen_random_uuid() を使って主キーとして UUID を自動生成できる
created_at と updated_at フィールドは常に追加する
- デバッグ時には、レコードの作成時点と変更時点が分かることが非常に有用
updated_at はトリガーによって自動更新されるように設定できる- 関数は一度だけ作成し、トリガーは各テーブルに適用する必要がある
外部キーには on update/delete restrict を設定する
- 外部キー制約を設定する際は、
on update restrict on delete restrict を必ず使うべき
- データ削除時に誤って連鎖削除が発生するのを防ぐ
- ストレージは安価だが、データ復旧は非常に難しいため、保守的に扱うのがよい
スキーマの利用を推奨
- デフォルトスキーマは
public だが、アプリケーションが大きくなったら別スキーマに分けるのがよい
- スキーマは名前空間のように機能し、異なるスキーマ間でも JOIN できる
- テーブル数が増えるほど、スキーマを活用する方が可読性と保守性に優れる
Enumテーブルパターンを使う
- PostgreSQL の enum 型や check constraint の代わりに、enum テーブルを使う方が柔軟
- enum 値を別テーブルで管理すれば、メタデータを追加したり、enum 値を簡単に拡張したりできる
- 外部キーで enum テーブルの値を参照して制約を維持する
テーブル名は単数形にする
- テーブル名は複数形ではなく単数形にするのが望ましい
- クエリを書く際に単数形の方が明確で、複数形は所有格や意味上の混乱を招くことがある
JOINテーブルは機械的に命名する
- 多対多関係のための JOIN テーブルは、2つのテーブル名をつなげて命名するのが安全で明確
- 例:
person_pet
- 組み合わせに対するユニークインデックスを追加して重複を防ぐ
削除の代わりに soft delete を使う
- データを実際に削除するより、削除時点を示す
revoked_at のような timestamp フィールドを使う方がよい
- 削除されたかどうかだけでなく、いつ削除されたかも追跡できる
- Boolean 値より timestamp の方が多くの情報を提供する
ステータスはログテーブルで表現する
- 単一カラムでステータスを表す代わりに、ステータス変更履歴を別テーブルに保存する
- ステータス発生時点は
valid_at カラムで明示する
- 最新ステータスを素早く取得できるよう、
latest フラグとユニークインデックス + トリガーを設定する
- これは非同期イベント処理や順序が前後し得る状況で有利
特別な行には system_id を追加する
- enum テーブル以外でも、特定の「システム行」が必要な場合がある
system_id テキストフィールドを nullable で追加し、ユニークインデックスを設定するsystem_id によって特定の行を明確に取得できる
ビュー（View）は最小限に使う
- ビューは複雑なクエリを抽象化するのに便利だが、保守が難しい
- カラム削除時にビューの再作成が必要
- ビューの上にビューを作ると、性能や可読性の問題が生じる
- 必要な分だけ慎重に使うこと
JSONクエリを積極的に活用する
- Postgres は JSON の保存だけでなく、JSON を返すクエリも非常に強力
- ネストした関係を1回のクエリで JSON 形式として返せる
- N+1 問題なしに必要なすべてのデータを一度に取得できる
- 欠点: 型情報が失われること、全データを一度にメモリへ読み込む必要があること
- それでも性能面や構造面での利点の方が大きい
4件のコメント
> 中間テーブルの名前は機械的に付ける
名前を付けるときにこういうルールがあること自体が良いと思います〜
UUID7を考慮すれば、時系列でのソートは可能なのではないでしょうか？
PostgreSQLでUUIDを主キーとして使用することについて の記事も参考になるでしょう。
soft delete 時に timestamp を入れる方法、いいですね。 基本キーに UUID を入れると時系列順に並べられないので、snowflake id や ulid を使うのもよさそうです。この場合は各サーバーが sequence number を持っている必要はありますが。