2026 年了，万物皆可 Postgres

-- BM25 全文搜索
CREATE EXTENSION pg_textsearch;

-- AI 向量搜索
CREATE EXTENSION vector;
CREATE EXTENSION vectorscale;

-- AI embedding 与 RAG 工作流
CREATE EXTENSION ai;

-- 时序数据
CREATE EXTENSION timescaledb;

-- 消息队列
CREATE EXTENSION pgmq;

-- 定时任务
CREATE EXTENSION pg_cron;

-- 地理空间
CREATE EXTENSION postgis;

-- 建表
CREATETABLE articles (
  id SERIAL PRIMARY KEY,
  title TEXT,
  content TEXT
);

-- 创建 BM25 索引
CREATE INDEX idx_articles_bm25 ON articles USING bm25(content)
WITH (text_config ='english');

-- 使用 BM25 评分搜索
SELECT title, -(content <@>'database optimization') as score
FROM articles
ORDERBY content <@>'database optimization'
LIMIT 10;

SELECT
  title,
-(content <@>'database optimization') as bm25_score,
  embedding <=> query_embedding as vector_distance,
0.7* (-(content <@>'database optimization')) +
0.3* (1- (embedding <=> query_embedding)) as hybrid_score
FROM articles
ORDERBY hybrid_score DESC
LIMIT 10;

-- 启用扩展
CREATE EXTENSION vector;
CREATE EXTENSION vectorscale CASCADE;

-- 带 embedding 的表
CREATETABLE documents (
  id SERIAL PRIMARY KEY,
  content TEXT,
  embedding vector(1536)
);

-- 高性能索引（DiskANN）
CREATE INDEX idx_docs_embedding ON documents USING diskann(embedding);

-- 查找相似文档
SELECT content, embedding <=>'[0.1, 0.2, ...]'::vector as distance
FROM documents
ORDERBY embedding <=>'[0.1, 0.2, ...]'::vector
LIMIT 10;

SELECT ai.create_vectorizer(
  'documents'::regclass,
  loading => ai.loading_column(column_name=>'content'),
  embedding => ai.embedding_openai(model=>'text-embedding-3-small', dimensions=>'1536')
);

-- 启用 TimescaleDB
CREATE EXTENSION timescaledb;

-- 建表
CREATETABLE metrics (
time TIMESTAMPTZ NOTNULL,
  device_id TEXT,
  temperature DOUBLE PRECISION
);

-- 转换为 hypertable
SELECT create_hypertable('metrics', 'time');

-- 按时间桶查询
SELECT time_bucket('1 hour', time) ashour,
       AVG(temperature)
FROM metrics
WHEREtime> NOW() -INTERVAL'24 hours'
GROUPBYhour;

-- 自动删除过期数据
SELECT add_retention_policy('metrics', INTERVAL'30 days');

-- 压缩（节省 90% 存储空间）
ALTERTABLE metrics SET (timescaledb.compress);
SELECT add_compression_policy('metrics', INTERVAL'7 days');

-- UNLOGGED = 无 WAL 开销，写入更快
CREATE UNLOGGED TABLE cache (
  key TEXT PRIMARY KEY,
value JSONB,
  expires_at TIMESTAMPTZ
);

-- 带过期时间的写入
INSERTINTO cache (key, value, expires_at)
VALUES ('user:123', '{"name": "Alice"}', NOW() +INTERVAL'1 hour')
ON CONFLICT (key) DO UPDATESETvalue= EXCLUDED.value;

-- 读取
SELECTvalueFROM cache WHERE key ='user:123'AND expires_at > NOW();

-- 清理（用 pg_cron 定时调度）
DELETEFROM cache WHERE expires_at < NOW();

CREATE TABLE jobs (
  id SERIAL PRIMARY KEY,
  payload JSONB,
  status TEXT DEFAULT'pending'
);

-- Worker 原子性地认领任务
UPDATE jobs SET status ='processing'
WHERE id = (
SELECT id FROM jobs WHERE status ='pending'
FORUPDATESKIP LOCKED LIMIT 1
) RETURNING *;

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  data JSONB
);

-- 插入嵌套文档
INSERTINTO users (data) VALUES ('{
  "name": "Alice",
  "profile": {"bio": "Developer", "links": ["github.com/alice"]}
}');

-- 查询嵌套字段
SELECT data->>'name', data->'profile'->>'bio'
FROM users
WHERE data->'profile'->>'bio'LIKE'%Developer%';

-- 索引 JSON 字段
CREATE INDEX idx_users_email ON users ((data->>'email'));

CREATE EXTENSION postgis;

CREATE TABLE stores (
  id SERIAL PRIMARY KEY,
  name TEXT,
  location GEOGRAPHY(POINT, 4326)
);

-- 查找 5km 内的门店
SELECT name, ST_Distance(location, ST_MakePoint(-122.4, 37.78)::geography) as meters
FROM stores
WHERE ST_DWithin(location, ST_MakePoint(-122.4, 37.78)::geography, 5000);

CREATE EXTENSION pg_cron;

-- 每小时执行
SELECT cron.schedule('cleanup', '0 * * * *',
  $$DELETE FROM cache WHERE expires_at < NOW()$$);

-- 每晚汇总
SELECT cron.schedule('rollup', '0 2 * * *',
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY daily_stats$$);

-- 倒数排名融合（RRF）：结合关键词搜索 + 语义搜索
WITH bm25 AS (
SELECT id, ROW_NUMBER() OVER (ORDERBY content <@> $1) as rank
FROM documents LIMIT 20
),
vectors AS (
SELECT id, ROW_NUMBER() OVER (ORDERBY embedding <=> $2) as rank
FROM documents LIMIT 20
)
SELECT d.*,
1.0/(60+COALESCE(b.rank, 1000)) +
1.0/(60+COALESCE(v.rank, 1000)) as score
FROM documents d
LEFTJOIN bm25 b ON d.id = b.id
LEFTJOIN vectors v ON d.id = v.id
WHERE b.id ISNOTNULLOR v.id ISNOTNULL
ORDERBY score DESC LIMIT 10;

CREATE EXTENSION pg_trgm;

CREATE INDEX idx_name_trgm ON products USING GIN (name gin_trgm_ops);

-- 即使拼错也能找到 "PostgreSQL"
SELECT name FROM products
WHERE name % 'posgresql'
ORDER BY similarity(name, 'posgresql') DESC;

-- 查找某个经理下的所有下属（组织架构图）
WITHRECURSIVE org_tree AS (
SELECT id, name, manager_id, 1as depth
FROM employees WHERE id =42

UNIONALL

SELECT e.id, e.name, e.manager_id, t.depth +1
FROM employees e
JOIN org_tree t ON e.manager_id = t.id
WHERE t.depth <10
)
SELECT*FROM org_tree;