Schema-on-Read vs Schema-on-Write

Whether the database checks the shape of data when it is written, or the reading code interprets it later, and how each choice changes the way you evolve a schema.

Overview

Every dataset has a schema: some assumption about which fields exist and what types they hold. Even 'schemaless' data has one, because the code that reads it expects certain fields. The real question is who checks the shape, and when.

With schema-on-write, the database checks every write against a declared schema and rejects data that does not fit. Relational databases work this way. With schema-on-read, the store accepts whatever it is given, and the code that reads the data decides how to interpret it. Document stores, JSON logs and files in a data lake usually work this way.

Where the check happens

A useful comparison is typing in programming languages. Schema-on-write is like static type checking: mistakes are caught early, at the cost of declaring types up front. Schema-on-read is like dynamic typing: flexible, but a wrong shape shows up only when some code trips over it.

Who enforces — The database, on every write — Every reader, in its own code
Bad data — Rejected before it is stored — Stored; found when read
Changing the shape — A migration (ALTER TABLE, sometimes a backfill) — Write the new shape; readers handle old and new
Good fit — Many writers and readers sharing one structure — Mixed or fast-changing shapes, data from outside sources

Schema-on-write in practice

The schema lives in the table definition: column types, NOT NULL, CHECK and foreign keys. A row that breaks any of them never reaches the table, so every reader can trust the columns.

The notice says the new row violates the check constraint users_email_check. The price is that changing the structure means changing the table, which on a large, busy table needs care (see below).

Schema-on-read in practice

The store keeps whatever arrives, so over time it holds several shapes of the same kind of record. Each reader handles all of them. Here, older records keep one name field and newer ones split it in two.

This makes change cheap: deploy code that writes the new shape, and nothing has to be rewritten. The cost moves to the readers. Every service that reads these records must carry the same fallback logic, and one that forgets fails at read time, perhaps long after the bad write.

Changing a schema-on-write table safely

Many schema changes are fast now. Others still rewrite or scan the whole table, and those need a plan on a large table.

Add a nullable column — Metadata only, no rewrite — ALGORITHM=INSTANT, the default since 8.0.12
Add a column with a constant default — No rewrite since PostgreSQL 11 — INSTANT; any position since 8.0.29, before that only as the last column
Change a column's type — Usually rewrites the table and its indexes — ALGORITHM=COPY only; writes wait until it ends
Make a filled column NOT NULL — Scans the table, unless a valid CHECK already proves no NULLs — Rebuilds the table in place; writes continue

For changes that would rewrite a big MySQL table, teams use online schema change tools. Both build a new copy of the table with the change applied, copy rows over in small chunks, and swap the tables at the end. They differ in how they keep up with writes during the copy.

pt-online-schema-change (Percona) — Triggers on the original table — Atomic RENAME TABLE
gh-ost (GitHub) — The binary log, applied asynchronously; no triggers — A cut-over step, and the migration can be paused

Expand, migrate, contract

The safest pattern for any breaking change is to never make one. Split it into steps where old and new code both keep working.

In PostgreSQL, the last step can avoid a long full-table check. Add the constraint as NOT VALID, which does not scan existing rows, then VALIDATE it, which scans while holding only a lock that still allows reads and writes. With a valid CHECK that proves no NULLs exist, SET NOT NULL skips its own scan.

1 — ana — free

The middle ground

Most real systems mix the two. The enforcement point is a dial, not a switch.

PostgreSQL jsonb with CHECK constraints — Flexible body, but a few required keys are enforced on write
MongoDB $jsonSchema validator — Checks inserts and updates; validationAction: "warn" logs instead of rejecting; validationLevel: "moderate" skips updates to documents that were already invalid
Avro reader and writer schemas — Data is written with one schema and read with another: fields match by name, a missing field takes the reader's default, and an extra field is ignored

1 — signup

What you will be able to do

Explain why 'schemaless' data still has a schema, and where it is enforced.
Compare what each approach costs when bad data arrives and when the shape changes.
Plan a zero-downtime schema change with expand, migrate, contract.
Say which PostgreSQL and MySQL changes are instant and which rewrite a table.
Choose a middle ground such as a jsonb column with a few enforced keys.

Key concepts

Schema-on-write: The database checks every write against a declared schema and rejects data that does not fit. Readers can trust the shape.
Schema-on-read: The store accepts any shape; reading code interprets it. Changes are cheap, but every reader must handle every shape.
Implicit schema: The assumptions about fields that live in the reading code of a schemaless store. It exists even when nothing declares it.
Migration: A change to a declared schema, such as ALTER TABLE, sometimes followed by a backfill of existing rows.
Expand-contract: Making a breaking change in safe steps: add the new form, write both, backfill, switch reads, then remove the old form.
Online schema change: Changing a large MySQL table by building a changed copy, copying rows in chunks while tracking new writes, then swapping. pt-online-schema-change and gh-ost do this.
NOT VALID constraint: In PostgreSQL, a constraint added without checking existing rows. A later VALIDATE checks them under a lock that still allows reads and writes.
Reader and writer schema: In Avro, the schema data was written with and the one the reader expects. Avro resolves the difference field by field.

When to use it

Choosing between a relational table and a document store based on how stable the shape is
Planning a schema change on a large production table
Ingesting data from outside sources whose shape you do not control
Explaining how services with different versions read the same records during a rollout

Interview questions

How do you change the schema of a large production table without downtime?
When would you prefer schema-on-read?
Is a schemaless database really schemaless?