Database Optimizer

Query Optimization

Index Strategies

// Add index for frequent queries
export const users = pgTable('users', {
  id: uuid('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  tier: integer('tier').notNull().default(1),
  createdAt: timestamp('created_at').defaultNow(),
}, (table) => ({
  emailIdx: index('email_idx').on(table.email),
  tierIdx: index('tier_idx').on(table.tier),
  createdAtIdx: index('created_at_idx').on(table.createdAt),
}));

Composite Indexes

export const agentExecutions = pgTable('agent_executions', {
  userId: uuid('user_id').references(() => users.id),
  agentId: varchar('agent_id', { length: 100 }),
  status: varchar('status', { length: 50 }),
  createdAt: timestamp('created_at'),
}, (table) => ({
  // Composite index for common query pattern
  userAgentIdx: index('user_agent_idx').on(table.userId, table.agentId),
  // Partial index for active executions
  activeIdx: index('active_idx').on(table.createdAt).where(eq(table.status, 'running')),
}));

Query Patterns

Efficient Joins

// Use select instead of include for specific fields
const usersWithPosts = await db
  .select({
    user: { id: users.id, name: users.name },
    postCount: count(posts.id),
    lastPost: max(posts.createdAt)
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId))
  .groupBy(users.id);

Pagination with Cursor

async function getPaginatedResults(cursor?: string) {
  return db
    .select()
    .from(posts)
    .where(cursor ? lt(posts.createdAt, cursor) : undefined)
    .orderBy(desc(posts.createdAt))
    .limit(20);
}

Migration Management

Safe Migrations

// Always make migrations reversible
export async function up(db: DB) {
  await db.schema
    .alterTable('users')
    .addColumn('tier', 'integer', (col) => col.defaultTo(1))
    .execute();
    
  // Backfill data
  await db.updateTable('users')
    .set({ tier: 1 })
    .where('tier', 'is', null)
    .execute();
}

export async function down(db: DB) {
  await db.schema
    .alterTable('users')
    .dropColumn('tier')
    .execute();
}

Protocol

1. Assess — Understand the specific requirement and context
2. Plan — Determine the approach based on the guidance above
3. Execute — Apply the database optimizer methodology
4. Verify — Validate the output against expected standards
5. Report — Document results and any issues encountered