1# Writing Guidelines for Postgres References
2 
3This document provides guidelines for creating effective Postgres best
4practice references that work well with AI agents and LLMs.
5 
6## Key Principles
7 
8### 1. Concrete Transformation Patterns
9 
10Show exact SQL rewrites. Avoid philosophical advice.
11 
12**Good:** "Use `WHERE id = ANY(ARRAY[...])` instead of
13`WHERE id IN (SELECT ...)`" **Bad:** "Design good schemas"
14 
15### 2. Error-First Structure
16 
17Always show the problematic pattern first, then the solution. This trains agents
18to recognize anti-patterns.
19 
20```markdown
21**Incorrect (sequential queries):** [bad example]
22 
23**Correct (batched query):** [good example]
24```
25 
26### 3. Quantified Impact
27 
28Include specific metrics. Helps agents prioritize fixes.
29 
30**Good:** "10x faster queries", "50% smaller index", "Eliminates N+1" 
31**Bad:** "Faster", "Better", "More efficient"
32 
33### 4. Self-Contained Examples
34 
35Examples should be complete and runnable (or close to it). Include `CREATE TABLE`
36if context is needed.
37 
38```sql
39-- Include table definition when needed for clarity
40CREATE TABLE users (
41  id bigint PRIMARY KEY,
42  email text NOT NULL,
43  deleted_at timestamptz
44);
45 
46-- Now show the index
47CREATE INDEX users_active_email_idx ON users(email) WHERE deleted_at IS NULL;
48```
49 
50### 5. Semantic Naming
51 
52Use meaningful table/column names. Names carry intent for LLMs.
53 
54**Good:** `users`, `email`, `created_at`, `is_active`
55**Bad:** `table1`, `col1`, `field`, `flag`
56 
57---
58 
59## Code Example Standards
60 
61### SQL Formatting
62 
63```sql
64-- Use lowercase keywords, clear formatting
65CREATE INDEX CONCURRENTLY users_email_idx
66  ON users(email)
67  WHERE deleted_at IS NULL;
68 
69-- Not cramped or ALL CAPS
70CREATE INDEX CONCURRENTLY USERS_EMAIL_IDX ON USERS(EMAIL) WHERE DELETED_AT IS NULL;
71```
72 
73### Comments
74 
75- Explain _why_, not _what_
76- Highlight performance implications
77- Point out common pitfalls
78 
79### Language Tags
80 
81- `sql` - Standard SQL queries
82- `plpgsql` - Stored procedures/functions
83- `typescript` - Application code (when needed)
84- `python` - Application code (when needed)
85 
86---
87 
88## When to Include Application Code
89 
90**Default: SQL Only**
91 
92Most references should focus on pure SQL patterns. This keeps examples portable.
93 
94**Include Application Code When:**
95 
96- Connection pooling configuration
97- Transaction management in application context
98- ORM anti-patterns (N+1 in Prisma/TypeORM)
99- Prepared statement usage
100 
101**Format for Mixed Examples:**
102 
103````markdown
104**Incorrect (N+1 in application):**
105 
106```typescript
107for (const user of users) {
108  const posts = await db.query("SELECT * FROM posts WHERE user_id = $1", [
109    user.id,
110  ]);
111}
112```
113````
114 
115**Correct (batch query):**
116 
117```typescript
118const posts = await db.query("SELECT * FROM posts WHERE user_id = ANY($1)", [
119  userIds,
120]);
121```
122 
123---
124 
125## Impact Level Guidelines
126 
127| Level | Improvement | Use When |
128|-------|-------------|----------|
129| **CRITICAL** | 10-100x | Missing indexes, connection exhaustion, sequential scans on large tables |
130| **HIGH** | 5-20x | Wrong index types, poor partitioning, missing covering indexes |
131| **MEDIUM-HIGH** | 2-5x | N+1 queries, inefficient pagination, RLS optimization |
132| **MEDIUM** | 1.5-3x | Redundant indexes, query plan instability |
133| **LOW-MEDIUM** | 1.2-2x | VACUUM tuning, configuration tweaks |
134| **LOW** | Incremental | Advanced patterns, edge cases |
135 
136---
137 
138## Reference Standards
139 
140**Primary Sources:**
141 
142- Official Postgres documentation
143- Supabase documentation
144- Postgres wiki
145- Established blogs (2ndQuadrant, Crunchy Data)
146 
147**Format:**
148 
149```markdown
150Reference:
151[Postgres Indexes](https://www.postgresql.org/docs/current/indexes.html)
152```
153 
154---
155 
156## Review Checklist
157 
158Before submitting a reference:
159 
160- [ ] Title is clear and action-oriented
161- [ ] Impact level matches the performance gain
162- [ ] impactDescription includes quantification
163- [ ] Explanation is concise (1-2 sentences)
164- [ ] Has at least 1 **Incorrect** SQL example
165- [ ] Has at least 1 **Correct** SQL example
166- [ ] SQL uses semantic naming
167- [ ] Comments explain _why_, not _what_
168- [ ] Trade-offs mentioned if applicable
169- [ ] Reference links included
170- [ ] `pnpm test` passes
171