Database Design for Scalable Applications

Good database design is invisible when done right and expensive to fix when done wrong — schema migrations on a live system with real data are far harder than getting the model right early.

In short: start with a normalized relational schema with real constraints, index for the queries you actually run, and scale in stages (pooling, then replicas and caching, then partitioning) only when measurements tell you to. Most applications never need sharding, but almost all of them need good keys, constraints, and indexes.

Key takeaways

• Let the database enforce integrity: foreign keys, NOT NULL, unique and check constraints.
• PostgreSQL is the right default for most products; choose a document store for a specific reason.
• Design composite indexes from real query patterns and confirm them with EXPLAIN ANALYZE.
• Scale in order: query fixes, pooling, replicas and caching, partitioning, and only then sharding.
• Every migration should be backward compatible with the currently deployed code.

1. Normalize First, Denormalize Deliberately

Start with a normalized schema (3NF) to avoid update anomalies and data duplication. Denormalize only for measured performance reasons — a read-heavy reporting table, a cache column for a frequently joined value — and document why.

Keys, Constraints, and Data Types

Application code changes often and has bugs; constraints in the database protect the data from all of them at once. A small example for an orders table in PostgreSQL:

CREATE TABLE orders (
  id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id    UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
  tenant_id    BIGINT NOT NULL REFERENCES tenants(id),
  customer_id  BIGINT NOT NULL REFERENCES customers(id),
  status       TEXT NOT NULL CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
  total_amount NUMERIC(12, 2) NOT NULL CHECK (total_amount >= 0),
  currency     CHAR(3) NOT NULL,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

• Internal vs public IDs: a compact integer key for joins, a UUID for URLs and APIs so IDs are not guessable or enumerable.
• Money: NUMERIC or integer minor units (cents), never floating point.
• Time: TIMESTAMPTZ stored in UTC; convert to local time at the edge.
• Status fields: a check constraint or lookup table, so a typo cannot create a new state.

2. Choosing SQL vs NoSQL

• PostgreSQL/MySQL — default choice for relational data with clear entities and transactions
• MongoDB — useful for genuinely document-shaped data with flexible, evolving schemas
• Redis — caching, session storage, and rate limiting, not primary data storage

Most applications — including most "we need NoSQL for scale" cases — are correctly served by PostgreSQL with proper indexing. Reach for NoSQL when the data model genuinely doesn't fit relational structure, not by default.

I have worked with PostgreSQL, MySQL, MongoDB, Oracle, and Informix across product and enterprise systems, and the deciding question is nearly always the same: does the data have relationships you need to query and keep consistent? If yes, use a relational database; PostgreSQL's JSONB columns cover the occasional flexible field. There's a fuller comparison in PostgreSQL vs MongoDB.

3. Indexing Strategy

• Index columns used in WHERE, JOIN, and ORDER BY clauses
• Composite indexes should match query patterns, ordered by selectivity
• Avoid over-indexing — every index slows down writes
• Use EXPLAIN ANALYZE to verify the planner is actually using your indexes

For a typical "latest orders for this customer in this tenant" query, one composite index serves the filter and the sort together. Equality columns go first, the range or sort column last:

-- query
SELECT id, status, total_amount, created_at
FROM orders
WHERE tenant_id = $1 AND customer_id = $2
ORDER BY created_at DESC
LIMIT 20;

-- index that matches it
CREATE INDEX CONCURRENTLY idx_orders_tenant_customer_created
  ON orders (tenant_id, customer_id, created_at DESC);

-- partial index for a hot subset
CREATE INDEX CONCURRENTLY idx_orders_pending
  ON orders (tenant_id, created_at) WHERE status = 'pending';

Use CONCURRENTLY on live tables so the index build does not block writes. Enable pg_stat_statements to find the queries that consume the most total time; those are where indexes pay off.

An index you never measured is a guess. Profile real query patterns before adding indexes, not after a slow query report from production.

4. Scaling Reads and Writes

• Read replicas for read-heavy workloads — route reporting and analytics queries away from primary
• Connection pooling (PgBouncer) to avoid exhausting database connections under load
• Partitioning large tables by date or tenant once single-table performance degrades
• Caching layer (Redis) in front of expensive, frequently repeated queries

The order matters. Each step adds complexity, so take the next one only when the signal appears:

With read replicas, remember that a user who just saved something may not see it on a replica yet. Route "read your own write" requests to the primary. The wider picture of where the database sits among app servers, caches, and queues is in system design for full stack developers.

5. Multi-Tenant and SaaS Schema Patterns

For SaaS applications, the schema decision that matters most is tenancy isolation: shared tables with a tenant_id column and composite indexes on (tenant_id, ...) are the right default for most products — see the SaaS architecture guide for the full trade-off breakdown.

• Shared tables + tenant_id: cheapest to run and migrate; isolation depends on every query filtering correctly.
• Schema per tenant: stronger separation, but migrations run once per tenant and get slow as tenants grow.
• Database per tenant: strongest isolation and per-customer backups; highest operating cost. Usually reserved for large enterprise customers.

With shared tables in PostgreSQL, row-level security adds a second safety net: set the tenant per connection or transaction and let a policy filter every row, so a missing WHERE tenant_id = ... no longer leaks data.

6. Migrations as a First-Class Practice

• Version-controlled migrations (Flyway, Prisma Migrate, Liquibase)
• Backward-compatible migrations for zero-downtime deploys — add columns nullable first, backfill, then constrain
• Never run destructive migrations without a tested rollback path

Renaming a column safely, for example, takes several deploys rather than one:

1. Add the new column (nullable) and deploy code that writes to both columns.
2. Backfill existing rows in small batches to avoid long locks.
3. Switch reads to the new column; add the NOT NULL constraint.
4. Stop writing the old column, then drop it in a later release.

Common Mistakes

• N+1 queries from ORMs loading relations one row at a time
• OFFSET pagination on large tables instead of keyset (cursor) pagination
• Soft-delete flags without matching partial indexes, so every query scans deleted rows
• Storing comma-separated lists or JSON blobs for data that is filtered and joined
• No tested restore: backups that have never been restored are a hope, not a plan

Bringing It Together

The database is the part of your system that's hardest to change after launch. Investing in schema design, indexing discipline, and a clear scaling path early pays off far more than optimizing application code later.

Frequently Asked Questions

Should I use SQL or NoSQL for a scalable application?

For most applications, a relational database such as PostgreSQL is the right default, because most product data has relationships that must stay consistent. Choose a document database when the data is genuinely document-shaped and rarely joined, not only because of expected scale.

How many indexes should a table have?

As many as your real query patterns need and no more. Each index speeds up some reads but slows every write and uses storage. Start from the slowest and most frequent queries, add composite indexes that match them, and remove indexes the planner never uses.

When should I shard a database?

Only when write volume or data size exceeds what a single well-tuned primary can handle, after query optimization, connection pooling, vertical scaling, read replicas, caching, and partitioning. Sharding changes application design, so it should be a measured decision.

What is the best multi-tenant database design for SaaS?

Shared tables with a tenant_id column and composite indexes starting with tenant_id suit most SaaS products. Add PostgreSQL row-level security for defence in depth, and reserve schema-per-tenant or database-per-tenant for customers with strict isolation requirements.

How do I run database migrations without downtime?

Make each migration backward compatible with the running code using expand and contract: add new structures first, deploy code that handles both, backfill in batches, switch over, and remove old structures in a later release. Build indexes concurrently on live tables.

I'm Sandaruwan Jayasundara — Senior Software Engineer | Full Stack Developer. See how to build a SaaS application or get in touch about your data architecture. For Java backends, see my Spring Boot development services.