What does this skill do?

The PostgreSQL Skill Guide provides a quick and practical reference to best practices in PostgreSQL: efficient indexing, data type selection, common query patterns such as UPSERT or cursor pagination, row-level security (RLS), and server configuration. Designed for developers who write SQL, create migrations, or design relational schemas and need to optimize their database’s performance.

Optimizing Slow Queries
Use `pg_stat_statements` to diagnose queries and apply the correct index to drastically reduce execution times.
Relational Schema Design
Choose the appropriate data types (bigint for IDs, text for strings, timestamptz for dates) from the start of the project.
Implementation of RLS
Create optimized Row Level Security policies by wrapping auth.uid() in a SELECT statement to improve the planner's performance.
Anti-Pattern Audit
Detect unindexed foreign keys, bloated tables, and slow queries using ready-to-run audit queries.

Usage examples

⚡ Frequently Asked Questions
I need to optimize a query that filters by status and created_at in the orders table. What index do you recommend?
🔄 UPSERT with a conflict
Write a UPSERT statement for the `settings` table that inserts or updates the value of a key for a specific user.
🔒 Effective RLS Policy
Create an RLS policy for the `orders` table that allows each user to view only their own orders, optimized for the planner.
🔍 Detect foreign keys without an index
Audit my database and tell me which foreign keys do not have an associated index.

Features

Indexing Guide by Query Pattern A quick reference table that matches each query type (equality, range, JSONB, temporal) with the appropriate index (B-tree, composite, GIN, BRIN).
Recommended Data Types Clear guidance on which data type to use in each case: `bigint` for IDs, `text` for strings, `timestamp(tz)` for dates, and `numeric` for currency.
Production-Ready SQL Patterns UPSERT with ON CONFLICT, O(1) cursor paging, queues with SKIP LOCKED, and partial indexes for active records.
Anti-Pattern Audit Queries to detect unindexed foreign keys, slow queries using pg_stat_statements, and bloated tables.
Security with Row-Level Security Optimized RLS policies that wrap `auth.uid()` in a `SELECT` statement to allow the planner to cache the result.

Frequently asked questions

No. Although these best practices are inspired by Supabase, the SQL and configuration patterns apply to any PostgreSQL instance.
Yes. The detection queries are read-only (SELECT) and do not modify the database. You can run them safely in production.
Most patterns work with PostgreSQL 9.5 and later. Some, such as SKIP LOCKED, require 9.5 or later, and the pg_stat_statements extension must be enabled.
Yes. Describe your table and the performance issue, and Claude will adapt the appropriate SQL query to your specific structure.
PostgreSQL Best Practices — Optimizing Queries and Schemas with Claude AI

¿Prefieres escuchar el contenido? Genera la narración de audio con un clic.