Db Sculptor

DB Sculptor

Design performant database schemas. Model for access patterns first, normalize later. Based on PostgreSQL internals, Prisma/Drizzle best practices, and production patterns from PlanetScale, Neon, and pganalyze.

Sub-Commands

| Command | Description | |---------|-------------| | design | Design a schema from access patterns and data volume estimates | | index | Analyze queries and recommend/create optimal indexes | | optimize | Diagnose slow queries with EXPLAIN ANALYZE and fix them | | migrate | Create a safe, zero-downtime migration (expand/contract) | | audit | Audit existing schema against best practices and anti-patterns |

Workflow

Step 1: Model for access patterns

| Question | Determine | |----------|-----------| | Read/write ratio? | How many indexes can the table support | | Data volume? | Current rows, growth rate/month | | Consistency requirements? | ACID vs eventual, read replicas OK? | | Latency budget? | p50 100000 ORDER BY id LIMIT 20;

-- Pattern 6: Unused indexes bloating writes -- Identify unused indexes: SELECT schemaname, tablename, indexname, idxscan FROM pgstatuserindexes WHERE idxscan = 0 ORDER BY pgrelation_size(indexrelid) DESC;

-- Pattern 7: Parallel query for large scans -- Force parallel workers on large analytical queries: SET maxparallelworkerspergather = 4; EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

### Step 5: Safe migrations (expand/contract)

**Prisma example:**
```prisma
// prisma/schema.prisma
model User {
  id        String   @id @default(uuid()) @db.Uuid
  email     String   @unique
  timezone  String?  // Phase 1: nullable
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  @@index([email])
  @@map("users")
}

Drizzle ORM example:

// db/schema/users.ts
import { pgTable, text, timestamp, uuid, index } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: uuid("id").defaultRandom().primaryKey(),
  email: text("email").notNull().unique(),
  timezone: text("timezone"), // Phase 1: nullable
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
}, (table) => ({
  emailIdx: index("idx_users_email").on(table.email),
}));

// Migration command: npx drizzle-kit generate && npx drizzle-kit migrate

Expand/contract SQL:

-- Phase 1 (expand): Add column as nullable
ALTER TABLE users ADD COLUMN timezone text;

-- Phase 2 (backfill): Fill data in separate deployment
UPDATE users SET timezone = 'UTC' WHERE timezone IS NULL;

-- Phase 3 (contract): Make NOT NULL, clean up
ALTER TABLE users ALTER COLUMN timezone SET NOT NULL;

-- Phase 4 (future): Drop old column
ALTER TABLE users DROP COLUMN old_timezone;

Safety rules:

• CREATE INDEX CONCURRENTLY — never blocks writes
• lock_timeout = '5s' on migration connections
• One logical change per migration file
• Backfill in separate migration from schema change
• Rollback written BEFORE applying forward
• Never rename + change type in same migration

PgBouncer connection pooling

# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 25
max_client_conn = 200
max_db_connections = 25

Rules:

• Use pool_mode = transaction (default) for most workloads — session-level pooling breaks SET and LISTEN/NOTIFY
• Use pool_mode = session only when you need prepared statements, SET session variables, or listen/notify
• Set max_db_connections to 25-50% of PostgreSQL max_connections — PgBouncer multiplexes client connections
• Set default_pool_size based on CPU cores: 4× cores for mixed workloads, 1× for CPU-bound
• Never use pool_mode = statement — breaks multi-statement transactions

Error Handling

| Scenario | Cause | Fix | |----------|-------|-----| | Migration lock timeout | Long-running query on same table | Set lock_timeout = '5s'. Run in low-traffic window. | | Query slow in prod, fast in dev | Different data volume and distribution | Test with prod-size data (~1M+ rows) in staging | | CREATE INDEX blocks writes | Non-concurrent CREATE | Always use CREATE INDEX CONCURRENTLY | | N+1 queries | ORM lazy loading | Use eager loading (include/JOIN), DataLoader, or batch queries | | Sequence gap on PK | ROLLBACK increments sequence | Accept gaps. Switch to UUIDv7 for new tables. | | Deadlock | Conflicting lock order across transactions | Ensure consistent lock ordering. Keep transactions short. |

Production Checklist

• [ ] Primary key: UUIDv7 or bigint. Never expose sequential PKs in URLs.
• [ ] created_at + updated_at on every table. updated_at auto-set via trigger.
• [ ] Indexes match WHERE + ORDER BY + JOIN columns. Verified with EXPLAIN ANALYZE.
• [ ] Composite indexes ordered by selectivity (most selective first)
• [ ] Partial indexes for common filtered queries
• [ ] Covering indexes (INCLUDE) for read-heavy lookups
• [ ] lock_timeout = '5s' on migration connections
• [ ] Migrations tested on staging with production-like data volume
• [ ] Connection pooling configured (PgBouncer for PostgreSQL)
• [ ] TEXT over VARCHAR(n) — no arbitrary length limits
• [ ] TIMESTAMPTZ over TIMESTAMP — always store with timezone
• [ ] FKs indexed — mandatory for join performance
• [ ] Soft deletes via deleted_at TIMESTAMPTZ (nullable)

Anti-Patterns

| Anti-pattern | Fix | |-------------|-----| | No primary key | Every table needs one (UUIDv7 preferred) | | Index on every column | Max 3-4 per write-heavy table. Check usage with pg_stat_user_indexes. | | SELECT * in application code | Name explicit columns. Avoids breaking changes + unnecessary data transfer. | | Functions on indexed columns (WHERE LOWER(email) = ...) | Use expression index: CREATE INDEX ON users (LOWER(email)) | | VARCHAR(255) on all strings | TEXT unless max length is a business rule | | Migrations without testing | Test against prod copy (anonymized). Never just run on dev. | | ENUM type for evolving values | TEXT with CHECK constraint or reference table | | Missing FK indexes | Every FK column needs an index for join performance | | Sorting by unindexed column on large tables | Add composite index with sort column last | | COUNT(*) on large tables without WHERE | Use estimates: SELECT reltuples FROM pg_class WHERE relname = 'table' |

Sources

• PostgreSQL docs (postgresql.org/docs)
• Use the Index, Luke! (use-the-index-luke.com)
• pganalyze EXPLAIN analyzer
• Prisma migration docs
• Drizzle ORM docs
• PlanetScale schema migration patterns
• Stormatics — composite and partial indexes
• Neon serverless PostgreSQL patterns

Checklist

• [ ] Skill loads without errors in the AI agent
• [ ] YAML frontmatter is valid (description, compatibility, audience)
• [ ] Workflow section provides clear step-by-step instructions
• [ ] Error handling section covers common failure modes
• [ ] All referenced files (references/, scripts/, assets/) exist
• [ ] Skill triggers correctly for intended use cases
• [ ] No broken links or missing resources

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: EliasOulkadi
• Source: EliasOulkadi/shokunin
• License: MIT
• Homepage: https://eliasoulkadi.github.io/shokunin/

Install and usage instructions live in the source repository linked above.