PostgreSQL 高级模式：数据库设计、查询优化与性能调优实战指南

postgres-advanced-patterns by 4444j99/a-i--skills

1 周安装量

2 GitHub Stars

GitHub

安装命令

npx skills add https://github.com/4444j99/a-i--skills --skill postgres-advanced-patterns

开发数据库性能优化

🇨🇳中文介绍

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

广告位招租

在这里展示您的产品或服务

触达数万 AI 开发者，精准高效

联系我们

公共表表达式 (CTE)

-- 用于层次数据的递归 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;

-- 用于复杂查询的多个 CTE
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 列
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"}';

数据库设计模式

-- 按日期范围分区
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');

-- 为昂贵的查询创建物化视图
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);

-- 检查约束
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;

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

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

🇺🇸English

PostgreSQL Advanced Patterns

Advanced patterns for high-performance PostgreSQL database design, querying, and optimization.

Performance Optimization

1. Effective Indexing

-- B-tree index for equality and range queries
CREATE INDEX idx_users_email ON users(email);

-- Partial index for filtered queries
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

-- Composite index for multiple columns
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- GiST index for full-text search
CREATE INDEX idx_products_search ON products USING GiST(to_tsvector('english', name || ' ' || description));

-- GIN index for JSON queries
CREATE INDEX idx_metadata_gin ON events USING GIN(metadata);

2. Query Optimization

-- Use EXPLAIN ANALYZE to understand query plans
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123;

-- Avoid SELECT *
SELECT id, name, email FROM users WHERE active = true;

-- Use EXISTS instead of IN for large subqueries
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- Batch operations instead of loops
INSERT INTO logs (event, created_at)
SELECT unnest(ARRAY['login', 'logout', 'update']), NOW();

3. Connection Pooling

import { Pool } from 'pg';

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

// Use pool for queries
const result = await pool.query('SELECT * FROM users WHERE id = $1', [userId]);

Advanced Query Patterns

Window Functions

-- Running totals
SELECT 
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) as running_total
FROM transactions;

-- Row numbering with partitions
SELECT 
  user_id,
  purchase_date,
  amount,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_date DESC) as purchase_rank
FROM purchases;

-- Moving averages
SELECT 
  date,
  price,
  AVG(price) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7d
FROM stock_prices;

Common Table Expressions (CTEs)

-- Recursive CTE for hierarchical data
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;

-- Multiple CTEs for complex queries
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 Operations

-- Query JSON columns
SELECT data->>'name' as name, 
       data->'address'->>'city' as city
FROM customers
WHERE data->>'status' = 'active';

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

-- JSON path queries
SELECT * FROM events
WHERE metadata @> '{"type": "purchase"}';

Database Design Patterns

1. Partitioning

-- Range partitioning by date
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. Materialized Views

-- Create materialized view for expensive queries
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 periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;

-- Create index on materialized view
CREATE INDEX idx_user_stats_user_id ON user_stats(user_id);

3. Constraints and Validation

-- Check constraints
ALTER TABLE products
ADD CONSTRAINT price_positive CHECK (price > 0);

-- Exclusion constraints
CREATE TABLE bookings (
  room_id INT,
  during TSRANGE,
  EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);

-- Domain types for reusable constraints
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
);

Transactions and Concurrency

Transaction Isolation

-- Serializable transactions for critical operations
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;

-- Repeatable read for consistent snapshots
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT SUM(balance) FROM accounts;
-- Consistent view maintained throughout transaction
COMMIT;

Row-Level Locking

-- Pessimistic locking
SELECT * FROM orders WHERE id = 123 FOR UPDATE;

-- Shared lock for read-only access
SELECT * FROM products WHERE id = 456 FOR SHARE;

-- Skip locked rows
SELECT * FROM queue WHERE processed = false
FOR UPDATE SKIP LOCKED
LIMIT 10;

Monitoring and Maintenance

Query Performance

-- Find slow queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Check index usage
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

-- Table bloat
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;

Maintenance Tasks

-- Vacuum and analyze
VACUUM ANALYZE users;

-- Reindex
REINDEX TABLE users;

-- Update statistics
ANALYZE users;

Best Practices

Always use parameterized queries to prevent SQL injection
Create indexes on foreign keys for join performance
Use connection pooling for better resource management
Monitor query performance with pg_stat_statements
Regular VACUUM and ANALYZE for statistics
Use appropriate transaction isolation levels
Avoid N+1 queries with proper joins or batching
Implement retry logic for transaction conflicts

Integration Points

Complements:

backend-implementation-patterns : For API data access
tdd-workflow : For database testing
verification-loop : For query performance checks
security-implementation-guide : For secure queries

Weekly Installs

Repository

4444j99/a-i--skills

GitHub Stars

First Seen

1 day ago

Security Audits

Gen Agent Trust HubPass SocketPass SnykPass

Installed on

zencoder1

amp1

cline1

openclaw1

opencode1

cursor1

React 组合模式指南：Vercel 组件架构最佳实践，提升代码可维护性

109,600 周安装

PostgreSQL 高级模式：数据库设计、查询优化与性能调优实战指南

🇨🇳中文介绍

PostgreSQL 高级模式

性能优化

1. 高效索引

2. 查询优化

3. 连接池

相关 Skills

高级查询模式

窗口函数

公共表表达式 (CTE)

JSON 操作

数据库设计模式

1. 分区

2. 物化视图

3. 约束和验证

事务和并发

事务隔离

行级锁定

监控和维护

查询性能

维护任务

最佳实践

集成点