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/security-privileges.md

title: Apply Principle of Least Privilege impact: MEDIUM impactDescription: Reduced attack surface, better audit trail tags: privileges, security, roles, permissions

Apply Principle of Least Privilege

Grant only the minimum permissions required. Never use superuser for application queries.

Incorrect (overly broad permissions):

-- Application uses superuser connection
-- Or grants ALL to application role
grant all privileges on all tables in schema public to app_user;
grant all privileges on all sequences in schema public to app_user;

-- Any SQL injection becomes catastrophic
-- drop table users; cascades to everything

Correct (minimal, specific grants):

-- Create role with no default privileges
create role app_readonly nologin;

-- Grant only SELECT on specific tables
grant usage on schema public to app_readonly;
grant select on public.products, public.categories to app_readonly;

-- Create role for writes with limited scope
create role app_writer nologin;
grant usage on schema public to app_writer;
grant select, insert, update on public.orders to app_writer;
grant usage on sequence orders_id_seq to app_writer;
-- No DELETE permission

-- Login role inherits from these
create role app_user login password 'xxx';
grant app_writer to app_user;

Revoke public defaults:

-- Revoke default public access
revoke all on schema public from public;
revoke all on all tables in schema public from public;

Reference: Roles and Privileges

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