Architectures Medium

PostgreSQL Schema Patterns

PostgreSQL schema design patterns including multi-schema organization, migration workflows, indexing strategies, and schema-as-code with Drizzle/Prisma.

Open in Lab

postgresql drizzle sql

Targets: HTML

Code

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>PostgreSQL Schema Patterns</title>
<style>
  *, *::before, *::after { box-sizing: border-box; margin: 0; padding: 0; }
  body {
    background: #0a0a0f;
    color: #e2e8f0;
    font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif;
    min-height: 100vh;
    display: flex;
    justify-content: center;
    padding: 2rem 1rem;
  }
  .card {
    max-width: 780px;
    width: 100%;
    background: linear-gradient(135deg, rgba(34,211,238,0.04), rgba(59,130,246,0.04));
    border: 1px solid rgba(255,255,255,0.08);
    border-radius: 16px;
    padding: 2rem;
    overflow: hidden;
  }
  .badge {
    display: inline-block;
    background: rgba(34,211,238,0.12);
    color: #22d3ee;
    font-size: 0.7rem;
    font-weight: 600;
    text-transform: uppercase;
    letter-spacing: 0.08em;
    padding: 0.25rem 0.75rem;
    border-radius: 999px;
    margin-bottom: 0.75rem;
  }
  h1 {
    font-size: 1.5rem;
    font-weight: 700;
    margin-bottom: 0.25rem;
    background: linear-gradient(135deg, #22d3ee, #3b82f6);
    -webkit-background-clip: text;
    -webkit-text-fill-color: transparent;
  }
  .subtitle {
    color: #94a3b8;
    font-size: 0.85rem;
    margin-bottom: 1.5rem;
    line-height: 1.5;
  }
  .section-title {
    font-size: 0.75rem;
    font-weight: 600;
    text-transform: uppercase;
    letter-spacing: 0.1em;
    color: #22d3ee;
    margin-bottom: 0.75rem;
    padding-bottom: 0.4rem;
    border-bottom: 1px solid rgba(34,211,238,0.15);
  }
  .tree {
    font-family: 'SF Mono', 'Fira Code', 'Cascadia Code', monospace;
    font-size: 0.8rem;
    line-height: 1.7;
    background: rgba(0,0,0,0.35);
    border: 1px solid rgba(255,255,255,0.06);
    border-radius: 10px;
    padding: 1.25rem;
    margin-bottom: 1.5rem;
    overflow-x: auto;
  }
  .tree .file { color: #e2e8f0; }
  .tree .comment { color: #64748b; }
  .tree .dir { color: #22d3ee; }
  .tree .accent { color: #3b82f6; }
  .index-grid {
    display: grid;
    grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
    gap: 0.75rem;
    margin-bottom: 1.5rem;
  }
  .index-card {
    background: rgba(0,0,0,0.25);
    border: 1px solid rgba(255,255,255,0.06);
    border-radius: 10px;
    padding: 0.85rem;
  }
  .index-card h4 {
    font-size: 0.8rem;
    margin-bottom: 0.3rem;
  }
  .index-card h4.btree { color: #3b82f6; }
  .index-card h4.gin { color: #22d3ee; }
  .index-card h4.partial { color: #c084fc; }
  .index-card h4.composite { color: #fbbf24; }
  .index-card p {
    font-size: 0.72rem;
    color: #94a3b8;
    line-height: 1.4;
    margin-bottom: 0.35rem;
  }
  .index-card code {
    font-family: 'SF Mono', monospace;
    font-size: 0.68rem;
    color: #64748b;
    background: rgba(0,0,0,0.3);
    padding: 0.15rem 0.35rem;
    border-radius: 4px;
  }
  .migration-flow {
    display: flex;
    flex-wrap: wrap;
    align-items: center;
    gap: 0.4rem;
    margin-bottom: 1.5rem;
  }
  .m-step {
    display: inline-flex;
    align-items: center;
    font-family: 'SF Mono', monospace;
    font-size: 0.72rem;
    padding: 0.4rem 0.7rem;
    border-radius: 8px;
    white-space: nowrap;
  }
  .m-step.schema {
    background: rgba(59,130,246,0.15);
    border: 1px solid rgba(59,130,246,0.3);
    color: #93c5fd;
  }
  .m-step.generate {
    background: rgba(34,211,238,0.12);
    border: 1px solid rgba(34,211,238,0.25);
    color: #22d3ee;
  }
  .m-step.review {
    background: rgba(245,158,11,0.12);
    border: 1px solid rgba(245,158,11,0.25);
    color: #fbbf24;
  }
  .m-step.migrate {
    background: rgba(34,197,94,0.12);
    border: 1px solid rgba(34,197,94,0.25);
    color: #4ade80;
  }
  .m-step.commit {
    background: rgba(168,85,247,0.12);
    border: 1px solid rgba(168,85,247,0.25);
    color: #c084fc;
  }
  .m-arrow {
    color: #475569;
    font-size: 1rem;
    flex-shrink: 0;
  }
  .schema-section {
    background: rgba(59,130,246,0.06);
    border: 1px solid rgba(59,130,246,0.15);
    border-radius: 10px;
    padding: 1rem 1.25rem;
    margin-bottom: 1.5rem;
  }
  .schema-section h3 {
    font-size: 0.75rem;
    text-transform: uppercase;
    letter-spacing: 0.1em;
    color: #3b82f6;
    margin-bottom: 0.5rem;
  }
  .schema-domains {
    display: flex;
    gap: 0.5rem;
    flex-wrap: wrap;
  }
  .domain-tag {
    font-family: 'SF Mono', monospace;
    font-size: 0.72rem;
    padding: 0.3rem 0.65rem;
    border-radius: 6px;
    background: rgba(0,0,0,0.3);
    border: 1px solid rgba(255,255,255,0.08);
  }
  .domain-tag .name { color: #3b82f6; }
  .domain-tag .tables { color: #64748b; font-size: 0.65rem; display: block; margin-top: 0.15rem; }
  .source {
    display: flex;
    gap: 1rem;
    flex-wrap: wrap;
    margin-top: 1rem;
    padding-top: 1rem;
    border-top: 1px solid rgba(255,255,255,0.06);
  }
  .source a {
    color: #64748b;
    font-size: 0.75rem;
    text-decoration: none;
    transition: color 0.2s;
  }
  .source a:hover { color: #22d3ee; }
</style>
</head>
<body>
<div class="card">
  <div class="badge">Architecture</div>
  <h1>PostgreSQL Schema Patterns</h1>
  <p class="subtitle">Multi-schema organization, indexing strategies, migration workflows, and schema-as-code with Drizzle.</p>

  <div class="section-title">Project Structure</div>
  <div class="tree">
<span class="file">drizzle.config.ts</span>     <span class="comment">(Connection + migrations)</span>
<span class="dir">src/db/</span>
<span class="accent">  ├── index.ts</span>          <span class="comment">(Client export)</span>
<span class="accent">  ├── schema/</span>
<span class="file">  │   ├── auth.ts</span>       <span class="comment">(users, sessions, accounts)</span>
<span class="file">  │   ├── billing.ts</span>    <span class="comment">(plans, subscriptions, invoices)</span>
<span class="file">  │   ├── content.ts</span>    <span class="comment">(posts, comments, tags)</span>
<span class="file">  │   └── index.ts</span>      <span class="comment">(Re-export all schemas)</span>
<span class="accent">  ├── migrations/</span>       <span class="comment">(Auto-generated SQL)</span>
<span class="file">  │   ├── 0000_init.sql</span>
<span class="file">  │   └── 0001_add_billing.sql</span>
<span class="accent">  └── seed.ts</span>           <span class="comment">(Development data)</span>
  </div>

  <div class="section-title">Multi-Schema Pattern</div>
  <div class="schema-section">
    <h3>Domain-Driven Schema Files</h3>
    <div class="schema-domains">
      <div class="domain-tag">
        <span class="name">auth.ts</span>
        <span class="tables">users, sessions, accounts</span>
      </div>
      <div class="domain-tag">
        <span class="name">billing.ts</span>
        <span class="tables">plans, subscriptions, invoices</span>
      </div>
      <div class="domain-tag">
        <span class="name">content.ts</span>
        <span class="tables">posts, comments, tags</span>
      </div>
    </div>
  </div>

  <div class="section-title">Indexing Strategies</div>
  <div class="index-grid">
    <div class="index-card">
      <h4 class="btree">B-tree</h4>
      <p>Default. Equality and range queries.</p>
      <code>CREATE INDEX ON users(email)</code>
    </div>
    <div class="index-card">
      <h4 class="gin">GIN</h4>
      <p>Full-text search, JSONB, arrays.</p>
      <code>CREATE INDEX ON posts USING gin(tags)</code>
    </div>
    <div class="index-card">
      <h4 class="partial">Partial</h4>
      <p>Conditional subset of rows.</p>
      <code>... WHERE deleted_at IS NULL</code>
    </div>
    <div class="index-card">
      <h4 class="composite">Composite</h4>
      <p>Multi-column for common queries.</p>
      <code>... ON orders(user_id, created_at)</code>
    </div>
  </div>

  <div class="section-title">Migration Workflow</div>
  <div class="migration-flow">
    <span class="m-step schema">Edit schema.ts</span>
    <span class="m-arrow">&rarr;</span>
    <span class="m-step generate">drizzle-kit generate</span>
    <span class="m-arrow">&rarr;</span>
    <span class="m-step review">Review SQL</span>
    <span class="m-arrow">&rarr;</span>
    <span class="m-step migrate">drizzle-kit migrate</span>
    <span class="m-arrow">&rarr;</span>
    <span class="m-step commit">Commit</span>
  </div>

  <div class="source">
    <a href="https://orm.drizzle.team/docs/sql-schema-declaration" target="_blank" rel="noopener">Source: Drizzle ORM Docs</a>
    <a href="https://www.postgresql.org/docs/current/indexes-types.html" target="_blank" rel="noopener">PostgreSQL Index Types</a>
    <a href="https://orm.drizzle.team/docs/kit-overview" target="_blank" rel="noopener">Drizzle Kit</a>
  </div>
</div>
</body>
</html>

PostgreSQL Schema Patterns

Well-organized PostgreSQL schemas are the foundation of a maintainable application. This resource covers multi-schema organization, naming conventions, indexing strategies, and schema-as-code using Drizzle ORM.

Multi-Schema Organization

Group related tables into separate schema files by domain. This keeps individual files focused and makes it easy to find and modify table definitions:

auth.ts: users, sessions, accounts, verification tokens
billing.ts: plans, subscriptions, invoices, payment methods
content.ts: posts, comments, tags, categories

Each schema file exports its tables, and a central index.ts re-exports everything for the Drizzle client.

Naming Conventions

Use snake_case for table and column names
Prefix join tables: user_roles, post_tags
Suffix timestamps: created_at, updated_at, deleted_at
Use singular table names: user not users

Indexing Strategies

Choose the right index type for your query patterns:

B-tree (default): Equality and range queries (WHERE, ORDER BY)
GIN: Full-text search, JSONB containment, array operations
Partial indexes: Index only rows matching a condition (WHERE deleted_at IS NULL)
Composite indexes: Multi-column indexes for common query patterns

Migration Workflow

Modify schema files (TypeScript)
Run drizzle-kit generate to produce SQL migration
Review the generated SQL
Run drizzle-kit migrate to apply
Commit both schema changes and migration files

Code

PostgreSQL Schema Patterns

Multi-Schema Organization

Naming Conventions

Indexing Strategies

Migration Workflow

Learn More