skills.rest
muhammaddadu
muhammaddaduCOMMUNITY
Explore all skills by this author →
WebsiteGitHub

postgres-best-practices

Applies priority-ranked Postgres rules for query performance, indexing, pooling, schema design, and RLS.

1|1|Updated Jul 6, 2026
Search Tags:#query optimization#indexing#sql#connection pooling#row-level security#postgres#database review
One-click install
npx skills add https://github.com/muhammaddadu/ai-skill-collection --skill postgres-best-practices-muhammaddadu
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgres-best-practices
Source: https://github.com/muhammaddadu/ai-skill-collection/tree/main/3-delivery/postgres-best-practices
Command: npx skills add https://github.com/muhammaddadu/ai-skill-collection --skill postgres-best-practices-muhammaddadu

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve? Slow queries, connection exhaustion, and unsafe schemas are the most common Postgres production issues, and they are hard to diagnose without deep database expertise. This Skill provides a curated, priority-ranked rulebook so you can write and review SQL correctly the first time. ## Core Features & Use Cases - Priority-ranked rule categories: Work top-down from critical issues (missing indexes, connection pooling, RLS) to lower-impact ones (partitioning, JSONB, full-text search), each with incorrect vs. correct SQL examples. - Query and schema review: Diagnose slow queries with EXPLAIN ANALYZE guidance, choose the right index type (B-tree, GIN, GiST, BRIN), and design schemas with proper data types, primary keys, and foreign key indexes. - Concurrency and security patterns: Prevent deadlocks with consistent lock ordering, build worker queues with SKIP LOCKED, and enforce tenant isolation with performant Row-Level Security policies. - Use Case: A developer reviewing a migration that adds a new table can check it against the schema design rules, verify foreign key columns are indexed, and confirm RLS policies use the performant (select ...) pattern before merging. ## Quick Start Review my SQL migration and slow query against the Postgres best practices rules and suggest fixes with the rule names cited.

Frequently Asked Questions about postgres-best-practices

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

FAQPage Schema
How do I fix a slow Postgres query?▼

Start with EXPLAIN ANALYZE to see the actual execution plan, then check for sequential scans indicating missing indexes on WHERE and JOIN columns. The rules prioritize query performance fixes first, since a missing index typically dominates all other issues.

Which Postgres index type should I use?▼

Use B-tree for equality and range comparisons, GIN for JSONB, arrays, and full-text search, GiST for geometric and range types, and BRIN for large time-series tables. Composite indexes should place equality columns first and range columns last.

Does this work with Postgres outside Supabase?▼

Yes, roughly 90% of the rules are generic Postgres. Supabase-specific snippets using auth.uid() or built-in roles are tagged, and the generic current_setting() pattern is shown as the portable alternative for RLS policies.

How do I prevent deadlocks in Postgres transactions?▼

Acquire locks in a consistent order by selecting rows with FOR UPDATE in sorted ID order before updating, or use a single atomic UPDATE statement. Keeping transactions short and enabling log_lock_waits also reduces deadlock frequency.

When should I not use OFFSET pagination in Postgres?▼

Avoid OFFSET for deep pages because it scans all skipped rows, making page 10000 far slower than page 1. Use cursor-based keyset pagination with WHERE id > last_seen_id, which stays O(1) regardless of page depth.

Why is my RLS policy slow on large tables?▼

RLS policies that call functions like auth.uid() per row evaluate them millions of times. Wrap the function in a scalar subquery like (select auth.uid()) so it executes once, and add indexes on columns referenced in the policy.

Related Skills

Explore More Software Engineering Skills→
hpsgdhpsgd

data-model

Design data models defining entities, relationships, constraints, and access patterns.

Official
Advanced
InteligentsensingsolutionsInteligentsensingsolutions
1

postgres-best-practices

Optimize PostgreSQL performance with EXPLAIN ANALYZE, indexing, and connection pooling guidance.

Official
Advanced
greyhaven-aigreyhaven-ai
29

Incident Response Skill

Automate incident response with runbooks, root cause analyses, and postmortem documentation.

Community
Basic
rodmena-limitedrodmena-limited
3

moai-domain-database

Provide database schema design, query optimization, and indexing strategies.

Official
Advanced