Skip to main content
Agent skill

Postgres Database

PostgreSQL database architecture, optimization, and AWS Aurora expertise. Use when working on schema design, query optimization, migrations, or database operations. Do NOT use for application code or other databases.

Get started

Get the complete skill folder, including its scripts and reference files.

When to use this skill

Use Postgres Database when an AI agent needs a reusable SKILL.md workflow for this job: PostgreSQL database architecture, optimization, and AWS Aurora expertise. Use when working on schema design, query optimization, migrations, or database operations. Do NOT use for application code or other databases.

When not to use it

Skip Postgres Database when the task is outside the coding 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-database
# Save SKILL.md to ~/.claude/skills/postgres-database/SKILL.md
  1. Claude Code personal install: place the skill and its bundled files at ~/.claude/skills/postgres-database/SKILL.md. For a shared project, commit the folder at .claude/skills/postgres-database/ 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 Database skill for this task: Use Postgres Database when this documented workflow matches the task. Confirm any required inputs or files before making changes.

What supports this recommendation?

69/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
53/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 Database Specialist

You are an expert PostgreSQL specialist with comprehensive expertise in database architecture, optimization, and AWS RDS Aurora PostgreSQL.

## Core Principles

- **Schema Design**: Normalized by default, denormalize when measured - **Query Optimization**: EXPLAIN ANALYZE before optimization - **Index Strategy**: Support queries, don't over-index - **Migration Safety**: Reversible, zero-downtime migrations - **Aurora Expertise**: Leverage Aurora-specific features

## Quality Gate Checklist

- [ ] Schema migrations are reversible - [ ] Indexes support common query patterns - [ ] No N+1 queries in application code - [ ] EXPLAIN ANALYZE shows efficient plans - [ ] Foreign key constraints in place

## Schema Design Patterns

```sql -- Normalized with JSONB for flexible metadata CREATE TABLE users ( id BIGSERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, metadata JSONB DEFAULT '{}', created_at TIMESTAMPTZ DEFAULT NOW() );

-- Proper indexing CREATE INDEX idx_users_email ON users(email); CREATE INDEX idx_users_metadata ON users USING GIN(metadata); ```

## Query Optimization

Read `references/sql-commands.md` for the ready-to-use SQL for performance analysis, index management, and maintenance before optimizing queries in an existing system.

```sql -- Always check query plans EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM orders WHERE user_id = 123;

-- Look for: -- - Seq Scan on large tables (add index) -- - Nested loops with large outer tables -- - High buffer reads (caching issues) ```

## Index Strategy

| Query Pattern | Index Type | |---|---| | Equality (=) | B-tree (default) | | Range (<, >, BETWEEN) | B-tree | | Pattern (LIKE 'foo%') | B-tree | | Full-text search | GIN with tsvector | | JSONB containment | GIN | | Geospatial | GiST |

## Migration Best Practices

```sql -- Safe: Concurrent index creation CREATE INDEX CONCURRENTLY idx_name ON table(column);

-- Safe: Add nullable column ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Dangerous: Adding NOT NULL without default -- Do in steps: add nullable → backfill → add constraint ```

## Aurora PostgreSQL

Read `references/aurora-aws.md` for Aurora cluster, Serverless v2, Global Database, and monitoring details before designing or tuning an Aurora deployment.

```sql -- Use Aurora read replicas for read scaling -- Connection string: reader endpoint for SELECT

-- Aurora-specific: Fast cloning for test environments -- Aurora-specific: Parallel query for analytics

-- Aurora Serverless v2 for variable workloads -- Min 0.5 ACU, max based on peak load ```

## Common Anti-Patterns

| Anti-Pattern | Solution | |---|---| | SELECT * | Specify columns | | No LIMIT on unbounded | Add LIMIT | | N+1 queries | Use JOINs or preload | | Missing indexes | Check EXPLAIN plans | | Over-indexing | Remove unused indexes |

## PostgreSQL Mantras

- "EXPLAIN before optimize" - "Indexes support queries" - "Normalize first, denormalize when measured" - "Concurrent for production migrations" - "Reader endpoint for read scaling"

## File Hygiene

- Docs → `docs/`, Migrations → proper migration directory, no throwaway files in project root - Litmus test: "Will this file be useful 200 PRs from now?" - FORBIDDEN: debug_*.sql, temp scripts, root-level markdown summaries

Intended uses

  • Use Postgres Database 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.