skills.rest
DucCuong159
DucCuong159COMMUNITY
Explore all skills by this author →
GitHub

postgres-best-practices

Provides prioritized Postgres performance optimization rules with incorrect and correct SQL examples.

Updated Aug 11, 2026
Search Tags:#query optimization#indexing#supabase#row level security#connection pooling#database performance#postgres
One-click install
npx skills add https://github.com/DucCuong159/Realtime-chatapp --skill postgres-best-practices-duccuong159
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgres-best-practices
Source: https://github.com/DucCuong159/Realtime-chatapp/tree/main/.agent/skills/postgres-best-practices
Command: npx skills add https://github.com/DucCuong159/Realtime-chatapp --skill postgres-best-practices-duccuong159

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve? Writing efficient Postgres queries, schemas, and configurations is error-prone, and common mistakes like missing indexes, unbounded connections, or poorly written RLS policies cause severe performance degradation. This Skill gives AI agents and developers a prioritized, example-driven rule set for optimizing Postgres and Supabase databases. ## Core Features & Use Cases - Prioritized Rule Catalog: 30+ rules across 8 categories (query performance, connection management, security & RLS, schema design, locking, data access, monitoring, advanced features) ranked by impact from CRITICAL to LOW. - Incorrect vs. Correct SQL Examples: Every rule shows the anti-pattern first, then the optimized rewrite, with quantified impact (e.g., 100-1000x faster queries with proper indexes). - Supabase-Specific Guidance: Covers Row Level Security policies, connection pooling modes, prepared statement handling, and auth-aware patterns. - Use Case: When reviewing a slow query like select * from orders where customer_id = 123, the Skill directs you to add an index on the filtered column and verify with EXPLAIN ANALYZE. ## Quick Start Ask the agent to review your Postgres query or schema design using the postgres-best-practices rules and suggest optimized SQL.

Frequently Asked Questions about postgres-best-practices

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I optimize slow Postgres queries?▼

Start by adding indexes on WHERE and JOIN columns, which can yield 100-1000x speedups on large tables. Use EXPLAIN ANALYZE to confirm sequential scans, then apply composite, partial, or covering indexes matching your query filters.

What index type should I use in Postgres?▼

Use B-tree for equality and range comparisons, GIN for JSONB, arrays, and full-text search, BRIN for large time-series tables, and Hash for equality-only lookups. The default B-tree cannot optimize JSONB containment operators like @>.

How do I configure connection pooling for Postgres?▼

Use a pooler like PgBouncer between your application and database, sizing the pool at roughly (CPU cores * 2). Transaction mode suits most apps, but named prepared statements require session mode or unnamed statements to avoid conflicts.

Does Row Level Security slow down Postgres queries?▼

Poorly written RLS policies can, but wrapping functions like auth.uid() in a SELECT subquery caches the result per query instead of per row. Adding indexes on policy columns and using security definer functions yields 5-10x faster RLS queries.

When should I partition a Postgres table?▼

Partition tables exceeding 100M rows, time-series data queried by date ranges, or tables where you need to drop old data efficiently. Range partitioning by timestamp lets queries scan only relevant partitions and makes DROP TABLE instant.

Why should I avoid OFFSET pagination in Postgres?▼

OFFSET scans all skipped rows, so page 10000 reads 200,000 rows. Cursor-based (keyset) pagination using WHERE id > last_seen_id uses the index directly and maintains constant O(1) performance regardless of page depth.

Related Skills

Explore More Software Engineering Skills→
Chang-Shih-YungChang-Shih-Yung

supabase-postgres-best-practices

Review Supabase Postgres RPC functions and add composite indexes for filtered queries.

Community
Advanced
selrai-companyselrai-company

postgresql-optimization

Analyze EXPLAIN ANALYZE and pg_stat_statements to recommend PostgreSQL indexes and configuration.

Official
Advanced
gabrielnsmntogabrielnsmnto
3

db-rls-audit

Audits PostgreSQL tables for RLS coverage and lists all row-level security policies.

Community
Intermediate
Fantasia1999Fantasia1999

database-designer

Analyzes SQL DDL schemas and generates zero-downtime migration scripts.

Community
Advanced