playbooks

home / skills / davila7 / claude-code-templates / supabase-postgres-best-practices

supabase-postgres-best-practices skill

/cli-tool/components/skills/database/supabase-postgres-best-practices

This skill helps optimize Postgres queries, schemas, and configurations using Supabase best practices to improve performance and reliability.

This is most likely a fork of the postgres-best-practices skill from first-fluke
npx playbooks add skill davila7/claude-code-templates --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
1.8 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.0.0"
---

# 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:

```
rules/query-missing-indexes.md
rules/schema-partial-indexes.md
rules/_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)

## Full Compiled Document

For the complete guide with all rules expanded: `AGENTS.md`

Overview

This skill provides Postgres performance optimization and best-practice guidance based on Supabase recommendations. It organizes rules by priority and category to help you focus on the highest-impact changes first. Use it to improve queries, schema design, connection handling, and security for Postgres databases.

How this skill works

The skill inspects common Postgres anti-patterns and suggests targeted fixes such as missing indexes, inefficient queries, and suboptimal schema choices. It maps rules to prioritized categories (query performance, connections, security, schema, locking, data access, monitoring, advanced features) and gives concrete examples, correct patterns, and rationale to guide changes. Use its guidance when reviewing EXPLAIN output, diagnosing slow queries, or planning schema changes.

When to use it

  • When writing or reviewing SQL queries to reduce execution time and resource usage.
  • When designing or refactoring schema, indexes, and constraints for growth and scale.
  • When diagnosing production performance issues or slow-running transactions.
  • When configuring connection pooling, scaling, or client connection behavior.
  • When implementing or auditing Row-Level Security and database access controls.
  • When adding monitoring, diagnostics, or Postgres-specific advanced features.

Best practices

  • Prioritize query performance: eliminate sequential scans with appropriate indexes and rewrite expensive joins and subqueries.
  • Tune connection usage: use pooling and limit idle connections to prevent resource exhaustion.
  • Design schemas for access patterns: use partial and multicolumn indexes that match WHERE and ORDER BY clauses.
  • Avoid long-running transactions and reduce lock contention by keeping transactions short and batching writes.
  • Monitor key metrics (vacuum activity, bloat, query duration) and use EXPLAIN/ANALYZE to validate optimizations.

Example use cases

  • Reviewing slow dashboard queries and applying missing or covering indexes to cut latency.
  • Refactoring a schema before a major feature to reduce table bloat and improve write throughput.
  • Configuring connection pooling for a serverless frontend to avoid connection storms.
  • Hardening Row-Level Security rules and checking for unintended full-table scans.
  • Using targeted monitoring alerts to detect increasing deadlocks or index bloat early.

FAQ

How do I decide which rule to apply first?

Start with high-priority rules: query performance, connection management, and security. Those typically deliver the largest, fastest wins.

What tools should I use to validate changes?

Use EXPLAIN/ANALYZE, pg_stat_statements, server metrics, and your connection pool logs to measure impact before and after changes.