AutoGineer
Generated banner for Supabase Postgres Best Practices
Generated logo for Supabase Postgres Best Practices

Supabase Postgres Best Practices

JetBrainsOfficialCreated by SupabaseDistributed by JetBrains

  • Skill
  • Guidelines
  • Software development
  • MIT
  • No issues found
Download ZIP

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

  1. Download the skill as a ZIP:

    https://mp-staging.autogineer.com/agent-skills/jetbrains/supabase-postgres-best-practices/download

  2. 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

Supabase Postgres Best Practices

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

PriorityCategoryImpactPrefix
1Query PerformanceCRITICALquery-
2Connection ManagementCRITICALconn-
3Security & RLSCRITICALsecurity-
4Schema DesignHIGHschema-
5Concurrency & LockingMEDIUM-HIGHlock-
6Data Access PatternsMEDIUMdata-
7Monitoring & DiagnosticsLOW-MEDIUMmonitor-
8Advanced FeaturesLOWadvanced-

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

references/data-pagination.md

title: Use Cursor-Based Pagination Instead of OFFSET impact: MEDIUM-HIGH impactDescription: Consistent O(1) performance regardless of page depth tags: pagination, cursor, keyset, offset, performance

Use Cursor-Based Pagination Instead of OFFSET

OFFSET-based pagination scans all skipped rows, getting slower on deeper pages. Cursor pagination is O(1).

Incorrect (OFFSET pagination):

-- Page 1: scans 20 rows
select * from products order by id limit 20 offset 0;

-- Page 100: scans 2000 rows to skip 1980
select * from products order by id limit 20 offset 1980;

-- Page 10000: scans 200,000 rows!
select * from products order by id limit 20 offset 199980;

Correct (cursor/keyset pagination):

-- Page 1: get first 20
select * from products order by id limit 20;
-- Application stores last_id = 20

-- Page 2: start after last ID
select * from products where id > 20 order by id limit 20;
-- Uses index, always fast regardless of page depth

-- Page 10000: same speed as page 1
select * from products where id > 199980 order by id limit 20;

For multi-column sorting:

-- Cursor must include all sort columns
select * from products
where (created_at, id) > ('2024-01-15 10:00:00', 12345)
order by created_at, id
limit 20;

Reference: Pagination

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.

Attribution and licence

Created by
Supabase
Publisher
JetBrains (Official)
Distributed by
JetBrains
Source
JetBrains/skills · postgres-best-practices · commit 3f53782
Licence
MIT License (redistribution allowed)
  • ATTRIBUTION.md (written by AutoGineer)
Synced

AutoGineer is not affiliated with or endorsed by Supabase or JetBrains.

Download ZIP

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