PostgreSQL 最佳实践速查表 | 索引、数据类型、反模式与性能优化指南

postgres-patterns by affaan-m/everything-claude-code

2,300 周安装量

110,400 GitHub Stars

GitHub

安装命令

npx skills add https://github.com/affaan-m/everything-claude-code --skill postgres-patterns

开发数据分析开发运维

🇨🇳中文介绍

PostgreSQL 模式

PostgreSQL 最佳实践速查表。如需详细指导，请使用 database-reviewer 代理。

何时启用

编写 SQL 查询或迁移时
设计数据库模式时
排查慢查询时
实现行级安全时
设置连接池时

速查表

索引速查表

查询模式	索引类型	示例
`WHERE col = value`	B-tree (默认)	`CREATE INDEX idx ON t (col)`
`WHERE col > value`	B-tree	`CREATE INDEX idx ON t (col)`

广告位招租

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

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

联系我们

数据类型速查表

使用场景	正确类型	应避免
ID	`bigint`	`int`, 随机 UUID
字符串	`text`	`varchar(255)`
时间戳	`timestamptz`	`timestamp`
货币	`numeric(10,2)`	`float`
标志位	`boolean`	`varchar`, `int`

复合索引顺序：

-- 等值列在前，范围列在后
CREATE INDEX idx ON orders (status, created_at);
-- 适用于：WHERE status = 'pending' AND created_at > '2024-01-01'

CREATE INDEX idx ON users (email) INCLUDE (name, created_at);
-- 避免为 SELECT email, name, created_at 进行表查找

CREATE INDEX idx ON users (email) WHERE deleted_at IS NULL;
-- 索引更小，仅包含活跃用户

RLS 策略（优化版）：

CREATE POLICY policy ON orders
  USING ((SELECT auth.uid()) = user_id);  -- 用 SELECT 包裹！

INSERT INTO settings (user_id, key, value)
VALUES (123, 'theme', 'dark')
ON CONFLICT (user_id, key)
DO UPDATE SET value = EXCLUDED.value;

SELECT * FROM products WHERE id > $last_id ORDER BY id LIMIT 20;
-- O(1) 复杂度，而 OFFSET 是 O(n)

UPDATE jobs SET status = 'processing'
WHERE id = (
  SELECT id FROM jobs WHERE status = 'pending'
  ORDER BY created_at LIMIT 1
  FOR UPDATE SKIP LOCKED
) RETURNING *;

-- 查找未建立索引的外键
SELECT conrelid::regclass, a.attname
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey)
  );

-- 查找慢查询
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC;

-- 检查表膨胀
SELECT relname, n_dead_tup, last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- 连接限制（根据内存调整）
ALTER SYSTEM SET max_connections = 100;
ALTER SYSTEM SET work_mem = '8MB';

-- 超时设置
ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';
ALTER SYSTEM SET statement_timeout = '30s';

-- 监控
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 安全默认设置
REVOKE ALL ON SCHEMA public FROM public;

SELECT pg_reload_conf();

代理：database-reviewer - 完整的数据库审查工作流
技能：clickhouse-io - ClickHouse 分析模式
技能：backend-patterns - API 和后端模式

基于 Supabase Agent Skills (credit: Supabase team) (MIT License)

🇺🇸English

PostgreSQL Patterns

Quick reference for PostgreSQL best practices. For detailed guidance, use the database-reviewer agent.

When to Activate

Writing SQL queries or migrations
Designing database schemas
Troubleshooting slow queries
Implementing Row Level Security
Setting up connection pooling

Quick Reference

Index Cheat Sheet

Query Pattern	Index Type	Example
`WHERE col = value`	B-tree (default)	`CREATE INDEX idx ON t (col)`
`WHERE col > value`	B-tree	`CREATE INDEX idx ON t (col)`
`WHERE a = x AND b > y`	Composite	`CREATE INDEX idx ON t (a, b)`
`WHERE jsonb @> '{}'`	GIN	`CREATE INDEX idx ON t USING gin (col)`
`WHERE tsv @@ query`	GIN	`CREATE INDEX idx ON t USING gin (col)`
Time-series ranges	BRIN	`CREATE INDEX idx ON t USING brin (col)`

Data Type Quick Reference

Use Case	Correct Type	Avoid
IDs	`bigint`	`int`, random UUID
Strings	`text`	`varchar(255)`
Timestamps	`timestamptz`	`timestamp`
Money	`numeric(10,2)`

Common Patterns

Composite Index Order:

-- Equality columns first, then range columns
CREATE INDEX idx ON orders (status, created_at);
-- Works for: WHERE status = 'pending' AND created_at > '2024-01-01'

Covering Index:

CREATE INDEX idx ON users (email) INCLUDE (name, created_at);
-- Avoids table lookup for SELECT email, name, created_at

Partial Index:

CREATE INDEX idx ON users (email) WHERE deleted_at IS NULL;
-- Smaller index, only includes active users

RLS Policy (Optimized):

CREATE POLICY policy ON orders
  USING ((SELECT auth.uid()) = user_id);  -- Wrap in SELECT!

UPSERT:

INSERT INTO settings (user_id, key, value)
VALUES (123, 'theme', 'dark')
ON CONFLICT (user_id, key)
DO UPDATE SET value = EXCLUDED.value;

Cursor Pagination:

SELECT * FROM products WHERE id > $last_id ORDER BY id LIMIT 20;
-- O(1) vs OFFSET which is O(n)

Queue Processing:

UPDATE jobs SET status = 'processing'
WHERE id = (
  SELECT id FROM jobs WHERE status = 'pending'
  ORDER BY created_at LIMIT 1
  FOR UPDATE SKIP LOCKED
) RETURNING *;

Anti-Pattern Detection

-- Find unindexed foreign keys
SELECT conrelid::regclass, a.attname
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey)
  );

-- Find slow queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC;

-- Check table bloat
SELECT relname, n_dead_tup, last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Configuration Template

-- Connection limits (adjust for RAM)
ALTER SYSTEM SET max_connections = 100;
ALTER SYSTEM SET work_mem = '8MB';

-- Timeouts
ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';
ALTER SYSTEM SET statement_timeout = '30s';

-- Monitoring
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Security defaults
REVOKE ALL ON SCHEMA public FROM public;

SELECT pg_reload_conf();

Agent: database-reviewer - Full database review workflow
Skill: clickhouse-io - ClickHouse analytics patterns
Skill: backend-patterns - API and backend patterns

Based on Supabase Agent Skills (credit: Supabase team) (MIT License)

Weekly Installs

1.5K

Repository

affaan-m/everyt…ude-code

GitHub Stars

70.6K

First Seen

Jan 26, 2026

Security Audits

Gen Agent Trust HubPass SocketPass SnykPass

Installed on

opencode1.2K

codex1.2K

gemini-cli1.1K

github-copilot1.1K

claude-code1.0K

cursor940

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

102,200 周安装