skills.rest
gmackie
gmackieCOMMUNITY
Explore all skills by this author →
Website𝕏@grahammackieGitHub

supabase-postgres-best-practices

Applies Postgres best practices for schema design, queries, indexes, RLS, and migrations.

Updated Mar 27, 2026
Search Tags:#query optimization#SQL#database#supabase#row level security#indexes#postgres
One-click install
npx skills add https://github.com/gmackie/agent-skills --skill supabase-postgres-best-practices-gmackie
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: supabase-postgres-best-practices
Source: https://github.com/gmackie/agent-skills/tree/main/skills/supabase-postgres-best-practices
Command: npx skills add https://github.com/gmackie/agent-skills --skill supabase-postgres-best-practices-gmackie

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve? Writing or changing anything in a Postgres database without established rules leads to slow queries, missing indexes, connection exhaustion, deadlocks, and insecure Row-Level Security policies. This Skill provides a prioritized rule set maintained by Supabase so agents and developers apply proven patterns before writing SQL, designing schemas, or diagnosing performance issues. ## Core Features & Use Cases - Prioritized rule library: 8 categories ranked by impact, from critical query performance and connection management to advanced features like full-text search and JSONB indexing. - Error-first SQL examples: Each rule shows an incorrect pattern with explanation, then the correct rewrite with quantified impact (e.g., 100x faster queries, 10x smaller indexes). - Security and RLS guidance: Covers RLS policy authoring, performance optimization with cached auth checks, SECURITY DEFINER pitfalls, and least-privilege role design. - Use Case: Before writing a migration that adds a column and index to a multi-tenant orders table, load this Skill to get the correct composite index column order, idempotent constraint syntax, and an RLS policy that avoids per-row function calls. ## Quick Start Ask the agent to review your Postgres schema or slow query using the Supabase Postgres best practices rules before writing any SQL.

Frequently Asked Questions about supabase-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 with EXPLAIN ANALYZE to see actual timings and row estimates, then check for sequential scans on large tables indicating missing indexes. Add indexes on WHERE and JOIN columns, use composite indexes with equality columns first, and consider partial or covering indexes for filtered queries.

What index type should I use in Postgres?▼

Use B-tree for equality and range comparisons, GIN for JSONB containment, arrays, and full-text search, GiST for geometric and range types, and BRIN for large time-series tables. The default B-tree cannot optimize JSONB @> operators or tsvector search.

Does this work with Postgres outside Supabase?▼

Yes, the rules apply to Postgres running anywhere, not only Supabase-hosted databases. Most guidance covers standard Postgres features like indexes, partitioning, and pg_stat_statements, with Supabase-specific notes included only where relevant such as auth.uid() in RLS policies.

How do I make RLS policies faster in Postgres?▼

Wrap functions like auth.uid() in a SELECT subquery so they execute once instead of per row, which can be over 100x faster on large tables. Always index columns referenced in policies and use security definer functions in a private schema for complex membership checks.

Why does ADD CONSTRAINT IF NOT EXISTS fail in Postgres migrations?▼

Postgres does not support IF NOT EXISTS for ADD CONSTRAINT, so that syntax raises error 42601. Use a DO block that checks pg_constraint for the constraint name before executing ALTER TABLE to make migrations idempotent.

When should I use connection pooling with Postgres?▼

Use pooling for all applications, since each Postgres connection consumes 1-3MB of RAM and high concurrency exhausts available connections. A pooler like PgBouncer in transaction mode lets hundreds of concurrent users share a small pool sized around CPU cores times two.

Related Skills

Explore More Software Engineering Skills→
Adithiya-SAdithiya-S

supabase-postgres-best-practices

Applies Postgres performance, schema, security, and migration rules to SQL authoring and database diagnostics.

Community
Intermediate
100Thieves-team100Thieves-team

sql-pro

Optimizes SQL queries, designs schemas, and analyzes execution plans across database dialects.

Official
Intermediate
scanadyscanady

engineering-database-optimizer

Diagnose slow queries and tune PostgreSQL and MySQL performance through execution plans, indexes, and configuration.

Community
Advanced
OpenAEC-FoundationOpenAEC-Foundation
163

frappe-core-database

Standardize ERPNext database operations with ORM, parameterized SQL, and transaction handling.

Official
Advanced