Setting the file. One moment.
Skill 01 · Supabase Postgres Best Practices
Subchapter 1.1
references/_contributing.mdMarkdown4 KBView on GitHub
This document provides guidelines for creating effective Postgres best practice references that work well with AI agents and LLMs.
Show exact SQL rewrites. Avoid philosophical advice.
Good: “Use WHERE id = ANY(ARRAY[...]) instead of
WHERE id IN (SELECT ...)“ Bad: “Design good schemas”
Always show the problematic pattern first, then the solution. This trains agents to recognize anti-patterns.
**Incorrect (sequential queries):** [bad example]
**Correct (batched query):** [good example]Include specific metrics. Helps agents prioritize fixes.
Good: “10x faster queries”, “50% smaller index”, “Eliminates N+1” Bad: “Faster”, “Better”, “More efficient”
Examples should be complete and runnable (or close to it). Include CREATE TABLE
if context is needed.
-- Include table definition when needed for clarity
CREATE TABLE users (
id bigint PRIMARY KEY,
email text NOT NULL,
deleted_at timestamptz
);
-- Now show the index
CREATE INDEX users_active_email_idx ON users(email) WHERE deleted_at IS NULL;Use meaningful table/column names. Names carry intent for LLMs.
Good: users, email, created_at, is_active
Bad: table1, col1, field, flag
-- Use lowercase keywords, clear formatting
CREATE INDEX CONCURRENTLY users_email_idx
ON users(email)
WHERE deleted_at IS NULL;
-- Not cramped or ALL CAPS
CREATE INDEX CONCURRENTLY USERS_EMAIL_IDX ON USERS(EMAIL) WHERE DELETED_AT IS NULL;sql - Standard SQL queriesplpgsql - Stored procedures/functionstypescript - Application code (when needed)python - Application code (when needed)Default: SQL Only
Most references should focus on pure SQL patterns. This keeps examples portable.
Include Application Code When:
Format for Mixed Examples:
**Incorrect (N+1 in application):**
```typescript
for (const user of users) {
const posts = await db.query("SELECT * FROM posts WHERE user_id = $1", [
user.id,
]);
}
```Correct (batch query):
const posts = await db.query("SELECT * FROM posts WHERE user_id = ANY($1)", [
userIds,
]);| Level | Improvement | Use When |
|---|---|---|
| CRITICAL | 10-100x | Missing indexes, connection exhaustion, sequential scans on large tables |
| HIGH | 5-20x | Wrong index types, poor partitioning, missing covering indexes |
| MEDIUM-HIGH | 2-5x | N+1 queries, inefficient pagination, RLS optimization |
| MEDIUM | 1.5-3x | Redundant indexes, query plan instability |
| LOW-MEDIUM | 1.2-2x | VACUUM tuning, configuration tweaks |
| LOW | Incremental | Advanced patterns, edge cases |
Primary Sources:
Format:
Reference:
[Postgres Indexes](https://www.postgresql.org/docs/current/indexes.html)Before submitting a reference:
pnpm test passes