PostgreSQL 深度——功能最全的开源 RDBMS / PostgreSQL as a Feature-Rich Open-Source RDBMS
📅 创建时间：2026-05-08 🏷️ 标签：#PostgreSQL #JSONB #pgvector #PostGIS #窗口函数 #CTE 📚 前置知识：[[00-db-overview]] [[01-mysql]]（了解索引和事务基础） 📚 相关知识：[[08-vector-database]]（pgvector）、[[07-distributed-sql]]（CockroachDB）
PostgreSQL 定位速览
┌─────────────────────────────────────────────────────────────┐
│ PostgreSQL 在数据库版图中的位置 │
├─────────────────────────────────────────────────────────────┤
│ │
│ GitHub Stars: 15K+，Stack Overflow 连续多年"最受喜爱数据库" │
│ 定位： 功能最丰富、最"正经"的开源关系型数据库 │
│ 核心优势： 几乎涵盖所有 SQL 标准的扩展功能 │
│ 适用场景： 复杂查询、AI 向量、地理空间、全文本搜索、ETL │
│ │
│ 一句话总结： │
│ 如果 MySQL 能做的事 PostgreSQL 都能做， │
│ 那么 PostgreSQL 能做的事，MySQL 往往做不了。 │
│ │
└─────────────────────────────────────────────────────────────┘第1部分：PostgreSQL vs MySQL——核心差异
┌─────────────────────────────────────────────────────────────┐
│ PostgreSQL vs MySQL 核心差异 │
├─────────────────────────────────────────────────────────────┤
│ │
│ │ 维度 │ PostgreSQL │ MySQL │
│ ├────────────────┼────────────────────────┼────────────────┤
│ │ SQL 标准遵循 │ 近乎完整（CTE/窗口函数） │ 部分遵循 │
│ │ MVCC │ 真正的 MVCC │ 改进版 │
│ │ 索引类型 │ 8+ 种（B-tree/Hash/ │ 5 种 │
│ │ │ GiST/SP-GiST/GIN/Brin）│ │
│ │ JSON 支持 │ JSONB（独立类型+索引） │ JSON（文本+索引）│
│ │ JSONB vs JSON │ JSONB: 二进制+可索引 │ JSON: 文本解析 │
│ │ │ │ │
│ │ 扩展性 │ CREATE EXTENSION │ 插件有限 │
│ │ │ (pgvector/PostGIS/...) │ │
│ │ 分区表 │ 声明式分区（原生） │ 分区表（有限） │
│ │ 并行查询 │ 真正的并行（多核利用） │ 有限并行 │
│ │ 复制方案 │ 逻辑复制 + 流复制 │ binlog 复制 │
│ │ 许可 │ PostgreSQL License │ GPL v2 │
│ │ │ （BSD 风格，无传染性） │ （GPL 传染） │
│ │
└─────────────────────────────────────────────────────────────┘第2部分：高级 SQL 特性
2.1 CTE（公用表表达式）
CTE 让复杂查询可读性大幅提升：
sql
-- 递归 CTE：查询组织架构
WITH RECURSIVE org_tree AS (
-- 基础：CEO
SELECT id, name, manager_id, 1 as level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- 递归：下属
SELECT e.id, e.name, e.manager_id, ot.level + 1
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY level, name;
-- 普通 CTE：让查询更清晰
WITH active_users AS (
SELECT id, name, created_at
FROM users
WHERE status = 'active'
),
user_orders AS (
SELECT user_id, COUNT(*) as order_count, SUM(amount) as total
FROM orders
GROUP BY user_id
)
SELECT u.name, u.created_at, o.order_count, o.total
FROM active_users u
JOIN user_orders o ON u.id = o.user_id
WHERE o.order_count > 5
ORDER BY o.total DESC;2.2 窗口函数
窗口函数在分组内进行有序计算，是 SQL 的高级特性：
sql
-- 窗口函数基础
SELECT
product_name,
category,
price,
-- 窗口函数：分组内排名
RANK() OVER (PARTITION BY category ORDER BY price DESC) as rank_in_category,
-- 窗口函数：分组内前一个价格（环比）
LAG(price, 1) OVER (PARTITION BY category ORDER BY sale_date) as prev_price,
-- 窗口函数：分组累计销售额
SUM(amount) OVER (PARTITION BY category ORDER BY sale_date) as running_total,
-- 窗口函数：分组平均值
AVG(amount) OVER (PARTITION BY category) as category_avg,
-- 窗口函数：当前行在分组中的位置百分比
PERCENT_RANK() OVER (PARTITION BY category ORDER BY amount) as pct_rank
FROM sales;
-- 实战：查询每个用户订单总额，以及占其所在城市的比例
SELECT
u.id,
u.city,
u.total_spent,
city_totals.city_total,
ROUND(u.total_spent * 100.0 / city_totals.city_total, 2) as pct_of_city
FROM (
SELECT id, city, SUM(order_amount) as total_spent
FROM users u JOIN orders o ON u.id = o.user_id
GROUP BY id, city
) u
JOIN (
SELECT city, SUM(order_amount) as city_total
FROM users u JOIN orders o ON u.id = o.user_id
GROUP BY city
) city_totals ON u.city = city_totals.city;2.3 UPSERT——冲突处理
sql
-- PostgreSQL UPSERT（INSERT ... ON CONFLICT）
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (1001, 1, NOW())
ON CONFLICT (user_id) -- 冲突键
DO UPDATE SET
login_count = user_stats.login_count + 1,
last_login = EXCLUDED.last_login;
-- 完整版本：带 WHERE 条件
INSERT INTO products (id, name, stock, updated_at)
VALUES (1, 'Widget', 100, NOW())
ON CONFLICT (id) DO UPDATE
SET stock = products.stock + EXCLUDED.stock,
updated_at = EXCLUDED.updated_at
WHERE products.stock < 1000; -- 只在库存未满时增加第3部分：JSONB——文档与关系型的融合
PostgreSQL 的 JSONB 是它的杀手级特性之一：
┌─────────────────────────────────────────────────────────────┐
│ JSONB 的优势 │
├─────────────────────────────────────────────────────────────┤
│ │
│ MySQL JSON： 存储为 TEXT，查询时解析，速度慢 │
│ PostgreSQL JSONB：存储为二进制，有独立类型，可建索引 │
│ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ CREATE INDEX idx_user_meta ON users │ │
│ │ USING GIN (metadata jsonb_path_ops); │ │
│ │ │ │
│ │ -- 查询任意嵌套字段，带索引 │ │
│ │ SELECT * FROM users │ │
│ │ WHERE metadata @> '{"preferences": {"theme": "dark"}}';│ │
│ │ │ │
│ │ -- GIN 索引支持的查询操作符： │ │
│ │ @> 包含（Contains） │ │
│ │ ? 键存在（Key exists） │ │
│ │ ?& 所有键存在 │ │
│ │ ?| 任一键存在 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘sql
-- JSONB 实战
-- 1. 存储用户配置（灵活字段）
CREATE TABLE user_configs (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id),
config JSONB DEFAULT '{}',
created_at TIMESTAMP DEFAULT NOW()
);
INSERT INTO user_configs (user_id, config) VALUES (1, '{
"theme": "dark",
"notifications": {
"email": true,
"push": false
},
"language": "zh-CN"
}');
-- 2. 查询：提取 JSON 字段
SELECT
user_id,
config->>'theme' as theme, -- 获取字符串
config->'notifications'->>'email' as email_notif, -- 嵌套
config->'notifications'->>'push' as push_notif,
#config->'notifications' as notif_count -- 数组长度
FROM user_configs;
-- 3. 更新 JSON
UPDATE user_configs
SET config = jsonb_set(config, '{notifications, email}', 'false')
WHERE user_id = 1;
UPDATE user_configs
SET config = config || '{"timezone": "Asia/Shanghai"}'
WHERE user_id = 1;
-- 4. 全文搜索 JSONB
SELECT * FROM user_configs
WHERE config @@ '$.notifications.email == true';第4部分：pgvector——PostgreSQL 原生向量检索
这是 AI 时代 PostgreSQL 最重要的扩展：
┌─────────────────────────────────────────────────────────────┐
│ pgvector 向量检索 │
├─────────────────────────────────────────────────────────────┤
│ │
│ pgvector 让 PostgreSQL 直接支持向量存储和相似度搜索， │
│ 无需额外的向量数据库。 │
│ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ -- 安装扩展 │ │
│ │ CREATE EXTENSION IF NOT EXISTS vector; │ │
│ │ │ │
│ │ -- 定义向量列（1536 维 = OpenAI embedding 维度） │ │
│ │ CREATE TABLE documents ( │ │
│ │ id SERIAL PRIMARY KEY, │ │
│ │ content TEXT, │ │
│ │ embedding VECTOR(1536) │ │
│ │ ); │ │
│ │ │ │
│ │ -- 创建 HNSW 索引（近似最近邻） │ │
│ │ CREATE INDEX ON documents │ │
│ │ USING hnsw (embedding vector_cosine_ops); │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 支持的相似度度量： │
│ • cosine_distance（余弦距离）← 最常用 │
│ • euclidean_distance（欧氏距离） │
│ • inner_product（内积） │
│ │
│ pgvector vs 专用向量数据库： │
│ • pgvector: <100万向量、已有 PG 表结构、需要事务支持 │
│ • Milvus: >100万向量、需要独立运维、超高 QPS │
│ │
└─────────────────────────────────────────────────────────────┘sql
-- pgvector 实战：RAG 语义检索
-- 1. 插入带 embedding 的文档
INSERT INTO documents (content, embedding)
VALUES (
'PostgreSQL 是一个功能强大的开源关系型数据库',
'[0.123, -0.456, ...]'::vector -- 1536 维
);
-- 2. 语义搜索（查询先 embedding）
SELECT id, content,
-- 计算余弦距离（越小越相似）
1 - (embedding <=> '[0.124, -0.455, ...]'::vector) as similarity
FROM documents
ORDER BY embedding <=> '[0.124, -0.455, ...]'::vector
LIMIT 5;
-- 3. 过滤 + 向量混合检索
SELECT id, content,
1 - (embedding <=> query_embedding) as similarity
FROM documents, (
SELECT '[0.124, -0.455, ...]'::vector as query_embedding
) q
WHERE created_at > '2026-01-01' -- 传统过滤条件
ORDER BY embedding <=> query_embedding
LIMIT 5;
-- 4. pgvectorscale（TimescaleDB 出品，性能提升 10x）
-- 在 pgvector 基础上支持 StreamingDiskANN 索引
-- 适合 billion 级别的向量数据第5部分：PostGIS 地理空间
sql
-- 启用 PostGIS
CREATE EXTENSION postgis;
-- 存储地理位置
CREATE TABLE stores (
id SERIAL PRIMARY KEY,
name TEXT,
location GEOGRAPHY(POINT, 4326) -- POINT + WGS84 坐标系
);
INSERT INTO stores (name, location) VALUES
('三里屯店', ST_SetSRID(ST_MakePoint(116.45, 39.93), 4326)),
('国贸店', ST_SetSRID(ST_MakePoint(116.46, 39.91), 4326));
-- 查询：附近门店（半径 5 公里内）
SELECT id, name,
ST_Distance(
location,
ST_SetSRID(ST_MakePoint(116.45, 39.93), 4326)::geography
) / 1000 as distance_km
FROM stores
WHERE ST_DWithin(
location,
ST_SetSRID(ST_MakePoint(116.45, 39.93), 4326)::geography,
5000 -- 5 公里
)
ORDER BY location <-> ST_SetSRID(ST_MakePoint(116.45, 39.93), 4326);第6部分：分区表与并行查询
sql
-- 声明式分区（PostgreSQL 10+）
CREATE TABLE logs (
id BIGSERIAL,
created_at TIMESTAMP NOT NULL,
level TEXT,
message TEXT
) PARTITION BY RANGE (created_at);
-- 按月分区
CREATE TABLE logs_2026_01 PARTITION OF logs
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE logs_2026_02 PARTITION OF logs
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
-- 查询自动路由到正确分区
INSERT INTO logs (created_at, level, message) VALUES
('2026-01-15', 'INFO', 'user login');
-- EXPLAIN 显示分区裁剪（Partition Pruning）
EXPLAIN SELECT * FROM logs WHERE created_at = '2026-01-15';
-- Output: -> Parallel Seq Scan on logs_2026_01 (cost=...)
-- Filter: (created_at = '2026-01-15'::date)
-- -> Partitioned index scan using logs on logs
-- Partition key constraints: (...)
-- 并行查询示例（利用多核）
SET max_parallel_workers_per_gather = 4;
EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status;
-- Output: Finalize GroupAggregate (cost=...)
-- -> Gather Motion (cost=...)
-- -> Partial GroupAggregate
-- -> Parallel Seq Scan on orders第7部分：FDW 外部表
PostgreSQL 的 FDW（Foreign Data Wrapper）让它可以查询外部数据源：
sql
-- 1. postgres_fdw：查询另一个 PostgreSQL
CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_db FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.168.1.100', port '5432', dbname 'analytics');
CREATE USER MAPPING FOR current_user SERVER remote_db
OPTIONS (user 'readonly_user', password 'secret');
CREATE FOREIGN TABLE remote_orders (...) SERVER remote_db;
-- 现在可以像本地表一样查询远程数据
SELECT * FROM remote_orders WHERE created_at > '2026-01-01';
-- 2. mysql_fdw：查询 MySQL
CREATE EXTENSION mysql_fdw;
CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host '192.168.1.101');
CREATE FOREIGN TABLE mysql_users (...) SERVER mysql_server
OPTIONS (dbname 'webapp', table_name 'users');
-- 3. 实战：跨数据库 JOIN
SELECT u.name, o.total
FROM mysql_users u
JOIN remote_orders o ON u.id = o.user_id
WHERE o.created_at > '2026-01-01';第8部分：PostgreSQL 在 LLM/Agent 中的核心地位
┌─────────────────────────────────────────────────────────────┐
│ PostgreSQL 在 AI 应用中的核心角色 │
├─────────────────────────────────────────────────────────────┤
│ │
│ PostgreSQL + pgvector = 一站式 AI 数据平台 │
│ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ PostgreSQL（主数据库） │ │
│ │ ┌─────────────────────────────────────────────┐ │ │
│ │ │ messages (JSONB) → 聊天记录 │ │ │
│ │ │ embeddings (vector) → 向量嵌入 [pgvector]│ │ │
│ │ │ user_memories (JSONB)→ 用户记忆 [pgvector]│ │ │
│ │ │ agents (JSONB) → Agent 配置 │ │ │
│ │ │ sessions → 会话管理 │ │ │
│ │ │ files → 文件元数据 │ │ │
│ │ └─────────────────────────────────────────────┘ │ │
│ │ │ │
│ │ ┌─────────────────────────────────────────────┐ │ │
│ │ │ RAG 管道（同一数据库内完成） │ │ │
│ │ │ 文档 → chunk → embedding → vector search │ │ │
│ │ │ → 检索结果 + LLM → 生成回答 │ │ │
│ │ └─────────────────────────────────────────────┘ │ │
│ │ │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ LobeChat 就使用 PostgreSQL + PGVector 作为主力数据库， │
│ 一套架构支撑：消息存储 + RAG 向量 + 用户记忆 + 文件元数据 │
│ │
└─────────────────────────────────────────────────────────────┘学习状态：🟡 开始学习