with one click
postgres-patterns
用于查询优化、模式设计、索引和安全性的PostgreSQL数据库模式。基于Supabase最佳实践。
Menu
用于查询优化、模式设计、索引和安全性的PostgreSQL数据库模式。基于Supabase最佳实践。
| name | postgres-patterns |
| description | 用于查询优化、模式设计、索引和安全性的PostgreSQL数据库模式。基于Supabase最佳实践。 |
| origin | ECC |
PostgreSQL 最佳实践快速参考。如需详细指导,请使用 database-reviewer 智能体。
| 查询模式 | 索引类型 | 示例 |
|---|---|---|
WHERE col = value | B-tree(默认) | CREATE INDEX idx ON t (col) |
WHERE col > value | B-tree | CREATE INDEX idx ON t (col) |
WHERE a = x AND b > y | 复合索引 | 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) |
| 时间序列范围查询 | BRIN | CREATE INDEX idx ON t USING brin (col) |
| 使用场景 | 正确类型 | 避免使用 |
|---|---|---|
| ID | bigint | int,随机 UUID |
| 字符串 | text | varchar(255) |
| 时间戳 | timestamptz | timestamp |
| 货币 | numeric(10,2) | float |
| 标志位 | boolean | varchar,int |
复合索引顺序:
-- 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'
覆盖索引:
CREATE INDEX idx ON users (email) INCLUDE (name, created_at);
-- Avoids table lookup for SELECT email, name, created_at
部分索引:
CREATE INDEX idx ON users (email) WHERE deleted_at IS NULL;
-- Smaller index, only includes active users
RLS 策略(优化版):
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;
游标分页:
SELECT * FROM products WHERE id > $last_id ORDER BY id LIMIT 20;
-- O(1) vs OFFSET which is 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 *;
-- 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 - 完整的数据库审查工作流clickhouse-io - ClickHouse 分析模式backend-patterns - API 和后端模式基于 Supabase 代理技能(致谢:Supabase 团队)(MIT 许可证)
Suggests manual context compaction at logical intervals to preserve context through task phases rather than arbitrary auto-compaction.
Suggests manual context compaction at logical intervals to preserve context through task phases rather than arbitrary auto-compaction.
在回答之前先读取仓库的实时状态,引导用户了解 ECC 当前的 agents、skills、命令、hooks、规则、安装配置档案以及项目接入流程。
当用户希望通过并行工作、并发 agents、批量工具调用、隔离 worktree 或多条独立验证通道来大幅加速任务、同时不损失正确性时使用。
Garbage collection for your Claude Code configuration. Periodically scans ~/.claude (skills, memory, hooks, permissions, MCP servers, caches) for redundant, stale, orphaned, or low-value items, then walks the user through a confirm-each-deletion cleanup. Use when the user says "clean up my config", "config GC", "too many skills", "audit my setup", "my .claude is bloated", or asks for a periodic config review.
Fact-forcing gate that blocks Edit/Write/Bash (including MultiEdit) and demands concrete investigation (importers, data schemas, user instruction) before allowing the action. Measurably improves output quality by +2.25 points vs ungated agents.