都 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 (
idSERIAL PRIMARY KEY,
  title TEXT,
contentTEXT
);

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

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

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
ORDER BY hybrid_score DESC
LIMIT 10;

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

-- 包含 embedding 的表
CREATETABLE documents (
idSERIAL PRIMARY KEY,
contentTEXT,
  embedding vector(1536)
);

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

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

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 DOUBLEPRECISION
);

-- 转成 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 TABLEcache (
keyTEXT PRIMARY KEY,
value JSONB,
  expires_at TIMESTAMPTZ
);

-- 写入并设置过期时间
INSERTINTOcache (key, value, expires_at)
VALUES ('user:123', '{"name": "Alice"}', NOW() + INTERVAL'1 hour')
ON CONFLICT (key) DOUPDATESETvalue = EXCLUDED.value;

-- 读取
SELECTvalueFROMcacheWHEREkey = 'user:123'AND expires_at > NOW();

-- 清理（可用 pg_cron 调度）
DELETEFROMcacheWHERE expires_at < NOW();

CREATE EXTENSION pgmq;
SELECT pgmq.create('my_queue');

-- 发送
SELECT pgmq.send('my_queue', '{"event": "signup", "user_id": 123}');

-- 接收（带可见性超时）
SELECT * FROM pgmq.read('my_queue', 30, 5);

-- 处理完成后删除
SELECT pgmq.delete('my_queue', msg_id);

CREATE TABLE jobs (
idSERIAL PRIMARY KEY,
  payload JSONB,
statusTEXTDEFAULT'pending'
);

-- Worker 以原子方式领取任务
UPDATE jobs SETstatus = 'processing'
WHEREid = (
SELECTidFROM jobs WHEREstatus = 'pending'
FORUPDATESKIPLOCKEDLIMIT1
) RETURNING *;

CREATE TABLEusers (
idSERIAL PRIMARY KEY,
data JSONB
);

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

-- 查询嵌套字段
SELECTdata->>'name', data->'profile'->>'bio'
FROMusers
WHEREdata->'profile'->>'bio'LIKE'%Developer%';

-- 为 JSON 字段建索引
CREATEINDEX idx_users_email ONusers ((data->>'email'));

CREATE EXTENSION postgis;

CREATETABLE stores (
idSERIAL PRIMARY KEY,
nameTEXT,
  location GEOGRAPHY(POINT, 4326)
);

-- 找出 5km 范围内的门店
SELECTname, 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$$);

-- Reciprocal Rank Fusion：合并关键词检索与语义检索
WITH bm25 AS (
SELECTid, ROW_NUMBER() OVER (ORDERBYcontent <@> $1) asrank
FROM documents LIMIT20
),
vectors AS (
SELECTid, ROW_NUMBER() OVER (ORDERBY embedding <=> $2) asrank
FROM documents LIMIT20
)
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 DESCLIMIT10;

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 (
SELECTid, name, manager_id, 1asdepth
FROM employees WHEREid = 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;

psql "postgresql://user:pass@your-instance.tsdb.cloud.timescale.com:5432/tsdb"

CREATE EXTENSION pg_textsearch;  -- BM25 搜索
CREATE EXTENSION vector;         -- 向量检索