home / skills / secondsky / claude-skills / supabase-postgres-best-practices

supabase-postgres-best-practices skill

/plugins/supabase-postgres-best-practices/skills/supabase-postgres-best-practices

This skill helps you optimize Postgres queries, schemas, and configurations using Supabase best-practices for faster, more scalable databases.

This is most likely a fork of the supabase-postgres-best-practices skill from davila7

npx playbooks add skill secondsky/claude-skills --skill supabase-postgres-best-practices

Review the files below or copy the command above to add this skill to your agents.

Files (37)

SKILL.md

2.5 KB

---
name: supabase-postgres-best-practices
description: Postgres performance optimization and best practices from Supabase. Use this skill when writing, reviewing, or optimizing Postgres queries, schema designs, or database configurations.
license: MIT
metadata:
  author: supabase
  version: "1.1.0"
  organization: Supabase
  date: January 2026
  abstract: Comprehensive Postgres performance optimization guide for developers using Supabase and Postgres. Contains performance rules across 8 categories, prioritized by impact from critical (query performance, connection management) to incremental (advanced features). Each rule includes detailed explanations, incorrect vs. correct SQL examples, query plan analysis, and specific performance metrics to guide automated optimization and code generation.
---

# 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

| 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

- https://www.postgresql.org/docs/current/
- https://supabase.com/docs
- https://wiki.postgresql.org/wiki/Performance_Optimization
- https://supabase.com/docs/guides/database/overview
- https://supabase.com/docs/guides/auth/row-level-security

Overview

This skill provides a concise, prioritized set of Postgres performance and design best practices based on Supabase guidance. It focuses on high-impact rules for query performance, connection management, and security while covering schema design, indexing, concurrency, and monitoring. Use it to make actionable changes to queries, schema, or database configuration that improve latency, throughput, and safety.

How this skill works

The skill groups rules into eight priority categories and gives concrete recommendations, incorrect/correct SQL examples, and Supabase-specific notes. It inspects query patterns, index usage, connection settings, RLS configurations, and schema choices to highlight hotspots and remediation steps. The output prioritizes critical fixes (missing indexes, connection pooling, RLS pitfalls) and suggests follow-up diagnostics like EXPLAIN and pg_stat views.

When to use it

Writing or reviewing SQL queries for production workloads
Designing or evolving database schemas and indexes
Diagnosing slow queries or high latency spikes
Configuring connection pooling or scaling database connections
Implementing or auditing Row-Level Security (RLS) policies

Best practices

Identify and add missing or selective indexes; prefer partial and covering indexes when appropriate
Use EXPLAIN/EXPLAIN ANALYZE to validate planner choices before applying changes
Avoid SELECT * in hot paths; select only needed columns to reduce IO
Configure connection pooling to match app concurrency and avoid connection storms
Design tables with appropriate data types, normalization balance, and thoughtful FK/index placement
Audit RLS rules for performance and correctness; ensure policies are selective and indexed

Example use cases

Optimize a slow JOIN by adding a composite index and rewriting predicates to be sargable
Reduce peak connection count by introducing a connection pooler and tuning max connections
Refactor a wide table read path to use a covering index and smaller row width
Add a partial index to speed queries filtered by active status without indexing all rows
Diagnose locking contention by inspecting pg_locks and applying row-level updates or partitioning

FAQ

How do I know which indexes to add?

Start with EXPLAIN on the slow query, review sequential scans and filter predicates, then add selective indexes that match WHERE and JOIN columns; consider partial indexes for highly skewed data.

When should I tune connections vs. queries?

If you see many idle or waiting connections and connection errors, prioritize pooling. If individual queries consume CPU or I/O, prioritize query/index optimization first.