Postgres Patterns
PostgreSQL database patterns for query optimization, schema design, indexing, and security. Based on Supabase best practices. Use when designing PostgreSQL schemas, indexes, or RLS policies, or when a query is too slow.
Get the complete skill folder, including its scripts and reference files.
·
When to use this skill
Use Postgres Patterns when an AI agent needs a reusable SKILL.md workflow for this job: PostgreSQL database patterns for query optimization, schema design, indexing, and security. Based on Supabase best practices. Use when designing PostgreSQL schemas, indexes, or RLS policies, or when a query is too slow.
When not to use it
Skip Postgres Patterns when the task is outside the creativity category, or when a more specific skill in this directory already covers the same workflow with clearer triggers.
How to install
Where will you use it?
mkdir -p ~/.claude/skills/postgres-patterns # Save SKILL.md to ~/.claude/skills/postgres-patterns/SKILL.md
- Claude Code personal install: place the skill and its bundled files at ~/.claude/skills/postgres-patterns/SKILL.md. For a shared project, commit the folder at .claude/skills/postgres-patterns/ instead.
- Current Claude Code detects SKILL.md changes in watched directories during the session. If you just created a top-level skills directory, run /reload-skills. Check that the skill is listed before testing its trigger.
- For Claude chat or Cowork in the Desktop app, manage uploaded skills in Customize > Skills; copying files into a local Claude Code folder is not a Desktop install. Other agents may use different install locations.
Full install guide for Claude, Cursor, and Codex →
Try a starter prompt
After installing the skill, paste this starter prompt into your agent to verify it triggers on a small task.
Use the Postgres Patterns skill for this task: Use Postgres Patterns when this documented workflow matches the task. Confirm any required inputs or files before making changes.
What supports this recommendation?
71/100 provisional content score. This measures documentation, practical detail and source signals. It is not a measured task success rate or a security certification.
- Documentation
- 77/100
- Practical detail
- 68/100
- Source evidence
- 63/100
- Source signals
- 72/100
Model compatibility evidence
- claudeuntested
No model-specific signal or recorded compatibility test was found.
- chatgptuntested
No model-specific signal or recorded compatibility test was found.
- geminiuntested
No model-specific signal or recorded compatibility test was found.
- copilotuntested
No model-specific signal or recorded compatibility test was found.
- llamauntested
No model-specific signal or recorded compatibility test was found.
- perplexityuntested
No model-specific signal or recorded compatibility test was found.
- mistraluntested
No model-specific signal or recorded compatibility test was found.
- grokuntested
No model-specific signal or recorded compatibility test was found.
Inferred compatibility is based on content signals. Check the app, its available tools and setup requirements before running a skill.
What this skill does
# PostgreSQL Patterns
Quick reference for PostgreSQL best practices. For detailed guidance, use the `database-reviewer` agent.
## When to Activate
- Writing SQL queries or migrations - Designing database schemas - Troubleshooting slow queries - Implementing Row Level Security - Setting up connection pooling
## Quick Reference
### Index Cheat Sheet
| Query Pattern | Index Type | Example | |--------------|------------|---------| | `WHERE col = value` | B-tree (default) | `CREATE INDEX idx ON t (col)` | | `WHERE col > value` | B-tree | `CREATE INDEX idx ON t (col)` | | `WHERE a = x AND b > y` | Composite | `CREATE INDEX idx ON t (a, b)` | | `WHERE jsonb @> '{}'` | GIN | `CREATE INDEX idx ON t USING gin (col)` | | `WHERE tsv @@ query` | GIN | `CREATE INDEX idx ON t USING gin (col)` | | Time-series ranges | BRIN | `CREATE INDEX idx ON t USING brin (col)` |
### Data Type Quick Reference
| Use Case | Correct Type | Avoid | |----------|-------------|-------| | IDs | `bigint` | `int`, random UUID | | Strings | `text` | `varchar(255)` | | Timestamps | `timestamptz` | `timestamp` | | Money | `numeric(10,2)` | `float` | | Flags | `boolean` | `varchar`, `int` |
### Common Patterns
**Composite Index Order:** ```sql -- Equality columns first, then range columns CREATE INDEX idx ON orders (status, created_at); -- Works for: WHERE status = 'pending' AND created_at > '2024-01-01' ```
**Covering Index:** ```sql CREATE INDEX idx ON users (email) INCLUDE (name, created_at); -- Avoids table lookup for SELECT email, name, created_at ```
**Partial Index:** ```sql CREATE INDEX idx ON users (email) WHERE deleted_at IS NULL; -- Smaller index, only includes active users ```
**RLS Policy (Optimized):** ```sql CREATE POLICY policy ON orders USING ((SELECT auth.uid()) = user_id); -- Wrap in SELECT! ```
**UPSERT:** ```sql INSERT INTO settings (user_id, key, value) VALUES (123, 'theme', 'dark') ON CONFLICT (user_id, key) DO UPDATE SET value = EXCLUDED.value; ```
**Cursor Pagination:** ```sql SELECT * FROM products WHERE id > $last_id ORDER BY id LIMIT 20; -- O(1) vs OFFSET which is O(n) ```
**Queue Processing:** ```sql UPDATE jobs SET status = 'processing' WHERE id = ( SELECT id FROM jobs WHERE status = 'pending' ORDER BY created_at LIMIT 1 FOR UPDATE SKIP LOCKED ) RETURNING *; ```
### Anti-Pattern Detection
```sql -- Find unindexed foreign keys SELECT conrelid::regclass, a.attname FROM pg_constraint c JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey) WHERE c.contype = 'f' AND NOT EXISTS ( SELECT 1 FROM pg_index i WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey) );
-- Find slow queries SELECT query, mean_exec_time, calls FROM pg_stat_statements WHERE mean_exec_time > 100 ORDER BY mean_exec_time DESC;
-- Check table bloat SELECT relname, n_dead_tup, last_vacuum FROM pg_stat_user_tables WHERE n_dead_tup > 1000 ORDER BY n_dead_tup DESC; ```
### Configuration Template
```sql -- Connection limits (adjust for RAM) ALTER SYSTEM SET max_connections = 100; ALTER SYSTEM SET work_mem = '8MB';
-- Timeouts ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s'; ALTER SYSTEM SET statement_timeout = '30s';
-- Monitoring CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Security defaults REVOKE ALL ON SCHEMA public FROM public;
SELECT pg_reload_conf(); ```
## Related
- Agent: `database-reviewer` - Full database review workflow - Skill: `clickhouse-io` - ClickHouse analytics patterns - Skill: `backend-patterns` - API and backend patterns
---
*Based on Supabase Agent Skills (credit: Supabase team) (MIT License)*
Intended uses
- Use Postgres Patterns when this documented workflow matches the task.
Implementation notes
- Review the source instructions and adapt inputs before running the workflow.
Try it, then tell us how it went
A copied prompt does not tell us whether the task worked. Report your result after checking the output. We collect your tool and outcome, never your files or prompt.