GitLabエンジニア必見！PostgreSQL活用の基礎知識

By /

GitLabエンジニア必見！PostgreSQL活用の基礎知識と実践ガイド

はじめに：なぜGitLabエンジニアはPostgreSQLを知る必要があるのか

GitLabは、その中核でPostgreSQLをリレーショナルデータベースとして利用しています。ユーザー情報、プロジェクト、リポジトリのメタデータ、CI/CDパイプライン、イシュー、マージリクエストなど、GitLab上のあらゆる情報はPostgreSQLに格納されています。したがって、GitLabの開発、運用、トラブルシューティングに携わるエンジニアにとって、PostgreSQLの理解は避けて通れません。

PostgreSQLの知識は、単にSQLを読み書きできるというレベルを超え、データベースの内部構造、パフォーマンス特性、運用上の考慮事項、そしてGitLabのコードベースとの連携方法に至るまで、多岐にわたります。これらの知識は、以下の状況で非常に役立ちます。

  • 機能開発: 新しい機能を実装する際に、効率的なデータベーススキーマ設計、適切なインデックスの使用、N+1クエリの回避など、データベースレベルでの考慮は必須です。
  • パフォーマンス改善: 遅い機能やAPIエンドポイントの原因を特定し、データベースクエリの最適化やインデックスの追加によって改善します。
  • 運用とトラブルシューティング: データベースの負荷が高い、ストレージが逼迫している、特定のエラーが発生しているなどの問題に対し、原因を特定し、適切な対策を講じます。
  • スケーリング: GitLabの規模が大きくなるにつれて発生するデータベースの課題（例: 接続数の増加、特定のテーブルへのアクセス集中）に対応するため、レプリケーションやシャーディングなどの知識が必要になります。
  • コードレビュー: 他のエンジニアが書いたデータベース関連のコード（マイグレーションやActiveRecordクエリ）のレビューにおいて、パフォーマンスや堅牢性の観点から適切なフィードバックを提供できます。

本記事では、GitLabエンジニアが知っておくべきPostgreSQLの基礎から応用までを網羅的に解説します。PostgreSQLの基本的な概念、内部アーキテクチャ、パフォーマンスチューニングの方法、運用上のベストプラクティス、そしてGitLab開発における具体的な注意点など、実践的な知識を中心に掘り下げていきます。この一冊を読むことで、あなたのPostgreSQLスキルが飛躍的に向上し、GitLabエンジニアとしての能力をさらに高められるでしょう。

PostgreSQL基礎の基礎：リレーショナルデータベースの概念と主要要素

まずは、PostgreSQLがどのようなデータベースシステムなのか、その基本的な概念から理解を始めましょう。

リレーショナルデータベース（RDBMS）とは

PostgreSQLは、RDBMS、つまりリレーショナルデータベース管理システムです。RDBMSは、データを「テーブル」と呼ばれる二次元の構造で表現し、これらのテーブル間を「リレーション（関連）」で結びつけることでデータを管理します。

  • テーブル: スプレッドシートのような行と列を持つ構造です。特定の種類の情報（例: ユーザー一覧、プロジェクト一覧）を格納します。
  • 行 (Record/Tuple): テーブル内の個々のデータ項目です。例えば、ユーザーテーブルの一つの行は、特定のユーザーの情報（ID, ユーザー名, メールアドレスなど）を表します。
  • 列 (Column/Attribute): テーブル内の特定の種類のデータを格納するフィールドです。例えば、ユーザーテーブルの「ユーザー名」や「メールアドレス」といった項目がこれにあたります。各列は特定のデータ型（文字列、数値、日付など）を持ちます。
  • リレーション (Relation): 複数のテーブル間を結びつける関連性です。通常、一方のテーブルの列（主キー）が、もう一方のテーブルの列（外部キー）を参照することで表現されます。例えば、プロジェクトテーブルのowner_id列が、ユーザーテーブルのid列を参照することで、「どのユーザーがそのプロジェクトのオーナーか」という関連性を表現できます。

RDBMSの強みは、データの整合性を保ちやすいこと、そしてSQL（Structured Query Language）という標準的な言語を使ってデータの操作（検索、挿入、更新、削除）を柔軟に行える点にあります。

PostgreSQLの特徴

PostgreSQLは、数あるRDBMSの中でも特に以下のような特徴を持っています。

  • オープンソース: 無償で利用でき、ソースコードが公開されています。活発なコミュニティによって開発・サポートされています。
  • 高機能: 標準SQLへの準拠度が高く、トランザクション処理、サブクエリ、ビュー、ストアドプロシージャ（PostgreSQLでは関数と呼びます）、トリガーなど、豊富な機能を備えています。また、JSON/JSONB型、幾何データ型、配列型など、多様なデータ型をサポートします。
  • 信頼性と堅牢性: ACID特性（Atomicity, Consistency, Isolation, Durability）を完全に満たしており、データの永続性と整合性を強力に保証します。Write-Ahead Logging (WAL) システムにより、クラッシュからの回復力も高いです。
  • 拡張性: 多くの拡張機能（Extension）が提供されており、GIS機能（PostGIS）、全文検索機能、外部データソースへのアクセス機能などを追加できます。
  • 大規模環境への対応: レプリケーション、パーティショニング、外部データラッパー（FDW）など、大規模なデータや高負荷なアクセスに対応するための機能が充実しています。GitLab.comのような非常に大きなスケールでもPostgreSQLが基盤として利用されています。

主要なデータ型

PostgreSQLは非常に多くのデータ型をサポートしていますが、GitLab開発で特によく利用される主要なデータ型をいくつか紹介します。

  • 数値型:
    • smallint, integer, bigint: 符号付き整数。格納できる値の範囲が異なります。IDやカウントなどによく使われます。
    • decimal, numeric: 厳密な数値計算が必要な場合に利用されます。通貨や精密な測定値など。
    • real, double precision: 浮動小数点数。近似値で十分な場合に使用されます。
  • 文字列型:
    • varchar(n): 最大n文字の可変長文字列。長さを指定するのが一般的ですが、指定しない場合は上限なしのvarcharとしても使えます。
    • text: 長さの制限がない可変長文字列。説明文や長いテキストデータに便利です。GitLabではtext型が多用されます。
  • 真偽値型:
    • boolean: 真 (true)、偽 (false)、不明 (null) の3つの値を取ります。フラグや状態を表すのに使われます。
  • 日付/時刻型:
    • date: 日付のみ（例: 2023-10-27）。
    • time: 時刻のみ（タイムゾーンなし）。
    • timestamp: 日付と時刻（タイムゾーンなし）。
    • timestamp with time zone (timestamptz): 日付と時刻（タイムゾーン付き）。PostgreSQLは入力されたタイムゾーン付きの時刻をUTCに変換して内部に保持し、表示時にクライアントのタイムゾーンに合わせて変換します。GitLabでは通常、timestamptzが使用されます。
  • バイナリ型:
    • bytea: バイナリデータを格納します。画像やファイルのデータなど。
  • JSON型:
    • json: 入力されたJSON文字列の構文チェックのみを行い、そのまま格納します。
    • jsonb: 入力されたJSONデータをバイナリ形式に分解して格納します。検索や操作が高速です。JSON内のキーや値に対するインデックス（GINインデックス）を張ることも可能です。GitLabでは設定やメタデータを格納するためにjsonb型がよく利用されます。
  • 配列型:
    • integer[], text[]など: 指定した型の配列を格納できます。複数のタグやIDリストなどを一つのカラムに格納するのに便利ですが、正規化の観点からは注意が必要です。

適切なデータ型を選択することは、データの整合性、ストレージ効率、そしてパフォーマンスに大きな影響を与えます。

SQLの基本操作

SQLはリレーショナルデータベースを操作するための標準言語です。GitLabエンジニアが日常的に使用する基本的なSQL操作を確認しましょう。

  • データの取得 (SELECT):
    sql
    SELECT column1, column2 FROM table_name WHERE condition ORDER BY column1 DESC LIMIT 10;
    特定の条件に合致する行から、指定した列の値を取得します。WHERE句で条件を指定し、ORDER BY句でソート順を指定し、LIMIT句で取得する行数を制限します。
  • データの挿入 (INSERT):
    sql
    INSERT INTO table_name (column1, column2) VALUES (value1, value2);
    新しい行をテーブルに挿入します。
  • データの更新 (UPDATE):
    sql
    UPDATE table_name SET column1 = new_value1 WHERE condition;
    特定の条件に合致する行の列の値を更新します。WHERE句を省略すると、テーブルの全行が更新されてしまうので注意が必要です。
  • データの削除 (DELETE):
    sql
    DELETE FROM table_name WHERE condition;
    特定の条件に合致する行を削除します。WHERE句を省略すると、テーブルの全行が削除されてしまうので、特に本番環境での実行は極めて慎重に行う必要があります。
  • テーブル結合 (JOIN):
    複数のテーブルを共通の列の値に基づいて結合し、関連するデータを一度に取得します。
    sql
    SELECT u.name, p.name FROM users u JOIN projects p ON u.id = p.owner_id;
    （例: ユーザーテーブルとプロジェクトテーブルを結合し、プロジェクトのオーナーの名前とプロジェクト名を取得）
    JOINの種類（INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN）によって、結合条件に一致しない行の扱いが変わります。GitLabではINNER JOINやLEFT JOINがよく使われます。

スキーマ、テーブル、カラム、制約

  • スキーマ (Schema): データベース内の名前空間のようなものです。テーブル、インデックス、ビュー、関数などのデータベースオブジェクトを整理するために使用されます。デフォルトではpublicスキーマが使われますが、大規模なデータベースでは機能や目的に応じてスキーマを分けることもあります。GitLabでは主にpublicスキーマを使用しますが、CI/CDデータベース分離機能では別のスキーマ（例: ci）も利用されます。
  • テーブル (Table): データを格納する基本的な構造です。
  • カラム (Column): テーブル内の各データ項目です。名前、データ型、そして制約を持ちます。
  • 制約 (Constraint): テーブル内のデータが満たすべきルールです。データの正確性と整合性を保証するために重要です。
    • PRIMARY KEY: テーブル内の各行を一意に識別するための列または列の組み合わせです。自動的にインデックスが作成されます。NOT NULLとUNIQUEを兼ね備えます。
    • FOREIGN KEY: 他のテーブルの主キーを参照する列です。テーブル間の関連性を定義し、参照整合性（例えば、存在しないユーザーIDをプロジェクトのオーナーIDとして登録できないようにする）を維持します。
    • UNIQUE: その列の値がテーブル内で一意であることを保証します。
    • NOT NULL: その列がNULL値（値が存在しない状態）を取らないことを保証します。
    • CHECK: その列の値が特定の条件を満たすことを保証します。
    • DEFAULT: 値が指定されなかった場合に自動的に挿入されるデフォルト値を定義します。

これらの基本的な概念とSQL構文は、PostgreSQLを操作し、GitLabのデータベースコードを理解する上での出発点となります。

インデックスの概念と重要性

インデックスは、データベースのパフォーマンスにおいて極めて重要な要素です。テーブルから特定の行を検索する際、インデックスがない場合、データベースはテーブルの全行を順番に調べる必要があります（フルテーブルスキャン）。これはテーブルのサイズが大きくなるにつれて非常に時間がかかります。

インデックスは、テーブルの特定の列の値とその値を持つ行の物理的な場所を記録したデータ構造です。本の索引のように、探している値がどこにあるかを素早く見つけるのに役立ちます。これにより、データベースはフルテーブルスキャンを行う代わりに、インデックスを使って目的の行に直接ジャンプできるようになります。

  • インデックスのメリット:

    • SELECTクエリ（特にWHERE句やJOIN句でインデックスが使われる場合）の検索速度が大幅に向上します。
    • ORDER BY句やGROUP BY句の処理を高速化できる場合があります。
    • UNIQUE制約やPRIMARY KEY制約を実装するために内部的に使用されます。

  • インデックスのデメリット:

    • データの挿入、更新、削除の際に、テーブル本体だけでなくインデックスも更新する必要があるため、これらの操作のオーバーヘッドが増加します。
    • ディスク容量を消費します。
    • インデックスを効果的に利用するためには、どの列にどの種類のインデックスを作成すべきかを適切に設計する必要があります。

GitLabのような大規模なアプリケーションでは、遅いクエリのほとんどがインデックスの欠如や不適切な使用に起因します。効率的なデータベース操作のためには、インデックスを適切に設計・管理することが不可欠です。PostgreSQLは様々な種類のインデックスをサポートしており（B-tree, Hash, GiST, GIN, BRINなど）、用途に応じて使い分けることができます。GitLabでは主にB-treeインデックスが使われますが、jsonbカラム内の検索にはGINインデックス、全文検索関連ではGIN/GiSTインデックスなどが利用されることもあります。

PostgreSQLのアーキテクチャ概要

PostgreSQLがどのように動作しているか、その基本的なアーキテクチャを知ることは、パフォーマンス問題の理解や運用におけるトラブルシューティングに役立ちます。

クライアント-サーバーモデル

PostgreSQLはクライアント-サーバーモデルを採用しています。

  • サーバー (Backend): PostgreSQLデータベースシステム自体を実行しているプロセス群です。クライアントからのリクエストを受け付け、データベースへのアクセスを管理し、クエリを実行します。
  • クライアント (Frontend): データベースに接続し、クエリを送信するアプリケーションやツールです。GitLabのRailsアプリケーションや、psqlコマンドラインツール、GUI管理ツールなどがこれにあたります。

クライアントがデータベースに接続要求を送信すると、サーバー側のメインプロセス（postgres）が新しい「バックエンドプロセス」をフォーク（または生成）して、そのクライアントとの通信を担当させます。各クライアント接続は、専用のバックエンドプロセスによって処理されます。

プロセスの構成

PostgreSQLサーバーは、いくつかの種類のプロセスが連携して動作しています。

  • Postgresプロセス (メインプロセス): サーバー起動時に最初に実行されるプロセスです。設定ファイルの読み込み、ポートの待受、クライアントからの接続要求の受け付け、そして新しいバックエンドプロセスの起動を行います。
  • バックエンドプロセス: クライアント接続ごとに生成され、そのクライアントからのSQLクエリを実行します。共有メモリへのアクセス、データファイルの読み書き、トランザクション管理などを行います。
  • バックグラウンドライター (bgwriter): 共有メモリ上のダーティページ（変更されたがディスクに書き込まれていないデータページ）を定期的にディスクに書き出すプロセスです。これは、バックエンドプロセスが直接書き込みを行う頻度を減らし、クライアントの応答時間を短縮するために行われます。
  • WALライター (walwriter): WALバッファ上のWALレコードを定期的にWALファイルに書き出すプロセスです。WALはトランザクションの永続性（Durability）を保証するために非常に重要です。
  • チェックポインター (checkpointer): 定期的に「チェックポイント」処理を行います。チェックポイントでは、共有メモリ上の全てのダーティページをディスクにフラッシュし、WALファイルの古い部分を再利用可能にします。これにより、クラッシュリカバリの際にスキャンする必要があるWALの量が減り、リカバリ時間を短縮できます。
  • オートバキュームランチャー (autovacuum launcher): autovacuumワーカープロセスを起動・管理するプロセスです。
  • オートバキュームワーカー (autovacuum worker): バックグラウンドでVACUUMおよびANALYZEコマンドを実行するプロセスです。テーブルのデッドタプルをクリーンアップし、プランナーのための統計情報を更新します。GitLabのような更新・削除が多いシステムでは、autovacuumの設定と動作がパフォーマンスに大きく影響します。

共有メモリとWAL

  • 共有メモリ: 複数のバックエンドプロセスが共有して利用するメモリ領域です。データキャッシュ（shared_buffers）、WALバッファ、ロック情報などが格納されます。データキャッシュは、ディスクI/Oを減らし、データへのアクセス速度を向上させる上で非常に重要です。
  • WAL (Write-Ahead Logging): すべてのデータベース変更は、実際のデータファイルに書き込まれる前に、まずWALファイルに追記されます。これにより、システムクラッシュが発生しても、WALファイルを再生することで直前のコミット状態までデータベースを回復できます（クラッシュリカバリ）。WALはデータの永続性と信頼性の基盤であり、レプリケーションにも利用されます。

データファイル構造

PostgreSQLのデータは、PGDATAと呼ばれるデータディレクトリ内に格納されます。

  • ベースディレクトリ: テーブルやインデックスのデータファイルが格納されます。ファイル名はOID（オブジェクト識別子）に基づいています。一つの大きなテーブルは複数のファイルに分割されることもあります（1GBごとに新しいファイル）。
  • WALディレクトリ (pg_wal): WALファイルが格納されます。WALファイルは通常16MB単位で管理されます（設定で変更可能）。
  • グローバルディレクトリ (global): システムカタログ（データベースのメタデータ）が格納されます。

これらのファイル構造を直接操作することは通常ありませんが、ディスク容量の確認やバックアップ・リカバリの際には、これらのディレクトリの存在を意識する必要があります。

GitLabにおけるPostgreSQL

GitLabはPostgreSQLをどのように利用し、どのような特徴的な使い方をしているのでしょうか。

GitLabが格納するデータ

GitLabインスタンスが稼働すると、以下のような多種多様なデータがPostgreSQLに格納されます。

  • ユーザー情報（アカウント、プロフィール設定）
  • プロジェクト情報（名前、説明、設定）
  • グループ情報
  • リポジトリ情報（コミット、ブランチ、タグなどのメタデータ。実際のGitリポジトリデータはファイルシステムに格納されます）
  • イシュー、マージリクエスト、エピック、タスク
  • コメント、ディスカッション
  • CI/CDパイプライン、ジョブ、アーティファクトのメタデータ（実際のアーティファクトはオブジェクトストレージ等に格納）
  • Wiki、スニペット
  • 権限設定、メンバー情報
  • システム設定、フィーチャーフラグの状態
  • 監査ログ

これらのデータは、数十から数百のテーブルに分散して格納されています。GitLabの機能が増えるたびに、新しいテーブルが追加されたり、既存のテーブルにカラムが追加されたりします。

GitLabのスキーマ設計概要

GitLabのデータベーススキーマは非常に大きく、複雑です。主要なテーブル名だけでもいくつか紹介します（これらは頻繁に変更される可能性があります）。

  • users: ユーザーアカウント情報
  • projects: プロジェクト情報
  • namespaces: グループや個人のトップレベルの名前空間情報
  • merge_requests: マージリクエスト情報
  • issues: イシュー情報
  • notes: コメントやディスカッション（イシュー、MR、コミットなどに紐づく）
  • ci_pipelines: CI/CDパイプライン情報
  • ci_builds (ci_jobs): CI/CDジョブ情報
  • members: プロジェクトやグループのメンバーシップ情報
  • system_note_metadata: システムノート（ステータス変更など）のメタデータ
  • commits: コミットのメタデータ（実際のリポジトリ内のコミットとは別に、表示のためにDBにキャッシュされる情報など）

これらのテーブルは、主キーと外部キーによって密接に関連付けられています。例えば、projectsテーブルはusersテーブルをowner_idで参照し、merge_requestsテーブルはprojectsテーブルをtarget_project_idやsource_project_idで参照するといった具合です。

GitLabのスキーマ設計は、機能の要件、パフォーマンス、スケーラビリティのバランスを取りながら進化しています。非常に多くのテーブルとリレーションが存在するため、特定の機能に関連するデータがどのテーブルにどのように格納されているかを理解するには、コードベース（特にActiveRecordモデルの定義）やデータベースマイグレーションファイルを読むことが重要です。

ActiveRecord（Ruby on Rails）とPostgreSQLの連携

GitLabはRuby on Railsフレームワーク上に構築されており、データベース操作の大部分はActiveRecord ORM（Object-Relational Mapper）を通じて行われます。ActiveRecordは、Rubyオブジェクトとデータベースのテーブル/行をマッピングし、Rubyコードからデータベースを操作できるようにします。

例えば、ユーザーを取得するRailsコードは以下のようになります。

ruby
user = User.find(1)
project = user.projects.first

このコードは、内部的に以下のようなSQLクエリを生成し、PostgreSQLに送信します。

“`sql
— User.find(1) が生成するクエリの例
SELECT “users”.* FROM “users” WHERE “users”.”id” = $1 LIMIT $2 [[“id”, 1], [“LIMIT”, 1]]

— user.projects.first が生成するクエリの例 (ActiveRecordのリレーション機能)
SELECT “projects”.* FROM “projects” WHERE “projects”.”owner_id” = $1 LIMIT $2 [[“owner_id”, 1], [“LIMIT”, 1]]
“`

ActiveRecordは非常に便利ですが、その動作を理解していないと、非効率なクエリ（特にN+1クエリ）を簡単に生成してしまう可能性があります。

N+1クエリ問題:
親オブジェクトを取得し、その後、子オブジェクトを繰り返し取得するループ内で、子オブジェクトを取得するためのクエリが親オブジェクトの数だけ実行されてしまう問題です。

“`ruby

N+1クエリの例

projects = Project.limit(10)
projects.each do |project|
puts project.owner.name # <– ここで各projectに対して別途ユーザーを取得するクエリが実行される
end

実行されるクエリ:

SELECT * FROM projects LIMIT 10; (1クエリ)

SELECT * FROM users WHERE id = …; (プロジェクトの数だけクエリ)

“`

この問題は、ActiveRecordのincludes, preload, eager_loadといったメソッドを使って、関連オブジェクトをまとめて事前にロードすることで回避できます。

“`ruby

N+1クエリを回避した例

projects = Project.includes(:owner).limit(10) # プロジェクトと関連するオーナーを事前にロード
projects.each do |project|
puts project.owner.name # <– 既にロードされているので、追加のクエリは発生しない
end

実行されるクエリ:

SELECT * FROM projects LIMIT 10; (1クエリ)

SELECT * FROM users WHERE id IN (…); (関連するオーナーのIDリストを使った1クエリ)

“`

GitLab開発においては、ActiveRecordが生成するSQLを意識し、N+1クエリのようなパフォーマンス上のボトルネックを回避するためのコーディングスキルが非常に重要です。開発環境でSQLログを確認する習慣をつけると良いでしょう。

PostgreSQLのパフォーマンスチューニング

GitLabのような大規模なアプリケーションにおいて、データベースのパフォーマンスはアプリケーション全体の応答性に直結します。PostgreSQLのパフォーマンスチューニングは、GitLabエンジニアにとって継続的に取り組むべき重要な課題です。

なぜパフォーマンスチューニングが必要か

GitLab.comのような大規模インスタンスでは、数百万のユーザー、数千万のプロジェクト、億単位のCI/CDジョブなどが存在します。これらのデータに対する毎秒数万件のデータベース操作が発生します。不適切なクエリ、インデックスの不足、非効率な設定は、即座にデータベースサーバーのリソース枯渇（CPU、メモリ、ディスクI/O）や、アプリケーション全体の遅延、タイムアウトを引き起こします。

パフォーマンスチューニングの目的は、以下の点を達成することです。

  • クエリ応答時間の短縮: ユーザー操作やAPIリクエストの応答性を向上させる。
  • スループットの向上: 単位時間あたりに処理できるリクエスト数を増やす。
  • リソース使用率の最適化: サーバーのリソース（CPU, メモリ, ディスク, ネットワーク）を効率的に利用し、コストを抑える。
  • システムの安定性向上: 高負荷時でもシステムがクラッシュしたり応答不能になったりしないようにする。

EXPLAIN/EXPLAIN ANALYZE の使い方

遅いクエリの原因を特定するための最も基本的なツールは、EXPLAINコマンドです。クエリの実行計画（プラン）を表示します。

sql
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

EXPLAINは、オプティマイザがそのクエリをどのように実行しようと計画したかを示します。どのテーブルをどのような順序でスキャンするか、どのインデックスを使用するか、どのようにテーブルを結合するか、フィルタリングをどこで行うかなどが含まれます。

EXPLAIN ANALYZEを使うと、実際にクエリを実行し、各ステップにかかった時間や、実際に処理された行数などの統計情報を取得できます。

sql
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

EXPLAIN ANALYZEの出力例（抜粋）:

Seq Scan on users (cost=0.00..36.50 rows=1 width=261) (actual time=0.015..0.016 rows=1 loops=1)
Filter: (email = '[email protected]'::text)
Rows Removed by Filter: 99
Planning Time: 0.060 ms
Execution Time: 0.029 ms

出力から読み取るべき重要な情報：

  • Node Type: Seq Scan (シーケンシャルスキャン = フルテーブルスキャン), Index Scan, Index Only Scan, Bitmap Scan, Hash Join, Merge Join, Nested Loop Join, Sort, Aggregate など。
  • Cost: クエリ実行にかかる推定コスト（ディスクI/Oを1、CPU処理を0.01とした相対的な値）。..で示される範囲は、「開始コスト..合計コスト」です。この値が低いほど良いプランと推定されます。
  • Rows: 各ステップで処理されると推定される行数。
  • Width: 各行の推定バイトサイズ。
  • Actual Time: EXPLAIN ANALYZEでの実際の実行時間（「開始時間..終了時間」）。
  • Loops: そのノードが実行された回数。
  • Rows Removed by Filter: フィルタリングによって除外された行数。

EXPLAIN ANALYZEの出力から、以下のような問題を特定できます。

  • Seq Scan: 大規模なテーブルでインデックスが使えるはずの条件なのにフルテーブルスキャンになっている場合、インデックスが存在しないか、インデックスが使えない条件になっている可能性があります。
  • CostとActual Timeの乖離: オプティマイザの推定（Cost）と実際の実行時間（Actual Time）が大きく乖離している場合、統計情報が古いか不正確である可能性があります。
  • 大量のRows: 期待以上に多くの行が処理されているステップがある場合、フィルタリングが効率的に行われていない可能性があります。

EXPLAIN ANALYZEは、パフォーマンスチューニングの最初のステップとして不可欠なツールです。

インデックスの重要性と種類

前述のように、インデックスは検索パフォーマンス向上に不可欠です。どのようなカラムにインデックスを張るべきか、どのような種類のインデックスを使うべきかを理解しましょう。

どのカラムにインデックスを張るべきか:

  • WHERE句やJOIN句で頻繁に使用されるカラム。
  • ORDER BY句やGROUP BY句で頻繁に使用されるカラム。
  • UNIQUE制約やPRIMARY KEY制約を持つカラム（これらは自動的にインデックスが作成されます）。
  • FOREIGN KEYを持つカラム（参照整合性のチェックやJOINの効率化に役立ちます）。

インデックスの種類:

  • B-tree: 最も一般的で汎用性の高いインデックスです。等価検索 (=)、範囲検索 (>, <, >=, <=, BETWEEN)、パターンマッチング (LIKE 'prefix%')、ソート (ORDER BY) に効果的です。GitLabで作成されるインデックスの大部分はB-treeです。
  • Hash: 等価検索 (=) にのみ効果的です。B-treeよりもコンパクトで構築が速い場合がありますが、クラッシュリカバリ後に再構築が必要な場合があり、B-treeほど汎用性が高くないため、あまり使われません。
  • GiST (Generalized Search Tree): 様々なデータ型やクエリタイプをサポートするためのインフラストラクチャです。空間データ（PostGIS）、全文検索（一部）、ツリー構造データなどに利用されます。
  • GIN (Generalized Inverted Index): 複数の値を格納するデータ型（配列、JSONB）に対して、その中に特定の値が含まれているか (@>, ?, ?& など) を検索するのに非常に効果的です。GitLabのjsonbカラム内の検索に利用されることがあります。
  • BRIN (Block Range INdex): 大規模で物理的に順序付けられているデータに対して、非常に小さく高速なインデックスを提供します。時系列データなどに向いています。

応用的なインデックス:

  • 複合インデックス (Composite Index): 複数のカラムを組み合わせて作成するインデックスです。WHERE a = 1 AND b = 2 のようなクエリで、aとbを含む複合インデックスは効果的です。複合インデックスの順序は重要です（左側のカラムが最初に評価されるクエリで特に有効）。
  • 部分インデックス (Partial Index): テーブル全体ではなく、特定の条件を満たす行のみに作成されるインデックスです。例えば、issuesテーブルで「オープンなイシューのみ」にインデックスを作成するなど。インデックスのサイズが小さくなり、メンテナンスコストが減ります。
    sql
    CREATE INDEX index_issues_on_project_id_and_state ON issues (project_id, state) WHERE state = 'opened';
  • 式インデックス (Expression Index): カラムの値そのものではなく、カラムに対して関数や演算子を適用した結果に作成されるインデックスです。例えば、大文字・小文字を区別しない検索のために、LOWER(email)にインデックスを作成するなど。
    sql
    CREATE INDEX index_users_on_lower_email ON users (lower(email));

インデックスの追加はパフォーマンスを向上させる強力な手段ですが、無闇に追加するとINSERT/UPDATE/DELETEのパフォーマンスを劣化させ、ディスク容量を圧迫します。EXPLAIN ANALYZEの結果に基づいて、真に必要で効果的なインデックスのみを追加することが重要です。

GitLabでは、データベースマイグレーションを通じて新しいインデックスが追加されますが、実行時間の長いマイグレーション（特に大規模テーブルでのインデックス作成）はダウンタイムや高負荷の原因となる可能性があります。GitLabはGitlab::Database::Migrationヘルパーを提供しており、ロックを最小限に抑えながらインデックスを作成する機能（add_concurrent_indexなど）を提供しています。

統計情報 (ANALYZE/VACUUM ANALYZE)

PostgreSQLのクエリプランナー（オプティマイザ）は、テーブル内のデータの分布に関する統計情報を使用して、最適な実行計画を決定します。この統計情報は、ANALYZEコマンドまたはVACUUM ANALYZEコマンドによって収集されます。

統計情報が古い、または不正確だと、オプティマイザは誤った（非効率な）実行計画を選択し、クエリのパフォーマンスが著しく低下する可能性があります。

autovacuumプロセスは、テーブルの変更量に応じて自動的にANALYZE（またはVACUUM ANALYZE）を実行しますが、大規模な変更があった直後など、手動で実行が必要な場合もあります。

sql
ANALYZE table_name; -- 特定のテーブルの統計情報のみを更新
ANALYZE; -- データベース全体の統計情報を更新

GitLabでは、autovacuumの設定が適切であることが、安定したパフォーマンスを維持する上で非常に重要です。

VACUUMとAUTOVACUUM

PostgreSQLは、UPDATEやDELETEによって行が変更または削除された際、古い行のバージョン（デッドタプル）を即座に物理的に削除しません。代わりに、それらは「参照されなくなった古いバージョン」としてマークされます。これは、MVCC (Multi-Version Concurrency Control) と呼ばれる仕組みによるもので、読み取り操作が書き込み操作をブロックしないようにするためです。

しかし、デッドタプルはディスク領域を占有し続け、テーブルを「肥大化 (bloat)」させます。また、新しいデータを挿入する際に、空き領域を探すために余分な処理が必要になるため、パフォーマンスを低下させる可能性があります。さらに、デッドタプルが一定数を超えると、トランザクションIDの周回（transaction ID wraparound）によるデータベース停止のリスクも発生します。

VACUUMコマンドは、これらのデッドタプルが占める領域を再利用可能にします（ただし、通常、ファイルサイズ自体は縮小しません）。また、トランザクションIDの周回を防ぐためのフリーズ処理も行います。

sql
VACUUM table_name; -- 特定のテーブルをバキューム
VACUUM FULL table_name; -- テーブルを完全に書き直してファイルサイズを縮小（テーブルロックが発生するため注意が必要）

手動でのVACUUMは手間がかかり、適切なタイミングで行わないと問題が発生する可能性があります。そこで登場するのがautovacuumです。

autovacuumプロセスは、バックグラウンドで継続的に稼働し、一定の条件（挿入、更新、削除された行数など）を満たしたテーブルに対して自動的にVACUUMおよびANALYZEを実行します。autovacuumの設定パラメータ（autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factorなど）は、各テーブルの更新頻度やサイズに応じて適切にチューニングすることが重要です。GitLabのような活発に更新されるシステムでは、autovacuumの設定はパフォーマンスと安定性に直接影響します。

設定パラメータの調整

PostgreSQLには数百もの設定パラメータがあり、これらを適切に調整することでパフォーマンスを大幅に改善できる可能性があります。GitLab環境で特に重要なパラメータの例をいくつか挙げます。

  • shared_buffers: データベースサーバーが共有データキャッシュとして使用するメモリ量。OSのファイルシステムキャッシュとは別に、PostgreSQLが直接管理するバッファです。ディスクI/Oを減らす上で最も重要なパラメータの一つです。システムメモリの約25%程度を目安に設定されることが多いですが、ワークロードによって最適値は異なります。
  • work_mem: 各バックエンドプロセスが、ソートやハッシュテーブルなどの一時的な内部操作に使用できるメモリ量。この値が小さすぎると、これらの操作がディスク上で行われ、パフォーマンスが著しく低下します。同時実行されるクエリ数を考慮して設定する必要があります。
  • maintenance_work_mem: VACUUM, ANALYZE, CREATE INDEX, FOREIGN KEYの追加などのメンテナンス操作に使用されるメモリ量。これらの操作を効率化するために、work_memよりも大きめの値を設定することが推奨されます。
  • effective_cache_size: OSのファイルシステムキャッシュを含む、カーネルが利用できる実質的なディスクキャッシュの推定サイズ。この値はPostgreSQLが内部的に使用するわけではありませんが、クエリプランナーが実行計画を立てる際のヒントとして使用されます。システムメモリの大部分（例えば75%以上）に設定されることが多いです。
  • wal_buffers: WALデータをディスクに書き出す前に保持するメモリ量。値を大きくするとコミット処理のI/Oを減らせる可能性がありますが、クラッシュ時のデータロスリスクがわずかに増えます（それでもWAL自体があるため安全性は高いです）。通常はデフォルト値で十分なことが多いです。
  • max_connections: 同時に許可されるクライアント接続の最大数。接続数が多すぎると、サーバーのリソース（特にメモリ）を消費し尽くし、パフォーマンスが劣化したりクラッシュしたりする可能性があります。GitLabアプリケーションサーバーやPgBouncerなどの接続プーラーの設定に合わせて調整します。

これらのパラメータは、サーバーのリソース（CPU、メモリ、ディスク）やワークロードの特性に応じて慎重に調整する必要があります。誤った設定はかえってパフォーマンスを悪化させる可能性があるため、変更を加える際は影響を十分に理解し、テストを行うことが重要です。

接続プーリング

PostgreSQLへの新しい接続を確立するには、ある程度のオーバーヘッド（プロセス生成、認証など）がかかります。GitLabのように多数のアプリケーションプロセスが短時間に多くのデータベース接続を開閉する場合、このオーバーヘッドが無視できなくなり、データベースサーバーの負荷を高めます。

接続プーラー（例: PgBouncer）は、アプリケーションからのデータベース接続要求を受け付け、既存のデータベース接続プールから接続を割り当てるミドルウェアです。これにより、アプリケーションは頻繁に接続を開閉しても、データベースサーバーへの実際の接続数は限定され、接続確立のオーバーヘッドもプールされた接続に対してのみ発生します。

GitLab.comのような大規模環境では、PgBouncerはデータベースのスケーラビリティと安定性を確保するために不可欠な要素となっています。自己ホスト型インスタンスでも、アクティブユーザーが多い場合はPgBouncerの導入を検討する価値があります。

パーティショニング

非常に大きなテーブル（例: ログ、イベント、CI/CDジョブなど）は、クエリ性能やメンテナンス性（VACUUM、インデックス再構築）の課題を引き起こす可能性があります。パーティショニングは、一つの大きなテーブルを、定義されたルール（例: 日付範囲、ハッシュ値）に基づいて複数の小さな「パーティションテーブル」に分割する技術です。

PostgreSQL 10以降は、宣言的パーティショニングがサポートされており、より簡単にパーティショニングを実装できるようになりました。

  • メリット:
    • 特定のパーティションに対するクエリ（例: 特定の日付範囲のデータ検索）が高速になります。
    • VACUUMなどのメンテナンス操作を特定のパーティションに対してのみ実行できるため、処理時間が短縮されます。
    • 古いパーティションの削除やアーカイブが容易になります。
    • パーティションごとに異なるテーブルスペースに配置することで、I/O負荷を分散できます。
  • デメリット:
    • パーティショニングキーを含まないクエリは、すべてのパーティションをスキャンする必要があるため、遅くなる可能性があります。
    • スキーマ設計やクエリ作成が複雑になる場合があります。

GitLabでは、ci_builds（CI/CDジョブ）やaudit_events（監査ログ）のような非常に大規模になる可能性のあるテーブルに対してパーティショニングが利用されています。

PostgreSQLの運用・管理

GitLabインスタンスを安定して稼働させるためには、PostgreSQLデータベースの適切な運用・管理が不可欠です。

バックアップとリカバリ

データの損失は事業にとって壊滅的な影響を与えかねません。定期的なバックアップと、そこからのリカバリ手順の確認は最も重要な運用タスクの一つです。

  • pg_dump: データベースの論理バックアップを作成する標準ツールです。SQLスクリプト形式またはカスタム形式でバックアップを取得できます。リストアはpsqlやpg_restoreコマンドで行います。小規模から中規模のデータベース、または特定のテーブルのみのバックアップに適しています。
    bash
    pg_dump dbname > dbname.sql # SQLスクリプト形式
    pg_dump -Fc dbname > dbname.dump # カスタム形式 (pg_restoreでリストア)
  • ファイルシステムレベルのバックアップ: PGDATAディレクトリ全体をコピーする方法です。データベースが停止している状態であればシンプルですが、オンラインで取得するにはファイルシステムのスナップショット機能などを利用する必要があります。リストアは単にディレクトリを元の場所に戻すだけです。
  • PITR (Point-in-Time Recovery) / ストリーミングレプリケーションからのバックアップ: WALアーカイブとベースバックアップ（pg_basebackupで取得）を組み合わせることで、任意の時点へのリカバリを可能にします。これは大規模でミッションクリティカルなシステムに推奨される方法です。通常、スタンバイサーバーを運用し、そこからベースバックアップを取得し、WALアーカイブと組み合わせます。

GitLabの公式ドキュメントには、GitLabインスタンス全体のバックアップ・リストア手順が詳細に記載されており、PostgreSQLのデータもこれに含まれます。これらの手順は、pg_dumpやpg_basebackupを内部的に利用しています。

ロギング

PostgreSQLのログ設定は、問題発生時の原因特定やパフォーマンス分析に役立ちます。

  • log_destination: ログの出力先（stderr, csvlogなど）。csvlog形式は後からの解析が容易です。
  • logging_collector: ログメッセージを収集し、指定されたファイルにリダイレクトするかどうか。通常はonに設定します。
  • log_directory, log_filename: ログファイルの出力ディレクトリとファイル名形式。
  • log_statement: どのSQL文をログに出力するか（none, ddl, mod, all）。開発やデバッグ時にallに設定すると大量のログが出力されるため注意が必要です。本番環境ではDDLやエラーに関連する設定に留めることが多いです。
  • log_min_duration_statement: 指定した時間（ミリ秒）より長くかかったSQL文のみをログに出力します。遅いクエリを特定するのに非常に役立ちます。例えば、1000と設定すると、1秒以上かかったクエリがログに出力されます。本番環境でパフォーマンス問題の調査に広く使われます。
  • log_lock_waits: ロック待機が発生した場合にログに出力するかどうか。デッドロックやロック競合の問題調査に役立ちます。
  • log_autovacuum_min_duration: autovacuum実行にかかった時間が指定した時間より長かった場合にログに出力するかどうか。autovacuumの動作監視に役立ちます。

これらのログ設定を適切に行うことで、データベースの状況を把握し、パフォーマンス問題やエラーを早期に発見・診断できます。

監視

PostgreSQLの監視は、システムの健全性を維持し、潜在的な問題を proactive に発見するために不可欠です。監視すべき主要なメトリクスは以下の通りです。

  • 接続数: max_connectionsに対する現在の接続数。上限に近づいている場合は注意が必要です。
  • QPS (Queries Per Second): 単位時間あたりのクエリ実行数。ワークロードの指標となります。
  • エラー率: クエリ実行エラーの発生率。
  • ディスクI/O: ディスクの読み書きスループット、キュー長、レイテンシ。ディスクI/Oがボトルネックになっていないかを確認します。
  • CPU使用率: データベースプロセスのCPU使用率。CPUがボトルネックになっていないかを確認します。
  • メモリ使用率: システム全体のメモリ使用率、共有メモリ、ワークメモリの使用状況。スワップが発生していないかなどを確認します。
  • バッファヒット率: 共有バッファからデータを取得できた割合。高いほどディスクI/Oが少なく効率が良いです（通常90%以上が目安）。
  • WAL生成量: WALファイルが生成されるレート。書き込み負荷の指標となります。
  • テーブル/インデックスの肥大化 (Bloat): pg_bloat_checkなどのツールで確認し、VACUUM FULLやテーブルの再構築が必要かを判断します。
  • レプリケーション遅延: プライマリとスタンバイ間のデータ同期の遅延（レプリカラグ）。
  • 遅いクエリ: log_min_duration_statementでログに出力されたクエリを収集・分析します。
  • ロック競合: pg_locksビューやログで確認します。

GitLab OmnibusパッケージにはPrometheus Exporterが含まれており、PostgreSQLを含む様々なコンポーネントのメトリクスを収集・監視できます。また、pg_stat_activity, pg_stat_statementsなどの組み込みビューも、現在の活動や統計情報を取得するのに役立ちます。

レプリケーション

レプリケーションは、データベースの冗長性を確保し、可用性を向上させるための重要な機能です。また、読み取り負荷を分散させるためにも利用されます。

PostgreSQLの主要なレプリケーション方法は「ストリーミングレプリケーション」です。

  • ストリーミングレプリケーション: プライマリサーバーのWALストリームをスタンバイサーバーにリアルタイムに近い形で送信し、スタンバイサーバーがそれを再生することでデータ同期を行います。これにより、プライマリとスタンバイの間で常に最新の状態が維持されます。
    • 同期レプリケーション: クライアントからのコミット要求に対し、プライマリだけでなく設定されたスタンバイサーバーもWALをディスクにフラッシュしたことを確認してから応答を返します。これにより、コミットされたデータが失われないことが保証されますが、スタンバイの遅延がプライマリの書き込み性能に影響します。
    • 非同期レプリケーション: プライマリはWALをローカルディスクにフラッシュした時点でクライアントに成功を返します。スタンバイへの転送はバックグラウンドで行われます。パフォーマンスは高いですが、スタンバイにWALが到達する前にプライマリがクラッシュした場合、わずかなデータロスが発生する可能性があります。

GitLabでは、可用性や読み取り負荷分散のためにストリーミングレプリケーションが利用されます。GitLab.comのような大規模構成では、複数のスタンバイサーバー（リードレプリカ）が運用され、特定の読み取り処理（例: APIリクエストの一部、Webhooksなど）がこれらのリードレプリカにオフロードされる構成が取られることもあります。

PostgreSQL 10で導入された論理レプリケーションは、テーブル単位でのレプリケーションや、異なるバージョンのPostgreSQL間でのレプリケーションを可能にするなど、より柔軟なレプリケーション手段を提供します。これは、データベースのマイグレーションや特定のデータセットの分離などに役立つ可能性があります。

アップグレード

PostgreSQLの新しいバージョンは、パフォーマンス改善、新機能の追加、バグ修正、セキュリティパッチなどを含んでいます。定期的なアップグレードは推奨されますが、慎重な計画と実行が必要です。

PostgreSQLのメジャーバージョン間のアップグレードは、データファイル形式が変更される可能性があるため、単純なファイルの置き換えでは行えません。主なアップグレード方法としては以下があります。

  • pg_upgrade: 異なるバージョンのPostgreSQLインストール間で、既存のデータファイルを再構築せずに直接アップグレードするツールです。最も推奨される方法であり、ダウンタイムを最小限に抑えられます。
  • pg_dumpall/pg_restore: pg_dumpallでデータベース全体の論理バックアップを取得し、新しいバージョンのPostgreSQLにリストアする方法です。シンプルですが、データベースのサイズが大きいほど時間がかかり、ダウンタイムが長くなります。
  • レプリケーションを使用したアップグレード: 新しいバージョンのPostgreSQLサーバーをセットアップし、既存のデータベースから論理レプリケーション（または一時的なストリーミングレプリケーション）を行い、データが同期された後に切り替える方法です。ダウンタイムをほぼゼロにできますが、設定が複雑になります。

GitLab Omnibusパッケージを利用している場合、パッケージのアップグレード手順に従うことでPostgreSQLのアップグレードも行われます。ただし、メジャーバージョンアップグレードの場合は、特別な手順やダウンタイムが必要になることがあります。事前にドキュメントを確認することが重要です。

GitLab開発者が知っておくべきPostgreSQLのベストプラクティス

PostgreSQLの基礎とアーキテクチャを理解した上で、GitLabのコードを書く際に意識すべきデータベース関連のベストプラクティスについて説明します。

効率的なSQLクエリの書き方

ActiveRecordを使用している場合でも、その背後でどのようなSQLが生成されるかを意識し、効率的なクエリを記述することが重要です。

  • 必要なカラムのみを選択する: SELECT * は便利ですが、不要なカラムまで取得すると、データ転送量が増え、キャッシュ効率が悪化する可能性があります。必要なカラムだけを明示的に指定しましょう（ActiveRecordではselectメソッド）。
  • WHERE句の条件を効率的に書く:
    • インデックスが利用できる条件を使用する。関数や演算子をカラムに適用すると、インデックスが使えなくなる場合があります（式インデックスがない限り）。
    • LIKE 'prefix%' のような前方一致検索はB-treeインデックスで高速化できますが、LIKE '%suffix' や LIKE '%substring%' のような後方一致・中間一致検索は通常インデックスを使用できず、フルテーブルスキャンになります（GINインデックスや全文検索機能の利用を検討）。
    • OR条件は、複数のインデックススキャンを組み合わせるか、フルテーブルスキャンになる可能性があります。場合によっては、UNION ALLで分割した方が効率的なことがあります。
    • NOT条件や否定的な条件（<>, !=, NOT IN, IS NOT NULL）はインデックスの利用を妨げたり、効率を下げたりすることがあります。肯定的な条件で書き換えられないか検討しましょう。
  • JOINを適切に使用する: 関連するデータを取得する際は、N+1クエリを避けるためにJOIN（またはActiveRecordのincludesなど）を使用します。結合条件に適切なインデックスが張られていることを確認します。
  • サブクエリやCTE (Common Table Expressions) を活用する: 複雑なクエリは、サブクエリやCTEを使って分解することで、可読性が向上し、オプティマイザがより良い実行計画を立てやすくなる場合があります。
  • EXPLAIN ANALYZEで確認する: 書いたクエリやActiveRecordが生成するクエリが効率的かどうかは、必ずEXPLAIN ANALYZEで実行計画を確認して検証します。特に本番環境と同等かそれ以上のデータ量があるテスト環境で実行することが望ましいです。

データベースマイグレーションの注意点

GitLabの開発サイクルでは、データベーススキーマの変更（テーブル作成、カラム追加・削除、インデックス追加など）が頻繁に行われます。これらの変更はデータベースマイグレーションとして実装され、ユーザーがGitLabをアップグレードする際に実行されます。データベースマイグレーションは、運用中のデータベースに直接変更を加えるため、非常に慎重に扱う必要があります。

  • ロックに注意: スキーマ変更の中には、テーブル全体にロックをかけてしまうものがあります（例: カラムの型変更、デフォルト値の追加/削除 – 特にNOT NULL制約付き）。ロックは、そのテーブルへの他のアクセス（読み書き）をブロックし、アプリケーション全体を停止させてしまう可能性があります。大規模なテーブルに対してロックをかける操作は、ダウンタイムを発生させるか、特別な手法（例: GitLabが提供する非同期マイグレーションヘルパー）を使用する必要があります。
  • インデックスの追加/削除: 大規模なテーブルへのインデックス作成は時間がかかり、I/O負荷を高めます。PostgreSQLのCREATE INDEX CONCURRENTLYを使えば、テーブルへの書き込みをブロックせずにインデックスを作成できますが、通常より時間がかかります。GitLabのマイグレーションヘルパーはこの機能を利用しています (add_concurrent_index)。インデックスの削除も同様に時間がかかる場合があります。
  • カラムの追加/削除: カラムの追加は通常高速ですが、NOT NULL制約とデフォルト値を同時に追加すると、テーブル全体を書き換える必要が生じ、ロックが発生する可能性があります（PostgreSQLのバージョンによっては改善されています）。GitLabでは、デフォルト値付きのNOT NULLカラムは2ステップで追加するなどの手法が取られます。カラムの削除は通常高速ですが、外部キー制約などに注意が必要です。
  • マイグレーションの実行時間: 運用環境でのマイグレーションは、その実行時間中にアプリケーションが部分的に利用できなくなったり、パフォーマンスが低下したりする可能性があります。実行時間の長いマイグレーションは、事前にテストし、予想実行時間に基づいてダウンタイム計画に含める必要があります。GitLabでは、非同期マイグレーションキューを利用して、アプリケーションが稼働したままバックグラウンドで実行される長時間かかるマイグレーションもあります。
  • 下位互換性: マイグレーションは、GitLabのコードとセットで実行されます。古いコードが新しいスキーマで動作し、新しいコードが古いスキーマ（アップグレード途中）で動作する可能性があるため、互換性に配慮したマイグレーションを記述する必要があります。

GitLabでは、これらの課題に対処するための多くのマイグレーションヘルパーが用意されています。新しいマイグレーションを作成する際は、既存のヘルパーを積極的に利用し、ドキュメントや他のマイグレーションコードを参考に、安全かつ効率的な変更を行うように心がけましょう。

トランザクションと分離レベル

トランザクションは、複数のデータベース操作を一つの論理的な単位としてまとめ、その操作全体が成功するか、あるいは全体が失敗して元に戻されるか（ロールバック）のいずれかを保証する仕組みです（ACID特性のAtomicity）。

SQLではBEGINでトランザクションを開始し、COMMITで確定、ROLLBACKで破棄します。ActiveRecordでは、通常、一つのリクエスト処理全体が暗黙的なトランザクションで囲まれるか、ActiveRecord::Base.transaction do ... endブロックで明示的にトランザクションを制御します。

分離レベル (Isolation Level) は、複数のトランザクションが同時に実行される際に、あるトランザクションが他のトランザクションの途中結果をどの程度「見て」良いかを定義するものです。PostgreSQLは以下の分離レベルをサポートしています（厳密さの順）。

  1. READ UNCOMMITTED: 他のトランザクションがコミットしていない変更を見ることが許される（ダーティリード）。PostgreSQLではサポートされず、代わりにREAD COMMITTEDとして扱われます。
  2. READ COMMITTED: 他のトランザクションがコミットした変更のみを見ることができる。PostgreSQLのデフォルト分離レベルです。同じトランザクション内で同じクエリを複数回実行すると、他のトランザクションがコミットした新しい行や変更された行が見える可能性があります（ノンリピータブルリード）。
  3. REPEATABLE READ: トランザクション開始後に他のトランザクションがコミットした変更は、そのトランザクション内では見えません。同じクエリを複数回実行しても同じ結果が得られることが保証されます。ただし、他のトランザクションが新しい行を挿入し、その行が現在のトランザクションの範囲内に含まれる場合、新しい行が見えることがあります（ファントムリード）。
  4. SERIALIZABLE: 最も厳格な分離レベルです。複数のトランザクションが同時に実行されたとしても、それらが順番に（シリアルに）実行されたかのような結果を保証します。ダーティリード、ノンリピータブルリード、ファントムリードの全てを防ぎます。コンフリクトが発生した場合は、一方のトランザクションがロールバックされます。

GitLabでは、ほとんどの場合デフォルトのREAD COMMITTEDが使用されます。しかし、特定の競合状態を防ぐ必要がある場合や、トランザクション内で一貫したスナップショットを参照する必要がある場合は、REPEATABLE READやSERIALIZABLEを使用することが検討されます。ただし、これらの高い分離レベルはロック競合やデッドロックのリスクを高める可能性があるため、注意が必要です。

ロックの種類とデッドロックの回避策

データベースシステムでは、複数のトランザクションが同じデータに同時にアクセスする際にデータの整合性を保つためにロックが使用されます。PostgreSQLには様々なロックモードがあります。

  • 共有ロック (SHARE): 他の共有ロックとの競合はありませんが、排他ロック (EXCLUSIVE) とは競合します。読み取り操作で使用されることが多いです。
  • 排他ロック (EXCLUSIVE): 他のいかなるロックとも競合します。書き込み操作などで使用され、他のトランザクションによる同時アクセスを完全に禁止します。
  • 行レベルロック: 特定の行に対するロックです。
  • テーブルレベルロック: テーブル全体に対するロックです。

GitLab開発において特に注意が必要なのは、ロック競合とデッドロックです。

  • ロック競合: あるトランザクションが保持しているロックを、別のトランザクションが必要とした場合に発生します。後者のトランザクションはロックが解放されるまで待機します。ロック競合が多発すると、システムの応答性が低下します。
  • デッドロック: 2つ以上のトランザクションが互いに相手が保持しているロックを待機し、どちらも先に進めなくなる状態です。PostgreSQLはデッドロックを自動的に検出し、どちらか一方のトランザクションをロールバックさせてデッドロックを解消します。

デッドロックを回避するための一般的な方法：

  • ロックを取得する順序を統一する: 複数のオブジェクト（テーブルや行）に対してロックを取得する場合、すべてのトランザクションで同じ順序でロックを取得するようにします。
  • トランザクションを短く保つ: ロックを保持する時間を最小限に抑えることで、ロック競合やデッドロックのリスクを減らします。
  • 適切な分離レベルを使用する: 必要以上に高い分離レベルを使用しないことで、不要なロックや競合を避けます。
  • FOR UPDATE / FOR SHARE を理解して使用する: SELECTクエリで取得した行を後で更新・削除する意図がある場合、SELECT ... FOR UPDATEを使用することで、他のトランザクションによる同時変更を防ぎ、後からの更新によるデッドロックを回避できます。
  • GitLabのロックヘルパーを利用する: GitLabには、データベースレベルでのロックを安全に扱うためのヘルパーやパターンが存在します。

デッドロックが発生した場合は、PostgreSQLのログに記録されます (log_lock_waits = on に設定している場合)。ログを確認することで、どのクエリがデッドロックを引き起こしたかを特定し、コードの修正に繋げることができます。

バルク操作の利用

ActiveRecordの標準的なcreateやupdateメソッドは、オブジェクトごとにSQLクエリを実行します。多数のオブジェクトを一括で操作する場合、これはN+1クエリと同様に非効率になります。

PostgreSQLのINSERT INTO ... VALUES (...), (...), ... や、単一のUPDATE ... WHERE id IN (...) クエリは、複数の行を効率的に操作できます。ActiveRecordには、このようなバルク操作をサポートするためのメソッドが追加されています。

  • insert_all: 複数のハッシュまたはモデルオブジェクトから、単一のINSERTクエリを生成し、複数の行を一括で挿入します。コールバックは実行されません。
  • upsert_all: 複数の行を一括で挿入または更新します（UPSERT機能）。
  • update_all: 特定の条件に合致する複数の行に対して、単一のUPDATEクエリで同じ変更を適用します。コールバックは実行されません。
  • delete_all: 特定の条件に合致する複数の行を、単一のDELETEクエリで削除します。コールバックは実行されません。

これらのバルク操作メソッドは、データインポート、一括ステータス更新、古いデータのクリーンアップなど、多数の行を効率的に処理する必要がある場面で非常に役立ちます。ただし、コールバックやバリデーションがスキップされる点に注意し、必要に応じて手動で処理する必要があります。

GitLab固有の考慮事項

GitLabは非常に大規模で複雑なアプリケーションであり、そのデータベース活用にも独自の側面があります。

GitLabのデータベースアーキテクチャの進化

GitLabのデータベースアーキテクチャは、スケーラビリティの要求に応えるために進化を続けています。

  • CI/CDデータベース分離: CI/CDワークロードはGitLabの他の部分（プロジェクト管理、コードレビューなど）とは性質が異なり、非常に高い書き込み負荷とデータ量の増加傾向があります。パフォーマンスとスケーラビリティを向上させるため、GitLab 14.0からCI/CD関連のテーブル（ci_pipelines, ci_buildsなど）をメインデータベースとは別のPostgreSQLインスタンスに分離する機能が導入されました。これは、Railsアプリケーションから見て複数のデータベース接続を持つことになります。
  • Shard分離: GitLab.comのような最大規模の環境では、単一のPostgreSQLインスタンスでは処理しきれないほどデータ量やアクセス負荷が増加します。そこで、ユーザーやプロジェクトなどのデータを複数のデータベースインスタンス（シャーディング）に分散する取り組みが進められています。これは非常に複雑なアーキテクチャ変更であり、アプリケーションコード側でもシャーディングキーに基づいて適切なデータベースコネクションを選択するロジックが必要になります。

これらのアーキテクチャ変更は、GitLabエンジニアがデータベース関連のコードを書く際に、どのデータベースインスタンスにアクセスするのか、異なるデータベース間のリレーションやトランザクションをどう扱うのか、といった点を意識する必要があることを意味します。

GitLab.comのような大規模環境での課題と対策

GitLab.comは、世界最大級のPostgreSQLデータベースインスタンスの一つを運用しています。このような極めて大規模な環境では、自己ホスト型インスタンスとは異なる固有の課題が発生し、それに対応するための高度な技術が導入されています。

  • データ量とインデックスサイズ: テーブルがテラバイト級になり、インデックスだけで数百ギガバイトに達することがあります。インデックスの作成や再構築、VACUUM FULLのようなメンテナンス操作は非常に時間がかかり、慎重な計画が必要です。
  • 高トラフィック: 秒間数万クエリ以上のアクセスが発生します。効率の悪いクエリは即座に全体のパフォーマンスに悪影響を与えます。クエリレベル、スキーマレベルでの継続的な最適化が不可欠です。
  • 接続数の管理: 数千、数万の同時接続が発生する可能性があります。PgBouncerによる接続プーリングが必須であり、その設定も重要です。
  • ロック競合とデッドロック: 多くのトランザクションが同時に実行されるため、ロック競合やデッドロックのリスクが高まります。アプリケーションコードでの適切なトランザクション管理やロック取得順序の統一、データベース設定でのデッドロック検出・解消設定などが重要です。
  • ハードウェアリソースの限界: どんなに高性能なサーバーでも、単一ノードで処理できるデータ量やトラフィックには限界があります。CI/CDデータベース分離やシャーディングは、この限界を超えるための手段です。
  • Citus Data (現在はAzure Cosmos DB for PostgreSQL): GitLab.comでは、分散PostgreSQL拡張であるCitus（現在はPostgreSQLの一部として開発が続けられている機能も多い）が利用されています。Citusは、データを複数のノードに分散し、クエリを並列実行することで、大規模なデータセットに対するクエリ性能とスケーラビリティを向上させます。GitLabエンジニアがCitusの機能を直接利用することは少ないかもしれませんが、シャーディングの基盤としてその存在を理解しておくことは役立ちます。

これらの大規模環境での課題は、自己ホスト型GitLabインスタンスでもある程度の規模になると直面する可能性があります。GitLab.comでの対策事例は、将来的なスケーリングの参考になります。

ツールとリソース

PostgreSQLをより深く理解し、効率的に操作・管理するためのツールとリソースを紹介します。

psqlコマンドラインツール

psqlはPostgreSQLに標準で付属するコマンドラインクライアントです。多機能で高機能であり、データベースの操作、管理、デバッグに非常に役立ちます。

  • 基本的なSQLクエリの実行。
  • メタコマンド (\d でテーブル定義表示, \l でデータベース一覧, \timing でクエリ実行時間表示など) を使った情報収集。
  • EXPLAIN ANALYZEの実行。
  • バッチファイルの実行。

GitLabのRailsコンソールからもActiveRecord::Base.connection.execute('...') のようにしてSQLを実行できますが、複雑なクエリのテストやデータベースの状態確認にはpsqlが便利です。

GUIツール

コマンドラインよりも視覚的に操作したい場合は、GUIツールが便利です。

  • pgAdmin: PostgreSQL公式の管理ツールです。データベースオブジェクトの閲覧・編集、クエリツール、サーバー状態の監視など、豊富な機能を備えています。
  • DBeaver: 多くのデータベースをサポートする汎用的なデータベースツールです。PostgreSQLもサポートしており、直感的なUIで操作できます。
  • DataGrip: JetBrainsが提供する商用のデータベースIDEです。高度なコード補完、リファクタリング、パフォーマンス分析機能などを備えています。

これらのツールを使うことで、スキーマの探索や簡単なデータ確認を効率的に行えます。

RailsコンソールでのDB操作

GitLab開発中は、Railsコンソール (rails c) からActiveRecordを通じてデータベースを操作することがよくあります。

“`ruby

ユーザーの取得

user = User.find_by(username: ‘your_username’)

プロジェクトの作成

project = user.projects.create!(name: ‘My Project’)

N+1クエリの確認（Bullet Gemなどを使用）

Project.includes(:owner).limit(10).map(&:owner).count # N+1が出ていないか確認
“`

Railsコンソールは、開発中の機能に関連するデータの確認や、ActiveRecordを使ったクエリのテストに便利です。ActiveRecord::Base.connection.execute('...')を使えば、生SQLを実行することも可能です。開発環境では、config/initializers/ に設定ファイルを作成してActiveRecord::Base.logger = Logger.new(STDOUT) のようにすると、ActiveRecordが生成するSQLがコンソールに表示されるようになり、クエリ効率の確認に役立ちます。

有用な拡張機能

PostgreSQLは拡張機能によって機能を強化できます。

  • pg_stat_statements: データベースサーバー上で実行されたすべてのクエリに関する統計情報（実行回数、合計実行時間、平均実行時間など）を収集します。システム全体で最も時間のかかっているクエリや、頻繁に実行されているクエリを特定するのに非常に役立ちます。本番環境でのパフォーマンス分析に不可欠です。
  • auto_explain: log_min_duration_statementと組み合わせて使用することで、遅いクエリがログに出力される際に、自動的にそのEXPLAIN ANALYZEの結果もログに出力させることができます。手動でEXPLAIN ANALYZEを実行する手間を省き、パフォーマンス問題の診断を効率化できます。

これらの拡張機能は、shared_preload_libraries設定パラメータでロードし、CREATE EXTENSIONコマンドで有効化することで利用できます。

公式ドキュメント、書籍、コミュニティ

PostgreSQLに関する最も正確で詳細な情報は、公式ドキュメントにあります。特定の機能やパラメータについて深く知りたい場合は、まず公式ドキュメントを参照しましょう。

  • PostgreSQL公式ドキュメント: https://www.postgresql.org/docs/
  • 書籍: PostgreSQLに関する専門書も多数出版されています。目的やレベルに合わせて選ぶと良いでしょう。
  • コミュニティ: PostgreSQLコミュニティは非常に活発です。メーリングリストやフォーラムで質問したり、情報を交換したりできます。

また、GitLabの公式ドキュメントには、GitLabのデータベースに関する特有の情報（推奨設定、バックアップ手順、CI/CD DB分離など）が記載されています。GitLabの特定のバージョンに関する情報は、GitLabのドキュメントを参照することが最も重要です。

まとめ：PostgreSQLマスターへの道

GitLabエンジニアにとって、PostgreSQLの知識は単なる付加価値ではなく、高品質なソフトウェアを開発・運用するために不可欠なスキルセットです。本記事では、PostgreSQLの基本的な概念から、アーキテクチャ、パフォーマンスチューニング、運用管理、そしてGitLab開発における具体的な注意点まで、幅広く解説しました。

PostgreSQLは奥深く、本記事でカバーできたのはその一部に過ぎません。しかし、ここで紹介した基礎知識、EXPLAIN ANALYZEを使ったクエリ分析、インデックスとVACUUMの理解、そしてGitLabのActiveRecordやマイグレーションに関するベストプラクティスは、あなたの日常業務においてすぐに役立つはずです。

PostgreSQLマスターへの道は一日にしてならず。継続的に学習し、実践を通じて経験を積むことが重要です。

  • GitLabのコードベースを探索する: 他のエンジニアがどのようにActiveRecordを使っているか、どのようなマイグレーションを書いているかを学びましょう。
  • 開発環境で実験する: 遅いと感じる処理があれば、開発環境で該当するコードを特定し、生成されるSQLをEXPLAIN ANALYZEで分析してみてください。インデックスを追加したり、クエリを書き換えたりして、パフォーマンスの変化を確認しましょう。
  • 本番環境の課題に積極的に関わる: データベースに関連するパフォーマンスインシデントや運用タスク（例: 遅いクエリの特定と改善、テーブルの肥大化対応）に積極的に関わることで、実践的なスキルが身につきます。
  • PostgreSQLコミュニティやGitLabのデータベースチームから学ぶ: 分からないことは質問し、他のエンジニアの知識や経験から学びましょう。

GitLabの成長と共に、PostgreSQLデータベースも進化し、より大きなスケールと複雑な要求に応える必要があります。PostgreSQLの知識を深めることは、あなた自身のエンジニアとしての市場価値を高めるだけでなく、GitLabという素晴らしいプロダクトをより堅牢で高性能なものにしていく貢献に繋がります。

さあ、今日からPostgreSQLの学習と活用をさらに一歩進めましょう！

コメントする

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

上部へスクロール