Supabase Postgres Best Practices

From the author

Postgres best practices maintained by Supabase, for Postgres running anywhere. Load this skill BEFORE writing or changing anything that lives in a Postgres database: creating or altering tables and columns (including choosing column types), schema design, migrations and declarative schema files, RLS policies and the tests that verify them, indexes, triggers, database functions, queues and scheduled jobs (pg_cron, pgmq), vector/semantic search (pgvector), and restoring dumps (pg_restore) or importing data. Also load it when diagnosing slow queries, high CPU, timeouts, EXPLAIN plans, connection exhaustion, locking, bloat, or rows visible to the wrong user or tenant. This is not just a performance guide — schema, migration, security, and SQL authoring tasks need these rules too, even for a one-column change or a single query.

— the description by supabase

AutoGineer summary

Written by AutoGineer from the skill’s own files, not by the author

This skill provides a structured set of guidelines for working with PostgreSQL databases. It organizes best practices into eight categories, including query performance, connection management, security, schema design, and concurrency. Each guideline follows a standardized format featuring concrete SQL transformation patterns, error-first examples, and quantified impact levels to assist developers in writing and maintaining database code.

What it does

Provides standardized SQL patterns for common database tasks
Categorizes database best practices by impact level
Offers incorrect versus correct SQL code examples for pattern recognition
Includes guidance on schema design, RLS policies, and indexing
Supplies diagnostic patterns for query and connection issues

When to use it

When designing database schemas or writing SQL queries
When implementing indexes or optimizing data access patterns
When configuring connection pooling or managing database security
When diagnosing database performance or locking issues

Advantages

Uses a consistent, error-first structure for learning patterns
Provides clear, runnable SQL examples for common anti-patterns
Prioritizes guidelines based on their impact on database operations
Focuses on concrete, actionable SQL transformations

Usage examples

“How should I optimize my SQL query to avoid N+1 issues?”: The agent provides a comparison between a problematic sequential query pattern and a corrected batched query pattern using SQL best practices.
“What is the best way to index a table for active users?”: The agent suggests a partial index pattern with a concrete SQL example, explaining why it is preferred over a standard index.

Install

Download the skill as a ZIP:
https://mp-staging.autogineer.com/agent-skills/supabase/supabase-postgres-best-practices/download

Unzip it into the cross-client skills folder of your project:

mkdir -p .agents/skills && unzip supabase-postgres-best-practices.zip -d .agents/skills

Documentation by supabase

Comprehensive performance optimization guide for Postgres, maintained by Supabase. Contains rules across 8 categories, prioritized by impact to guide automated query optimization and schema design.

When to Apply

Reference these guidelines when:

Writing SQL queries or designing schemas
Implementing indexes or query optimization
Reviewing database performance issues
Configuring connection pooling or scaling
Optimizing for Postgres-specific features
Working with Row-Level Security (RLS)

Rule Categories by Priority

Priority	Category	Impact	Prefix
1	Query Performance	CRITICAL	`query-`
2	Connection Management	CRITICAL	`conn-`
3	Security & RLS	CRITICAL	`security-`
4	Schema Design	HIGH	`schema-`
5	Concurrency & Locking	MEDIUM-HIGH	`lock-`
6	Data Access Patterns	MEDIUM	`data-`
7	Monitoring & Diagnostics	LOW-MEDIUM	`monitor-`
8	Advanced Features	LOW	`advanced-`

How to Use

Read individual rule files for detailed explanations and SQL examples:

references/query-missing-indexes.md
references/query-partial-indexes.md
references/_sections.md

Each rule file contains:

Brief explanation of why it matters
Incorrect SQL example with explanation
Correct SQL example with explanation
Optional EXPLAIN output or metrics
Additional context and references
Supabase-specific notes (when applicable)

References

Files

ATTRIBUTION.mdAdded by AutoGineer948 B
CHANGELOG.md5.6 KiB
LICENSELicence file added by AutoGineer1.0 KiB
SKILL.md3.2 KiB
references/_contributing.md4.0 KiB
references/_sections.md1.6 KiB
references/_template.md1.0 KiB
references/advanced-full-text-search.md1.4 KiB
references/advanced-jsonb-indexing.md1.4 KiB
references/conn-idle-timeout.md1.3 KiB
references/conn-limits.md1.3 KiB
references/conn-pooling.md1.3 KiB
references/conn-prepared-statements.md1.5 KiB
references/data-batch-inserts.md1.3 KiB
references/data-n-plus-one.md1.3 KiB
references/data-pagination.md1.3 KiB
references/data-upsert.md1.4 KiB
references/lock-advisory.md1.5 KiB
references/lock-deadlock-prevention.md1.8 KiB
references/lock-short-transactions.md1.3 KiB
references/lock-skip-locked.md1.3 KiB
references/monitor-explain-analyze.md1.4 KiB
references/monitor-pg-stat-statements.md1.4 KiB
references/monitor-vacuum-analyze.md1.5 KiB
references/query-composite-indexes.md1.4 KiB
references/query-covering-indexes.md1.3 KiB
references/query-index-types.md1.5 KiB
references/query-missing-indexes.md1.2 KiB
references/query-partial-indexes.md1.3 KiB
references/schema-constraints.md1.8 KiB
references/schema-data-types.md1.4 KiB
references/schema-foreign-key-indexes.md1.6 KiB
references/schema-lowercase-identifiers.md1.7 KiB
references/schema-partitioning.md1.5 KiB
references/schema-primary-keys.md1.8 KiB
references/security-privileges.md1.5 KiB
references/security-rls-basics.md1.3 KiB
references/security-rls-performance.md2.1 KiB

references/_sections.md

Section Definitions

This file defines the rule categories for Postgres best practices. Rules are automatically assigned to sections based on their filename prefix.

Take the examples below as pure demonstrative. Replace each section with the actual rule categories for Postgres best practices.

1. Query Performance (query)

Impact: CRITICAL Description: Slow queries, missing indexes, inefficient query plans. The most common source of Postgres performance issues.

2. Connection Management (conn)

Impact: CRITICAL Description: Connection pooling, limits, and serverless strategies. Critical for applications with high concurrency or serverless deployments.

3. Security & RLS (security)

Impact: CRITICAL Description: Row-Level Security policies, privilege management, and authentication patterns.

4. Schema Design (schema)

Impact: HIGH Description: Table design, index strategies, partitioning, and data type selection. Foundation for long-term performance.

5. Concurrency & Locking (lock)

Impact: MEDIUM-HIGH Description: Transaction management, isolation levels, deadlock prevention, and lock contention patterns.

6. Data Access Patterns (data)

Impact: MEDIUM Description: N+1 query elimination, batch operations, cursor-based pagination, and efficient data fetching.

7. Monitoring & Diagnostics (monitor)

Impact: LOW-MEDIUM Description: Using pg_stat_statements, EXPLAIN ANALYZE, metrics collection, and performance diagnostics.

8. Advanced Features (advanced)

Impact: LOW Description: Full-text search, JSONB optimization, PostGIS, extensions, and advanced Postgres features.

Requirements and compatibility

Works with: Claude Code, Claude apps, OpenAI Codex, GitHub Copilot, Cursor, Gemini CLI, Kiro, OpenCode, Goose, Amp, Roo Code, JetBrains Junie, Factory Droid, Devin (ex-Windsurf), Mistral Vibe, OpenHands, Letta

“Works with” means the tool reads the open Agent Skills format; a skill written for one tool may use features others lack.

Security

No issues found by our static scan· No issues found

A static scan is not a guarantee. Skills are third-party content: review a skill before you let an agent use it.

Anndata
K-Dense· Community
A Python framework for managing annotated data matrices in single-cell genomics and general research workflows.
Data and AIClaude CodeClaude appsOpenAI Codex+14 more
MITLow riskGitHub stars of the source repository56.4k
Liteparse
K-Dense· Community
Local document and PDF parsing with spatial text extraction and bounding box support.
Data and AIClaude CodeClaude appsOpenAI Codex+14 more
Apache-2.0Low riskHas scriptsGitHub stars of the source repository56.4k
Usfiscaldata
K-Dense· Community
Access U.S. Treasury federal financial data via the Fiscal Data REST API.
Data and AIClaude CodeClaude appsOpenAI Codex+14 more
MITLow riskGitHub stars of the source repository56.4k
Geopandas
K-Dense· Community
Guidance and local audit tools for Python workflows using GeoPandas 1.2.0 for spatial data analysis.
Data and AIClaude CodeClaude appsOpenAI Codex+14 more
MITLow riskHas scriptsGitHub stars of the source repository56.4k
Markitdown
K-Dense· Community
A lightweight utility for converting diverse document formats into structured Markdown for analysis, search, and LLM ingestion.
Data and AIClaude CodeClaude appsOpenAI Codex+14 more
MITLow riskHas scriptsGitHub stars of the source repository56.4k
Zarr Python
K-Dense· Community
A guide for storing and querying chunked N-D scientific arrays using Zarr-Python 3.
Data and AIClaude CodeClaude appsOpenAI Codex+14 more
MITLow riskGitHub stars of the source repository56.4k

Download ZIP

The ZIP holds the skill’s files, its licence files and an ATTRIBUTION.md. Download supabase-postgres-best-practices.zip