Supabase Postgres Best Practices

From the author

Postgres performance optimization and best practices from Supabase. Use this skill when writing, reviewing, or optimizing Postgres queries, schema designs, or database configurations.

— the description by Supabase

AutoGineer summary

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

This skill provides a comprehensive set of performance-oriented guidelines for PostgreSQL, specifically curated for developers using Supabase. It organizes best practices into eight prioritized categories ranging from critical query performance and connection management to advanced features. Each rule follows a standardized format that includes clear explanations, anti-pattern identification, and concrete SQL transformation examples to help developers and AI agents write more efficient database code.

What it does

Provides actionable SQL transformation patterns to replace inefficient queries.
Categorizes database best practices by performance impact level.
Offers guidance on connection pooling and serverless database configurations.
Includes standards for Row-Level Security and schema design.
Supplies examples for resolving common issues like N+1 queries and missing indexes.
Standardizes the documentation of database performance rules for AI consumption.

When to use it

When writing or reviewing SQL queries for production applications.
During database schema design to ensure long-term scalability.
When troubleshooting slow query performance or high database load.
When configuring connection limits or implementing transaction management.
When optimizing Row-Level Security policies for better performance.

Advantages

Prioritizes optimizations based on real-world performance impact.
Uses a consistent error-first structure that makes anti-patterns easy to identify.
Provides concrete, runnable SQL examples for immediate application.
Includes quantified impact metrics to help developers prioritize tasks.
Reduces ambiguity by focusing on specific transformation patterns.

Usage examples

“How can I optimize my Postgres query to avoid N+1 issues when fetching user posts?”: The agent will reference the data-n-plus-one guidelines, showing an incorrect loop-based query followed by a corrected, batched SQL query using the ANY operator.
“What is the best way to handle connection pooling in a serverless environment?”: The agent will provide guidance from the connection management section, explaining the necessity of connection pooling and how to configure it effectively.

Install

Download the skill as a ZIP:
https://mp-staging.autogineer.com/agent-skills/jetbrains/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/schema-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

AGENTS.md2.1 KiB
ATTRIBUTION.mdAdded by AutoGineer877 B
CLAUDE.md9 B
README.md3.1 KiB
SKILL.md2.7 KiB
references/_contributing.md4.1 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.md1.5 KiB

references/conn-idle-timeout.md

title: Configure Idle Connection Timeouts impact: HIGH impactDescription: Reclaim 30-50% of connection slots from idle clients tags: connections, timeout, idle, resource-management

Configure Idle Connection Timeouts

Idle connections waste resources. Configure timeouts to automatically reclaim them.

Incorrect (connections held indefinitely):

-- No timeout configured
show idle_in_transaction_session_timeout;  -- 0 (disabled)

-- Connections stay open forever, even when idle
select pid, state, state_change, query
from pg_stat_activity
where state = 'idle in transaction';
-- Shows transactions idle for hours, holding locks

Correct (automatic cleanup of idle connections):

-- Terminate connections idle in transaction after 30 seconds
alter system set idle_in_transaction_session_timeout = '30s';

-- Terminate completely idle connections after 10 minutes
alter system set idle_session_timeout = '10min';

-- Reload configuration
select pg_reload_conf();

For pooled connections, configure at the pooler level:

# pgbouncer.ini
server_idle_timeout = 60
client_idle_timeout = 300

Reference: Connection Timeouts

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.

Implement Spec
Matt Pocock· Community
A workflow for implementing technical specifications and tickets using concurrent subagents.
Software developmentClaude CodeClaude appsOpenAI Codex+14 more
MITGitHub stars of the source repository317.5k
MCP Builder
AnthropicOfficial
A comprehensive guide for building high-quality Model Context Protocol (MCP) servers to integrate external services with LLMs.
Software developmentClaude CodeClaude appsOpenAI Codex+14 more
Apache-2.0Low riskHas scriptsGitHub stars of the source repository188.6k
Schema
Corey Haines· Community
Expert guidance for implementing and optimizing JSON-LD schema markup to improve search engine content understanding.
Software developmentClaude CodeClaude appsOpenAI Codex+14 more
MITGitHub stars of the source repository59k
Postgresql Table Design
Seth Hobson· Community
A comprehensive guide for designing, reviewing, and optimizing PostgreSQL database schemas.
Software developmentClaude CodeClaude appsOpenAI Codex+14 more
MITGitHub stars of the source repository42.3k
SQL Optimization Patterns
Seth Hobson· Community
A comprehensive guide to optimizing SQL database performance through indexing, query plan analysis, and efficient coding patterns.
Software developmentClaude CodeClaude appsOpenAI Codex+14 more
MITGitHub stars of the source repository42.3k
API Scaffolding Fastapi Pro
Seth Hobson· Community
An expert FastAPI developer focused on high-performance, async-first API architecture and production-ready microservices.
Software developmentClaude Code
MITGitHub stars of the source repository42.3k

Download ZIP

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