Modern Database Design Using PostgreSQL

PostgreSQL has quietly become the default choice for a huge share of new applications, and for good reason — it combines the reliability of a mature relational database with genuinely modern features (JSONB, full-text search, extensions like pgvector) that used to require reaching for a separate specialized database.

Normalize first, denormalize deliberately

Start with a normalized schema — it prevents update anomalies and keeps your data model honest. Denormalize only once a specific, measured query pattern justifies it (a dashboard that aggregates millions of rows on every page load, for instance), and document why the denormalized field exists so the next engineer doesn't 'fix' it back into a slow query.

-- Normalized core schema
CREATE TABLE customers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id UUID NOT NULL REFERENCES customers(id),
  status TEXT NOT NULL DEFAULT 'pending',
  total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Denormalized, deliberate: avoids a join + aggregate on every dashboard load
ALTER TABLE customers ADD COLUMN lifetime_order_count INTEGER NOT NULL DEFAULT 0;
-- Kept in sync via a trigger or application-level transaction on order creation.

JSONB for genuinely semi-structured data

JSONB earns its place when a subset of your data is legitimately variable-shaped — user-defined custom fields, webhook payloads, feature flags — not as a substitute for modeling relationships you already understand. Overusing JSONB as a schema-less escape hatch gives up query planning, foreign keys, and type safety for no real benefit.

ALTER TABLE products ADD COLUMN attributes JSONB NOT NULL DEFAULT '{}';

-- GIN index enables efficient containment queries against the JSONB column
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

-- Query: find all products where attributes.color = 'crimson'
SELECT * FROM products WHERE attributes @> '{"color": "crimson"}';

Index for your actual queries

The most common indexing mistake is guessing instead of measuring. Run EXPLAIN ANALYZE against your real, production-shaped queries and let the query planner tell you what's actually happening — sequential scans on large tables under real load are the clearest possible signal that an index is missing.

-- Partial index: only indexes the rows that matter for this query pattern,
-- far smaller and faster to maintain than indexing the whole table.
CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

-- This index is useless for "all orders" queries, and that's the point —
-- it's purpose-built for the dashboard that only ever looks at pending orders.

An index you can't point to a query for is a write-performance tax with no benefit. — Arjun Mehta, Principal Software Engineer, NavikaTech

Conclusion

PostgreSQL's combination of strict relational guarantees and pragmatic modern features — JSONB, partial indexes, generated columns — means most application teams no longer need to reach for a separate specialized database for semi-structured data or search. Design normalized, measure before indexing, and use JSONB deliberately rather than as a schema-less default.