# Review PostgreSQL Migrations and Queries

PostgreSQL migrations can pass tests while causing locks, slow scans, or index bloat. This skill gives Claude, Codex, and Claude Code practical patterns for safer schema and query review.

## Install

```bash
npx skillstore add cjharmath/postgres-patterns
```

## Metadata

- Status: approved
- Slug: cjharmath-postgres-patterns
- Skillstore revision: r1
- Version status: missing
- Tree hash: 9d29a268f60bb551f51801923bdd5d6e139f6e2e7c26a5821b3cbee184a60636
- Author: CJHarmath
- GitHub username: CJHarmath
- License: MIT
- Repository: https://github.com/CJHarmath/claude-agents-skills/tree/main/skills/postgres-patterns
- Ref: dd4a3ef9f20ddf38830950b4bb713df96b431fd6
- Supported tools: Claude, Codex, Claude Code
- Audit status: complete
- Agent install advisory: allowed
- Manual install advisory: allowed
- Artifact signature: available
- Audit attestation: unavailable
- Human verification: not\_verified
- Risk factors: external\_commands
- Quality score: 78
- Quality tier: bronze
- Public page: https://skillstore.pages.dev/skills/cjharmath-postgres-patterns
- Manifest: https://skillstore.pages.dev/api/skills/cjharmath-postgres-patterns/manifest

## Capabilities

- Explains when to add or skip PostgreSQL indexes.
- Covers partial, JSONB, composite, and concurrent indexes.
- Guides Alembic migration review for locks and non-null columns.
- Shows query performance checks with execution plans and catalog views.
- Summarizes data type choices for UUIDs, timestamps, money, JSONB, and text.
- Provides diagnostic query patterns for table size, index usage, locks, and slow queries.

## Use Cases

- Review Alembic Migrations: Check schema changes for lock risk, missing indexes, constraint names, and safe non-null column patterns.
- Improve Query Performance: Match indexes to filter, join, and sort patterns before slow queries affect users.
- Prepare Database Reviews: Use concise checklists for index usage, table size, locks, JSONB access, and data type choices.

## Prompt Templates

### Check a Migration

```
Review this PostgreSQL migration for missing indexes, unsafe locks, and constraint naming. Explain each concern in plain language.
```

### Plan Indexes

```
Given these common queries and table sizes, recommend PostgreSQL indexes. Include which indexes to avoid and why.
```

### Analyze Slow Queries

```
Interpret this PostgreSQL execution plan. Identify scans, sorts, joins, row estimates, and index changes that may improve performance.
```

### Design a Safe Rollout

```
Create a safe PostgreSQL migration rollout plan for a large table. Cover concurrent indexes, batching, constraints, locks, and rollback checks.
```

## Limitations

- Does not connect to a database or inspect live production data.
- Does not validate actual row counts, query plans, or workload frequency.
- Uses example table names that users must adapt to their schema.
- Does not replace review by a DBA for high-risk production migrations.

## Best Practices

- Pair each index recommendation with the query pattern it supports.
- Check table size and write volume before approving schema changes.
- Review lock impact before running migrations on production tables.

## Anti Patterns

- Adding indexes without knowing the matching query pattern.
- Using blocking schema changes on large tables during active writes.
- Treating generic migration output as production-ready without PostgreSQL review.

## Security Audit

- Audited at: 2026-07-06T06:27:52.312\+00:00
- Summary: I found no evidence of prompt injection, malware, data exfiltration, or unauthorized host execution in SKILL.md. The static findings are false positives from Markdown code fences, inline code formatting, and PostgreSQL example queries. The skill is instructional database review content for migrations, indexes, locks, and query performance.

## Stats

- Views: 244
- Downloads: 8
- Favorites: 0
- Popularity score: 0