# Postgres

> PostgreSQL best practices, query optimization, connection troubleshooting, and performance improvement. Load when working with Postgres databases.

- Skill: `somtougeh/postgres` (Agent Skill, multi-file: 23 files)
- Install (CLI): `npx skillmds@latest add somtougeh/postgres`
- Raw SKILL.md: https://api.skillmd.com/api/skills/somtougeh/postgres/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- License: MIT
- Author: SomtoUgeh (https://skillmd.com/u/somtougeh)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/somtougeh/postgres

---


# PostgreSQL

Start with the provider, deployment model, PostgreSQL version, workload, and constraints already present in the repository or supplied by the user. Keep provider-specific advice within that provider's section. When the user asks for a new provider recommendation, compare current operational needs, regions, extensions, compliance, migration cost, pricing, and existing team tooling before recommending one.

## Generic Postgres

| Topic                  | Reference                                                        | Use for                                                   |
| ---------------------- | ---------------------------------------------------------------- | --------------------------------------------------------- |
| Schema Design          | [references/schema-design.md](references/schema-design.md)           | Tables, primary keys, data types, foreign keys            |
| Indexing               | [references/indexing.md](references/indexing.md)                      | Index types, composite indexes, performance               |
| Index Optimization     | [references/index-optimization.md](references/index-optimization.md) | Unused/duplicate index queries, index audit               |
| Partitioning           | [references/partitioning.md](references/partitioning.md)             | Large tables, time-series, data retention                 |
| Query Patterns         | [references/query-patterns.md](references/query-patterns.md)         | SQL anti-patterns, JOINs, pagination, batch queries       |
| Optimization Checklist | [references/optimization-checklist.md](references/optimization-checklist.md) | Pre-optimization audit, cleanup, readiness checks  |
| MVCC and VACUUM        | [references/mvcc-vacuum.md](references/mvcc-vacuum.md)               | Dead tuples, long transactions, xid wraparound prevention |

## Operations and Architecture

| Topic                  | Reference                                                                    | Use for                                                         |
| ---------------------- | ---------------------------------------------------------------------------- | --------------------------------------------------------------- |
| Process Architecture   | [references/process-architecture.md](references/process-architecture.md)     | Multi-process model, connection pooling, auxiliary processes     |
| Memory Architecture    | [references/memory-management-ops.md](references/memory-management-ops.md)   | Shared/private memory layout, OS page cache, OOM prevention     |
| MVCC Transactions      | [references/mvcc-transactions.md](references/mvcc-transactions.md)           | Isolation levels, XID wraparound, serialization errors          |
| WAL and Checkpoints    | [references/wal-operations.md](references/wal-operations.md)                 | WAL internals, checkpoint tuning, durability, crash recovery    |
| Replication            | [references/replication.md](references/replication.md)                       | Streaming replication, slots, sync commit, failover             |
| Storage Layout         | [references/storage-layout.md](references/storage-layout.md)                | PGDATA structure, TOAST, fillfactor, tablespaces, disk mgmt     |
| Monitoring             | [references/monitoring.md](references/monitoring.md)                         | pg_stat views, logging, pg_stat_statements, host metrics        |
| Backup and Recovery    | [references/backup-recovery.md](references/backup-recovery.md)              | pg_dump, pg_basebackup, PITR, WAL archiving, backup tools      |

## PlanetScale-Specific

Use these references only when the project already uses PlanetScale or the user is evaluating it.

| Topic              | Reference                                                                    | Use for                                               |
| ------------------ | ---------------------------------------------------------------------------- | ----------------------------------------------------- |
| Connection Pooling | [references/ps-connection-pooling.md](references/ps-connection-pooling.md)   | PgBouncer, pool sizing, pooled vs direct              |
| PgBouncer Config   | [references/pgbouncer-configuration.md](references/pgbouncer-configuration.md) | default_pool_size, max_user_connections, pool limits  |
| Extensions         | [references/ps-extensions.md](references/ps-extensions.md)                   | Supported extensions, compatibility                   |
| Connections        | [references/ps-connections.md](references/ps-connections.md)                 | Connection troubleshooting, drivers, SSL              |
| Insights           | [references/ps-insights.md](references/ps-insights.md)                       | Slow queries, MCP server, pscale CLI                  |
| CLI Commands       | [references/ps-cli-commands.md](references/ps-cli-commands.md)               | pscale CLI reference, branches, deploy requests, auth |
| CLI API Insights   | [references/ps-cli-api-insights.md](references/ps-cli-api-insights.md)       | Query insights via `pscale api`, schema analysis      |