PostgreSQL Best Practices
Relevant source files
The following files were used as context for generating this wiki page:
PostgreSQL Best Practices is a comprehensive performance optimization guide maintained to assist developers and AI agents in writing, reviewing, and optimizing database interactions. The guide covers eight distinct categories of database management, ranging from critical query performance to advanced features like JSONB optimization and full-text search.
Sources: bundled-skills/postgres-best-practices/SKILL.md:8-13, bundled-skills/postgres-best-practices/rules/_sections.md:3-5
Core Rule Categories and Priorities
The project organizes best practices into a prioritized hierarchy to guide automated query optimization and schema design. Each category is assigned an impact level reflecting its potential for performance improvement.
| Priority | Category | Impact | Prefix | Description |
|---|---|---|---|---|
| 1 | Query Performance | CRITICAL | query- | Addresses slow queries and missing indexes. |
| 2 | Connection Management | CRITICAL | conn- | Focuses on pooling and serverless strategies. |
| 3 | Security & RLS | CRITICAL | security- | Covers Row-Level Security and privileges. |
| 4 | Schema Design | HIGH | schema- | Table design, indexing, and partitioning. |
| 5 | Concurrency & Locking | MEDIUM-HIGH | lock- | Transaction management and deadlock prevention. |
| 6 | Data Access Patterns | MEDIUM | data- | N+1 elimination and batch operations. |
| 7 | Monitoring | LOW-MEDIUM | monitor- | Performance diagnostics and EXPLAIN usage. |
| 8 | Advanced Features | LOW | advanced- | JSONB, PostGIS, and extensions. |
Sources: bundled-skills/postgres-best-practices/SKILL.md:20-30, bundled-skills/postgres-best-practices/rules/_sections.md:10-38
Impact Quantification
The project utilizes specific metrics to categorize the severity and benefit of applying rules. This quantification helps prioritize database fixes.
| Level | Improvement | Use Case Examples |
|---|---|---|
| CRITICAL | 10-100x | Missing indexes, connection exhaustion. |
| HIGH | 5-20x | Poor partitioning, wrong index types. |
| MEDIUM-HIGH | 2-5x | RLS optimization, N+1 query fixes. |
| MEDIUM | 1.5-3x | Redundant indexes, stale statistics. |
Sources: bundled-skills/postgres-best-practices/rules/_contributing.md:68-75, bundled-skills/postgres-best-practices/README.md:82-89
Implementation Standards
Rule Structure and Formatting
Each rule file follows a strict "Error-First" structure to train AI agents in recognizing anti-patterns before presenting the solution.
The diagram above illustrates the logical flow of a standard Postgres best practice rule within the repository.
Sources: bundled-skills/postgres-best-practices/rules/_contributing.md:14-22, bundled-skills/postgres-best-practices/SKILL.md:38-46
Writing Concrete Transformation Patterns
Rules must prioritize concrete SQL rewrites over philosophical advice. For example, instead of advising "design good schemas," a rule should demonstrate specific syntax such as using ANY(ARRAY[...]) instead of IN (SELECT ...).
Correct Indexing Example:
-- Semantic naming and concurrent index creation
CREATE INDEX CONCURRENTLY users_active_email_idx
ON users(email)
WHERE deleted_at IS NULL;
Sources: bundled-skills/postgres-best-practices/rules/_contributing.md:9-12, bundled-skills/postgres-best-practices/rules/_contributing.md:46-51
Development and Build Workflow
The project includes a build system located in packages/postgres-best-practices-build/ to validate and compile rules into agent-facing documentation.
The sequence diagram shows the automated workflow for contributing new Postgres best practices to the codebase.
Sources: bundled-skills/postgres-best-practices/README.md:7-14, bundled-skills/postgres-best-practices/README.md:35-42
Data Access and Application Integration
While the default focus is pure SQL, application-level code (TypeScript/Python) is included for patterns that involve connection pooling, transaction management, or ORM anti-patterns like N+1 queries.
N+1 Query Elimination
The project emphasizes batching over sequential queries to improve performance by 2-5x.
Incorrect (Sequential):
for (const user of users) {
const posts = await db.query("SELECT * FROM posts WHERE user_id = $1", [user.id]);
}
Correct (Batched):
const posts = await db.query("SELECT * FROM posts WHERE user_id = ANY($1)", [userIds]);
Sources: bundled-skills/postgres-best-practices/rules/_contributing.md:104-123, bundled-skills/postgres-best-practices/rules/_sections.md:30-31
Conclusion
PostgreSQL Best Practices serves as a load-bearing abstraction for database performance within the project. By enforcing strict SQL formatting, semantic naming, and quantified impact levels, the system ensures that both human developers and AI agents can optimize data flow and state effectively, maintaining high performance across critical query and connection paths.
Sources: bundled-skills/wiki-onboarding/SKILL.md:27-31, bundled-skills/postgres-best-practices/SKILL.md:5-7