PostgreSQL高级模式 postgres-advanced-patterns

---

名称: postgres-advanced-patterns 描述: 用于性能优化、复杂查询、索引策略和数据库设计的高级 PostgreSQL 模式 许可证: MIT 元数据: 来源: affaan-m/everything-claude-code 改编者: ai-skills 类别: 数据库

PostgreSQL 高级模式

用于高性能 PostgreSQL 数据库设计、查询和优化的高级模式。

性能优化

1. 有效索引

-- B-tree 索引用于相等和范围查询
CREATE INDEX idx_users_email ON users(email);

-- 部分索引用于过滤查询
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

-- 复合索引用于多列
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- GiST 索引用于全文搜索
CREATE INDEX idx_products_search ON products USING GiST(to_tsvector('english', name || ' ' || description));

-- GIN 索引用于 JSON 查询
CREATE INDEX idx_metadata_gin ON events USING GIN(metadata);

2. 查询优化

-- 使用 EXPLAIN ANALYZE 来理解查询计划
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123;

-- 避免使用 SELECT *
SELECT id, name, email FROM users WHERE active = true;

-- 对于大型子查询，使用 EXISTS 代替 IN
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 使用批处理操作代替循环
INSERT INTO logs (event, created_at)
SELECT unnest(ARRAY['login', 'logout', 'update']), NOW();

3. 连接池

import { Pool } from 'pg';

const pool = new Pool({
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

// 使用连接池进行查询
const result = await pool.query('SELECT * FROM users WHERE id = $1', [userId]);

高级查询模式

窗口函数

-- 运行总计
SELECT 
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) as running_total
FROM transactions;

-- 使用分区进行行编号
SELECT 
  user_id,
  purchase_date,
  amount,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_date DESC) as purchase_rank
FROM purchases;

-- 移动平均
SELECT 
  date,
  price,
  AVG(price) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7d
FROM stock_prices;

公共表表达式 (CTEs)

-- 递归 CTE 用于层次数据
WITH RECURSIVE org_chart AS (
  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, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;

-- 多个 CTEs 用于复杂查询
WITH 
  active_users AS (
    SELECT id FROM users WHERE active = true
  ),
  recent_orders AS (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    WHERE created_at > NOW() - INTERVAL '30 days'
    GROUP BY user_id
  )
SELECT u.id, u.name, COALESCE(ro.order_count, 0) as recent_orders
FROM active_users au
JOIN users u ON au.id = u.id
LEFT JOIN recent_orders ro ON u.id = ro.user_id;

JSON 操作

-- 查询 JSON 列
SELECT data->>'name' as name, 
       data->'address'->>'city' as city
FROM customers
WHERE data->>'status' = 'active';

-- JSON 聚合
SELECT user_id,
       json_agg(json_build_object('id', id, 'title', title)) as posts
FROM posts
GROUP BY user_id;

-- JSON 路径查询
SELECT * FROM events
WHERE metadata @> '{"type": "purchase"}';

数据库设计模式

1. 分区

-- 按日期范围分区
CREATE TABLE events (
  id BIGSERIAL,
  event_type TEXT,
  created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_01 PARTITION OF events
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE events_2026_02 PARTITION OF events
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

2. 物化视图

-- 创建物化视图用于昂贵查询
CREATE MATERIALIZED VIEW user_stats AS
SELECT 
  user_id,
  COUNT(DISTINCT order_id) as total_orders,
  SUM(amount) as total_spent,
  MAX(created_at) as last_order_date
FROM orders
GROUP BY user_id;

-- 定期刷新
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;

-- 在物化视图上创建索引
CREATE INDEX idx_user_stats_user_id ON user_stats(user_id);

3. 约束和验证

-- 检查约束
ALTER TABLE products
ADD CONSTRAINT price_positive CHECK (price > 0);

-- 排除约束
CREATE TABLE bookings (
  room_id INT,
  during TSRANGE,
  EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);

-- 域类型用于可重用约束
CREATE DOMAIN email_address AS TEXT
CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Z|a-z]{2,}$');

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email email_address NOT NULL UNIQUE
);

事务和并发

事务隔离

-- 串行化事务用于关键操作
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 可重复读用于一致快照
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT SUM(balance) FROM accounts;
-- 在整个事务中保持一致视图
COMMIT;

行级锁

-- 悲观锁
SELECT * FROM orders WHERE id = 123 FOR UPDATE;

-- 共享锁用于只读访问
SELECT * FROM products WHERE id = 456 FOR SHARE;

-- 跳过已锁行
SELECT * FROM queue WHERE processed = false
FOR UPDATE SKIP LOCKED
LIMIT 10;

监控和维护

查询性能

-- 查找慢查询
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- 检查索引使用情况
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

-- 表膨胀
SELECT schemaname, tablename, 
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

维护任务

-- 清理和分析
VACUUM ANALYZE users;

-- 重建索引
REINDEX TABLE users;

-- 更新统计信息
ANALYZE users;

最佳实践

1. 始终使用参数化查询 以防止 SQL 注入
2. 在外键上创建索引 以提升连接性能
3. 使用连接池 以优化资源管理
4. 监控查询性能 使用 pg_stat_statements
5. 定期执行 VACUUM 和 ANALYZE 以更新统计信息
6. 使用适当的事务隔离级别
7. 避免 N+1 查询 通过适当的连接或批处理
8. 实现重试逻辑 以处理事务冲突

集成点

补充：

• backend-implementation-patterns：用于 API 数据访问
• tdd-workflow：用于数据库测试
• verification-loop：用于查询性能检查
• security-implementation-guide：用于安全查询