Adding a row is cheap, adding a column can take down prod

Early on, I thought of a migration as a small thing. You write ALTER TABLE, it runs in a few milliseconds on your laptop, the tests pass, and you ship it. Inserting a row felt the same, maybe even smaller.

On a busy production database they are not the same at all. These days I keep one line in my head whenever I design a table:

Changing data is routine. Changing the shape of the data is surgery.

Here's why, and what it changes about how you design things.

Two kinds of SQL

SQL statements fall into two groups.

DML (Data Manipulation Language) works on the rows: INSERT, UPDATE, DELETE, SELECT. Your application runs these thousands of times a minute.

DDL (Data Definition Language) changes the structure: CREATE TABLE, ALTER TABLE, DROP COLUMN, CREATE INDEX. These usually run in migrations.

Think of a supermarket. DML is putting a new box of cereal on a shelf. DDL is rebuilding the shelf while customers are still shopping in that aisle.

Why changing the shelf is riskier

1. It locks the whole table, and a queue forms behind it

When you insert a row, Postgres locks only that row. Everyone else carries on.

Most ALTER TABLE statements need an ACCESS EXCLUSIVE lock on the whole table. Nobody can read or write that table until the ALTER finishes.

"But my ALTER only takes 5 ms," you say. The problem is the wait before it runs:

10:00:00  A report query starts reading `orders`. It will take 2 minutes.
10:00:05  Your migration runs: ALTER TABLE orders ADD COLUMN note text;
          It needs an exclusive lock, so it waits for the report.
10:00:06  A customer places an order. Their INSERT queues behind your ALTER.
10:00:07  Another customer... and another... and another.
10:02:00  The report finishes. The ALTER runs in 5 ms. The queue drains.

For two minutes nobody could place an order. Over a 5 ms change. Locks are granted in order, so once your ALTER is waiting, every query that arrives after it waits too.

2. Some changes rewrite every row

Changing a column type (say int to bigint) makes Postgres copy the whole table, and it holds that exclusive lock the entire time. On a table with 50 million rows that can take minutes or hours.

3. It's hard to undo

Insert a bad row and you can delete it. Run DROP COLUMN and that data is gone unless you restore a backup. In MySQL, DDL can't even be rolled back inside a transaction, because each DDL statement commits on its own.

4. Your running code depends on the shape

During a deploy, old and new versions of your app run at the same time. If you rename a column, the old version is still querying the old name. A data change doesn't break running code, but a shape change can.

5. One mistake hits everything

A bad row affects one customer. A bad schema change affects every query that touches the table.

How this mindset changes the way you design

Once you accept that schema changes are expensive, a useful design question follows:

When the business changes next month, will I be adding rows or adding columns?

Aim for rows.

Example 1: rules as rows, not columns

Say you're building delivery fees. The first version often looks like this:

CREATE TABLE shop (
  id                  bigint PRIMARY KEY,
  name                text,
  fee_bangkok         numeric,
  fee_chiang_mai      numeric,
  free_delivery_over  numeric
);

Then the business adds a new province, and you need a migration. Then they want weekend surcharges, and you need another one. Every business decision turns into DDL.

Instead, treat the rule as data:

CREATE TABLE delivery_fee_rule (
  id          bigint PRIMARY KEY,
  shop_id     bigint NOT NULL REFERENCES shop(id),
  province    text   NOT NULL,
  fee         numeric NOT NULL,
  valid_from  date   NOT NULL,
  valid_to    date
);

Adding a new province is now an INSERT, and so is a price change. An ops person can do it from an admin screen without a developer, a migration or a deploy.

The same pattern works for order statuses, payment methods, user roles, and feature flags. Anything the business is likely to change belongs in rows.

Example 2: don't overdo it

The extreme version of "everything is a row" is a single table like this:

CREATE TABLE thing (entity_id bigint, key text, value text);

This is called EAV (entity-attribute-value), and it causes plenty of problems of its own. You lose types, foreign keys, and NOT NULL, and every query becomes a pile of self-joins.

A rule of thumb that works for me:

Things the code needs to understand (an order's total, a user's email) are columns. They're stable and they need real types and constraints.
Things the business changes its mind about (which provinces, which fees, which statuses exist) are rows in a lookup or rule table.

Example 3: when you must change the schema, expand then contract

Sometimes a column really does have to change. Say you want to rename orders.addr to orders.shipping_address. Doing it in one step breaks the old app code mid-deploy. Do it in small steps instead, keeping the app working after each one:

Expand. Add the new column: ALTER TABLE orders ADD COLUMN shipping_address text; (fast, no rewrite).
Dual write. Deploy code that writes to both columns.

Backfill. Copy old data across in small batches. This is DML, so it's safe to stop, retry or slow down:

UPDATE orders SET shipping_address = addr
WHERE id IN (
  SELECT id FROM orders WHERE shipping_address IS NULL LIMIT 5000
);

Switch reads. Deploy code that reads only the new column.
Contract. Once nothing uses addr, drop it in a later release.

That's five steps instead of one, and you can stop safely after any of them.

Example 4: give every migration a timeout

Remember the lock queue from earlier? You can protect against it with one line:

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;

If the ALTER can't get its lock within 3 seconds, it gives up instead of blocking every customer. Retry it a minute later. A migration that fails is much better than a checkout page that hangs.

Two more habits from the same idea:

Use CREATE INDEX CONCURRENTLY so building an index doesn't block writes. It takes longer, but nothing waits on it.
Add a new column as nullable, backfill it, and only add NOT NULL after the data is in place.

How it shapes architecture

The same thinking works one level up.

Each service owns its own tables

If three services read the orders table directly, every schema change has to work for all three, and they deploy separately. If only the order service touches orders and everyone else calls its API, the schema is a private detail. The API is the contract, and you can change the tables behind it.

Ship migrations separately from code

Run the migration first and make sure the currently deployed code still works with it. Then deploy the new code. If something goes wrong, you can roll the code back without needing to roll back the schema.

Keep configuration in data

Feature flags, pricing tiers and per-customer settings stored as rows can be changed in seconds and are easy to audit. Put them in columns or in code and every change needs a release.

APIs follow the same rule

Adding an optional field to an API response is like adding a row: nobody breaks. Renaming or removing a field is like a DDL change, so it needs the same expand-and-contract care.

The question to keep asking

Before you write a migration, ask: could this be a row instead?

Often the answer is no, and that's fine. An order total is a column and always will be. But I've been surprised how often the answer is yes. When it is, the next pricing change is an admin form and a coffee, not a migration at 11pm.