Postgres Patterns

PostgreSQL Patterns

Quick reference for PostgreSQL best practices.

When to Activate

• Writing SQL queries or migrations
• Designing database schemas
• Troubleshooting slow queries
• Implementing Row Level Security
• Setting up connection pooling
• Defining Drizzle ORM schemas and relations (TypeScript)

Index Cheat Sheet

Data Type Quick Reference

Common Patterns

Composite Index Order

-- Equality columns first, then range columns
CREATE INDEX idx ON orders (status, created_at);
-- Works for: WHERE status = 'pending' AND created_at > '2024-01-01'

Covering Index (avoids table lookup)

CREATE INDEX idx ON users (email) INCLUDE (name, created_at);
-- SELECT email, name, created_at — no heap fetch needed

Partial Index (smaller, faster)

CREATE INDEX idx ON users (email) WHERE deleted_at IS NULL;

RLS Policy (optimized)

CREATE POLICY policy ON orders
  USING ((SELECT auth.uid()) = user_id);  -- Wrap in SELECT!

UPSERT

INSERT INTO settings (user_id, key, value)
VALUES (123, 'theme', 'dark')
ON CONFLICT (user_id, key)
DO UPDATE SET value = EXCLUDED.value;

Cursor Pagination (O(1) vs OFFSET O(n))

SELECT * FROM products WHERE id > $last_id ORDER BY id LIMIT 20;

Queue Processing (skip locked)

UPDATE jobs SET status = 'processing'
WHERE id = (
  SELECT id FROM jobs WHERE status = 'pending'
  ORDER BY created_at LIMIT 1
  FOR UPDATE SKIP LOCKED
) RETURNING *;

Diagnostics

-- Find unindexed foreign keys
SELECT conrelid::regclass, a.attname
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey)
  );

-- Find slow queries (requires pg_stat_statements)
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC;

-- Check table bloat
SELECT relname, n_dead_tup, last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Configuration Template

-- Connection limits (tune for RAM)
ALTER SYSTEM SET max_connections = 100;
ALTER SYSTEM SET work_mem = '8MB';

-- Timeouts
ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';
ALTER SYSTEM SET statement_timeout = '30s';

-- Monitoring
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Security defaults
REVOKE ALL ON SCHEMA public FROM public;

SELECT pg_reload_conf();

Anti-Patterns

Based on Supabase Agent Skills — MIT License

Drizzle ORM (TypeScript)

Применяй когда схема и запросы пишутся на TypeScript через Drizzle ORM.

Config

// drizzle.config.ts
export default defineConfig({
  dialect: 'postgresql',
  schema: './src/database/schemas/*',
  out: './src/database/migrations',
  strict: true,
});

Naming Conventions

Column Patterns

Primary Key — prefixed text ID (читаемый, distinguishable по типу):

id: text('id')
  .primaryKey()
  .$defaultFn(() => idGenerator('agents'))
  .notNull(),
// Для внутренних таблиц: uuid вместо text

Foreign Key с cascade:

userId: text('user_id')
  .references(() => users.id, { onDelete: 'cascade' })
  .notNull(),

Timestamps — через хелперы, не вручную:

// _helpers.ts
export const createdAt = () => timestamptz('created_at').defaultNow().notNull();
export const updatedAt = () => timestamptz('updated_at').$onUpdate(() => new Date());
export const timestamps = { createdAt: createdAt(), updatedAt: updatedAt() };

// В схеме:
...timestamps,

Indexes — возвращай массив (не объект):

(t) => [uniqueIndex('client_id_user_id_unique').on(t.clientId, t.userId)],

Full Table Example

export const agents = pgTable(
  'agents',
  {
    id: text('id')
      .primaryKey()
      .$defaultFn(() => idGenerator('agents'))
      .notNull(),
    slug: varchar('slug', { length: 100 })
      .$defaultFn(() => randomSlug(4))
      .unique(),
    userId: text('user_id')
      .references(() => users.id, { onDelete: 'cascade' })
      .notNull(),
    clientId: text('client_id'),
    config: jsonb('config').$type<AgentConfig>(),
    ...timestamps,
  },
  (t) => [uniqueIndex('client_id_user_id_unique').on(t.clientId, t.userId)],
);

Type Inference

export const insertAgentSchema = createInsertSchema(agents);
export type NewAgent = typeof agents.$inferInsert;
export type AgentItem  = typeof agents.$inferSelect;

Junction Table (Many-to-Many)

export const agentsKnowledgeBases = pgTable(
  'agents_knowledge_bases',
  {
    agentId: text('agent_id')
      .references(() => agents.id, { onDelete: 'cascade' }).notNull(),
    knowledgeBaseId: text('knowledge_base_id')
      .references(() => knowledgeBases.id, { onDelete: 'cascade' }).notNull(),
    userId: text('user_id')
      .references(() => users.id, { onDelete: 'cascade' }).notNull(),
    enabled: boolean('enabled').default(true),
    ...timestamps,
  },
  (t) => [primaryKey({ columns: [t.agentId, t.knowledgeBaseId] })],
);

Query Style — критическое правило

Всегда db.select(). Никогда db.query.*.

db.query.findMany/findFirst/with: генерирует сложные lateral joins с json_build_array — хрупкие и трудно отлаживаемые.

Select single row:

// ✅
const [result] = await db.select().from(agents).where(eq(agents.id, id)).limit(1);

// ❌
return db.query.agents.findFirst({ where: eq(agents.id, id) });

Select with JOIN:

// ✅
const rows = await db
  .select({
    runId: evalRunTopics.runId,
    score: evalRunTopics.score,
    testCase: evalTestCases,
  })
  .from(evalRunTopics)
  .leftJoin(evalTestCases, eq(evalRunTopics.testCaseId, evalTestCases.id))
  .where(eq(evalRunTopics.runId, runId))
  .orderBy(asc(evalRunTopics.createdAt));

// ❌
return db.query.evalRunTopics.findMany({
  where: eq(evalRunTopics.runId, runId),
  with: { testCase: true },
});

Aggregation:

// ✅
const rows = await db
  .select({
    id: datasets.id,
    name: datasets.name,
    count: count(testCases.id).as('count'),
  })
  .from(datasets)
  .leftJoin(testCases, eq(datasets.id, testCases.datasetId))
  .groupBy(datasets.id);

One-to-Many — два отдельных запроса:

// ✅ Два простых запроса вместо relational with:
const [parent] = await db.select().from(datasets).where(eq(datasets.id, id)).limit(1);
if (!parent) return undefined;

const children = await db
  .select()
  .from(testCases)
  .where(eq(testCases.datasetId, id))
  .orderBy(asc(testCases.sortOrder));

return { ...parent, testCases: children };

UPSERT:

await db
  .insert(settings)
  .values({ userId, key, value })
  .onConflictDoUpdate({
    target: [settings.userId, settings.key],
    set: { value: sql`excluded.value` },
  });

Cursor pagination в Drizzle:

const rows = await db
  .select()
  .from(products)
  .where(gt(products.id, lastId))
  .orderBy(asc(products.id))
  .limit(20);

Транзакции:

await db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values(userData).returning();
  await tx.insert(profiles).values({ userId: user.id, ...profileData });
});

Drizzle Anti-Patterns