Skip to main content
Agent skill

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 started

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?

Install scope
Claude Code folder setup
mkdir -p ~/.claude/skills/postgres-patterns
# Save SKILL.md to ~/.claude/skills/postgres-patterns/SKILL.md
  1. 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.
  2. 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.
  3. 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.

Starter prompt
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.