Normalization vs denormalization in a real Postgres schema: what I fixed, and the copy I kept

If you own a product, you should know it all the way down. In many products, the database is the main part: it's where every customer, every message and every decision actually lives. Screens change every month; the data stays. So it has to be well structured, and it has to be usable by the app that sits on top of it.

For the structure part, we have an old, well-known answer: the normal forms, and above all 3NF. For the usable part, the textbook isn't enough. A serverless mobile app talks to the database directly from the phone, and access rules, latency and the absence of a backend in between all push back on the textbook. Some differences have to be made on purpose.

My app, Hearback, drafts replies to comments on my posts. It runs on Postgres (Supabase) with about 25 tables. I built it with an AI coding agent in three days: 17 migrations, one feature after another. Every feature worked, and the tests passed.

Then I went back to the basics and checked the whole schema against the normal forms. I didn't use a blog summary; I used the papers that defined them.

What an ordinary agent build gives you

A usual build with an agent, without asking for anything special in this area, does not produce a 3NF database. The agent makes each feature work. When a value is handy to have on a row, it puts it there, and the schema grows copies one reasonable step at a time.

Here's what the audit found in my existing setup:

What	Count
Columns that repeated or recomputed another fact (2NF and 3NF)	8
Derived values maintained by hand in app code (one writer forgot)	3
Arrays used as hidden tables (1NF)	1
Copies with no guarantee they match their parent (cross-project or cross-person mismatches possible)	10
Places where the same fact could disagree with itself	22
Duplicates reviewed and kept on purpose, with the reason written down	4

The good news: on the live data, none of them had gone wrong yet. Nothing was broken. It just had 22 ways to break silently later, and no test would have caught it.

The other good news: the same agent did the audit well once I asked for it explicitly, with the original sources in hand. Database structure is something you have to ask for. It doesn't come out of "make this feature work".

Most of the duplication went away. One copy I kept on purpose. This post is about both: what normalization actually buys you, and how to break it deliberately without losing that.

The whole theory in one line

William Kent's A Simple Guide to Five Normal Forms (1983) is still the clearest text on this. His summary:

Every non-key field must provide a fact about the key, the whole key, and nothing but the key.

• 1NF: one value per field. No lists you search inside.
• 2NF: "the whole key". If the key has two columns, nothing may depend on just one of them.
• 3NF: "nothing but the key". Nothing may be derivable from another non-key column or from a parent row.

Why bother? Because a fact stored twice will eventually disagree with itself, and then nobody knows which copy is true. Normal forms are a checklist for finding the places where that can happen.

Before touching anything: count

Before writing a single constraint, I counted on the live data how many rows each new rule would break. These were read-only queries, like this one:

-- Does any item claim a different project than its author?
select count(*)
from items i
join people p on p.id = i.person_id
where p.project_id <> i.project_id;

All zero. That's what made the rest a calm afternoon instead of a data-repair weekend.

What normalization fixed

2NF: a link table that knew too much

idea_items connects feature ideas to the comments that asked for them. Its key is (idea_id, item_id). It also stored person_id, who wrote the comment.

But the person depends on the item alone. That's half the key: a textbook 2NF violation. My "how many people asked for this" counter read that copy, so if two person records were ever merged, the counter would quietly go wrong.

Fix: drop the column and read the person through the item. The count moved into a trigger that also re-counts when an item changes hands.

3NF: children repeating their parents

Text chunks (for retrieval) repeated their document's kind, label and URL. Every chunk of a document had to agree with the document, and nothing enforced it. I dropped those columns. The search function joins documents now and returns exactly the same shape as before.

Derived values, written by hand

This is where I found the most. Each case got one of two fixes:

• A function of the same row → a generated column. is_public was just label = 'quotable' stored twice. Token totals were sums of a JSON breakdown sitting next to them.

  alter table drafts add column tokens_in int generated always as (
    (tokens->>'input')::int
    + coalesce((tokens->>'cache_read')::int, 0)
    + coalesce((tokens->>'cache_write')::int, 0)
  ) stored;

Postgres computes it, and nobody can write it wrong. (An insert that tries to set it fails, so update your writers first.)

• An aggregate over other rows → a trigger as the only writer. "Last touched" dates were kept by read-then-write in app code. Two writers at once could lose an update, and one code path forgot to update them at all. Now a trigger on the touches table does it, with greatest()/least(), which is safe under concurrent inserts.

1NF: an array that was a table

plans.allowed_models was a text array, searched with = any(...) in three functions. A list you search inside is a relation in disguise, so it became a plan_models table, with an ord column so the app keeps its order.

When to denormalize: the copy I kept

Here's the one place I broke 3NF on purpose: project_id on child tables. People, items, touches and chunks each carry the project they belong to, even though their parent already knows it.

Why would you denormalize data? I had two reasons.

This is the serverless mobile part. There is no backend of mine between the phone and the database.

Security. On Supabase, the app talks to Postgres directly, and row-level security decides which rows each user sees. A policy that checks the row itself is simple and cheap:

create policy members_all on items for all
  using (is_member(project_id));

Without the column, every policy would need a join through the parent, on every row of every query.

Performance. The inbox and the vector search both filter by project first, using indexes on that column.

So the copy stays. What was wrong wasn't the copy; it was that nothing stopped it from lying. An item could claim project A while its author lived in project B, and the policy would trust the claim.

Keep the copy, make the database guard it

The fix is a composite foreign key. The parent exposes the pair:

alter table people add constraint people_id_project unique (id, project_id);

And the child references the pair instead of the id alone:

alter table items
  drop constraint items_person_id_fkey,
  add constraint items_person_fkey
    foreign key (person_id, project_id)
    references people (id, project_id) on delete cascade;

Now items.project_id can only hold the person's real project. RLS still checks one local column, and that column is now guaranteed correct.

The same trick checks more than tenancy:

• An item's account must be on the item's platform: (account_id, project_id, platform).
• A touch about an item must be with that item's author: (item_id, project_id, person_id), with on update cascade so it follows if the item moves to another person.
• For a nullable reference that should clear on delete, Postgres 15+ lets you null only one column: on delete set null (theme_id). Otherwise you'd wipe the tenant column too.

What I left alone, and wrote down why

Not every duplicate is a bug. I kept these, with the reason written as a comment in the migration:

• Snapshots. A thread as it looked when fetched is a fact about that moment, not a copy.
• A URL column that sometimes holds a link that can't be derived from other columns.
• A hash computed in JavaScript before insert. A generated column in Postgres might normalize whitespace differently.

If you can't write down why a copy exists, that's your sign to remove it.

Don't forget your API layer

Composite foreign keys add relationships, and tools that infer joins from foreign keys can get confused. PostgREST (Supabase's REST layer) picks the join from the foreign keys. After the change there were two between plans and plan_models: a plan's models, and the plan's default model. That makes a plain embedded select ambiguous, so I named the constraint in the query:

plans?select=id,default_model,plan_models!plan_models_plan_fkey(model)

So after the migration, re-run every embedded query your app and functions use, not just your SQL tests. I did this on a local Supabase (supabase start), inserted deliberately bad rows to confirm they were rejected, then deployed the migration and the server code together and ran the end-to-end suite.

The balance

Normalization isn't a purity test. It's how you, as the owner, know where every fact in your product lives. The app's real constraints decide where you bend it. The working rule I ended up with:

1. Audit against 1NF, 2NF and 3NF, using the original definitions. If an agent builds your app, ask it for this audit explicitly; it won't happen on its own.
2. For each duplicate, choose: remove it, compute it (generated column), cache it (trigger as the only writer), or keep it and enforce it (composite foreign key).
3. Count violations on live data before adding any constraint.
4. Write the reason next to every copy you keep.

Remember the basics, then break them deliberately, with the database holding the line.

---

The step-by-step versions, including a read-only check you can run on your own schema:

• Database normalization example: the 1NF, 2NF and 3NF problems in my live Postgres schema
• Normalization vs denormalization: the copy I kept on purpose, and how Postgres guards it

Have you kept a copy on purpose? I'd like to hear what made it worth it, and what keeps it honest.

Comments

[Arhan Canli]

The composite foreign keys only guard what they cover when every column in them is NOT NULL. Postgres defaults to MATCH SIMPLE: if any column of the key is NULL, the constraint is not checked at all. So (account_id, project_id, platform) is safe only if platform can't be NULL; a row with a NULL there passes with whatever project_id it claims, and RLS will trust it. Either add NOT NULL to the copied columns or declare the key MATCH FULL where the reference itself is optional (then it is all NULL or all set).

One related thing in the generated column: (tokens->>'input')::int + coalesce(...) has no coalesce on the first term, so a row whose JSON lacks an input key gets tokens_in = NULL rather than the cache sum, and a value like "12.0" makes the cast raise and fails the whole insert. Neither is wrong, but it's worth a test row for each, since the column can't be written around once it's generated.

For the counts-before-constraints step, ALTER TABLE ... ADD CONSTRAINT ... NOT VALID followed by VALIDATE CONSTRAINT lets you add the key without a long lock on a table that is already in use.