$ loading_
提供 PostgreSQL 查询优化、模式设计、索引与安全实践建议
复制安装指令，让 AI 自动完成配置 · 推荐新手
请帮我安装 askskill 上的 "postgres-patterns" 技能： 1. 下载 https://raw.githubusercontent.com/affaan-m/ECC/main/skills/postgres-patterns/SKILL.md 2. 保存为 ~/.claude/skills/postgres-patterns/SKILL.md 3. 装好后重载技能，告诉我可以用了
请帮我优化这条 PostgreSQL 慢查询。表结构如下……SQL 如下……当前执行计划如下……请分析瓶颈，并给出可执行的优化建议，包括索引、SQL 改写和是否需要分区。
一份针对慢查询的诊断结果，包含性能瓶颈分析、推荐索引、改写后的 SQL 与优化理由。
我在做一个 SaaS 应用，需要为用户、团队、订阅和权限设计 PostgreSQL 数据库。请基于最佳实践给出表结构、主外键关系、约束、索引建议，并说明如何兼顾可扩展性与查询性能。
一套清晰的 PostgreSQL 模式设计方案，包含表定义思路、关系设计、约束规则与索引建议。
请审查这个 PostgreSQL 数据库的安全设计。当前有多个应用角色和读写权限需求。请给出基于最佳实践的安全方案，包括角色划分、最小权限、RLS 策略、敏感数据保护和常见风险排查清单。
一份数据库安全加固建议，包含角色权限设计、RLS 策略示例、风险点说明与整改清单。
Quick reference for PostgreSQL best practices. For detailed guidance, use the database-reviewer agent.
| 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) |
| Use Case | Correct Type | Avoid |
|---|---|---|
| IDs | bigint | int, random UUID |
| Strings | text | varchar(255) |
| Timestamps | timestamptz | timestamp |
| Money | numeric(10,2) | float |
| Flags | boolean | varchar, int |
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 *;
-- 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;
-- 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();
database-reviewer - Full database review workflowclickhouse-io - ClickHouse analytics patternsbackend-patterns - API and backend patternsBased on Supabase Agent Skills (credit: Supabase team) (MIT License)
通过双评审智能体对结果进行对抗式校验，提升输出发布前的可靠性
提供 PostgreSQL 查询优化、模式设计、索引与安全实践建议