OLTP Modeling: Designing Operational Schemas

01 · Overview

Start here

⏱ 6 min · Topic 1 of 18

OLTP means online transaction processing. It is the database behind an app. Many people read and write a few rows at the same time.

In this chapter the shop is the app, and you design its database. You design it from what the app does, not from what the data looks like.

You already know the parts. Normalization & Denormalization put each fact in one table. Keys & Identity gave each row an id and each link a rule.

Now you add what a live app needs. Many users write at once, and requests are sent twice. A payment must never be charged twice.

What you will learn

List the writes and reads of the app first.
Turn them into tables, with real PostgreSQL code.
Choose safe column types for money and time.
Let constraints refuse bad rows.
Group changes into transactions, and stop double charges.
Keep status history, and choose how to delete.
Add indexes, and change the schema without long locks.

The next chapter, OLTP vs OLAP: Choosing How To Structure Data, covers why analysts get a second copy of this data. This chapter stays inside the app database.

02 · Requirements

Start with the app's writes

⏱ 5 min · Topic 2 of 18

A schema is a plan for the app's work. So start with the work.

A write adds or changes rows. A read only looks at rows. This table lists what the shop must do, and what the database must keep true:

The shop app: writes and reads

What happens	Kind	Tables it touches	What the database must keep true
Register a customer	Write	customers	One account for each email.
Add an address	Write	addresses	An address belongs to a customer.
Place an order	Write	orders, order_items, products	All lines or none. Stock never goes below 0.
Pay an order	Write	payments, orders	One charge for each payment attempt.
Cancel or refund	Write	orders, order_status_history	Only from a legal status. Keep the old status.
Show one order	Read	orders, order_items	Fast, by order_id.
List my orders	Read	orders	Fast, by customer_id, newest first.
Log in with an email	Read	customers	Fast, by email.

Look at the row "Place an order". One click changes three tables. All three must change, or none.

The last column is the most useful one. Each line becomes a constraint, a transaction or an index. An index helps the database find rows fast.

Watch out

Do not start from a report or a screen. A table shaped for a report is hard to write safely. Reports belong to the analyst's copy, in OLTP vs OLAP: Choosing How To Structure Data.

03 · Schema

From writes to tables

⏱ 9 min · Topic 3 of 18

Each table has one grain. The grain is what one row means. Here are the seven tables of the shop, with the grain of each:

customers: one row is one person with an account.
addresses: one row is one address of one customer.
products: one row is one product we sell.
orders: one row is one order placed by one customer.
order_items: one row is one product on one order.
payments: one row is one accepted charge for one order.
order_status_history: one row is one status change of one order.

Here is the PostgreSQL code. The comments after -- explain the new parts:

The shop schemaworked example

SQL

CREATE TABLE customers (
  customer_id BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 470) PRIMARY KEY,
  email       TEXT NOT NULL UNIQUE,
  name        TEXT NOT NULL,
  country     TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at  TIMESTAMPTZ            -- NULL means the account is live
);

CREATE TABLE addresses (
  address_id  BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 470) PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers (customer_id) ON DELETE CASCADE,
  line1       TEXT NOT NULL,
  city        TEXT NOT NULL,
  country     TEXT NOT NULL
);

CREATE TABLE products (
  product_id  BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 470) PRIMARY KEY,
  name        TEXT NOT NULL UNIQUE,
  price       NUMERIC(10,2) NOT NULL CHECK (price >= 0),
  stock_qty   INTEGER NOT NULL CHECK (stock_qty >= 0)
);

CREATE TABLE orders (
  order_id    BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 470) PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers (customer_id) ON DELETE RESTRICT,
  address_id  BIGINT REFERENCES addresses (address_id),
  status      TEXT NOT NULL DEFAULT 'pending'
              CHECK (status IN ('pending', 'paid', 'refunded', 'cancelled')),
  amount      NUMERIC(10,2) CHECK (amount >= 0),
  order_date  DATE NOT NULL,
  version     INTEGER NOT NULL DEFAULT 1,   -- used for safe edits
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  order_id    BIGINT NOT NULL REFERENCES orders (order_id) ON DELETE CASCADE,
  product_id  BIGINT NOT NULL REFERENCES products (product_id),
  quantity    INTEGER NOT NULL CHECK (quantity > 0),
  unit_price  NUMERIC(10,2) NOT NULL CHECK (unit_price >= 0),  -- price when ordered
  PRIMARY KEY (order_id, product_id)
);

CREATE TABLE payments (
  payment_id      BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 470) PRIMARY KEY,
  order_id        BIGINT NOT NULL REFERENCES orders (order_id),
  amount          NUMERIC(10,2) NOT NULL CHECK (amount > 0),
  idempotency_key TEXT NOT NULL UNIQUE,
  paid_at         TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_status_history (
  history_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id   BIGINT NOT NULL REFERENCES orders (order_id) ON DELETE CASCADE,
  old_status TEXT,                      -- NULL on the first row of an order
  new_status TEXT NOT NULL
             CHECK (new_status IN ('pending', 'paid', 'refunded', 'cancelled')),
  changed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

The code is long. Read it one table at a time, with the grain sentence in mind.

Most of this you know from Keys & Identity. Note these parts:

IDENTITY is from Keys & Identity. START WITH 470 makes the first new id 470.
To load the old rows with their own ids, add OVERRIDING SYSTEM VALUE to the INSERT. It does not move the counter, so START WITH must be above the largest old id.
amount has no NOT NULL. Order 105 is a pending order with no amount yet.
unit_price is a copy of the price at order time. A later section explains why.
version, updated_at, deleted_at and the history table each get their own section later.

Here are the shop's rows in this schema. The emails and stock numbers are made up:

customers (5 rows)

customer_id	email	name	country
1	asha@example.com	Asha	IN
2	ben@example.com	Ben	GB
3	carla@example.com	Carla	US
4	dev@example.com	Dev	NULL
5	omar@example.com	Omar	IN

products (5 rows)

product_id	name	price	stock_qty
1	Pen	5.00	100
2	Notebook	30.00	40
3	Bag	25.00	8
4	Lamp	60.00	3
5	Chair	20.00	1

orders (some columns) (6 rows)

order_id	customer_id	status	amount	order_date
101	1	paid	40.00	2026-01-03
102	1	paid	25.00	2026-01-10
103	2	refunded	60.00	2026-01-11
104	3	paid	15.50	2026-01-15
105	3	pending	NULL	2026-01-20
106	4	paid	80.00	2026-01-21

order_items (7 rows)

order_id	product_id	quantity	unit_price
101	1	2	5.00
101	2	1	30.00
102	3	1	25.00
103	4	1	60.00
104	1	3	5.00
106	5	1	20.00
106	4	1	60.00

The order_items rows are the seven you saw before, with unit_price added. Orders 101 to 103 and 106 add up to their amount. For example, order 101 is 2 pens at 5.00 plus 1 notebook at 30.00, which is 40.00.

Order 104 is 15.00 in items but 15.50 in amount. The shop data has always had this gap. Treat the extra 0.50 as a made-up fee that this schema does not store.

04 · Column types

Money and time columns

⏱ 9 min · Topic 4 of 18

Two kinds of column go wrong in many apps: money and time.

Money: use NUMERIC

FLOAT stores numbers in binary. Some decimal values, such as 0.1, cannot be stored exactly. Small errors then appear in sums and comparisons.

NUMERIC(10,2) stores exact decimals: 10 digits in total, 2 after the point. float8 is PostgreSQL's 8-byte FLOAT. Compare the two:

FLOAT and NUMERICworked example

SQL

SELECT 0.1::float8 + 0.2::float8 = 0.3::float8     AS float_equal,
       0.1::numeric + 0.2::numeric = 0.3::numeric AS numeric_equal;

Result · 1 row

float_equal	numeric_equal
f	t

psql, the PostgreSQL command-line tool, prints f for false and t for true. With FLOAT, 0.1 + 0.2 is not equal to 0.3.

A money total built from FLOAT can be off by a small amount. Then it will not match the bank. Use NUMERIC for every amount and price.

Time: use timestamptz

timestamptz means timestamp with time zone. PostgreSQL stores the exact moment in UTC. UTC is the world time standard, and it has no daylight saving.

PostgreSQL shows the moment in the time zone of your session. Set the session to UTC, then to India time, and read the same moment:

One moment, two displaysworked example

SQL

SET TIME ZONE 'UTC';
SELECT TIMESTAMPTZ '2026-01-22 09:00:00+00' AS paid_at;

SET TIME ZONE 'Asia/Kolkata';
SELECT TIMESTAMPTZ '2026-01-22 09:00:00+00' AS paid_at;

What psql prints for each SELECT

session time zone	paid_at
UTC	2026-01-22 09:00:00+00
Asia/Kolkata	2026-01-22 14:30:00+05:30

Both lines are the same moment. India time is 5 hours 30 minutes ahead of UTC.

A plain timestamp column has no zone. The same text can then mean different moments on different servers.

Watch out

Use timestamptz for every moment in time, such as created_at and paid_at. Use DATE only for a calendar day, such as order_date. Convert to a local zone only when you show the value.

05 · Snapshots

Copy the price into the line

⏱ 5 min · Topic 5 of 18

Normalization & Denormalization warned that a price changes. Here is the fix in the schema.

An old order must keep the price the customer paid.

That is why order_items has its own unit_price. The app copies the product price into it when the order is placed. It never looks the price up again.

Say the Lamp price goes up from 60.00 to 65.00. This is a made-up change. Then look at order 103, where Ben bought a Lamp:

Price paid and price nowworked example

SQL

Input data

order_items (one row)1 row

order_id	product_id	quantity	unit_price
103	4	1	60.00

products (one row)1 row

product_id	name	price
4	Lamp	60.00

UPDATE products SET price = 65.00 WHERE product_id = 4;

SELECT i.order_id, p.name,
       i.unit_price AS price_paid,
       p.price      AS price_now
FROM order_items AS i
JOIN products    AS p ON p.product_id = i.product_id
WHERE i.order_id = 103;

Result · 1 row

order_id	name	price_paid	price_now
103	Lamp	60.00	65.00

Ben paid 60.00, and the refund was 60.00. If the app used price_now, the order would show 65.00.

The same idea applies to other facts that change. The address on an order and the product name are examples. Copy them into the order, or never edit the old row and add a new one.

06 · Constraints

Constraints refuse bad rows

⏱ 11 min · Topic 6 of 18

A constraint is a rule that the database checks on every write. If a row breaks the rule, the write fails.

The app can have a bug, and two services can write to the same table. A constraint still holds. It is the last line of defence.

PostgreSQL has five kinds. This table shows what each one refuses in the shop:

The five constraints

Constraint	What it refuses	Example in the shop
PRIMARY KEY	A duplicate or NULL id.	A second order 101.
FOREIGN KEY	A link to a row that does not exist.	An order for customer 9.
UNIQUE	A repeated value.	The same email twice.
NOT NULL	A missing value.	An order line with no price.
CHECK	A value that breaks a rule.	A quantity of 0.

Keys & Identity showed the first two errors. Here are the other three. First, NOT NULL, for an order line with no unit_price:

NOT NULL refuses a missing valueworked example

SQL

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (101, 5, 1);

Error from PostgreSQL

message
ERROR: null value in column "unit_price" of relation "order_items" violates not-null constraint

PostgreSQL adds a DETAIL line that shows the failing row. It is left out here.

The row is not added.

Next, CHECK. A quantity of 0 makes no sense, so the rule is quantity > 0:

CHECK refuses a bad valueworked example

SQL

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (101, 5, 0, 20.00);

Error from PostgreSQL

message
ERROR: new row for relation "order_items" violates check constraint "order_items_quantity_check"

PostgreSQL names a column CHECK as table, column and check. Here it is order_items_quantity_check.

A CHECK can also limit a status to a list of values. Try to set order 101 to a status that is not on the list:

CHECK limits a statusworked example

SQL

UPDATE orders
SET status = 'shipped'
WHERE order_id = 101;

Error from PostgreSQL

message
ERROR: new row for relation "orders" violates check constraint "orders_status_check"

The list is paid, pending, refunded and cancelled. Adding "shipped" needs a schema change first.

Last, UNIQUE. Someone signs up again with the email of Asha:

UNIQUE refuses a repeatworked example

SQL

INSERT INTO customers (email, name, country)
VALUES ('asha@example.com', 'Asha', 'IN');

Error from PostgreSQL

message
ERROR: duplicate key value violates unique constraint "customers_email_key"
DETAIL: Key (email)=(asha@example.com) already exists.

The insert fails. A failed insert still uses up an id, so the counter moves on.

Watch out

A CHECK passes when its value is NULL. The rule amount >= 0 accepts order 105, which has no amount. Add NOT NULL when the value must always be there.

What a delete does

Each foreign key also needs a delete rule, called ON DELETE. Keys & Identity showed the ON DELETE rules. This table shows the choice for the main links in the shop:

Delete rules in the shop

Link	Rule	Why
orders to customers	RESTRICT	A customer with orders cannot be deleted.
order_items to orders	CASCADE	Lines belong to their order. They go with it.
addresses to customers	CASCADE	An address has no meaning without its customer.
payments to orders	None set (NO ACTION)	An order with a payment cannot be deleted.

RESTRICT refuses the delete. CASCADE deletes the children too. If you set no rule, PostgreSQL uses NO ACTION, which also refuses the delete.

07 · Transactions

Transactions: all or nothing

⏱ 9 min · Topic 7 of 18

A transaction is a group of changes that succeed together or fail together.

BEGIN starts it. COMMIT saves it. ROLLBACK undoes it.

Omar (customer 5) buys 1 Bag. The made-up stock is 8. The app locks the stock row, saves the order, and lowers the stock:

Place an order in one transactionworked example

SQL

Input data

products (one row)1 row

product_id	name	stock_qty
3	Bag	8

BEGIN;

SELECT stock_qty FROM products
WHERE product_id = 3
FOR UPDATE;

INSERT INTO orders (customer_id, status, amount, order_date)
VALUES (5, 'pending', 25.00, '2026-01-22')
RETURNING order_id;

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (470, 3, 1, 25.00);

UPDATE products SET stock_qty = stock_qty - 1
WHERE product_id = 3;

INSERT INTO order_status_history (order_id, old_status, new_status)
VALUES (470, NULL, 'pending');

COMMIT;

What PostgreSQL prints

statement	output
BEGIN	BEGIN
SELECT ... FOR UPDATE	stock_qty = 8
INSERT INTO orders ... RETURNING	order_id = 470
INSERT INTO order_items	INSERT 0 1
UPDATE products	UPDATE 1
INSERT INTO order_status_history	INSERT 0 1
COMMIT	COMMIT

RETURNING gives back the id that the database made. The app uses 470 in the next statement. The history table keeps each status change.

All four changes are saved at COMMIT, or none are. Nobody sees an order without its line, or a line without its stock change.

The app checks the stock before it saves. The CHECK on stock_qty is the backstop. Now Omar buys 2 Chairs, but the made-up stock is 1:

A failed step undoes everythingworked example

SQL

Input data

products (one row)1 row

product_id	name	stock_qty
5	Chair	1

BEGIN;

INSERT INTO orders (customer_id, status, amount, order_date)
VALUES (5, 'pending', 40.00, '2026-01-22');

UPDATE products SET stock_qty = stock_qty - 2
WHERE product_id = 5;

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (471, 5, 2, 20.00);

ROLLBACK;

What PostgreSQL prints

statement	output
BEGIN	BEGIN
INSERT INTO orders	INSERT 0 1
UPDATE products	ERROR: new row for relation "products" violates check constraint "products_stock_qty_check"
INSERT INTO order_items	ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK	ROLLBACK

The first insert worked, but ROLLBACK undoes it. No half order remains. Id 471 is used up, so the next order gets 472.

Two buyers, one Chair

Now two buyers want the last Chair at the same time. PostgreSQL runs each statement on the data committed before it started. This is the READ COMMITTED level, the default.

Alone, that does not stop both buyers from reading a stock of 1. FOR UPDATE locks the row, so the second buyer waits. Follow the steps:

Two buyers and a lock

Step	Buyer A	Buyer B
1	BEGIN; SELECT stock_qty ... FOR UPDATE. Gets 1.
2		BEGIN; the same SELECT ... FOR UPDATE. It waits for the lock.
3	UPDATE stock_qty to 0. COMMIT.	Still waiting.
4		The lock is free. The SELECT now returns 0. The app refuses the order and rolls back.

Buyer B sees the new value 0, not the old 1. After the wait, FOR UPDATE reads the newest saved row. Only buyer A gets the Chair.

Keep transactions short

A lock lasts until COMMIT or ROLLBACK. Do not wait for a user or a slow web call inside a transaction. If two transactions lock the same rows in different orders, PostgreSQL stops one with "deadlock detected".

08 · Payments

Stop double charges

⏱ 8 min · Topic 8 of 18

A retry is when the app sends the same request again, for example after a time-out. Without care, the customer pays twice.

An idempotency key is an id that the app makes once for each payment. Every retry sends the same key. A UNIQUE rule on payments.idempotency_key lets only one row in.

Omar pays for order 470. The made-up key is key-8d41. The app saves the payment and the status change in one transaction:

Pay order 470worked example

SQL

BEGIN;

INSERT INTO payments (order_id, amount, idempotency_key)
VALUES (470, 25.00, 'key-8d41');

UPDATE orders
SET status = 'paid', version = version + 1, updated_at = now()
WHERE order_id = 470 AND status = 'pending';

INSERT INTO order_status_history (order_id, old_status, new_status)
VALUES (470, 'pending', 'paid');

COMMIT;

What PostgreSQL prints

statement	output
BEGIN	BEGIN
INSERT INTO payments	INSERT 0 1
UPDATE orders	UPDATE 1
INSERT INTO order_status_history	INSERT 0 1
COMMIT	COMMIT

The first payment gets id 470. AND status = 'pending' changes only a pending order. If it prints UPDATE 0, the app rolls back, so no payment is kept.

The network times out, and the app does not know if the payment worked. It retries with the same key:

The retry is refusedworked example

SQL

Input data

payments (one row)1 row

payment_id	order_id	amount	idempotency_key
470	470	25.00	key-8d41

INSERT INTO payments (order_id, amount, idempotency_key)
VALUES (470, 25.00, 'key-8d41');

Error from PostgreSQL

message
ERROR: duplicate key value violates unique constraint "payments_idempotency_key_key"
DETAIL: Key (idempotency_key)=(key-8d41) already exists.

There is still one payment. The customer is charged once.

The app should not show an error to Omar. ON CONFLICT DO NOTHING skips the insert without an error. Then the app reads the stored payment and gives the same answer as the first time:

Skip the repeat, read the first answerworked example

SQL

INSERT INTO payments (order_id, amount, idempotency_key)
VALUES (470, 25.00, 'key-8d41')
ON CONFLICT (idempotency_key) DO NOTHING;

SELECT payment_id, amount
FROM payments
WHERE idempotency_key = 'key-8d41';

Result · 1 row

payment_id	amount
470	25.00

The INSERT prints INSERT 0 0, because no row was added. The SELECT returns the first payment.

Watch out

The UNIQUE rule only protects your own table. Send the same key to the card provider too. Many payment providers accept an idempotency key for this.

Interview question

"How do you stop a customer from being charged twice?" Answer: the app makes one key for each payment. It reuses the key on retries. A UNIQUE constraint lets one row in.

09 · Edits

Two people edit one row

⏱ 6 min · Topic 9 of 18

Omar and a support agent both open order 470 before it ships. Both change the delivery address.

The second save would silently erase the first. This is a lost update.

Optimistic locking stops it. Each row has a version number. An update says: change the row only if the version is still the one I read.

Omar has two made-up addresses. Both people read order 470 at version 2. The order became version 2 when it was paid:

The first save winsworked example

SQL

Input data

addresses (made up)2 rows

address_id	customer_id	line1	city
470	5	12 Park Road	Pune
471	5	8 Lake View	Mumbai

orders (one row)1 row

order_id	status	address_id	version
470	paid	NULL	2

-- Omar picks address 470.
UPDATE orders
SET address_id = 470, version = version + 1, updated_at = now()
WHERE order_id = 470 AND version = 2;

-- A moment later, the agent picks address 471.
UPDATE orders
SET address_id = 471, version = version + 1, updated_at = now()
WHERE order_id = 470 AND version = 2;

What PostgreSQL prints

statement	output
First UPDATE	UPDATE 1
Second UPDATE	UPDATE 0

After the first save, the version is 3. The second UPDATE finds no row with version 2, so it changes nothing.

The app sees UPDATE 0 and tells the agent: "This order changed. Reload it." The agent decides again, with the new data.

You can also lock the row with SELECT ... FOR UPDATE, as in the stock example. Compare the two:

Two ways to protect an edit

	Optimistic (version column)	Pessimistic (FOR UPDATE)
How it works	No lock. The update fails if the version moved.	The row is locked while you work.
Best when	Clashes are rare, such as a form a person edits.	Clashes are common, such as the last Chair.
Cost	The loser must retry.	Others wait, and a long lock hurts.

10 · History

Status and its history

⏱ 7 min · Topic 10 of 18

The status column in orders holds the current state. It forgets how the order got there. A history table keeps every change as a new row.

The schema above has order_status_history, with one row for each change. The first row of an order has old_status NULL, because a new order had no status before. The app only adds rows here.

Order 103 (Ben) went from pending to paid to refunded. The times are made up. This query reads its path:

The path of order 103worked example

SQL

Input data

order_status_history (made-up times, UTC)5 rows

order_id	old_status	new_status	changed_at
103	NULL	pending	2026-01-11 08:00:00+00
103	pending	paid	2026-01-11 08:02:00+00
103	paid	refunded	2026-01-13 10:30:00+00
470	NULL	pending	2026-01-22 09:00:00+00
470	pending	paid	2026-01-22 09:01:30+00

SELECT old_status, new_status, changed_at
FROM order_status_history
WHERE order_id = 103
ORDER BY changed_at;

Result · 3 rows

old_status	new_status	changed_at
NULL	pending	2026-01-11 08:00:00+00
pending	paid	2026-01-11 08:02:00+00
paid	refunded	2026-01-13 10:30:00+00

The orders table says only "refunded". The history says Ben paid in 2 minutes and was refunded 2 days later.

Now guard the change itself. A second refund request arrives for order 103. The WHERE clause names the status you expect:

A second refund does nothingworked example

SQL

UPDATE orders
SET status = 'refunded', version = version + 1, updated_at = now()
WHERE order_id = 103 AND status = 'paid';

What PostgreSQL prints

output
UPDATE 0

Order 103 is already refunded, so no row matches. The app sees 0 rows and writes no history row.

Write the status change and its history row in one transaction, as in the payment example. Then they cannot disagree.

Two ways to limit a status

	CHECK with a list	enum type
Add a value	Drop and add the CHECK rule.	ALTER TYPE ... ADD VALUE.
Remove a value	Drop and add the CHECK rule.	PostgreSQL has no command for this.
Error message	violates check constraint	invalid input value for enum

Both work. A CHECK list is easier to change, so the shop uses it.

11 · Access paths

Indexes for the app's queries

⏱ 8 min · Topic 11 of 18

An index is a second structure that finds rows fast, like the index of a book. PostgreSQL makes one for every PRIMARY KEY and every UNIQUE rule.

PostgreSQL does not make one for a foreign key. You add it. Start from the app's reads and ask which column each one filters on:

Queries and their indexes

Query	Filters on	Index PostgreSQL may use
Show one order	orders.order_id	The primary key index of orders.
Show its lines	order_items.order_id	The primary key (order_id, product_id). It starts with order_id.
Log in with an email	customers.email	The unique index of customers_email_key.
List my orders, newest first	orders.customer_id, order_date	None yet. You add one.
Find the payments of an order	payments.order_id	None yet. You add one.
Which orders have product 4?	order_items.product_id	None yet. You add one.

The three "None yet" rows are foreign key columns or sorted lists. Add one index for each:

Three indexesworked example

SQL

CREATE INDEX orders_customer_date_idx
  ON orders (customer_id, order_date DESC);

CREATE INDEX payments_order_idx
  ON payments (order_id);

CREATE INDEX order_items_product_idx
  ON order_items (product_id);

The first index holds each customer's orders already sorted, newest first.

Now run the list-my-orders query for Asha (customer 1):

List my ordersworked example

SQL

Input data

orders (some columns)6 rows

order_id	customer_id	status	amount	order_date
101	1	paid	40.00	2026-01-03
102	1	paid	25.00	2026-01-10
103	2	refunded	60.00	2026-01-11
104	3	paid	15.50	2026-01-15
105	3	pending	NULL	2026-01-20
106	4	paid	80.00	2026-01-21

SELECT order_id, status, amount, order_date
FROM orders
WHERE customer_id = 1
ORDER BY order_date DESC;

Result · 2 rows

order_id	status	amount	order_date
102	paid	25.00	2026-01-10
101	paid	40.00	2026-01-03

Asha has orders 101 and 102. The newest, 102, comes first.

The planner decides how to run a query. On a table this small, PostgreSQL will probably read the whole table, because that is cheapest.

With millions of rows, PostgreSQL may choose the index. Use EXPLAIN to see its choice.

Watch out

Every index makes writes slower and uses disk. Add one for each real query, not for every column. Without an index on a foreign key, deleting a parent row reads the whole child table.

12 · Tenancy

Many shops in one database

⏱ 6 min · Topic 12 of 18

A platform can host many shops in one database. Each shop is a tenant. Their rows sit in the same tables, so a query must never mix them.

The fix is a tenant_id column in every table, and keys that include it. This example is separate from the shop schema. Shop 10 is ours, and shop 20 is made up:

Keys that include tenant_idworked example

SQL

Input data

customers3 rows

tenant_id	customer_id	name
10	1	Asha
10	5	Omar
20	8	Zara

orders3 rows

tenant_id	order_id	customer_id
10	101	1
10	102	1
20	101	8

CREATE TABLE customers (
  tenant_id   BIGINT NOT NULL,
  customer_id BIGINT NOT NULL,
  name        TEXT   NOT NULL,
  PRIMARY KEY (tenant_id, customer_id)
);

CREATE TABLE orders (
  tenant_id   BIGINT NOT NULL,
  order_id    BIGINT NOT NULL,
  customer_id BIGINT NOT NULL,
  PRIMARY KEY (tenant_id, order_id),
  FOREIGN KEY (tenant_id, customer_id)
    REFERENCES customers (tenant_id, customer_id)
);

Both shops have an order 101. The pair (tenant_id, order_id) is the key, so there is no clash.

The foreign key is also a pair. It checks that the customer belongs to the same tenant. Try to give shop 20 an order for customer 1, who belongs to shop 10:

No order across shopsworked example

SQL

INSERT INTO orders (tenant_id, order_id, customer_id)
VALUES (20, 102, 1);

Error from PostgreSQL

message
ERROR: insert or update on table "orders" violates foreign key constraint "orders_tenant_id_customer_id_fkey"
DETAIL: Key (tenant_id, customer_id)=(20, 1) is not present in table "customers".

Customer 1 exists, but not in shop 20. A foreign key on customer_id alone would have accepted this row.

Every query must name the tenant too. This one reads order 101 of shop 20:

Always filter by tenantworked example

SQL

SELECT o.order_id, c.name
FROM orders AS o
JOIN customers AS c
  ON c.tenant_id = o.tenant_id AND c.customer_id = o.customer_id
WHERE o.tenant_id = 20 AND o.order_id = 101;

Result · 1 row

order_id	name
101	Zara

Without the tenant_id filter, order 101 would match two rows, one from each shop.

There are other designs: one schema or one database for each tenant. They separate tenants more, but they cost more to run and to change. PostgreSQL also has row-level security, which can add the tenant filter for you.

13 · Lifecycle

Soft delete or hard delete

⏱ 9 min · Topic 13 of 18

A hard delete removes the row. A soft delete keeps the row and marks it as deleted, with a deleted_at column.

Omar now has order 470. Try to hard delete him:

The foreign key refusesworked example

SQL

DELETE FROM customers
WHERE customer_id = 5;

Error from PostgreSQL

message
ERROR: update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
DETAIL: Key (customer_id)=(5) is still referenced from table "orders".

The order of Omar is a financial record. The database keeps his row. A soft delete is the way to close the account.

A soft delete sets deleted_at. Then every query for live customers must add WHERE deleted_at IS NULL:

Soft delete Omarworked example

SQL

Input data

customers (emails are made up)5 rows

customer_id	email	name	country
1	asha@example.com	Asha	IN
2	ben@example.com	Ben	GB
3	carla@example.com	Carla	US
4	dev@example.com	Dev	NULL
5	omar@example.com	Omar	IN

UPDATE customers
SET deleted_at = '2026-01-25 10:00:00+00',
    updated_at = '2026-01-25 10:00:00+00'
WHERE customer_id = 5;

SELECT customer_id, name
FROM customers
WHERE deleted_at IS NULL
ORDER BY customer_id;

Live customers

customer_id	name
1	Asha
2	Ben
3	Carla
4	Dev

Omar is still in the table, so order 470 still has a customer. Only the filter hides him.

Soft delete has a cost. Omar now signs up again with omar@example.com. The old row still holds that email, so UNIQUE refuses the new account:

The old email blocks the new accountworked example

SQL

INSERT INTO customers (email, name, country)
VALUES ('omar@example.com', 'Omar', 'IN');

Error from PostgreSQL

message
ERROR: duplicate key value violates unique constraint "customers_email_key"
DETAIL: Key (email)=(omar@example.com) already exists.

The deleted account still owns the email.

Replace the plain UNIQUE rule with a partial unique index. It checks only live rows:

Unique only among live rowsworked example

SQL

ALTER TABLE customers DROP CONSTRAINT customers_email_key;

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

Two live accounts cannot share an email. A deleted row no longer blocks a new account. The same insert would now work.

Choose by what the row means:

Hard delete or soft delete

	Hard delete	Soft delete
The row	Gone for good.	Stays, marked with deleted_at.
Rows that point at it	The foreign key refuses, or CASCADE removes them too.	They keep working.
Undo	Only from a backup.	Set deleted_at back to NULL.
Queries	No extra filter.	Every query needs WHERE deleted_at IS NULL.
Use it for	Temporary rows, such as an empty cart.	Accounts and records you may need later.

Retention is how long you keep data. Keep money records as long as the law requires.

Some laws also require you to remove or anonymise personal data on request. Ask your legal team.

14 · Change

Change the schema safely

⏱ 8 min · Topic 14 of 18

The app keeps running while you change the schema. A change that locks a big table can stop every order. Say the shop must now record the sales channel of each order.

The obvious way is one step. Add a NOT NULL column. It fails, because old orders have no value:

One step failsworked example

SQL

Input data

orders (7 rows: 101 to 106, and 470)7 rows

order_id
101
102
103
104
105
106
470

ALTER TABLE orders ADD COLUMN channel TEXT NOT NULL;

Error from PostgreSQL

message
ERROR: column "channel" of relation "orders" contains null values

The seven existing orders would all get NULL, which NOT NULL refuses.

The safe way has steps. Step 1: add the column, and let it be NULL:

Step 1: add a nullable columnworked example

SQL

ALTER TABLE orders ADD COLUMN channel TEXT;

SELECT COUNT(*) AS missing
FROM orders
WHERE channel IS NULL;

Result · 1 row

missing
7

Adding a column that can be NULL is quick in PostgreSQL. It does not rewrite the table. All 7 orders have no channel yet.

Step 2: deploy the app so every new order writes a channel. Step 3: fill the old rows in small batches. The made-up value is web:

Step 3: backfill in batchesworked example

SQL

UPDATE orders
SET channel = 'web'
WHERE order_id IN (
  SELECT order_id FROM orders
  WHERE channel IS NULL
  ORDER BY order_id
  LIMIT 1000
);

SELECT COUNT(*) AS missing
FROM orders
WHERE channel IS NULL;

Result · 1 row

missing
0

The UPDATE prints UPDATE 7, because only 7 rows exist. On a big table, repeat it until it prints UPDATE 0.

Step 4: now add the rule. Step 5: build the index:

Steps 4 and 5: the rule and the indexworked example

SQL

ALTER TABLE orders ALTER COLUMN channel SET NOT NULL;

CREATE INDEX CONCURRENTLY orders_channel_idx
  ON orders (channel);

Plain SET NOT NULL scans the whole table while it holds a strong lock. CONCURRENTLY builds the index without blocking writes, but it cannot run inside BEGIN … COMMIT.

Locks and indexes

On a big table, add CHECK (channel IS NOT NULL) NOT VALID first, then VALIDATE CONSTRAINT it. In PostgreSQL 12 and later, SET NOT NULL then skips the scan. Drop the invalid index that a failed CREATE INDEX CONCURRENTLY leaves.

Watch out

An ALTER TABLE waits for every other transaction that uses the table. New queries then wait behind it. Run SET lock_timeout = '2s' first, so it gives up fast instead of blocking the app.

15 · Data engineers

What data engineers read

⏱ 6 min · Topic 15 of 18

Data engineers copy rows out of this database into a warehouse. A good operational schema makes that easy. It already has most of what they need.

Primary keys that never change, so a row keeps its identity.
created_at and updated_at as timestamptz, so they can read only new or changed rows.
Deletes that they can see, with a soft delete or with change data capture.
A status history, so they know the path and not only the last state.

PostgreSQL has no column that updates itself on every change. Set updated_at in the app, or with a trigger. A trigger is code that the database runs on each change.

Say a job runs each day and reads rows changed since 2026-01-05. These customer rows have made-up times. Omar was soft deleted in the delete section:

Read what changedworked example

SQL

Input data

customers (made-up times)5 rows

customer_id	name	updated_at	deleted_at
1	Asha	2026-01-08 10:00:00+00	NULL
2	Ben	2026-01-01 00:00:00+00	NULL
3	Carla	2026-01-01 00:00:00+00	NULL
4	Dev	2026-01-01 00:00:00+00	NULL
5	Omar	2026-01-25 10:00:00+00	2026-01-25 10:00:00+00

SELECT customer_id, name, updated_at, deleted_at
FROM customers
WHERE updated_at > '2026-01-05 00:00:00+00'
ORDER BY customer_id;

Result · 2 rows

customer_id	name	updated_at	deleted_at
1	Asha	2026-01-08 10:00:00+00	NULL
5	Omar	2026-01-25 10:00:00+00	2026-01-25 10:00:00+00

The job sees Omar and his deleted_at, so the warehouse can mark him deleted. If Omar had been hard deleted, the job would not see him at all.

A query on updated_at cannot see a row that no longer exists. Log-based change data capture reads the database log, so it sees every insert, update and delete.

Review checklist

⏱ 4 min · Topic 16 of 18

Before you ship a schema, check these eight things:

Every table has a one-sentence grain and a primary key.
Every link is a foreign key, with a chosen ON DELETE rule.
Money is NUMERIC, and every moment is timestamptz in UTC.
Rules that must never break are NOT NULL, UNIQUE or CHECK.
Each business action is one short transaction. Stock rows are locked.
Every payment has an idempotency key with a UNIQUE rule.
Foreign keys and frequent filters have indexes.
Deletes, status changes and migrations are planned, with no long locks.

Interview question

"Design the database for an online shop checkout." Walk through the writes, tables and constraints. Then show one transaction, the idempotency key and the indexes. Say what you would lock.

17 · Practice

Practice lab

⏱ 3 min · Topic 17 of 18

Each exercise opens an ERD canvas. You draw the tables, and the review checks them against seeded rows.

Start at the top. One exercise is harder and is marked optional.

Build it Practice Lab

1Support ticket lifecycleA ticket moves through states. One owner at a time. Keep every change.Intermediate · Intervals · ExclusivityOpen 2Fulfillment networkStore a parcel as a list of scans. Where it is now is the latest scan.Foundations · Event log · TrackingOpen 3Orders you can trust eight months laterKeep an order exactly as placed. Stop a double submit from making two orders.Intermediate · Idempotency · SnapshotOpen 4Seat holds and the release-night raceA hold lasts ten minutes and ends by itself. A constraint decides who wins the race.Intermediate · Concurrency · ExpiryOpen 5Dispatch that can't double-book a driverTwo dispatchers pick one driver in the same second. App code cannot stop it. A constraint can.Intermediate · Concurrency · RolesOpen 6Chats, members and read receiptsOptional. Harder. One message goes to a group of 200 people. Decide what you store and what you count.Intermediate · WhatsApp-style · Fan-outOpen

18 · Next Chapter

Next Chapter

You can now design a safe app database. It has constraints, transactions, indexes and a history.

⏱ 3 min · Topic 18 of 18

Next chapter

OLTP vs OLAP: Choosing How To Structure Data

You can now design a safe app database. It has constraints, transactions, indexes and a history.

The next chapter shows why analysts need a different shape for the same data. It also shows how they get a copy without slowing the app.

Preview chapter 07