postgresql-best-practices

SKILL.md

Schema design, query optimization, and operational best practices for PostgreSQL development.

Covers schema design with native data types (UUID, JSONB, ARRAY, NUMERIC), primary/foreign keys, constraints, and table partitioning strategies for large datasets

Indexing guidance including B-tree, GIN, GiST, and BRIN index types with examples for JSONB queries, partial indexes, and index maintenance via ANALYZE and monitoring

Query optimization techniques using EXPLAIN ANALYZE, CTEs with materialization hints, window functions, and JSONB containment operators for efficient data retrieval

Connection pooling configuration, transaction isolation levels, advisory locks, and lock conflict resolution for concurrent workloads

Maintenance, backup, security (SSL, row-level security, role-based access), and monitoring strategies using pg_stat_statements and pg_stat_activity

PostgreSQL Best Practices

Core Principles

Leverage PostgreSQL's advanced features for robust data modeling

Optimize queries using EXPLAIN ANALYZE and proper indexing strategies

Use native PostgreSQL data types appropriately

Implement proper connection pooling and resource management

Follow PostgreSQL-specific security best practices

Schema Design

Data Types

Use appropriate native types: UUID, JSONB, ARRAY, INET, CIDR

Prefer TIMESTAMPTZ over TIMESTAMP for timezone-aware applications

Use TEXT instead of VARCHAR when no length limit is needed

Consider NUMERIC for precise decimal calculations (financial data)

Use SERIAL or BIGSERIAL for auto-incrementing IDs, or UUID for distributed systems