Designing Scalable Databases

A database schema is a product decision, not just a technical artifact. The shape of your tables determines what’s easy, what’s painful, and what becomes impossible later.

This guide is a practical checklist for designing PostgreSQL schemas that can evolve safely as your application grows.

1) Start with the real questions your product asks

Before you model tables, list the top queries your app must support:

• What pages load on every request?
• What needs to be searchable/filterable?
• What needs strict uniqueness or ordering?
• What relationships are optional vs required?

Designing a schema without queries is like designing an API without use-cases.

2) Model entities and boundaries

A scalable schema is usually the one that is clear:

• Each table represents one concept.
• Columns are “atomic” (avoid stuffing multiple concepts into one string).
• Relationships are explicit.

Example: a blog system

• BlogPost (core entity)
• Tag (optional normalization depending on usage)
• BlogPostTag (many-to-many if you need tag metadata)
• BlogComment

If tags are just strings and you don’t need tag pages or analytics, arrays (text[]) are fine. If tags become “first-class”, normalize.

3) Pick constraints first (they prevent production disasters)

Constraints are your last line of defense.

• UNIQUE for slugs/emails
• NOT NULL for required fields
• foreign keys with ON DELETE rules

In PostgreSQL:

sql
ALTER TABLE "BlogPost"
  ADD CONSTRAINT blogpost_slug_unique UNIQUE (slug);

In Prisma, you express this as:

prisma
model BlogPost {
  id    String @id @default(cuid())
  slug  String @unique
  title String
}

4) Index based on your filters and sorts

An index is useful when:

• you filter on a column (WHERE published = true)
• you sort on a column (ORDER BY publishedAt DESC)
• you join on a column (userId foreign key)

A common pattern:

• published boolean (filter)
• publishedAt timestamp (sort)
• featured boolean (sort)

So you might add:

sql
CREATE INDEX blogpost_published_idx ON "BlogPost" (published);
CREATE INDEX blogpost_featured_idx ON "BlogPost" (featured);
CREATE INDEX blogpost_slug_idx ON "BlogPost" (slug);

For compound indexes, make sure they match your query pattern.

5) Prefer immutable IDs, keep slugs mutable

• Use a stable internal ID (cuid, uuid, bigint)
• Use slug for human URLs

When a slug changes, you can preserve SEO by keeping a redirect table:

• SlugRedirect with fromSlug, toSlug, resourceType, resourceId

6) Avoid “text blob as everything” (but accept it when it’s right)

Sometimes content is a blob (blog markdown, project write-ups). That’s fine.

But don’t store important queryable fields inside markdown:

• don’t parse tags out of content
• don’t parse published dates out of content

Store them separately.

7) Schema evolution: migrations are part of the design

A scalable schema is one you can migrate safely.

Rules of thumb:

• Add columns as nullable first, backfill, then make NOT NULL
• Avoid renaming columns without a compatibility period
• Add new indexes concurrently on large tables (CREATE INDEX CONCURRENTLY)

8) Performance debugging: always look at the query plan

When performance goes bad:

1. Capture the query and real parameters
2. Run EXPLAIN (ANALYZE, BUFFERS)
3. Check for sequential scans

Example:

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, published_at
FROM "BlogPost"
WHERE published = true
ORDER BY published_at DESC
LIMIT 20;

If it’s doing a sequential scan on a big table, you’re missing an index.

9) Practical checklist

• Does every table have a primary key?
• Do important entities have immutable IDs?
• Are slugs/emails/usernames unique?
• Are ON DELETE semantics correct?
• Do your frequent queries have indexes?
• Do you have a migration strategy?

Closing

A scalable database isn’t about “microservices” or “NoSQL vs SQL”. It’s about having constraints, clarity, and predictable query paths.