المهارات postgres-best-practices

📦

postgres-best-practices

Name: postgres-best-practices
Author: sickn33

آمن

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.

يدعم: Claude Codex Code(CC)

🥉 74 برونزي

تنزيل ZIP المهارة

رفع في Claude

اذهب إلى Settings → Capabilities → Skills → Upload skill

فعّل وابدأ الاستخدام

اختبرها

استخدام "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.

التدقيق الأمني

آمن

v1 • 2/24/2026

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.

الملفات التي تم فحصها

3,490

الأسطر التي تم تحليلها

النتائج

إجمالي عمليات التدقيق

لا توجد مشكلات أمنية

تم تدقيقه بواسطة: claude

درجة الجودة

الهندسة المعمارية

100

قابلية الصيانة

المحتوى

المجتمع

100

الأمان

الامتثال للمواصفات

ماذا يمكنك بناءه

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.

جرّب هذه الموجهات

Basic query optimization

Review this PostgreSQL query for performance issues and suggest improvements: [paste query]

Index recommendation

Analyze this table schema and query workload. What indexes should I create for optimal performance? Schema: [paste schema], Queries: [paste queries]

N+1 query fix

I'm fetching users and their orders with separate queries. Show me how to rewrite this as a single efficient JOIN with proper indexing.

Row-Level Security policy

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

الأسئلة المتكررة

How do I know which columns need indexes?

Index columns used in WHERE clauses, JOIN conditions, ORDER BY, and foreign keys. Use pg_stat_user_tables to identify frequently accessed columns without indexes.

What is N+1 and how do I fix it?

N+1 occurs when you fetch a parent record then make N additional queries for child records. Fix by using JOINs or batch loading with IN clauses to fetch all children in one query.

When should I use partial indexes?

Use partial indexes when queries frequently filter on a specific condition (e.g., WHERE status='active'). This creates smaller, faster indexes covering only relevant rows.

How does Row-Level Security work?

RLS enforces access policies at the database level. Enable with ALTER TABLE ... ENABLE ROW LEVEL SECURITY, then create policies that filter rows based on user attributes like organization_id.

What connection pool size should I use?

Start with 10-20 connections per application instance. Monitor pg_stat_activity and adjust based on: (CPU cores * 2) + effective_spindle_count for CPU-bound workloads.

Why are my queries slow after adding indexes?

Possible causes: wrong column order in composite indexes, using functions on indexed columns, outdated statistics (run ANALYZE), or indexes on low-cardinality columns. Use EXPLAIN ANALYZE to diagnose.