# Postgres Best Practices

> PostgreSQL best practices for database design, query optimization, and performance tuning Use when this capability is needed.

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

---


# PostgreSQL Best Practices

## Query Optimization

### EXPLAIN ANALYZE (CRITICAL)
- Use `EXPLAIN ANALYZE` to understand query plans
- Identify slow operations: Seq Scan, Nested Loop
- Check row estimates vs actual rows
- Monitor buffers: shared hit vs read

### Indexing (CRITICAL)
- B-tree: default, most use cases
- GIN: JSONB, arrays, full-text search
- GiST: geometry, range types
- BRIN: large sequential tables (time-series)
- Partial indexes: filtered queries
- Covering indexes (INCLUDE): avoid heap fetches

### Index Maintenance
- Create indexes concurrently: `CREATE INDEX CONCURRENTLY`
- Monitor usage: `pg_stat_user_indexes`
- Remove unused indexes
- Reindex bloated indexes

## Table Design

### Partitioning (HIGH)
- Range partitioning: time-series data
- List partitioning: categorical data
- Hash partitioning: even distribution
- Declarative partitioning (PG 10+)

### Data Types
- Use appropriate types (int vs bigint, varchar vs text)
- JSONB for semi-structured data
- Arrays for multi-value columns
- UUIDs for distributed IDs

## Performance Tuning

### Vacuum and Autovacuum
- Autovacuum: default enabled
- Monitor bloat: `pg_stat_user_tables`
- Tune autovacuum thresholds
- Manual VACUUM for large updates

### Connection Pooling
- Use pgBouncer or PgPool
- Transaction pooling for short transactions
- Session pooling for long transactions
- Max connections: tune based on workload

### Configuration
- `shared_buffers`: 25% of RAM
- `work_mem`: per operation, tune carefully
- `effective_cache_size`: 50-75% of RAM
- `random_page_cost`: 1.1 for SSD

## References
- [PostgreSQL Performance Optimization](https://wiki.postgresql.org/wiki/Performance_Optimization)

---
> Converted and distributed by [TomeVault](https://tomevault.io/claim/baekenough) — claim your Tome and manage your conversions.
<!-- tomevault:4.0:skill_md:2026-04-12 -->