postgres-best-practices
Optimize PostgreSQL queries and schemas
Struggling with slow database queries and poor performance? This skill provides battle-tested PostgreSQL optimization rules from Supabase with concrete SQL examples and measurable performance improvements.
下載技能 ZIP
在 Claude 中上傳
前往 設定 → 功能 → 技能 → 上傳技能
開啟並開始使用
測試它
正在使用「postgres-best-practices」。 This query takes 5 seconds on 100k rows: SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending'
預期結果:
Create a composite index: CREATE INDEX idx_orders_customer_status ON orders (customer_id, status). This changes the execution plan from sequential scan to index scan, reducing query time to under 10ms. The column order matters: place the equality filter (customer_id) before the status filter for optimal index usage.
正在使用「postgres-best-practices」。 How do I prevent N+1 queries when fetching users with their profile data?
預期結果:
Replace individual queries with a JOIN: SELECT u.*, p.bio, p.avatar_url FROM users u LEFT JOIN user_profiles p ON p.user_id = u.id. Add an index on user_profiles(user_id) for fast lookups. This reduces 101 queries (1 for users + 100 for profiles) to a single query.
安全審計
安全This skill contains educational documentation for PostgreSQL best practices from Supabase. All 711 static analysis findings are false positives: the 'external_commands' detections are SQL code blocks in markdown documentation (not shell commands), 'network' URLs are reference links to postgresql.org and supabase.com, 'scripts' findings are SQL WITH clauses (CTEs), and 'crypto' warnings are text pattern matches in documentation. No executable code or security risks present.
品質評分
你能建構什麼
Backend developer optimizing slow queries
Identify missing indexes, fix N+1 patterns, and apply query optimization techniques to reduce response times from seconds to milliseconds.
Database architect designing new schemas
Apply best practices for data types, constraints, foreign keys, and indexing strategies during initial schema design.
DevOps engineer tuning production databases
Configure connection limits, vacuum settings, and monitoring to maintain database health under load.
試試這些提示
Review this PostgreSQL query for performance issues and suggest improvements: [paste query]
Analyze this table schema and query workload. What indexes should I create for optimal performance? Schema: [paste schema], Queries: [paste queries]
I'm fetching users and their orders with separate queries. Show me how to rewrite this as a single efficient JOIN with proper indexing.
Create a Row-Level Security policy for a multi-tenant SaaS where users can only access data belonging to their organization. Table structure: [paste schema]
最佳實務
- Always add indexes on WHERE, JOIN, and ORDER BY columns
- Use EXPLAIN ANALYZE to verify query execution plans before deploying
- Implement connection pooling to prevent database exhaustion
避免
- Running unindexed queries on large tables causing sequential scans
- Fetching all columns with SELECT * instead of specific fields
- Using LIKE with leading wildcards which prevents index usage