mastering-postgresql

PostgreSQL Python 開発

PostgreSQL と Python を使用して、検索、ベクトル類似度、大量のデータを扱うアプリケーションを構築します。

クイックリファレンス

タスク	移動先
Docker／ローカル環境のセットアップ	setup-and-docker.md
全文検索と BM25	search-fulltext.md
pgvector と JSONB のインデックス作成	search-vectors-json.md
Python ドライバーとプール	python-drivers.md
Python のクエリパターン	python-queries.md
AWS RDS／Aurora	cloud-aws.md
GCP Cloud SQL／AlloyDB	cloud-gcp.md
Azure Flexible Server	cloud-azure.md
Neon と Supabase	cloud-serverless.md
クラウド共通（プーリング、設定）	cloud-common.md

このスキルを使用しないケース

• DBA タスク： バックアップ戦略、レプリケーションの設定、ユーザー管理、セキュリティ強化
• その他のデータベース： MySQL、MongoDB、Redis、Elasticsearch 固有のクエリ
• スキーマ設計： 正規化理論、データモデリングのパターン
• ストアドプロシージャ： PL/pgSQL 関数の開発
• アプリケーションフレームワーク： Django ORM の詳細、FastAPI の統合に関する詳細

クイックスタートチェックリスト

進捗を記録するには、このチェックリストをコピーしてください。

Setup Progress:
- [ ] Docker environment running (docker-compose up -d)
- [ ] Connected to database (psql or Python)
- [ ] Extensions created (pgvector, pg_trgm)
- [ ] Table created with search_vector and embedding columns
- [ ] GIN index on search_vector created
- [ ] HNSW index on embedding created
- [ ] Test full-text query returns results
- [ ] Test vector query returns results

クイックスタート：5 分で検索とベクトルを使う

1. pgvector を使用して PostgreSQL を起動する

# docker-compose.yml
services:
  postgres:
    image: pgvector/pgvector:pg17
    environment:
      POSTGRES_PASSWORD: devpass
    ports: ["5432:5432"]
    volumes: [pgdata:/var/lib/postgresql/data]
volumes:
  pgdata:

docker-compose up -d

# Verify container is running:
docker-compose ps
# Expected: postgres service with status "Up"

2. 拡張機能を有効にする

CREATE EXTENSION vector;      -- pgvector for embeddings
CREATE EXTENSION pg_trgm;     -- Trigram for fuzzy search

-- Verify extensions installed:
SELECT extname, extversion FROM pg_extension 
WHERE extname IN ('vector', 'pg_trgm');
-- Expected: 2 rows with version numbers

3. ベクトルを含む検索可能なテーブルを作成する

CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    embedding vector(1536),
    search_vector tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(content, '')), 'B')
    ) STORED
);

-- Create indexes
CREATE INDEX idx_docs_search ON documents USING GIN (search_vector);
CREATE INDEX idx_docs_embedding ON documents USING hnsw (embedding vector_cosine_ops);

-- Verify indexes created:
SELECT indexname FROM pg_indexes WHERE tablename = 'documents';
-- Expected: idx_docs_search, idx_docs_embedding, documents_pkey

4. Python からクエリを実行する

import asyncpg

async def search(pool, query: str, embedding: list[float], limit: int = 10):
    return await pool.fetch("""
        SELECT id, title,
               ts_rank(search_vector, websearch_to_tsquery('english', $1)) AS text_rank,
               embedding <=> $2::vector AS vector_dist
        FROM documents
        WHERE search_vector @@ websearch_to_tsquery('english', $1)
        ORDER BY vector_dist
        LIMIT $3
    """, query, embedding, limit)

# Verify connection works:
# pool = await asyncpg.create_pool('postgresql://postgres:devpass@localhost/postgres')
# rows = await pool.fetch("SELECT 1 AS test")
# assert rows[0]['test'] == 1

選択ツリー

どの検索方法を選ぶか？

Need search? ─┬─► Exact keyword match ──────► B-tree index + WHERE =
              │
              ├─► Full-text search (FTS) ───► tsvector + GIN + ts_rank
              │
              ├─► Relevance like Google ────► pg_search BM25 (ParadeDB)
              │
              ├─► Typo tolerance ───────────► pg_trgm + similarity()
              │
              ├─► Semantic/AI search ───────► pgvector + embeddings
              │
              └─► Hybrid (keywords + semantic) ► Combine tsvector + pgvector

どのベクトルインデックスを選ぶか？

Vector index? ─┬─► Dataset < 100K rows ────► No index (exact search OK)
               │
               ├─► Need best recall ────────► HNSW (slower build, fast query)
               │
               ├─► Fast index build ────────► IVFFlat (needs data first)
               │
               ├─► On AlloyDB ──────────────► ScaNN (Google optimized)
               │
               ├─► On Azure ────────────────► pg_diskann (32x less memory)
               │
               ├─► Billions of vectors ─────► VectorChord vchordrq (self-host)
               │
               └─► Dimensions > 2000 ───────► halfvec or binary quantization

どの Python ライブラリを選ぶか？

Python lib? ──┬─► Sync, simple, stable ─────► psycopg2
              │
              ├─► Async + modern features ──► psycopg3
              │
              ├─► Max async performance ────► asyncpg
              │
              └─► ORM needed ───────────────► SQLAlchemy + asyncpg/psycopg

列にどのインデックス種別を選ぶか？

Column type? ─┬─► Scalar (int, text, timestamp) ─► B-tree (default)
              │
              ├─► JSONB ────────────────────────┬► GIN (general queries)
              │                                 └► GIN jsonb_path_ops (@> only)
              │
              ├─► Array ────────────────────────► GIN
              │
              ├─► tsvector ─────────────────────► GIN (or GiST for updates)
              │
              ├─► vector ───────────────────────► HNSW or IVFFlat
              │
              └─► Range / Geometric ────────────► GiST

よく使うパターン

実装の詳細については、リファレンスファイルを参照してください。

パターン	リファレンス
ランキング付き全文検索	search-fulltext.md#ranking-functions
BM25 検索	search-fulltext.md#bm25-with-pg_search
ベクトル類似度	search-vectors-json.md#distance-operators
JSONB の包含検索	search-vectors-json.md#jsonb-indexing
配列の重複要素検索	search-vectors-json.md#array-indexing
一括挿入	python-queries.md#bulk-insert-strategies
コネクションプール	python-drivers.md#asyncpg-async-only

インデックス調整のクイックリファレンス

HNSW パラメーター

パラメーター	デフォルト	指針
m	16	値を大きくすると再現率が向上し、メモリ使用量が増えます。一般的な値は 12～48 です
ef_construction	64	値を大きくするとインデックスの品質が向上し、構築に時間がかかります。64～200 が目安です
hnsw.ef_search	40	クエリ実行時に設定します。値を大きくすると再現率が向上し、処理が遅くなります

CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops) WITH (m=16, ef_construction=100);
SET hnsw.ef_search = 100;  -- Before querying

-- Verify setting applied:
SHOW hnsw.ef_search;

IVFFlat パラメーター

パラメーター	指針
lists	行数が 100 万未満の場合は sqrt（行数）、100 万を超える場合は行数／1000
ivfflat.probes	sqrt（lists）から始め、再現率を高めるには増やします

CREATE INDEX ON docs USING ivfflat (embedding vector_l2_ops) WITH (lists=100);
SET ivfflat.probes = 10;

トラブルシューティングのクイックリファレンス

症状	考えられる原因	対処法
インデックス対象の列でシーケンシャルスキャンが発生する	統計情報が古い	ANALYZE tablename;
ベクトル検索が遅い	インデックスがない、または ef_search の値が低い	HNSW インデックスを作成し、ef_search を増やします
ベクトル検索の再現率が低い	IVFFlat の probes の値が低い	ivfflat.probes を増やします
全文検索で一致しない	言語設定が誤っている	to_tsvector('english', ...) を確認します
インデックスが使用されない	クエリが演算子と一致していない	演算子クラスがクエリと一致するか確認します
接続がタイムアウトする	プールが枯渇している	プールサイズを増やすか、リークを修正します
拡張機能が見つからない	インストールされていない	CREATE EXTENSION name;
HNSW のビルドで OOM が発生する	メモリ不足	maintenance_work_mem を増やします
フィルタリングされたクエリの結果が少ない	インデックススキャン後にフィルタリングされている	hnsw.iterative_scan を有効にします
本番環境で接続が切断される	ヘルスチェックがない	check=ConnectionPool.check_connection を使用します
ベクトル数が 1 億件を超える規模へのスケーリング	pgvector の制限	VectorChord vchordrq の導入を検討します

詳しいトラブルシューティングについては、search-vectors-json.md を参照してください。

スクリプトの使用方法

pip install -r scripts/requirements.txt  # Install dependencies first

スクリプト	目的	使用するタイミング
setup_extensions.py	pgvector、pg_trgm 拡張機能をインストールする	データベースの初期セットアップ時
create_search_tables.py	search_vector 列と embedding 列を含むテーブルを作成する	拡張機能のインストール後
health_check.py	インデックスの状態、肥大化、パフォーマンスを確認する	クエリの遅延を診断するとき
vector_search.py --demo	ベクトル類似性クエリを実演する	pgvector のパターンを学ぶとき
bulk_insert.py	高性能なデータ読み込みを行う	大規模なデータセットをインポートするとき
fts_examples.py	全文検索クエリの例を示す	FTS の構文を学ぶとき
connection_pool.py	コネクションプーリングのパターンを示す	本番環境へのデプロイ時

例：

python scripts/setup_extensions.py --host localhost --dbname mydb
python scripts/create_search_tables.py --host localhost --dbname mydb
python scripts/health_check.py --host localhost --dbname mydb

クラウドのクイックリファレンス

プロバイダー	pgvector	BM25 のサポート	コネクションプーリング
AWS RDS/Aurora	0.8.0	pg_textsearch（プレビュー）	RDS Proxy
GCP Cloud SQL	0.8.0	pg_textsearch（プレビュー）	Cloud SQL Proxy
GCP AlloyDB	0.8.0 + ScaNN	pg_textsearch（プレビュー）	組み込み
Azure Flexible	0.8.0 + pg_diskann	pg_textsearch（プレビュー）	組み込みの PgBouncer
Neon	✅	pg_search	組み込み
Supabase	✅	pg_search	組み込み

サーバーレスの選択肢： Neon（ゼロへのスケール、即時ブランチ作成）と Supabase（認証／リアルタイム機能を備えた BaaS）は、開発／テストやスタートアップに最適です。cloud-serverless.md を参照してください。

BM25 の選択肢：

• pg_search（ParadeDB）：本番環境に対応しており、セルフホストまたは ParadeDB のマネージドサービスを利用できます
• pg_textsearch（TigerData）：プレビュー段階で、マネージド PostgreSQL サービスで利用できます

セットアップコマンドについては、プロバイダーごとのファイルを参照してください：AWS | GCP | Azure

参照ファイル

実装の詳細なガイダンスについては、以下を読み込んでください。

参照先	読み込むタイミング
setup-and-docker.md	Docker のセットアップ、拡張機能のインストール、postgresql.conf のチューニング
search-fulltext.md	全文検索（FTS）、BM25 のセットアップ、トライグラムによるあいまい検索
search-vectors-json.md	pgvector のチューニング、JSONB／配列のインデックス作成、メンテナンス
python-drivers.md	psycopg2／psycopg3／asyncpg、コネクションプール、SQLAlchemy
python-queries.md	一括挿入、FTS クエリ、ベクトルクエリ、JSONB 操作
cloud-aws.md	AWS RDS／Aurora のセットアップ、RDS Proxy
cloud-gcp.md	GCP Cloud SQL／AlloyDB、ScaNN インデックス
cloud-azure.md	Azure Flexible Server、pg_diskann
cloud-serverless.md	Neon、Supabase（ゼロスケール、ブランチ作成）
cloud-common.md	拡張機能の対応表、プーリング、本番環境の設定、コスト