home / skills / first-fluke / fullstack-starter / postgres-best-practices

postgres-best-practices skill

This skill helps optimize Postgres queries, schemas, and configurations by applying Supabase best practices for performance, security, and reliability.

npx playbooks add skill first-fluke/fullstack-starter --skill postgres-best-practices

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

Files (32)

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 practices curated from Supabase guidance. It targets query tuning, schema design, connection management, and Postgres-specific features to improve reliability and latency. Use it as a practical checklist when building, reviewing, or optimizing databases for production workloads.

How this skill works

The skill organizes rules into prioritized categories (query performance, connections, security, schema, locking, access patterns, monitoring, advanced features). Each rule explains the problem, shows incorrect and corrected SQL or configuration examples, and includes EXPLAIN output or Supabase-specific notes when relevant. It emphasizes high-impact changes first so you can apply quick wins, then deeper improvements for scalability and observability.

When to use it

Writing or refactoring SQL queries to reduce latency and cost
Designing or changing table schemas and indexes
Investigating slow queries, high CPU, or connection exhaustion
Configuring connection pooling, RLS, or connection limits
Preparing a database for production or scaling to more users

Best practices

Prioritize query performance fixes: add missing indexes, avoid sequential scans on large tables, and rewrite expensive joins or aggregations
Manage connections: use pooling, set proper max_connections, and prefer prepared statements where appropriate
Design schemas for access patterns: choose appropriate data types, normalize selectively, and consider partial or covering indexes
Harden security and RLS early: define least-privilege roles and test RLS policies against real queries
Monitor and diagnose: collect EXPLAIN plans, track pg_stat_activity, and alert on long-running queries or lock contention
Apply advanced features selectively: use partitioning, materialized views, or JSONB when they clearly reduce cost or complexity

Example use cases

Optimize a subscription endpoint that scans large tables by adding a partial index matching the filter
Tune connection settings for a Next.js/ FastAPI monorepo to avoid connection spikes under bursty traffic
Review RLS policies for a multi-tenant app before a production rollout to ensure both security and query performance
Refactor slow reports by replacing client-side joins with server-side optimized queries and appropriate indexes
Introduce monitoring queries and alerting for long-running transactions and frequent deadlocks

FAQ

Which rules should I apply first?

Start with query performance and connection management rules. They tend to deliver the largest, fastest impact on latency and stability.

When should I use partitioning or materialized views?

Use partitioning when tables grow very large and queries filter on the partition key. Use materialized views for expensive aggregations that can be refreshed on a schedule rather than computed per request.