Schema mistakes that are expensive to undo

Most schema mistakes are cheap. You add a column, drop a column, rename something during a quiet hour, and nobody remembers by Friday.

A few are not. They are expensive because fixing them means rewriting every row, or changing every query, or admitting that data you needed was never recorded. This lesson is those, in rough order of how much they cost.

The theme worth noticing: the expensive mistakes are the ones that destroy information. You can always add a column. You cannot recover a fact you never stored.

1. float for money

price numeric NOT NULL,       -- fine
price_paise int NOT NULL,     -- fine
price double precision        -- a bug

0.1 + 0.2 is not 0.3 in binary floating point. Proved in PostgreSQL:

SELECT 0.1::float8 + 0.2::float8 = 0.3::float8;       -- f
SELECT (0.1::float8 + 0.2::float8)::text;             -- 0.30000000000000004

Be precise about the damage, because this is usually described badly. Summing ₹93.30 ten thousand times:

     float_sum     | numeric_sum
-------------------+-------------
 933000.0000001579 | 933000.00

The error is 0.00000016 — a tiny fraction of a paisa, not "a few rupees". Anybody telling you floating point will visibly mangle your totals at this scale is overstating it, and I had written exactly that before I measured it.

The real damage is different and worse:

• Equality stops working, which is the one that costs you:

          paid         | due | settled
  ---------------------+-----+---------
   0.30000000000000004 | 0.3 | f
  

  WHERE paid = amount_due is how you decide whether an invoice is settled. A customer who has paid in full shows as owing money, and staring at the two figures on a screen never explains it, because rounded to two places they are identical.

• The digits leak out. 933000.0000001579 reaches a report, an export, an API response.

• The error compounds with scale and with mixed magnitudes. Add a ₹1 item to a ₹10,00,000 total repeatedly and it grows.

Why it is expensive: by the time somebody notices, the affected numbers are in invoices, reconciliation reports and customer emails. Changing the column type is the easy part. Working out which historical comparisons were wrong is not.

The fix: numeric(12,2), or integer paise with the unit in the column name. Never float for anything you add up and show to a person.

float is right for genuinely measured, approximate quantities — a temperature, a latitude, a model score.

2. Storing local time, or timestamp without a zone

created_at timestamp NOT NULL DEFAULT now(),        -- wrong
created_at timestamptz NOT NULL DEFAULT now(),      -- right

timestamp (without time zone) stores the digits you gave it and nothing about what they mean. Two rows written at the same instant, one from a session in Asia/Kolkata and one in Europe/London:

             a              |               b
----------------------------+-------------------------------
 2026-09-27 22:17:03.659288 | 2026-09-27 17:47:03.659288+01
 2026-09-27 17:47:03.660456 | 2026-09-27 17:47:03.660456+01

Column a is timestamp, column b is timestamptz. The two a values differ by four and a half hours for two events a millisecond apart, and nothing in the row tells you why. The two b values are correctly one millisecond apart.

Why it is expensive: the data is genuinely ambiguous. Converting later means guessing what zone each row was written in, and if your servers moved region or you had one client in another country, the information is gone.

ALTER TABLE t ALTER COLUMN created_at TYPE timestamptz
  USING created_at AT TIME ZONE 'Asia/Kolkata';

That is a guess, applied to every row. If it is wrong for some of them, you cannot tell which.

The fix: timestamptz everywhere, always. It stores an instant; the client's zone decides how it is displayed. Use plain date for a genuine calendar date — a birthday, a due date — where there is no instant involved.

Note that PostgreSQL's timestamptz does not store a zone; it stores UTC and converts on the way out. That is exactly what you want.

3. int for a primary key that will grow

id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY      -- max 2,147,483,647
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY   -- max 9.2 quintillion

Why it is expensive: when you run out, every INSERT fails immediately:

ERROR:  nextval: reached maximum value of sequence "ovf_id_seq" (2147483647)

The fix is int → bigint, which rewrites the table and every index referencing it, holding ACCESS EXCLUSIVE. On a table big enough to have hit the limit that is hours of downtime, and every foreign key pointing at it needs the same change. It has taken down well-known services.

Note that you hit it at 2.1 billion sequence values, not rows — and a sequence is not reused when a transaction rolls back, so a table with a few hundred million rows and a lot of failed inserts can get there.

The fix: bigint from the start. Eight bytes instead of four, which is nothing.

4. No foreign keys

"We enforce it in the application." Every application eventually has a second writer — an admin script, a data fix, a new service, somebody in psql.

Why it is expensive: you end up with orphans — loans rows whose member_id points at nothing. Adding the constraint later fails:

ERROR:  insert or update on table "loans" violates foreign key constraint "loans_member_id_fkey"
DETAIL:  Key (member_id)=(999) is not present in table "members".

Note that it names one offending key. You fix that row, re-run, and it names the next one.

So first you have to decide what to do with every orphan, one at a time, with no information about what it was meant to reference. That is a data-archaeology project.

The fix: declare them. Add them NOT VALID then VALIDATE if the table is large, and index the referencing column.

5. Booleans where you needed dates

is_active boolean,       -- when did it stop being active?
is_paid boolean,         -- when was it paid?
is_deleted boolean       -- when, and by whom?

Why it is expensive: the first time somebody asks "how long between order and payment", or "how many were cancelled last month", the answer is we did not record that. Adding paid_at gives you the answer from that day forward and nothing for the past.

The fix: store the timestamp. paid_at timestamptz — NULL means not paid, so you get the boolean for free:

WHERE paid_at IS NOT NULL

One column, strictly more information. This is the cheapest of these fixes to apply and the most commonly skipped, which makes it the best value in the lesson.

6. One table for things that are not the same thing

CREATE TABLE items (
  id bigint PRIMARY KEY,
  type text,                -- 'product' | 'service' | 'subscription'
  name text,
  price int,
  weight_grams int,         -- products only
  duration_minutes int,     -- services only
  billing_period text       -- subscriptions only
);

Every column is nullable, so the database can no longer enforce anything. A product with a billing_period is nonsense the schema permits, and every query carries WHERE type = '…' that you will one day forget.

Why it is expensive: by the time it hurts, the table has millions of rows and hundreds of queries. Splitting it means rewriting all of them.

The fix: separate tables, or — when they genuinely share behaviour — a shared table with the common columns plus one table per subtype holding the specific ones, joined on the same id. And CHECK constraints that make the nonsense impossible:

CHECK ((type = 'product' AND weight_grams IS NOT NULL AND duration_minutes IS NULL)
    OR (type = 'service' AND duration_minutes IS NOT NULL AND weight_grams IS NULL))

Ugly, and much better than nothing if you are stuck with the shape.

The test: do the two things have the same columns, and do you ever query them together? If no to both, two tables.

7. jsonb as a way of avoiding a decision

CREATE TABLE events (id bigint PRIMARY KEY, data jsonb NOT NULL);

jsonb is excellent for genuinely variable data — third-party webhook payloads, user-defined fields, an audit snapshot.

It is a poor place for fields you know you have. Inside jsonb you lose NOT NULL, foreign keys, type checking, and readable names; every query gets data->>'customer_id' with a cast; and '42' and 42 are different values that both look right.

Why it is expensive: the data drifts. After two years the same logical field is customer_id, customerId and customer in different rows, with two of them as strings. Nothing rejected any of it. Extracting it into columns means writing a parser and deciding what to do with the rows that do not fit.

The fix: promote the fields you know to real columns and keep jsonb for the rest. This is the best of both, and is what a mature schema usually looks like:

CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id),
  kind text NOT NULL,
  occurred_at timestamptz NOT NULL,
  payload jsonb NOT NULL DEFAULT '{}'
);

8. Soft delete everywhere, thoughtlessly

deleted_at timestamptz     -- NULL = not deleted

Reasonable for things people restore — a document, an account. The cost is that every query everywhere must remember WHERE deleted_at IS NULL, and one that forgets is a bug that shows deleted data to users. Unique constraints stop working, because two "deleted" rows may legitimately share a value.

The fix, when you need it: partial unique indexes,

CREATE UNIQUE INDEX ON customers (phone) WHERE deleted_at IS NULL;

and a view that hides the deleted rows so the default is safe:

CREATE VIEW active_customers AS SELECT * FROM customers WHERE deleted_at IS NULL;

Then only soft-delete what somebody will actually restore. For an audit trail, an append-only history table is a better tool.

9. Not recording who and when

created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
created_by bigint REFERENCES users(id)

Why it is expensive: the first serious bug report, or the first dispute about who changed a price, has no answer. And unlike most things on this list you cannot backfill it at all — the information never existed.

The fix: put created_at and updated_at on every table from the start. They cost 16 bytes and repay themselves the first time you debug anything.

10. Meaning inside a string

order_ref text     -- 'PUN-2026-00123'

Encoding city, year and sequence in one text column means parsing with substring to query by any of them, no index can help, and the day somebody uses a four-letter city code every parser breaks.

The fix: store the parts as columns and generate the display reference:

city_code text NOT NULL,
year int NOT NULL,
seq int NOT NULL,
order_ref text GENERATED ALWAYS AS (city_code || '-' || year || '-' || lpad(seq::text, 5, '0')) STORED

Queryable parts, one authoritative format, and the string cannot disagree with the columns.

The ranking, and what it tells you

The four worst share a property: the fix does not recover the past. Everything else is laborious; those are irreversible.

The five-minute checklist

Before creating any table:

• bigint primary key.
• timestamptz, never timestamp. date only for genuine calendar dates.
• Money as numeric or integer minor units, with the unit in the name.
• created_at and updated_at.
• Every foreign key declared, and indexed.
• NOT NULL on everything that should never be missing — the default should be NOT NULL, with nullability the exception you justify.
• Every boolean interrogated: should it be a timestamp?
• CHECK constraints for the rules you can state.
• jsonb only for genuinely variable data.
• Does this table describe one kind of thing?

Ten items, two minutes, and it prevents almost everything in this lesson.

Check your work

Why float for money is expensive. The wrong numbers are already in invoices and reports.

Why float8 fails. Binary floating point cannot represent decimal fractions exactly — 0.1 + 0.2 gives 0.30000000000000004.

The real damage, stated accurately. Not large arithmetic errors — measured at 1.6×10⁻⁷ over 10,000 additions — but broken equality (paid = amount_due is false for a settled invoice) and digits leaking into reports.

When float is right. Genuinely approximate measured quantities.

Why timestamp without a zone is expensive. The rows are ambiguous, and converting later is a guess you cannot verify.

What timestamptz actually stores. An instant as UTC, converted on output.

Why an int primary key is urgent when it fails. Every insert fails, and the fix rewrites the table and its indexes.

Why you can exhaust it below 2.1 billion rows. Sequence values are consumed by rolled-back transactions.

Why adding foreign keys later is hard. Existing orphans must each be resolved with no information about their intent.

Why a boolean is worse than a timestamp. NULL gives you the boolean for free, and the timestamp answers questions the boolean cannot — and never retroactively.

What a multi-purpose table costs. Every column nullable, so no constraint can be enforced.

The test for splitting it. Same columns, and ever queried together?

Why jsonb for known fields drifts. Nothing rejects a misspelled key or a wrong type.

The mature pattern. Known fields as columns, jsonb for the remainder.

Two costs of soft delete. Every query must filter, and unique constraints break.

Two mitigations. Partial unique indexes, and a view that excludes deleted rows.

Why created_at cannot be backfilled. The information never existed.

The four irreversible mistakes. Local time, boolean-instead-of-date, no created_at, and float money.

Practice

1. Run SELECT (0.1::float8 + 0.2::float8)::text and then the numeric equivalent.
2. Sum 10,000 rows of 93.30 as float8 and as numeric. Compare with 933000.
3. Insert the same instant as timestamp and timestamptz from two different TimeZone settings and compare.
4. Write the USING ... AT TIME ZONE conversion and explain what it assumes.
5. Create a table with an int identity, ALTER SEQUENCE ... RESTART WITH 2147483646, and insert three rows. Read the error.
6. Time an int → bigint conversion on a million-row table with an index.
7. Create two tables without a foreign key, insert an orphan, then try to add the constraint. Read the error.
8. Write the query to find every orphan, then decide what you would do with each.
9. Take a table with is_paid boolean and write the query for "average days from order to payment". Explain why you cannot.
10. Replace it with paid_at and write the query.
11. Build the multi-purpose items table, insert a product with a billing_period, and note that nothing stopped you. Add the CHECK and try again.
12. Store an event as jsonb three times with customer_id, customerId and "42". Write the query that has to cope with all three.
13. Add a partial unique index on a soft-deleted table and confirm two deleted rows can share a phone number.
14. Write a SELECT that forgets WHERE deleted_at IS NULL and count the rows it wrongly returns.
15. Build the generated order_ref column and try to make the string disagree with its parts.
16. Run the ten-item checklist against the largest table in a project you work on. Write down what you find.

Official documentation

• PostgreSQL — Numeric types — The floating-point warning, and why numeric is slower but exact.
• PostgreSQL — Date/time types — What timestamptz stores, and the AT TIME ZONE conversion.
• PostgreSQL — Identity columns — GENERATED ALWAYS AS IDENTITY, and sequence behaviour on rollback.
• PostgreSQL — Constraints — CHECK, foreign keys and NOT VALID.
• PostgreSQL — JSON types — And the "Designing JSON documents" advice in the JSON functions chapter.
• PostgreSQL — Generated columns — For the order_ref pattern.

Next module: connecting a real program to a real database.