⚡

PostgreSQL高级模式 postgres-advanced-patterns

PostgreSQL高级模式技能专注于数据库性能优化、复杂查询处理、索引策略和数据库设计。包含有效的索引创建、查询优化技巧、窗口函数、CTEs、JSON操作、分区、物化视图等高级功能。适用于后端开发人员、数据工程师和数据库管理员，提升PostgreSQL数据库的效率和可扩展性。关键词：PostgreSQL, 数据库优化, 性能调优, 查询优化, 索引策略, 数据库设计。

数据工程 0 次安装 0 次浏览 更新于 3/7/2026

name: postgres-advanced-patterns description: 用于性能优化、复杂查询、索引策略和数据库设计的高级PostgreSQL模式 license: MIT metadata: source: affaan-m/everything-claude-code adapted-by: ai-skills category: database

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: 用于安全查询