PostgreSQL：ベストプラクティスとインデックス作成

（セクション： 技術とインフラ）

概要

PostgreSQLは、iGamingのマネー、KYC、法的に重要なレコードの「真実のカーネル」です。ACID保証、強力なSQL、拡張性を提供します。トーナメントやPSPのWebhookのピークに耐えるためには、有能なスキーム、インデックス、パーティショニング、自動真空、WALのチューニング、観測性などが重要です。以下は、プラクティスとテンプレートのコンストラクタです。

1）アーキテクチャとSLO

PostgreSQLの役割：読み取りのための+replicasを書くためのリーダー；ホットスクリーンの場合-キャッシュ/投影（Redis/実体化ビュー）。

SLOの例： p99 'INSERT/UPDATE'ウォレット≤ 25-40 ms；p99バランスの読書≤ 10-15 ms；レプリカラグ≤ 2-5秒；99 ≥可用性。9%.

読み取り後のポリシー：トランザクションがリーダーから読み取られた後、またはレプリケーションラグを待った後のユーザー画面。

2）回路図の設計

お金のコアの正規化（財布、台帳）+読み取りのための非正規化（CQRS/投影）。
厳密な制限：'NOT NULL'、 'CHECK'、 'UNIQUE'、 'ON DELETE/UPDATE' （RESTRICT/SET NULL/NO ACTION）。
スキーマバージョニング：アップ/ダウン移行、フィーチャーフラグ；ホットタイムの名前変更は避けてください。
識別子：'BIGINT'+シーケンス（または配布用のULID/UUIDv7）。ホットインサートの場合-個別のテーブルスペース/WALボリューム内のシーケンス。

3）索引付け： 何、どこで、どのように

3.1 Bツリー（デフォルト）

とき：正確なマッチ、範囲、ソート、'JOIN' by FK。
パターン：'WHERE player_id=？'、'ORDER BY created_at DESC LIMIT 100'。

• 「複数列インデックス」（Multi-column indexes）-条件の順序にマッチします。
• カバー：'INCLUDE（……）'インデックスのみのスキャン。
• 「ORDER BY」の下の個々のインデックス……DESC 'on 「tapes」。

sql
CREATE INDEX idx_tx_player_created_desc
ON tx (player_id, created_at DESC) INCLUDE (amount_cents, status);

3.2 GIN （JSONB、配列、全文）

とき：JSONBキー/パス検索、タグ配列、FTS。
バリエーション：'gin_trgm_ops'、 'jsonb_path_ops'/'jsonb_ops'。
練習：厳密に検索フィールドを制限し、カーディナリティについて考えます。

sql
CREATE INDEX idx_profile_jsonb_gin
ON player_profile USING GIN (data jsonb_path_ops);

-- Search by email/name with typos
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_player_trgm ON player USING GIN (email gin_trgm_ops);

3.3 GiST（地理/範囲/署名）

とき：位置情報（IP-geo、 radii）、間隔、最寄りの隣人。
お金のコアではあまり使用されない、地理的制限/責任あるプレーに役立ちます。

3.4 BRIN（時間内の大きな「付録」テーブル）

いつ：数十億行、時間と自然な相関関係（賭け/イベントログ）。
長所：小型、安いサービス。
短所：粗い選択性→パーティショニングと組み合わせる。

sql
CREATE INDEX idx_bets_brin ON bets USING BRIN (created_at) WITH (pages_per_range = 64);

3.5ハッシュ

めったに必要とされない：範囲がない場合の1つの列による平等；多くの場合、Bツリーで十分です。

3.6部分的および表現

部分インデックス：ホットサブセット（アクティブ、'status='pending'）を加速します。
式インデックス：precompute key （'lower （email）'、 '（data->>' psp_tx'）'）。

sql
CREATE INDEX idx_withdraw_pending ON withdrawals (player_id)
WHERE status = 'pending';

CREATE INDEX idx_tx_psp_tx ON tx ((data->>'psp_tx'));

3.7つの索引のAntipatterns

「すべてのインデックス」：インデックスの過剰は書き込みとVACUUMを遅くします。

インデックスの複製（同一列セット/順序）

cardinalityが非常に低いカラムへのインデックス（例えば、'status' with 2 values）-do partial。

4）パーティショニング

理由：膨らみを減らし、VACUUM/スキャンを高速化し、保存/アーカイブを容易にします。
スキーム：賭けログの日付（日/週）によるRANGE；大きなユーザーテーブルの'player_id'によるHASH；結合された。
ローテーションの練習：将来のパーティーを事前に作成する、'ATTACH PARTITION'、古いパーティションのアーカイブ-'DETACH'+movement。

sql
CREATE TABLE bets (
bet_id BIGINT PRIMARY KEY,
player_id BIGINT NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
amount_cents BIGINT NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE bets_2025_11_05 PARTITION OF bets
FOR VALUES FROM ('2025-11-05') TO ('2025-11-06');

5）断片化を最小限に抑えた挿入/更新

HOTアップデート：'fillfactor'を維持します（例：90）ページ上の空き領域のためのホットスプレッドシート上。
TOAST：大きなJSONB/テキスト-圧縮に注意してください。かさばるフィールドを別のテーブルに格納します。
UPSERT： "ON CONFLICT……DO UPDATE'とidempotent logic。

sql
INSERT INTO wallet (player_id, balance_cents, updated_at)
VALUES ($1, $2, now())
ON CONFLICT (player_id) DO UPDATE
SET balance_cents = wallet. balance_cents + EXCLUDED. balance_cents,
updated_at = now();

6）取引、ロック、競争

分離レベル：ほとんどのパスの'READ COMMITTED'；'REPEATABLE READ'/'SERIIALIZABLE'（レポート、オフラインバッチ）を指す。長い間持たないで。
ロックの種類：row-level （pessimism 'FOR UPDATE'）、 table-level （DDL）、分散ミューテックスのアドバイザリロック。
デッドロック：短いトランザクション、エンティティを更新する均一な順序、タイムアウト（'lock_timeout'、 'statement_timeout'）。
タスクキュー：分散ワークプールの'SKIP LOCKED'。

sql
-- batch processor removes work without racing
SELECT id FROM jobs
WHERE status='pending'
FOR UPDATE SKIP LOCKED
LIMIT 100;

7）自動真空、統計およびbloat

VACUUM/ANALYZE：現在の統計情報（'default_statistics_target'）を保持し、ホットテーブルでAVを調整します（下のトリガしきい値）。
wraparound （age （txid） <20億）、'vacuum_freeze_min_age'に注意してください。
bloat制御：重いオフピークの索引の規則的なreindex/CLUSTER；パーティショニングは問題の規模を縮小します。

sql
ALTER TABLE tx SET (
autovacuum_vacuum_scale_factor = 0. 05,
autovacuum_analyze_scale_factor = 0. 02,
autovacuum_vacuum_cost_limit = 4000
);

8）記憶、チェックポイントおよびWAL

メモリ（Memory）

'shared_buffers'： 20-25％ RAM（プロファイル依存）。
'work_mem'：操作で！（たとえば、8-64MBなど）保守的に設定し、役割を報告するためのポイントを増やします。
'maintenance_work_mem'：大きなインデックス/リカバリ（タスク512MB-2GB）。

WALとチェックポイント

WALのためのNVMe、単一ボリューム；'wal_compression=on'

'checkpoint_timeout' 10-15分、'max_wal_size'は変更される可能性があり、'checkpoint_completion_target ≈ 0。9`.

insert peaks-バッチインサートとグループコミット。

9）レプリケーションとフォールトトレランス

リーダーのレプリカ：RPO≈0 -30cのための最も近い（半同期）に同期；読み取り/分析の非同期。
昇進および囲うこと：Patroni/レプリカのマネージャー；「デュアルリーダー」を除外します。
ホットスタンバイ：'hot_standby_feedback'慎重に（bloat growth）、より良い-レプリカで長いトランザクションを管理。

10）バックアップとPITR

フル+インクリメンタルバックアップ、オフサイトコピー；WALの'archive_command'

PITR：スタンドのタイムポイントに回復をチェックします。RPO/RTOを調整します（財布-分、ログ-数十分）。
DR（ゲームの日）演習：定期的な回復チェック。

11） JSONBと「フレキシブル回路」モデル

JSONB-まれに読み取り/変数属性（KYCメタデータ、 PSPパラメータ）に優れています。
リレーショナルカラムの必須フィールドを厳密に検証します。JSONB-ニュアンスの「尾」のために。
インデックス：使用されるパスによるポイントGIN；「すべてのための1つの巨大なGIN」を避けてください。

sql
-- partial GIN index only for documents with the desired key
CREATE INDEX idx_kyc_docnum ON kyc USING GIN ((data->'doc'->'number'))
WHERE data? 'doc';

12）全文とファジー検索

作り付けFTS： 'tsvector'+GIN；typos-'pg_trgm'。
ログ/ゲームで難しい検索の場合は、検索エンジン（ES/OpenSearch）に取り出し、リンク/メタデータをPGに保存します。

13）観察可能性およびプロファイリング

pg_stat_statements：トップスロー/頻繁なクエリ、正規化。
説明（ANALYZE、 BUFFERS）：計画を読み、ホットトラックでseqスキャンを探します。
メトリクス：TPS、 p95/p99、チェックポイント、'replication_lag'、デッドロック、bloat、 AVループ、キャッシュヒット≥ 95％。
アラート：ラグの増加、「取引中のアイドル」、予期しないseqスキャン、WAL嵐。

14）接続プールと準備されたクエリ

Webトラフィックの'トランザクション'モードでのプーラー（PgBouncer）；'session'-長いカーソル/バックオフィスの場合。
準備された文/パラメータ化→解析が少なく、安定した計画。
バックエンドの最大数を制限します（'max_connections' low；プールはバルブに引き継がれます）。

15）安全性とコンプライアンス

トランジット、ディスク暗号化、KMS/外部キーのTLS。
RBAC：最小限の権利、読み取り/書き込み/管理ロールの分離。
マルチテナントのシナリオのためのRLS （Row-Level Security）。
PII：マスキング/エイリアシング、保存性。
監査：'pgaudit'/監査は重要なテーブル（ウォレット/レジャー）でトリガーされます。

16） iGamingの典型的なテンプレート

16.1財布と台帳（厳密な一貫性）

'wallet （player_id PK）'、 'ledger （player_id、 ts DESC）'+'INCLUDE （delta_cents、 reason）'。
トランザクションは残高を更新し、元帳に書き込みます。セミシンク・レプリケーション；キャッシュは投影のみです。

16.2賭けの歴史（高いTPS、プレーヤー/時間の読書）

日/週、BRIN by time+B-Tree '（player_id、 created_at DESC）'。
'DETACH PARTITION'→OLAPへのアーカイブによる保持。

16.3 Webhooks PSP（バースタミ、レトライ）

"raw"イベントのキュー（追加のみ）、タイムパーティション、'status='pending"の部分インデックス"

'idempotency_key'/'psp_tx' （UNIQUE）によるidempotency。

17）実装チェックリスト

1.SLOとread-after-writeポリシーをコミットします。
2.実際のクエリ（クエリプロファイル）のキーインデックスを設計します。
3.保持/付録パターンがある場合のパーティショニングを含める。
4.ホットテーブルにAV/ANALYZEを設定し、bloat/wraparoundを監視します。
5.ピークウィンドウのメモリ、WAL、チェックポイントを調整します。
6.接続プールと'pg_stat_statement'を置きます。警報を出してくれ。
7.Replication+DR+PITR-必須;演習を行います。
8.JSONBとGINを必要なパスのみに制限します。部分インデックスを使用します。
9.トランザクション時間を最小限に抑え、ワークキューに'SKIP LOCKED'を使用します。
10.監査/PII/暗号化/ロールモデル-支払いを開始する前に。

18） Antipatterns

すべてに1つの「ユニバーサル」インデックスとクエリ分析はありません。
長い「ぶら下がり」トランザクション（'トランザクション中にアイドル'）→ブロック/ブロート成長。
レプリカに依存して、遅延なしで読み取り後の書き込みを行います。
すべてをJSONB 「just in case」に保存し、1つのGINでドキュメント全体をインデックス化します。
ゼロ制御VACUUM/ANALYZEとラグ/チェックポイントの監視なし。
ピーク時の質量移動/DDL。

19）便利なスニペット

クエリ計画とバッファ

sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT amount_cents
FROM ledger
WHERE player_id = $1
ORDER BY ts DESC
LIMIT 100;

重いクエリプロファイリング

sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

パーティーローテーション（アイデア）

sql
-- create a party for tomorrow
CREATE TABLE bets_2025_11_06 PARTITION OF bets
FOR VALUES FROM ('2025-11-06') TO ('2025-11-07');

概要

PostgreSQLは、インデックス、パーティー、自動真空、WAL/メモリ、 レプリカ/DR、および観測性が規律されている場合、負荷の線形増加でiGamingプラットフォーム上の「お金と真実」を引き出すことができます。クエリとSLOのプロファイルから始め、実際のパターンのインデックスを作成し、ホットテーブルを分離し、厳格な運用衛生を有効にします。そして、ベースは予測的にトーナメントとピーク支払いを維持します。