PostgreSQL 扩展生态实战：表分区、pgvector 向量搜索、PostGIS、TimescaleDB 与 Citus 水平扩展

-- 按月分区的日志表
CREATE TABLE logs (
  id BIGSERIAL,
  created_at TIMESTAMPTZ NOT NULL,
  level TEXT,
  message TEXT
) PARTITION BY RANGE (created_at);

-- 预创建最近 3 个月的分区
CREATE TABLE logs_2025_07 PARTITION OF logs
  FOR VALUES FROM ('2025-07-01') TO ('2025-08-01');
CREATE TABLE logs_2025_08 PARTITION OF logs
  FOR VALUES FROM ('2025-08-01') TO ('2025-09-01');
CREATE TABLE logs_2025_09 PARTITION OF logs
  FOR VALUES FROM ('2025-09-01') TO ('2025-10-01');

-- 默认分区（超出已定义范围的兜底）
CREATE TABLE logs_default PARTITION OF logs DEFAULT;

-- 插入数据（自动路由到对应分区）
INSERT INTO logs (created_at, level, message)
VALUES (NOW(), 'INFO', 'Service started');

-- 查询（PostgreSQL 自动裁剪无关分区）
EXPLAIN SELECT * FROM logs WHERE created_at >= '2025-08-01';
-- Seq Scan on logs_2025_08 only ✅

-- 第一层：RANGE 按月
CREATE TABLE events (
  id BIGSERIAL,
  event_time TIMESTAMPTZ NOT NULL,
  user_id INT,
  event_type TEXT
) PARTITION BY RANGE (event_time);

-- 第二层：每个 RANGE 分区内部再 HASH user_id
CREATE TABLE events_2025_08 PARTITION OF events
  FOR VALUES FROM ('2025-08-01') TO ('2025-09-01')
  PARTITION BY HASH (user_id);

CREATE TABLE events_2025_08_p0 PARTITION OF events_2025_08
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_2025_08_p1 PARTITION OF events_2025_08
  FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE events_2025_08_p2 PARTITION OF events_2025_08
  FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE events_2025_08_p3 PARTITION OF events_2025_08
  FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- 自动创建新分区（PostgreSQL 16+ 无原生自动，需 pg_partman 或 cron）
-- 安装 pg_partman 扩展实现自动分区管理
CREATE EXTENSION pg_partman;

-- 手动 Detach 旧分区（归档后删除）
ALTER TABLE logs DETACH PARTITION logs_2025_01;
DROP TABLE logs_2025_01; -- 已归档后可直接删除

-- ATTACH 已存在的表为分区
CREATE TABLE logs_2025_10 (LIKE logs INCLUDING ALL);
ALTER TABLE logs ATTACH PARTITION logs_2025_10
  FOR VALUES FROM ('2025-10-01') TO ('2025-11-01');

-- 安装扩展（需先安装系统包 postgresql-17-pgvector）
CREATE EXTENSION IF NOT EXISTS vector;

-- 验证
SELECT * FROM pg_extension WHERE extname = 'vector';

-- 创建向量表（存储 OpenAI 嵌入 text-embedding-3-small = 1536 维）
CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  title TEXT,
  content TEXT,
  embedding VECTOR(1536),        -- 维度需与模型输出一致
  metadata JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 插入示例（实际嵌入由应用层调用 AI API 生成）
INSERT INTO documents (title, content, embedding)
VALUES (
  'PostgreSQL 简介',
  'PostgreSQL 是开源关系型数据库...',
  '[0.001, -0.023, ..., 0.456]'::vector
);

-- HNSW 索引（推荐）：高召回、快速构建、动态插入
CREATE INDEX idx_docs_embedding ON documents
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- ivfflat 索引（旧方案，内存友好但静态）
CREATE INDEX idx_docs_embedding_ivf ON documents
  USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 100);

-- 相似度查询（余弦相似度 ≤ 1，越大越相似）
SELECT id, title,
       1 - (embedding <=> '[0.001, -0.023, ... ]'::vector) AS similarity
FROM documents
ORDER BY embedding <=> '[0.001, -0.023, ... ]'::vector
LIMIT 10;

-- 1. 存储文档片段
CREATE TABLE chunks (
  id SERIAL PRIMARY KEY,
  doc_id INT,
  chunk_text TEXT,
  embedding VECTOR(1536)
);

-- 2. 创建 HNSW 索引
CREATE INDEX idx_chunks_embedding ON chunks
  USING hnsw (embedding vector_cosine_ops);

-- 3. 搜索（应用层先嵌入 query，再传向量到 SQL）
SELECT chunk_text, 1 - (embedding <=> query_emb::vector) AS sim
FROM chunks
WHERE 1 - (embedding <=> query_emb::vector) > 0.8   -- 相似度阈值
ORDER BY embedding <=> query_emb::vector
LIMIT 5;

-- 系统需先安装 postgresql-17-postgis-3
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;

-- 验证
SELECT PostGIS_Version();

-- 存储地理位置的店铺表
CREATE TABLE stores (
  id SERIAL PRIMARY KEY,
  name TEXT,
  address TEXT,
  location GEOGRAPHY(POINT, 4326),  -- WGS 84 坐标系
  geom GEOMETRY(POINT, 4326),        -- 几何类型（可转换）
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 插入店铺坐标
INSERT INTO stores (name, address, location) VALUES
  ('星巴克北京路店', '北京路 100 号',
   ST_SetSRID(ST_MakePoint(113.2644, 23.1291), 4326)::GEOGRAPHY),
  ('麦当劳天河店', '天河路 200 号',
   ST_SetSRID(ST_MakePoint(113.3231, 23.1322), 4326)::GEOGRAPHY);

-- 1. 查找某用户 5km 范围内的店铺
SELECT s.name, s.address,
       ST_Distance(s.location, user_loc) AS distance_meters
FROM stores s,
  (SELECT ST_SetSRID(ST_MakePoint(113.2644, 23.1291), 4326)::GEOGRAPHY AS user_loc) u
WHERE ST_DWithin(s.location, u.user_loc, 5000)  -- 5000 米
ORDER BY distance_meters;

-- 2. 创建空间索引（R-tree over GIST）
CREATE INDEX idx_stores_location ON stores USING GIST(location);

-- 3. 多边形围栏查询
SELECT * FROM stores
WHERE ST_Within(
  geom::GEOMETRY,
  ST_GeomFromText('POLYGON((113.2 23.1, 113.3 23.1, 113.3 23.2, 113.2 23.2, 113.2 23.1))', 4326)
);

-- 4. 路径规划（LineString 距离）
INSERT INTO routes (name, path) VALUES
('Route A', ST_GeomFromText('LINESTRING(113.264 23.129, 113.323 23.132)', 4326));

SELECT SUM(ST_Length(path::GEOGRAPHY)) FROM routes;

-- 系统需先安装 timescaledb2-postgresql-17
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- 创建普通表后转换为超表
CREATE TABLE metrics (
  time TIMESTAMPTZ NOT NULL,
  device_id INT,
  temperature DOUBLE PRECISION,
  humidity DOUBLE PRECISION
);

-- 按时间 + 设备 ID 分片创建超表
SELECT create_hypertable('metrics', 'time', chunk_time_interval => INTERVAL '1 day');
-- 或按字节大小分区：SELECT create_hypertable('metrics', 'time', chunk_time_interval => '7 days');

-- 连续聚合（预计算 1 小时统计）
CREATE MATERIALIZED VIEW hourly_stats
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', time) AS bucket,
  device_id,
  AVG(temperature) AS avg_temp,
  MAX(temperature) AS max_temp,
  MIN(humidity) AS min_humidity
FROM metrics
GROUP BY bucket, device_id;

-- 查询预聚合数据（比实时聚合快 50x）
SELECT * FROM hourly_stats WHERE bucket > NOW() - INTERVAL '7 days';

-- 数据保留策略（超过 30 天的数据自动删除）
SELECT add_retention_policy('metrics', INTERVAL '30 days');

-- 数据压缩（旧分片自动压缩，省 90% 空间）
ALTER TABLE metrics SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'device_id'
);
SELECT add_compression_policy('metrics', INTERVAL '7 days');

-- 系统安装 citus 扩展后
CREATE EXTENSION IF NOT EXISTS citus;

-- 注册 Worker 节点（Coordinator 上执行）
SELECT * FROM citus_add_node('worker-1', 5432);
SELECT * FROM citus_add_node('worker-2', 5432);
SELECT * FROM citus_add_node('worker-3', 5432);

-- 1. Session 表：用户会话量极大，按 user_id 分布（查询往往是 user_id = ?）
CREATE TABLE user_sessions (
  id BIGSERIAL,
  user_id INT,
  session_token TEXT,
  created_at TIMESTAMPTZ,
  last_active TIMESTAMPTZ
);

-- 分片数为 Worker × 2（16 shards for 8 workers）
SELECT create_distributed_table('user_sessions', 'user_id');

-- 查询会自动路由到对应 Worker
SELECT * FROM user_sessions WHERE user_id = 42;

-- 2. 时序数据用 append-only 分片（与 TimescaleDB 可协同）
CREATE TABLE events (
  event_id BIGSERIAL,
  event_time TIMESTAMPTZ,
  user_id INT,
  event_type TEXT
);
SELECT create_distributed_table('events', 'user_id');

-- Co-located JOIN（如果两张表都以 user_id 分片，JOIN 在本地 Worker 执行）
SELECT u.name, COUNT(s.id) AS sessions
FROM users u
JOIN user_sessions s ON u.id = s.user_id
GROUP BY u.id;
-- ✅ Coordinator 只汇总，JOIN 本地执行

-- 跨 Worker 聚合
SELECT event_type, COUNT(*) AS cnt
FROM events
GROUP BY event_type;
-- ✅ Coordinator 收集各 Worker 的中间结果后汇总