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://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/conn-limits.md

title: Set Appropriate Connection Limits impact: CRITICAL impactDescription: Prevent database crashes and memory exhaustion tags: connections, max-connections, limits, stability

Set Appropriate Connection Limits

Too many connections exhaust memory and degrade performance. Set limits based on available resources.

Incorrect (unlimited or excessive connections):

-- Default max_connections = 100, but often increased blindly
show max_connections;  -- 500 (way too high for 4GB RAM)

-- Each connection uses 1-3MB RAM
-- 500 connections * 2MB = 1GB just for connections!
-- Out of memory errors under load

Correct (calculate based on resources):

-- Formula: max_connections = (RAM in MB / 5MB per connection) - reserved
-- For 4GB RAM: (4096 / 5) - 10 = ~800 theoretical max
-- But practically, 100-200 is better for query performance

-- Recommended settings for 4GB RAM
alter system set max_connections = 100;

-- Also set work_mem appropriately
-- work_mem * max_connections should not exceed 25% of RAM
alter system set work_mem = '8MB';  -- 8MB * 100 = 800MB max

Monitor connection usage:

select count(*), state from pg_stat_activity group by state;

Reference: Database Connections

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