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 structured set of guidelines for optimizing Postgres database performance, schema design, and connection management. It is organized into eight priority-based categories, ranging from critical query performance and connection handling to advanced features. Each rule follows a standardized format featuring concrete transformation patterns, error-first examples, and quantified performance impacts to assist developers in writing efficient, scalable SQL.

What it does

Provides actionable SQL transformation patterns to replace inefficient anti-patterns.
Categorizes performance rules by impact level from critical to incremental.
Offers guidance on connection pooling, RLS, and transaction management.
Includes standardized templates for analyzing query plans and performance metrics.
Focuses on semantic naming and clear, portable SQL examples.
Supplies best practices for schema design and indexing strategies.

When to use it

When writing or refactoring SQL queries for better performance.
During database schema design to ensure long-term scalability.
When troubleshooting slow queries or high database load.
When configuring connection limits or implementing row-level security.
When auditing existing database code for common performance anti-patterns.

Advantages

Standardized format makes it easy for AI agents to parse and apply rules.
Prioritized impact levels help developers focus on the most critical bottlenecks first.
Concrete before-and-after examples reduce ambiguity in optimization tasks.
Maintained by experts with a focus on real-world Postgres performance.
Promotes clean, maintainable, and efficient SQL coding standards.

Usage examples

“How can I optimize my N+1 query issue in Postgres?”: The agent identifies the N+1 pattern and suggests replacing sequential queries with a single batched query using the ANY operator or JOINs, referencing the data-n-plus-one guide.
“Review this SQL query for potential performance improvements.”: The agent analyzes the query against the provided guidelines, identifying missing indexes or inefficient scan patterns and proposing a corrected SQL version with an explanation of the expected performance gain.

Install

Download the skill as a ZIP:
https://autogineer.com/agent-skills/openai/supabase-postgres-best-practices-lhfb/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 AutoGineer981 B
CHANGELOG.md2.0 KiB
LICENSELicence file added by AutoGineer1.0 KiB
SKILL.md2.5 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/schema-primary-keys.md

title: Select Optimal Primary Key Strategy impact: HIGH impactDescription: Better index locality, reduced fragmentation tags: primary-key, identity, uuid, serial, schema

Select Optimal Primary Key Strategy

Primary key choice affects insert performance, index size, and replication efficiency.

Incorrect (problematic PK choices):

-- identity is the SQL-standard approach
create table users (
  id serial primary key  -- Works, but IDENTITY is recommended
);

-- Random UUIDs (v4) cause index fragmentation
create table orders (
  id uuid default gen_random_uuid() primary key  -- UUIDv4 = random = scattered inserts
);

Correct (optimal PK strategies):

-- Use IDENTITY for sequential IDs (SQL-standard, best for most cases)
create table users (
  id bigint generated always as identity primary key
);

-- For distributed systems needing UUIDs, use UUIDv7 (time-ordered)
-- Requires pg_uuidv7 extension: create extension pg_uuidv7;
create table orders (
  id uuid default uuid_generate_v7() primary key  -- Time-ordered, no fragmentation
);

-- Alternative: time-prefixed IDs for sortable, distributed IDs (no extension needed)
create table events (
  id text default concat(
    to_char(now() at time zone 'utc', 'YYYYMMDDHH24MISSMS'),
    gen_random_uuid()::text
  ) primary key
);

Guidelines:

Single database: bigint identity (sequential, 8 bytes, SQL-standard)
Distributed/exposed IDs: UUIDv7 (requires pg_uuidv7) or ULID (time-ordered, no fragmentation)
serial works but identity is SQL-standard and preferred for new applications
Avoid random UUIDs (v4) as primary keys on large tables (causes index fragmentation)

Reference: Identity Columns

Requirements and compatibility

Written for: OpenAI Codex

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