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	对于少于 1M 行的数据，使用 sqrt(rows)；对于超过 1M 行的数据，使用 rows/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 探测数量过少	提高 ivfflat.probes 值
全文搜索未匹配	语言配置错误	检查 to_tsvector('english', ...)
未使用索引	查询与操作符不匹配	确认运算符类与查询匹配
连接超时	连接池已耗尽	增大连接池，或修复泄漏
找不到扩展	尚未安装	CREATE EXTENSION name;
构建 HNSW 时内存溢出	内存不足	增大 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 设置、trigram 模糊搜索
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	扩展矩阵、连接池、生产环境配置、成本