# Optimize PostgreSQL Performance

Slow database queries harm application reliability and scale. This skill gives practical PostgreSQL patterns for faster queries, safer migrations, and clearer monitoring.

## Install

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

## Metadata

- Status: approved
- Slug: cjharmath-postgres-performance
- Skillstore revision: r1
- Version status: missing
- Tree hash: 613061f2e96bec0ce831027669ca79071742b17657825e0ad7707e9501785e61
- Author: CJHarmath
- GitHub username: CJHarmath
- License: MIT
- Repository: https://github.com/CJHarmath/claude-agents-skills/tree/main/skills/postgres-performance
- 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, env\_access
- Quality score: 78
- Quality tier: bronze
- Public page: https://skillstore.pages.dev/skills/cjharmath-postgres-performance
- Manifest: https://skillstore.pages.dev/api/skills/cjharmath-postgres-performance/manifest

## Capabilities

- Guides slow query analysis with pg\_stat\_statements and EXPLAIN.
- Recommends indexes for filters, ordering, and index-only scans.
- Shows cursor pagination, batch updates, and efficient aggregation patterns.
- Explains connection pool settings for serverless and long-running services.
- Covers read replicas, denormalization, partitioning, and caching tradeoffs.
- Provides monitoring queries for bloat, unused indexes, cache hit ratio, and long-running queries.

## Use Cases

- Diagnose Slow Queries: Backend engineers can structure investigation from slow-query discovery through plan analysis and index verification.
- Plan Scalable Data Access: Application architects can choose pagination, batching, replicas, partitioning, and caching patterns before load increases.
- Review Database Health: Database administrators can use the monitoring checklist to spot bloat, unused indexes, and long-running queries.

## Prompt Templates

### Review a Slow Query

```
I have a slow PostgreSQL query: [paste query]. Help me analyze likely bottlenecks and suggest safe next steps.
```

### Design an Index Strategy

```
Given this table shape and workload: [details], propose PostgreSQL indexes for filters, sorting, and frequent reads.
```

### Plan High-Volume Pagination

```
Design cursor pagination for this feed: [requirements]. Include cursor fields, ordering rules, and index guidance.
```

### Audit a Production Workload

```
Use these EXPLAIN plans, pg_stat_statements rows, and schema notes: [data]. Prioritize fixes by risk, effort, and impact.
```

## Limitations

- It provides examples, not automated database profiling.
- It does not inspect live schemas unless the user supplies data.
- It cannot validate production changes without benchmarks and execution plans.
- It assumes PostgreSQL and does not cover other database engines deeply.

## Best Practices

- Measure with real execution plans before changing indexes.
- Create indexes concurrently when production writes must continue.
- Verify each optimization with before and after latency data.

## Anti Patterns

- Adding indexes without matching actual query filters and ordering.
- Using large OFFSET pagination on growing tables.
- Refreshing cached or denormalized data without an invalidation plan.

## Security Audit

- Audited at: 2026-07-06T06:31:18.25\+00:00
- Summary: The flagged external command patterns are markdown code fences and inline backticks in SQL/Python examples, not executable shell usage. The flagged database URLs and reconnaissance patterns are placeholder variables and instructional queries inside examples. No prompt injection or data exfiltration intent was found.

## Stats

- Views: 184
- Downloads: 10
- Favorites: 0
- Popularity score: 0