The Complete Guide to Data Modeling: From Logical Model to PostgreSQL Physical Schema

• Introduction
• 1. What Gets Decided When a Logical Model Becomes Physical
• 2. Key Design — What Identifies a Row
• 3. What Type Choices Actually Change
• 4. Constraints Are Code, Not Documentation
• 5. Where the jsonb Boundary Sits
• 6. Three Ways to Represent Time
• 7. When to Break Normalization, and What It Costs
• 8. Physical Layout — Column Order and TOAST
• Quiz: Check Your Understanding
• Closing Thoughts
• References
• Further Reading

Introduction

Articles about data modeling usually start with normalization and end with normalization. This blog's The Complete Guide to Database Fundamentals belongs to that lineage, and it is useful in its own right.

This guide starts where normalization ends. Drawing all the entities and relationships does not settle the schema. Should the identifier be a bigint or a uuid? Should the amount be numeric? Should the timestamp be timestamptz or timestamp? Which rules move into database constraints and which stay in the application? How far does jsonb go? These decisions outlive the logical model and are harder to change — a column type change is, in most cases, a full table rewrite.

The reference engine is PostgreSQL 18, and every storage size and behavior was confirmed in the PostgreSQL 18 documentation. On another engine the same judgment can come out differently.

1. What Gets Decided When a Logical Model Becomes Physical

The logical model says what exists and how things connect. The physical model adds four things on top.

Types — which data type each attribute is stored as. Storage size, arithmetic exactness, and comparison rules are all decided here.

Constraints — which rules the database enforces. Any rule you choose not to enforce will eventually show up as broken data.

Access paths — which indexes exist. These are derived from query patterns and should be settled alongside the schema.

Changeability — whether you can change it later. This perspective is the one most often missing.

Take the last one first. In PostgreSQL, changing a column type, in the documentation's words, "will normally cause the entire table and its indexes to be rewritten," holding an ACCESS EXCLUSIVE lock throughout. The only exceptions are when the USING clause does not change the column contents and the old type is binary coercible to the new type or is an unconstrained domain over it.

In other words, a type choice is effectively an irreversible decision. Indexes, by contrast, can be added and dropped at any time, and constraints can be added in stages with NOT VALID. That asymmetry sets the design priority. Spend the time on types; decide indexes later, once you can see the data.

2. Key Design — What Identifies a Row

The first fork is natural key versus surrogate key.

A natural key uses an identifier that already exists in the domain — a business registration number, an ISBN, an email address. The advantage is that joins need no extra lookup, but it brings three problems: the value can change (people change their email), a long value inflates the index of every referencing table, and a change to the domain rules breaks the schema.

A surrogate key adds a separate, meaningless identifier. Most production schemas take this route. Even so, if a natural key exists, express its uniqueness as a constraint. Using a surrogate key does not mean abandoning the natural key's uniqueness.

CREATE TABLE users (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text   NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- Even with a surrogate key, keep the natural key's uniqueness as a constraint
CREATE UNIQUE INDEX uq_users_email ON users (lower(email));

GENERATED ALWAYS AS IDENTITY is standard SQL syntax and is preferred over serial. serial is closer to a macro that creates a sequence and attaches a default, which makes ownership relationships easy to confuse.

The second fork is integer versus UUID.

bigint is 8 bytes and increases monotonically, so insertion locality in a B-tree index is excellent. The downsides are that values are guessable (do not expose them in URLs) and that global uniqueness is hard to guarantee in a distributed setup.

uuid is 16 bytes and globally unique. The downside is that random UUIDs have poor insertion locality. New values scatter across the whole index, so index pages split constantly and cache hit rates drop.

PostgreSQL 18 has a function aimed squarely at this problem. The documentation describes uuidv7() as generating "a version 7 (time-ordered) UUID. The timestamp is computed using UNIX timestamp with millisecond precision + sub-millisecond timestamp + random." In other words, you get UUID global uniqueness together with integer-like insertion locality.

-- PostgreSQL 18: a UUID that carries time ordering
CREATE TABLE events (
  id         uuid PRIMARY KEY DEFAULT uuidv7(),
  tenant_id  bigint NOT NULL,
  occurred_at timestamptz NOT NULL DEFAULT now()
);

The same documentation page also lists gen_random_uuid() and uuidv4(). Confirm the availability of uuidv7() in the documentation for the version you run. On older versions you need an extension or application-side generation.

3. What Type Choices Actually Change

Strings. The PostgreSQL documentation's tip is unambiguous: "There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead."

So in PostgreSQL, the n in varchar(n) is a constraint, not a performance choice. Attach it when the length limit is a domain rule; leave it off when it is just habit. Widening it later works without a rewrite; narrowing it is a rewrite. Per the documentation, n cannot exceed 10,485,760, and the longest storable string is about 1 GB.

The storage overhead is documented too: "The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which includes the space padding in the case of character. Longer strings have 4 bytes of overhead instead of 1."

Numbers. The documentation's recommendation is firm. numeric "can store numbers with a very large number of digits" and "is especially recommended for storing monetary amounts and other quantities where exactness is required." For floating point it states: "If you require exact storage and calculations (such as for monetary amounts), use the numeric type instead."

The price is stated just as plainly: "calculations on numeric values are very slow compared to the integer types, or to the floating-point types." So the rule is numeric for money and quantities, floating point for statistical and scientific computation. Remember the documentation's two warnings about floating point as well: "Inexact means that some values cannot be converted exactly to the internal format and are stored as approximations," and "Comparing two floating-point values for equality might not always work as expected."

Storage sizes are smallint 2 bytes, integer 4 bytes, bigint 8 bytes, real 4 bytes, and double precision 8 bytes.

Timestamps. This is the item people get wrong most often. Per the documentation, timestamp with time zone values are stored internally in UTC; an input string with an explicit time zone is converted to UTC using that offset, and one without is assumed to be in the zone named by the TimeZone parameter and converted. And "the originally stated or assumed time zone is not retained." On output the value "is always converted from UTC to the current timezone zone, and displayed as local time in that zone."

Both types occupy 8 bytes. timestamptz is not larger. So "we use timestamp to save space" is not a valid argument.

The base rule is this: use timestamptz for any value that denotes an instant. Order times, log times, creation times. Use timestamp without time zone only for wall-clock values that are independent of any zone (for example, "notify at 9 a.m. every day"). Note also that the documentation states "the SQL standard requires that writing just timestamp be equivalent to timestamp without time zone, and PostgreSQL honors that behavior" — so writing timestamp without thinking gives you the zone-less type.

4. Constraints Are Code, Not Documentation

Put the rule "this value must always be greater than zero" only in the application and it breaks through three paths: batch scripts, ad-hoc SQL run in production, and a newly added service.

Database constraints block all three. Here is the set of constraints worth putting in.

CREATE TABLE order_items (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id    bigint  NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
  sku         text    NOT NULL,
  quantity    integer NOT NULL CHECK (quantity > 0),
  unit_price  numeric(12, 2) NOT NULL CHECK (unit_price >= 0),
  status      text    NOT NULL
              CHECK (status IN ('PENDING', 'SHIPPED', 'CANCELLED')),
  created_at  timestamptz NOT NULL DEFAULT now(),
  UNIQUE (order_id, sku)
);

A few practical guidelines.

Index the child side of a foreign key. Deleting or updating a parent key requires checking the child table, and without an index that means a full scan every time. PostgreSQL does not create this index for you.

Choose between CHECK and an enum type for enumerations. A CHECK is simple because adding a value is just replacing the constraint; an enum type joins the type system but makes removing a value awkward. If values change often, a lookup table plus a foreign key is the most flexible.

Add constraints to production tables in two steps. As the documentation states, with NOT VALID the ADD CONSTRAINT command "does not scan the table and can be committed immediately," and the subsequent VALIDATE CONSTRAINT "acquires only a SHARE UPDATE EXCLUSIVE lock on the table being altered," so it does not block concurrent updates.

ALTER TABLE order_items
  ADD CONSTRAINT chk_qty_positive CHECK (quantity > 0) NOT VALID;
ALTER TABLE order_items VALIDATE CONSTRAINT chk_qty_positive;

5. Where the jsonb Boundary Sits

jsonb is powerful, and therefore overused. Here are three criteria for drawing the boundary.

Criterion 1 — do you query or sort on this field? If so, promote it to a column. You can index values inside jsonb, but there is no type checking and estimation is imprecise, which makes plans go bad.

Criterion 2 — does this field need a constraint? NOT NULL, foreign keys, and uniqueness require a column. None of them can be attached inside jsonb.

Criterion 3 — is the schema genuinely unpredictable? "A field might get added later" is not a reason. Adding a column with a non-volatile default finishes immediately without a rewrite.

The cases where jsonb is justified are clear: preserving the raw payload an external system sent for audit purposes, holding arbitrary user-defined attributes, and holding configuration whose shape differs per tenant.

CREATE TABLE webhook_events (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  -- Values used for querying, joining, or constraints get promoted to columns
  provider     text        NOT NULL,
  event_type   text        NOT NULL,
  received_at  timestamptz NOT NULL DEFAULT now(),
  -- The rest of the original payload is preserved as-is
  payload      jsonb       NOT NULL
);

-- If you query a specific key often, build an expression index
CREATE INDEX idx_webhook_payload_orderid
  ON webhook_events ((payload ->> 'order_id'));

-- If you need containment searches, use GIN
CREATE INDEX idx_webhook_payload_gin ON webhook_events USING gin (payload);

A GIN index is an inverted index optimized for data where one row holds many values. It is useful for jsonb containment operators, but account for the fact that it is large and expensive to update.

6. Three Ways to Represent Time

Time-related requirements split into three kinds, and each wants a different model.

First, an audit log (what happened and when). Append change events to a separate history table. Never update, never delete.

CREATE TABLE order_status_history (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id   bigint      NOT NULL REFERENCES orders (id),
  old_status text,
  new_status text        NOT NULL,
  changed_by bigint,
  changed_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_osh_order_time ON order_status_history (order_id, changed_at DESC);

Second, a validity period (from when until when was this value in force). Price history and contract terms belong here. A range type plus an exclusion constraint makes the database enforce the rule that periods must not overlap.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE product_prices (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id bigint  NOT NULL REFERENCES products (id),
  price      numeric(12, 2) NOT NULL CHECK (price >= 0),
  valid      tstzrange NOT NULL,
  -- Validity periods for the same product cannot overlap
  EXCLUDE USING gist (product_id WITH =, valid WITH &&)
);

An exclusion constraint is implemented with a GiST index, and using an integer column for equality inside it requires the btree_gist extension. Checking this rule in the application leaves a hole where two concurrent requests both pass, but a constraint is enforced atomically by the database.

Third, soft deletes (rows that look deleted but are still there). The most common pattern and the most problematic. Every query needs WHERE deleted_at IS NULL, and missing it even once exposes deleted data. Uniqueness also breaks, because a deleted row's email still occupies the slot.

A partial index is the answer to the second problem.

-- Unique only among live rows
CREATE UNIQUE INDEX uq_users_email_alive
  ON users (lower(email))
  WHERE deleted_at IS NULL;

The first problem can be handled with views or row-level security. But fundamentally, it is better to ask again whether soft deletes are really needed. If the goal is audit, a history table fits better; if the goal is recovery, backups and point-in-time recovery are the right tools.

7. When to Break Normalization, and What It Costs

Normalization is a default, not a religion. There are times to break it, and you should know what you pay when you do.

The classic justification for denormalization is materializing an aggregate — keeping a comment count in a column instead of counting every time. The cost is explicit: the two values can drift apart, and the responsibility for preventing that moves to the application.

-- If you decide to denormalize, pin the update path to exactly one place
CREATE OR REPLACE FUNCTION bump_comment_count() RETURNS trigger AS $fn$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE posts SET comment_count = comment_count + 1 WHERE id = NEW.post_id;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE posts SET comment_count = comment_count - 1 WHERE id = OLD.post_id;
  END IF;
  RETURN NULL;
END;
$fn$ LANGUAGE plpgsql;

CREATE TRIGGER trg_comment_count
AFTER INSERT OR DELETE ON comments
FOR EACH ROW EXECUTE FUNCTION bump_comment_count();

Warning: CREATE TRIGGER takes a SHARE ROW EXCLUSIVE lock on the target table. That lock conflicts with ROW EXCLUSIVE — that is, INSERT, UPDATE, DELETE, and MERGE — so writes to that table are blocked while the trigger is being created. Reads pass through. Run it during low traffic with SET LOCAL lock_timeout in place.

Maintaining it with a trigger carries another cost. When comments pile onto a popular post, everyone tries to update the same posts row and lock contention appears. Alternatives are to append increments to a separate table and total them periodically, or to ask again whether an exact real-time value is needed at all. Most counters do not care about being five seconds late.

Three things must be left behind when you decide to denormalize: why you broke it, what guarantees consistency, and the recomputation query that fixes it when it drifts. Fail to write the third one in advance and you will improvise it in the middle of an incident.

8. Physical Layout — Column Order and TOAST

Finally, the layer that is hard to see.

Column order and alignment padding. PostgreSQL lays out each column according to its type's alignment requirement, so ordering changes how much padding creeps into the row. Arranging large fixed-width types before small ones reduces the waste. On a table with hundreds of millions of rows that difference reaches gigabytes. That said, it is rarely worth badly hurting readability for. Consider it only on very large tables.

TOAST. Large values do not sit inline in the row; they move to a separate storage area where they are compressed or split. The benefit is that a query which does not read a big text or jsonb column pays almost nothing for it. Conversely, habitually reading everything with SELECT * throws that benefit away. On wide tables, listing only the columns you need makes a real performance difference.

The partition key is part of the schema. The unique constraints, exclusion constraints, and partial indexes from sections 5 and 6 all interact with the partition key. To create a unique constraint on a partitioned table, the documentation requires that the constraint's columns include all of the partition key columns. If partitioning is a possibility later, review it during key design. Discovering it afterwards means tearing up the schema.

The schema itself is a design decision too. Giving each tenant its own schema in a multi-tenant system looks like good isolation, but with thousands of tenants the catalog grows, migrations repeat thousands of times, and search_path switching collides with connection pooling. In most cases a tenant_id column plus row-level security scales better.

Quiz: Check Your Understanding

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE product_prices
  ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (product_id WITH =, valid WITH &&);

UPDATE posts p
   SET comment_count = c.cnt
  FROM (SELECT post_id, count(*) AS cnt FROM comments GROUP BY post_id) c
 WHERE p.id = c.post_id AND p.comment_count IS DISTINCT FROM c.cnt;

Closing Thoughts

The most expensive mistake in data modeling is not picking the wrong normal form. It is making an irreversible decision without grounds. A type change is a table rewrite, a key design change ripples through every referencing table, and a partition key even changes the shape a unique constraint is allowed to take.

So set the order this way. Decide types and keys first, and record the reasoning. Then move every domain rule the database can enforce into a constraint. Add indexes once the real query patterns are visible. Denormalize only when there is a measured bottleneck, and when you do, commit the recomputation query along with it.

To experiment with schemas and queries directly, use the Postgres Playground and the SQL Playground; to fill your schema with test data, use the Mock Data Generator.

References

• PostgreSQL 18, Character Types: https://www.postgresql.org/docs/18/datatype-character.html (accessed 2026-08-15)
• PostgreSQL 18, Numeric Types: https://www.postgresql.org/docs/18/datatype-numeric.html (accessed 2026-08-15)
• PostgreSQL 18, Date/Time Types: https://www.postgresql.org/docs/18/datatype-datetime.html (accessed 2026-08-15)
• PostgreSQL 18, UUID Functions: https://www.postgresql.org/docs/18/functions-uuid.html (accessed 2026-08-15)
• PostgreSQL 18, ALTER TABLE: https://www.postgresql.org/docs/18/sql-altertable.html (accessed 2026-08-15)
• PostgreSQL 18, Explicit Locking: https://www.postgresql.org/docs/18/explicit-locking.html (accessed 2026-08-15)
• PostgreSQL 18, Table Partitioning: https://www.postgresql.org/docs/18/ddl-partitioning.html (accessed 2026-08-15)
• PostgreSQL 18, Index Types: https://www.postgresql.org/docs/18/indexes-types.html (accessed 2026-08-15)

Further Reading

• Previous in series: The Complete Guide to Bulk Data Processing — COPY and chunked batches
• Next in series: The Complete Guide to Database Performance Tuning — the order in which to measure
• The Complete Guide to Database Fundamentals — normalization and core concepts
• The Complete Guide to PostgreSQL Indexes — designing the indexes for your schema
• Postgres Playground — experiment with schemas and constraints
• SQL Playground — experiment with query syntax
• Mock Data Generator — fill the schema you designed