E-Commerce Schema Design
Designing an e-commerce schema is a rite of passage for backend engineers. The domain appears simple — products, orders, payments — but the details are brutal: variant combinations, inventory races, price histories, tax calculation, and order state machines. This page presents a production-grade schema with commentary on why each decision was made.
The page is built in the order you would design it for real: first the requirements and access patterns, then the entities and tables, then the hot-path queries and the indexes they need, then the concurrency hot spots (overselling, double charging), and finally how the schema evolves as traffic grows. It ends with common mistakes and an interview-style walkthrough. If you have not modelled entities before, read ER Modeling & Schema Design first; for general OLTP principles see OLTP Database Design.
Requirements & Access Patterns
A schema is only "good" relative to the queries it has to serve. Before drawing a single table, write down what the system does and how often. The numbers below describe a mid-sized store: 20 million registered users, 5 million SKUs, about 500,000 orders per day, and seasonal sales that spike traffic 10 to 20 times.
| Operation | Steady rate | Peak rate | Read or write | Latency target | Notes |
|---|---|---|---|---|---|
| Product detail page (PDP) | 3,000/s | 30,000/s | Read | p99 < 50 ms | Mostly served from cache/CDN; DB sees cache misses |
| Category listing | 1,500/s | 15,000/s | Read | p99 < 100 ms | Filter by category, sort by newness/price, paginate |
| Free-text search | 800/s | 8,000/s | Read | p99 < 150 ms | Belongs in a search engine, not Postgres, at this scale |
| Add/update cart line | 200/s | 3,000/s | Write | p99 < 50 ms | Very high abandon rate; cheap, disposable data |
| Checkout (create order + reserve stock) | 6/s | 100/s | Write | p99 < 300 ms | Must never oversell, must never double charge |
| Flash-sale checkout on one SKU | 0 | 2,000/s | Write | Best effort | The single hottest row in the system |
| Payment webhook | 6/s | 100/s | Write | Async | Gateways retry, so duplicates are normal |
| Order history ("My orders") | 300/s | 2,000/s | Read | p99 < 100 ms | Per-user, newest first, paginated |
| Ops dashboard: orders by status | 20/s | 50/s | Read | p99 < 500 ms | "Paid but not shipped for 48 h" |
| Stock adjustments (warehouse feeds) | 50/s | 500/s | Write | Async | Batch-friendly |
| Price changes | 5/s | 5,000/s | Write | Async | Bulk repricing before a sale |
Three things fall out of this table immediately:
- Reads outnumber writes by roughly 100 to 1 on the catalog, so the catalog tables are optimised for reading and are heavily cached. Orders and inventory are the opposite: write-critical, correctness-critical, and never served stale.
- Checkout is low volume but high stakes. Six orders per second is trivial for Postgres; the design effort goes into correctness (no overselling, no duplicate orders, no double charges), not throughput.
- One row can become the bottleneck. A flash sale concentrates thousands of writes per second on the inventory row of one SKU. That is a concurrency problem, not a capacity problem, and it shapes the inventory design below.
The hot queries, which drive every index decision on this page:
| # | Hot query | Index it needs |
|---|---|---|
| Q1 | Product by slug | UNIQUE (slug) on products |
| Q2 | Active products in a category, newest first, page of 20 | (category_id, created_at DESC) WHERE is_active |
| Q3 | Variants and available stock for a product | (product_id) on variants, UNIQUE (variant_id, warehouse) on inventory |
| Q4 | A user's orders, newest first | (user_id, created_at DESC) on orders |
| Q5 | Line items for an order | (order_id) on order_items |
| Q6 | Payment by gateway transaction id (webhooks) | UNIQUE (gateway, gateway_txn_id) |
| Q7 | Open orders by status, oldest first (ops) | partial (status, created_at) on non-terminal statuses |
| Q8 | Expired stock reservations to release | partial (expires_at) WHERE status = 'held' |
| Q9 | Price of a variant at a point in time | GiST exclusion index on (variant_id, valid_during) |
Entity Relationship Overview
erDiagram
USERS ||--o{ ADDRESSES : has
USERS ||--o{ ORDERS : places
USERS ||--o{ REVIEWS : writes
USERS ||--o{ CART_ITEMS : adds
PRODUCTS ||--o{ PRODUCT_VARIANTS : has
PRODUCTS ||--o{ PRODUCT_IMAGES : has
PRODUCTS }o--|| CATEGORIES : belongs_to
PRODUCTS ||--o{ REVIEWS : receives
PRODUCT_VARIANTS ||--o{ INVENTORY : tracked_by
PRODUCT_VARIANTS ||--o{ ORDER_ITEMS : ordered_as
ORDERS ||--o{ ORDER_ITEMS : contains
ORDERS ||--o{ PAYMENTS : paid_by
ORDERS }o--o| ADDRESSES : shipped_to
ORDERS ||--o{ ORDER_EVENTS : logs
Correction (2026-11-08)
An earlier version drew ORDERS ||--|| PAYMENTS and ORDERS ||--|| ADDRESSES. Both were too strict. An order routinely has several payment rows: a declined card followed by a successful one, a split payment (gift card plus card), or a later refund. The payments table below already allowed this (its order_id is not unique). And one address is used by many orders, while orders actually store a JSONB snapshot of the address rather than a foreign key, so the link is optional and historical.
The fuller logical model, with the tables this page adds (carts, reservations, price history, idempotency keys, payment events), looks like this. Attributes are trimmed to keys and the columns that matter for relationships:
erDiagram
USERS ||--o{ CARTS : owns
CARTS ||--o{ CART_LINES : contains
PRODUCT_VARIANTS ||--o{ CART_LINES : "added as"
PRODUCT_VARIANTS ||--o{ PRICE_HISTORY : "priced by"
USERS ||--o{ IDEMPOTENCY_KEYS : sends
IDEMPOTENCY_KEYS |o--o| ORDERS : "produced"
ORDERS ||--o{ INVENTORY_RESERVATIONS : holds
INVENTORY ||--o{ INVENTORY_RESERVATIONS : "reserved from"
PAYMENTS ||--o{ PAYMENT_EVENTS : "updated by"
ORDERS ||--o{ PAYMENTS : "paid by"
USERS {
bigint id PK
text email UK
}
CARTS {
uuid id PK
bigint user_id FK "nullable for guests"
text session_token UK "guest carts"
text status "active, converted, merged, abandoned"
}
CART_LINES {
uuid cart_id PK, FK
bigint variant_id PK, FK
int quantity
}
PRODUCT_VARIANTS {
bigint id PK
bigint product_id FK
text sku UK
int price_cents "current price, cached"
}
PRICE_HISTORY {
bigint variant_id FK
tstzrange valid_during "no overlaps per variant"
int price_cents
}
INVENTORY {
bigint id PK
bigint variant_id FK
text warehouse
int quantity
int reserved
}
INVENTORY_RESERVATIONS {
bigint id PK
bigint order_id FK
bigint variant_id FK
int quantity
text status "held, committed, released"
timestamptz expires_at
}
ORDERS {
bigint id PK
bigint user_id FK
text idempotency_key "unique per user"
order_status status
int total_cents
}
IDEMPOTENCY_KEYS {
bigint user_id PK, FK
text idem_key PK
bigint order_id FK
}
PAYMENTS {
bigint id PK
bigint order_id FK
text gateway
text gateway_txn_id "unique per gateway"
payment_status status
}
PAYMENT_EVENTS {
bigint id PK
text gateway
text gateway_event_id "unique per gateway"
bigint payment_id FK
}
Two modelling rules from ER modeling do most of the work here:
- M:N becomes a junction table with attributes. A cart and a variant are M:N;
cart_linesis the junction and carriesquantity. An order and a variant are M:N;order_itemsis the junction and carries the captured price. - Historical facts are separate facts. The price on an order line, the address on an order, and the price in
price_historyall look like duplicates of a current value. They are not. They are what was true at a point in time, and they must not change when the current value changes.
Core Tables
Users & Addresses
CREATE TABLE users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
full_name TEXT NOT NULL,
phone TEXT,
is_verified BOOLEAN DEFAULT FALSE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE addresses (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
label TEXT DEFAULT 'home', -- 'home', 'work', 'other'
line1 TEXT NOT NULL,
line2 TEXT,
city TEXT NOT NULL,
state TEXT NOT NULL,
postal_code TEXT NOT NULL,
country TEXT NOT NULL DEFAULT 'US',
is_default BOOLEAN DEFAULT FALSE,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_addresses_user ON addresses(user_id);
Why Separate Addresses?
Users have multiple shipping/billing addresses. Embedding them in the users table would require arrays or JSON, losing the ability to query and index individual address fields.
Two constraints worth adding in production:
-- Case-insensitive email uniqueness: 'Asha@x.com' and 'asha@x.com' are the same person.
-- (Alternatively use the citext extension for the column type.)
CREATE UNIQUE INDEX uq_users_email_lower ON users (lower(email));
-- At most one default address per user. A partial unique index enforces
-- "only one row where is_default is true" without a trigger.
CREATE UNIQUE INDEX uq_addresses_one_default
ON addresses (user_id) WHERE is_default;
The partial unique index is a pattern you will reuse all over this schema: "at most one active cart per user", "at most one open reservation per order line". It turns a business rule into a constraint the database enforces under concurrency, which application code cannot do reliably.
Products & Variants
CREATE TABLE categories (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
slug TEXT NOT NULL UNIQUE,
parent_id BIGINT REFERENCES categories(id),
sort_order INT DEFAULT 0
);
CREATE TABLE products (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL,
slug TEXT NOT NULL UNIQUE,
description TEXT,
category_id BIGINT REFERENCES categories(id),
brand TEXT,
is_active BOOLEAN DEFAULT TRUE,
metadata JSONB DEFAULT '{}', -- flexible attributes
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE product_variants (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
sku TEXT NOT NULL UNIQUE,
name TEXT NOT NULL, -- e.g., "Red / Large"
price_cents INT NOT NULL, -- store money as cents
compare_price INT, -- original price for "was $X"
weight_grams INT,
attributes JSONB DEFAULT '{}', -- {"color": "red", "size": "L"}
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE product_images (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
variant_id BIGINT REFERENCES product_variants(id) ON DELETE SET NULL,
url TEXT NOT NULL,
alt_text TEXT,
sort_order INT DEFAULT 0,
is_primary BOOLEAN DEFAULT FALSE
);
CREATE INDEX idx_products_category ON products(category_id) WHERE is_active;
CREATE INDEX idx_products_slug ON products(slug);
CREATE INDEX idx_variants_product ON product_variants(product_id);
CREATE INDEX idx_variants_sku ON product_variants(sku);
CREATE INDEX idx_images_product ON product_images(product_id);
Money as Integers
Never store money as FLOAT or DOUBLE. Use INT (cents) or NUMERIC(12,2). A price of $19.99 is stored as 1999. This avoids floating-point rounding errors that can cause financial discrepancies.
Correction (2026-11-08): two redundant indexes
idx_products_slug and idx_variants_sku duplicate indexes PostgreSQL already created. A UNIQUE constraint is implemented as a unique B-tree index, so slug TEXT NOT NULL UNIQUE already gives you an index on slug. The extra indexes double the write cost of every insert and update to those columns and buy nothing. In a real migration, drop them. See the redundant-index anti-pattern in Indexing Deep Dive.
Constraints and indexes the product tables need beyond the basics:
-- Prices must be non-negative and "compare at" must actually be higher.
ALTER TABLE product_variants
ADD CONSTRAINT chk_price_nonneg CHECK (price_cents >= 0),
ADD CONSTRAINT chk_compare_gt_price
CHECK (compare_price IS NULL OR compare_price > price_cents);
-- One primary image per product.
CREATE UNIQUE INDEX uq_images_one_primary
ON product_images (product_id) WHERE is_primary;
-- Q2: category listing sorted by newness. Replaces the need to sort
-- every active product in the category (see the EXPLAIN section).
CREATE INDEX idx_products_cat_new
ON products (category_id, created_at DESC) WHERE is_active;
-- Attribute filtering ("color = red") on variants via JSONB containment.
CREATE INDEX idx_variants_attrs ON product_variants USING GIN (attributes jsonb_path_ops);
jsonb_path_ops builds a smaller, faster GIN index that supports only the containment operator @>. That is exactly what facet filters use (attributes @> '{"color":"red"}'), so the smaller operator class is the right trade. If you also need "does key exist" (?), use the default jsonb_ops.
Variants versus options. The schema stores each sellable combination ("Red / Large") as one product_variants row with its own SKU, price and stock. The alternative, storing options (color: red, blue; size: S, M, L) and generating combinations on the fly, fails as soon as one combination has a different price, is out of stock, or simply does not exist. The rule: one row per thing you can put in a box and ship. Options can still be stored in products.metadata for rendering the picker.
Inventory
CREATE TABLE inventory (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
variant_id BIGINT NOT NULL REFERENCES product_variants(id),
warehouse TEXT NOT NULL DEFAULT 'main',
quantity INT NOT NULL DEFAULT 0 CHECK (quantity >= 0),
reserved INT NOT NULL DEFAULT 0 CHECK (reserved >= 0),
updated_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE (variant_id, warehouse)
);
-- Available = quantity - reserved
CREATE INDEX idx_inventory_variant ON inventory(variant_id);
Note: one more redundant index, one missing constraint
UNIQUE (variant_id, warehouse) creates a B-tree whose leading column is variant_id, so it already serves WHERE variant_id = ? by the leftmost-prefix rule; idx_inventory_variant is redundant. More important is a constraint that is missing: nothing stops reserved from exceeding quantity. Add it, so even a buggy code path cannot reserve stock that does not exist:
ALTER TABLE inventory ADD CONSTRAINT chk_reserved_le_qty CHECK (reserved <= quantity);
Inventory reservation flow:
-- 1. Reserve stock when order is placed (atomic)
UPDATE inventory
SET reserved = reserved + $qty,
updated_at = NOW()
WHERE variant_id = $variant_id
AND warehouse = 'main'
AND quantity - reserved >= $qty -- sufficient available stock
RETURNING id;
-- If 0 rows returned: out of stock
-- 2. Confirm (after payment): deduct from quantity
UPDATE inventory
SET quantity = quantity - $qty,
reserved = reserved - $qty,
updated_at = NOW()
WHERE variant_id = $variant_id AND warehouse = 'main';
-- 3. Cancel: release reservation
UPDATE inventory
SET reserved = reserved - $qty,
updated_at = NOW()
WHERE variant_id = $variant_id AND warehouse = 'main';
The counters on inventory say how much is reserved, but not by whom or until when. Without that, an abandoned checkout holds stock forever, and you cannot tell which reservations to release. Add a reservation ledger:
CREATE TABLE inventory_reservations (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders(id),
variant_id BIGINT NOT NULL,
warehouse TEXT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
status TEXT NOT NULL DEFAULT 'held'
CHECK (status IN ('held', 'committed', 'released')),
expires_at TIMESTAMPTZ NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
FOREIGN KEY (variant_id, warehouse) REFERENCES inventory (variant_id, warehouse),
UNIQUE (order_id, variant_id, warehouse)
);
-- Q8: the sweeper only ever looks at live holds, ordered by expiry.
CREATE INDEX idx_resv_expiring ON inventory_reservations (expires_at)
WHERE status = 'held';
The composite foreign key (variant_id, warehouse) works because that pair is UNIQUE on inventory. The partial index stays tiny: committed and released rows (the overwhelming majority over time) are not in it at all. The full concurrency story, including the sweeper, is in Concurrency Hot Spots.
Orders & Order Items
CREATE TYPE order_status AS ENUM (
'pending', -- order created, awaiting payment
'paid', -- payment confirmed
'processing', -- being prepared/packed
'shipped', -- handed to carrier
'delivered', -- confirmed delivery
'cancelled', -- order cancelled
'refunded' -- money returned
);
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
status order_status NOT NULL DEFAULT 'pending',
shipping_address JSONB NOT NULL, -- snapshot at order time
billing_address JSONB,
subtotal_cents INT NOT NULL,
tax_cents INT NOT NULL DEFAULT 0,
shipping_cents INT NOT NULL DEFAULT 0,
discount_cents INT NOT NULL DEFAULT 0,
total_cents INT NOT NULL,
currency TEXT NOT NULL DEFAULT 'USD',
notes TEXT,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE order_items (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
variant_id BIGINT NOT NULL REFERENCES product_variants(id),
product_name TEXT NOT NULL, -- snapshot
variant_name TEXT NOT NULL, -- snapshot
sku TEXT NOT NULL, -- snapshot
unit_price INT NOT NULL, -- snapshot price in cents
quantity INT NOT NULL CHECK (quantity > 0),
line_total INT NOT NULL -- unit_price * quantity
);
CREATE TABLE order_events (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders(id),
status order_status NOT NULL,
note TEXT,
actor TEXT DEFAULT 'system', -- 'system', 'admin', 'customer'
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status) WHERE status NOT IN ('delivered', 'cancelled');
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_events_order ON order_events(order_id);
Why Snapshot Product Data in Order Items?
Products can be renamed, repriced, or deleted after an order is placed. The order record must reflect what the customer actually bought at the time of purchase. Always copy product_name, variant_name, sku, and unit_price into order_items.
Arithmetic invariants are cheap to enforce in the database and expensive to discover broken in an accounting audit:
ALTER TABLE orders
ADD CONSTRAINT chk_order_total
CHECK (total_cents = subtotal_cents + tax_cents + shipping_cents - discount_cents),
ADD CONSTRAINT chk_order_nonneg
CHECK (subtotal_cents >= 0 AND tax_cents >= 0 AND shipping_cents >= 0
AND discount_cents >= 0 AND total_cents >= 0),
ADD COLUMN idempotency_key TEXT,
ADD CONSTRAINT uq_orders_idem UNIQUE (user_id, idempotency_key);
ALTER TABLE order_items
ADD CONSTRAINT chk_line_total CHECK (line_total = unit_price * quantity),
ADD CONSTRAINT uq_order_variant UNIQUE (order_id, variant_id);
-- Q4: "my orders" newest first, without a sort, and mostly index-only.
CREATE INDEX idx_orders_user_recent
ON orders (user_id, created_at DESC) INCLUDE (status, total_cents);
-- Q7: ops queue. Composite (status, created_at) on the open states only.
CREATE INDEX idx_orders_open_by_age
ON orders (status, created_at)
WHERE status IN ('pending', 'paid', 'processing', 'shipped');
Why these shapes:
(user_id, created_at DESC)puts the equality column first and the sort column second, so "orders of user 42, newest first, limit 10" is a single descending range read that stops after 10 entries. With only(user_id), Postgres fetches all of the user's orders and sorts them.INCLUDE (status, total_cents)makes the list view (which shows only id, date, status and total) an index-only scan when the visibility map is current. Once this index exists, the originalidx_orders_useris redundant by leftmost prefix.(status, created_at) WHERE status IN (...)serves "oldest paid-but-not-shipped orders". Status first because it is the equality predicate; created_at second because it is the sort. The partial predicate excludes delivered, cancelled and refunded orders, which are 95%+ of rows after a year, so the index stays small and hot in memory. It supersedesidx_orders_status.UNIQUE (user_id, idempotency_key)is the database half of idempotent checkout, explained below. Rows with a NULL key are not considered duplicates of each other, so legacy orders without keys are unaffected.
Payments
CREATE TYPE payment_status AS ENUM (
'pending', 'authorized', 'captured', 'failed', 'refunded', 'partially_refunded'
);
CREATE TYPE payment_method AS ENUM (
'credit_card', 'debit_card', 'upi', 'bank_transfer', 'wallet', 'cod'
);
CREATE TABLE payments (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders(id),
status payment_status NOT NULL DEFAULT 'pending',
method payment_method NOT NULL,
amount_cents INT NOT NULL,
currency TEXT NOT NULL DEFAULT 'USD',
gateway TEXT NOT NULL, -- 'stripe', 'razorpay'
gateway_txn_id TEXT, -- external reference
gateway_response JSONB, -- raw response for debugging
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_payments_order ON payments(order_id);
CREATE INDEX idx_payments_gateway_txn ON payments(gateway_txn_id);
The gateway's transaction id identifies a payment in the outside world, and webhooks arrive keyed by it. It should be unique per gateway, which a plain index does not enforce. Gateways also send every event at least once, often more, so webhook processing needs its own dedupe table:
-- Q6: webhook lookup, and a guarantee one gateway txn maps to one row.
CREATE UNIQUE INDEX uq_payments_gateway_txn
ON payments (gateway, gateway_txn_id) WHERE gateway_txn_id IS NOT NULL;
ALTER TABLE payments
ADD COLUMN attempt_no INT NOT NULL DEFAULT 1,
ADD CONSTRAINT chk_amount_pos CHECK (amount_cents > 0),
ADD CONSTRAINT uq_payment_attempt UNIQUE (order_id, attempt_no);
-- Every webhook delivery is recorded once; replays hit the unique key.
CREATE TABLE payment_events (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
gateway TEXT NOT NULL,
gateway_event_id TEXT NOT NULL,
payment_id BIGINT REFERENCES payments(id),
event_type TEXT NOT NULL, -- 'payment.captured', 'charge.refunded'
payload JSONB NOT NULL,
received_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
processed_at TIMESTAMPTZ,
UNIQUE (gateway, gateway_event_id)
);
The attempt_no column gives each retry a deterministic identity. Send order-{order_id}-attempt-{attempt_no} as the idempotency key to the gateway, and a network timeout followed by a retry cannot create two charges on the gateway's side either.
Reviews
CREATE TABLE reviews (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
user_id BIGINT NOT NULL REFERENCES users(id),
rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
title TEXT,
body TEXT,
is_verified BOOLEAN DEFAULT FALSE, -- purchased the product?
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE (product_id, user_id) -- one review per product per user
);
CREATE INDEX idx_reviews_product ON reviews(product_id);
CREATE INDEX idx_reviews_rating ON reviews(product_id, rating);
idx_reviews_product is redundant: both UNIQUE (product_id, user_id) and idx_reviews_rating lead with product_id. The query reviews actually run, "newest reviews for a product, page 1", wants (product_id, created_at DESC). And the rating summary shown on every product card should not be computed per request; keep it denormalized on products (see Performance Considerations) and update it in the same transaction as the review insert, or from an async job if review volume is high.
Cart
CREATE TABLE cart_items (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
variant_id BIGINT NOT NULL REFERENCES product_variants(id),
quantity INT NOT NULL DEFAULT 1 CHECK (quantity > 0),
added_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE (user_id, variant_id)
);
This simple cart works for a logged-in-only store. Real stores need more, covered in Carts in Depth.
Common Queries
-- Product listing with primary image and price range
SELECT
p.id, p.name, p.slug,
MIN(pv.price_cents) AS min_price,
MAX(pv.price_cents) AS max_price,
pi.url AS image_url,
COALESCE(rs.avg_rating, 0) AS avg_rating,
COALESCE(rs.review_count, 0) AS review_count
FROM products p
LEFT JOIN product_variants pv ON pv.product_id = p.id AND pv.is_active
LEFT JOIN product_images pi ON pi.product_id = p.id AND pi.is_primary
LEFT JOIN LATERAL (
SELECT
AVG(rating)::numeric(3,2) AS avg_rating,
COUNT(*) AS review_count
FROM reviews WHERE product_id = p.id
) rs ON TRUE
WHERE p.is_active AND p.category_id = $1
GROUP BY p.id, p.name, p.slug, pi.url, rs.avg_rating, rs.review_count
ORDER BY p.created_at DESC
LIMIT 20;
-- Order history for a user
SELECT
o.id, o.status, o.total_cents, o.created_at,
jsonb_agg(jsonb_build_object(
'product', oi.product_name,
'variant', oi.variant_name,
'qty', oi.quantity,
'price', oi.unit_price
)) AS items
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.user_id = $1
GROUP BY o.id
ORDER BY o.created_at DESC;
These work, but both have performance traps at scale. The next section walks through them.
Hot-Path Queries with EXPLAIN Reasoning
The plans below are illustrative (row counts and costs are trimmed), but the shapes are what PostgreSQL produces. Read them the way the planner does: what is the access path, how many rows does each node touch, and is there a sort or aggregate that has to see everything before it can return the first row. For the full guide to reading plans, see Query Planning & Optimization.
Q2: The category listing
The listing query above has three expensive habits: it aggregates reviews per product on every request, it joins every variant to compute a price range, and it sorts. With only idx_products_category (category_id) WHERE is_active, a category with 40,000 active products produces this:
Limit (rows=20)
-> Sort (Sort Key: p.created_at DESC) (rows=40000)
-> HashAggregate (Group Key: p.id, ...) (rows=40000)
-> Nested Loop Left Join
-> Hash Left Join (product_variants, product_images)
-> Bitmap Heap Scan on products p (rows=40000)
-> Bitmap Index Scan on idx_products_category
-> Aggregate (reviews for one product) (loops=40000)
-> Index Scan on reviews_product_id_user_id_key
The killer is loops=40000 on the lateral review aggregate and the sort over 40,000 rows, all to return 20. The fix is to change the data, not just the index:
-- Denormalize what every listing shows.
ALTER TABLE products
ADD COLUMN min_price_cents INT,
ADD COLUMN max_price_cents INT,
ADD COLUMN avg_rating NUMERIC(3,2) NOT NULL DEFAULT 0,
ADD COLUMN review_count INT NOT NULL DEFAULT 0,
ADD COLUMN primary_image_url TEXT;
-- The listing becomes a single-table, index-ordered read.
SELECT id, name, slug, min_price_cents, max_price_cents,
primary_image_url, avg_rating, review_count, created_at
FROM products
WHERE is_active AND category_id = $1
AND (created_at, id) < ($cursor_created_at, $cursor_id) -- keyset page
ORDER BY created_at DESC, id DESC
LIMIT 20;
Limit (rows=20)
-> Index Scan using idx_products_cat_new on products
Index Cond: (category_id = 7) AND (created_at <= ...)
Filter: ((created_at, id) < (...))
No sort node, no aggregate, and the scan stops after 20 matching rows. For the tie-breaker on id to be fully index-driven, make the index (category_id, created_at DESC, id DESC) WHERE is_active. The denormalized columns are maintained when variants, images or reviews change, which happens orders of magnitude less often than listings are read. Keyset pagination replaces OFFSET, which would read and throw away every row before the page; see Pagination Patterns.
Sorting by price, by rating, or filtering on facets (brand, color, size) multiplies the number of indexes you would need. That is the signal to move listing and search to a search engine, discussed under Evolution.
Q4: Order history
The original query has no LIMIT and joins every line item of every order. A customer with 300 orders gets all of them. With idx_orders_user_recent and a page size:
SELECT o.id, o.status, o.total_cents, o.created_at
FROM orders o
WHERE o.user_id = $1
AND o.created_at < $cursor
ORDER BY o.created_at DESC
LIMIT 10;
Limit (rows=10)
-> Index Only Scan using idx_orders_user_recent on orders o
Index Cond: ((user_id = 42) AND (created_at < '2026-10-01'))
Heap Fetches: 0
Heap Fetches: 0 means every column came from the index thanks to INCLUDE. The line items for those ten orders are a second query, WHERE order_id = ANY($ids), served by idx_order_items_order. Two cheap queries beat one expensive join-and-aggregate.
Q3: Product detail with availability
SELECT v.id, v.sku, v.name, v.price_cents, v.attributes,
COALESCE(SUM(i.quantity - i.reserved), 0) AS available
FROM product_variants v
LEFT JOIN inventory i ON i.variant_id = v.id
WHERE v.product_id = $1 AND v.is_active
GROUP BY v.id;
A product has a handful of variants and each variant a handful of warehouse rows, so this is a nested loop of a few index probes: idx_variants_product, then the inventory unique index once per variant. Microseconds. The PDP is still cached, but availability is usually fetched separately with a short TTL (seconds), because stock changes far more often than descriptions.
Q7: The ops queue
SELECT id, user_id, total_cents, created_at
FROM orders
WHERE status = 'paid' AND created_at < NOW() - INTERVAL '48 hours'
ORDER BY created_at
LIMIT 100;
With idx_orders_open_by_age this is an index range scan over the small partial index. Without the partial predicate, a full (status, created_at) index works too, but it carries tens of millions of delivered orders that no query ever asks for by status.
The checkout write path, counted
| Step | Statement | Rows touched | Locks |
|---|---|---|---|
| 1 | INSERT INTO idempotency_keys ... ON CONFLICT DO NOTHING | 1 | new row |
| 2 | INSERT INTO orders ... RETURNING id | 1 | new row |
| 3 | INSERT INTO order_items (multi-row) | N lines | new rows |
| 4 | UPDATE inventory ... WHERE quantity - reserved >= qty per line, in variant_id order | N | row lock per SKU until commit |
| 5 | INSERT INTO inventory_reservations (multi-row) | N | new rows |
| 6 | UPDATE carts SET status = 'converted' | 1 | row lock |
| 7 | COMMIT | all released |
Everything happens in one transaction of a few milliseconds. The only contended locks are step 4's inventory rows, and they are held for the length of the transaction, which is why the payment gateway call happens after commit, never inside it.
Concurrency Hot Spots: Overselling and Double Charging
Two failures dominate e-commerce incidents: selling stock you do not have, and charging a customer twice. Both are race conditions; both are solved mostly in the schema. The general theory is in Transactions, ACID & Concurrent Updates; this section applies it.
Why the naive version oversells
# WRONG: read, decide in the app, then write
avail = db.query_one("SELECT quantity - reserved FROM inventory WHERE variant_id = %s", v)
if avail >= qty:
db.execute("UPDATE inventory SET reserved = reserved + %s WHERE variant_id = %s", qty, v)
Under PostgreSQL's default read committed isolation, two checkouts can both read avail = 1, both pass the check, and both reserve. The CHECK (reserved <= quantity) constraint added above would reject the second write, which is good, but you would rather not rely on a constraint violation as normal control flow.
Fix 1: the conditional UPDATE (the default)
The reservation flow's step 1 already does the right thing: the check and the change live in one statement.
UPDATE inventory
SET reserved = reserved + $qty, updated_at = NOW()
WHERE variant_id = $variant_id AND warehouse = $wh
AND quantity - reserved >= $qty
RETURNING id;
When two transactions run this on the same row, the second blocks on the row lock. When the first commits, PostgreSQL re-evaluates the WHERE clause against the newly committed row version (the EvalPlanQual recheck). If stock is now insufficient, the second update matches zero rows. No overselling, no retry loop, no isolation level change.
Multi-line orders and deadlocks
An order for SKUs A and B, racing with an order for B and A, can deadlock: each holds one row lock and waits for the other. PostgreSQL detects it after deadlock_timeout (1 s by default) and aborts one transaction. Avoid it by always locking in a consistent order:
-- Reserve every line in one statement, in variant_id order.
WITH wanted(variant_id, warehouse, qty) AS (
SELECT * FROM unnest($variant_ids::bigint[], $warehouses::text[], $qtys::int[])
ORDER BY 1, 2
)
UPDATE inventory i
SET reserved = i.reserved + w.qty, updated_at = NOW()
FROM wanted w
WHERE i.variant_id = w.variant_id AND i.warehouse = w.warehouse
AND i.quantity - i.reserved >= w.qty
RETURNING i.variant_id;
-- If fewer rows come back than lines requested: ROLLBACK, report which SKUs failed.
A caveat worth knowing: PostgreSQL does not strictly guarantee that an UPDATE ... FROM locks rows in the order of the CTE. The guaranteed-order version is to lock first with SELECT ... FROM inventory WHERE (variant_id, warehouse) IN (...) ORDER BY variant_id, warehouse FOR UPDATE, then update. In practice either works for small orders; the explicit FOR UPDATE ... ORDER BY is the one to write when you need to be sure.
Fix 2: SKIP LOCKED for unit-level stock
Some stock is not a number but a set of distinct units: serial-numbered electronics, gift-card codes, event seats, limited-edition items with certificates. Model them as rows and claim them with FOR UPDATE SKIP LOCKED, which lets concurrent buyers take different units instead of queueing on the same one:
CREATE TABLE stock_units (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
variant_id BIGINT NOT NULL REFERENCES product_variants(id),
serial TEXT NOT NULL UNIQUE,
status TEXT NOT NULL DEFAULT 'available'
CHECK (status IN ('available', 'held', 'sold')),
order_id BIGINT REFERENCES orders(id),
held_until TIMESTAMPTZ,
CHECK ((status = 'available') = (order_id IS NULL))
);
CREATE INDEX idx_units_available ON stock_units (variant_id)
WHERE status = 'available';
-- Claim $n units for an order. Concurrent callers skip rows another
-- transaction has locked instead of waiting for them.
UPDATE stock_units
SET status = 'held', order_id = $order_id, held_until = NOW() + INTERVAL '15 minutes'
WHERE id IN (
SELECT id FROM stock_units
WHERE variant_id = $variant_id AND status = 'available'
LIMIT $n
FOR UPDATE SKIP LOCKED
)
RETURNING id, serial;
-- Fewer than $n rows returned: not enough stock right now. ROLLBACK.
The CHECK ties the status to the presence of an order, so "available but assigned" is unrepresentable. SKIP LOCKED trades strict fairness for throughput: a unit locked by a transaction that later rolls back is momentarily invisible, so a buyer might be told "sold out" a few milliseconds before it frees up. For stock, that is an acceptable trade.
Reservation expiry: a SKIP LOCKED sweeper
Holds placed at checkout must be released if payment never arrives. A background job, which can safely run on several workers at once thanks to SKIP LOCKED:
WITH expired AS (
SELECT id, variant_id, warehouse, quantity
FROM inventory_reservations
WHERE status = 'held' AND expires_at < NOW()
ORDER BY expires_at
LIMIT 500
FOR UPDATE SKIP LOCKED
), released AS (
UPDATE inventory_reservations r
SET status = 'released'
FROM expired e
WHERE r.id = e.id
RETURNING e.variant_id, e.warehouse, e.quantity
)
UPDATE inventory i
SET reserved = i.reserved - s.qty, updated_at = NOW()
FROM (SELECT variant_id, warehouse, SUM(quantity) AS qty
FROM released GROUP BY variant_id, warehouse) s
WHERE i.variant_id = s.variant_id AND i.warehouse = s.warehouse;
The GROUP BY is not decoration: a single UPDATE ... FROM cannot change the same target row twice, so two expired holds on the same SKU must be summed first. Payment confirmation must race-safely beat the sweeper too: commit a reservation with UPDATE inventory_reservations SET status = 'committed' WHERE id = $id AND status = 'held'. If that matches zero rows, the hold already expired; re-reserve or refund. Whoever flips the status first wins, and the other side sees zero rows.
The flash-sale hot row
Conditional updates serialize on the row lock, so throughput on one SKU is bounded by one transaction at a time: a few thousand per second at best with very short transactions, far fewer if the transaction does anything else. Options, in order of escalation:
- Keep the transaction tiny. Reserve stock in its own short transaction before creating the order rows, and do nothing slow while holding the lock.
- Split the counter. Store the SKU's stock as 10 rows (
bucket0 to 9) and have each checkout try a random bucket, falling back to others. Ten rows means ten independent locks. Total availability isSUMover buckets. - Gate in front of the database. Keep a Redis counter (
DECRBYin a Lua script that refuses to go negative) as the admission gate; only winners proceed to the Postgres reservation, which remains the source of truth. See Redis Internals. - Queue it. Put purchase intents on a queue and have one consumer per SKU process them in order. Latency rises, contention disappears.
War Story (illustrative)
A store launched a limited sneaker drop with optimistic locking on the inventory row. Thousands of requests per second read version 41, and all but one failed on write and retried, many times. The database was saturated and almost nothing sold. Switching to the single-statement conditional UPDATE let PostgreSQL queue the writers on the row lock; adding a Redis admission gate in front then cut the queue to roughly the number of pairs actually available. Optimistic locking remained right for the rest of the site, where conflicts are rare.
Order & Payment State Machines
order_status is an ENUM, which stops invalid values. It does not stop invalid transitions: nothing in the original schema prevents delivered going back to pending. State machines are where e-commerce bugs hide, so draw them and enforce them.
stateDiagram-v2
[*] --> pending
pending --> paid : payment captured
pending --> cancelled : timeout or customer cancel
paid --> processing : picked for packing
paid --> refunded : cancelled before packing
processing --> shipped : handed to carrier
processing --> refunded : cannot fulfil
shipped --> delivered : carrier confirms
delivered --> refunded : return accepted
cancelled --> [*]
refunded --> [*]
delivered --> [*]
stateDiagram-v2
[*] --> pending
pending --> authorized : gateway authorizes
pending --> failed : declined
authorized --> captured : capture on ship or immediately
authorized --> failed : auth expired or voided
captured --> partially_refunded : partial refund
captured --> refunded : full refund
partially_refunded --> refunded : remaining refunded
failed --> [*]
refunded --> [*]
Note that the two machines are separate. An order is paid when some payment row reaches captured for the full amount; a failed payment creates a new payments row with attempt_no + 1, it does not move the order backwards.
Enforcing transitions
The cheapest enforcement is a compare-and-set update: name the expected current state in the WHERE clause.
-- Only a pending order can become paid. Concurrent webhook replays and
-- a racing cancel cannot both win: one of them matches zero rows.
UPDATE orders SET status = 'paid', updated_at = NOW()
WHERE id = $order_id AND status = 'pending'
RETURNING id;
For defence in depth, put the allowed transitions in a table and check them in a trigger, so even an ad-hoc admin UPDATE cannot make an illegal move:
CREATE TABLE order_transitions (
from_status order_status NOT NULL,
to_status order_status NOT NULL,
PRIMARY KEY (from_status, to_status)
);
INSERT INTO order_transitions VALUES
('pending','paid'), ('pending','cancelled'),
('paid','processing'), ('paid','refunded'),
('processing','shipped'), ('processing','refunded'),
('shipped','delivered'), ('delivered','refunded');
CREATE FUNCTION enforce_order_transition() RETURNS trigger AS $$
BEGIN
IF NEW.status IS DISTINCT FROM OLD.status THEN
IF NOT EXISTS (SELECT 1 FROM order_transitions
WHERE from_status = OLD.status AND to_status = NEW.status) THEN
RAISE EXCEPTION 'illegal order transition % -> %', OLD.status, NEW.status;
END IF;
INSERT INTO order_events (order_id, status, actor)
VALUES (NEW.id, NEW.status, current_setting('app.actor', true));
END IF;
RETURN NEW;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER trg_order_transition
BEFORE UPDATE OF status ON orders
FOR EACH ROW EXECUTE FUNCTION enforce_order_transition();
The trigger also writes order_events automatically, so the audit trail can never be forgotten by a code path. current_setting('app.actor', true) returns NULL instead of erroring when the application did not set it.
Evolving an ENUM
ALTER TYPE order_status ADD VALUE 'partially_shipped' is cheap and safe. Removing or renaming a value is not supported directly; you would create a new type and rewrite the column. If your state list changes often, a TEXT column with a CHECK constraint, or a lookup table with a foreign key, is easier to evolve. See Database Migrations.
Idempotent Checkout
The customer clicks "Place order", the request times out on a flaky mobile connection, and the app retries. Without idempotency, that is two orders and possibly two charges. The client generates a unique key per checkout attempt (a UUID created when the checkout page loads) and sends it with every retry. The server stores it.
CREATE TABLE idempotency_keys (
user_id BIGINT NOT NULL REFERENCES users(id),
idem_key TEXT NOT NULL,
request_hash TEXT NOT NULL, -- hash of the request body
status TEXT NOT NULL DEFAULT 'in_progress'
CHECK (status IN ('in_progress', 'completed', 'failed')),
order_id BIGINT REFERENCES orders(id),
response_code INT,
response_body JSONB,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY (user_id, idem_key)
);
CREATE INDEX idx_idem_created ON idempotency_keys (created_at); -- for TTL cleanup
sequenceDiagram
participant C as Client
participant API as Checkout API
participant DB as PostgreSQL
participant PG as Payment gateway
C->>API: POST /checkout with Idempotency-Key K
API->>DB: INSERT idempotency_keys K ON CONFLICT DO NOTHING
alt key is new
API->>DB: BEGIN, insert order and items, reserve stock, COMMIT
API->>PG: charge with key order-N-attempt-1
PG-->>API: captured
API->>DB: mark payment captured, order paid, key completed
API-->>C: 201 order N
else key already completed
API->>DB: SELECT stored response for K
API-->>C: same 201 order N
else key in progress
API-->>C: 409 retry shortly
end
The rules that make it correct:
- The unique key is the lock.
INSERT ... ON CONFLICT DO NOTHING RETURNINGtells you atomically whether you are the first request with this key. Two concurrent retries cannot both proceed. - Compare the request hash. If a reused key arrives with a different body, return
422; the client has a bug. - Belt and braces.
orders.idempotency_keywithUNIQUE (user_id, idempotency_key)means even if the key table is cleaned up early, a second order for the same key fails at insert. - Never call the gateway inside the database transaction. The transaction would hold inventory row locks for the length of a network call. Commit the order as
pending, then charge, then update. - Pass an idempotency key to the gateway too, derived from
(order_id, attempt_no). Your own retries of the charge call are then safe on the gateway's side. - Expire keys after 24 hours to a few days with a batch
DELETE ... WHERE created_at < NOW() - INTERVAL '3 days'.
For a wider treatment of idempotency keys across APIs, see Idempotency; for the gateway side, Payment System. If the "mark paid" step must also publish an event (to fulfilment, email, analytics), write the event to an outbox table in the same transaction; see Transactional Outbox.
Carts in Depth
The original cart_items table is keyed by user_id, which assumes everyone is logged in. Most stores let guests shop and ask for login at checkout, and most carts are abandoned. A more realistic model separates the cart from its lines:
CREATE TABLE carts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
session_token TEXT UNIQUE, -- guest cart cookie
status TEXT NOT NULL DEFAULT 'active'
CHECK (status IN ('active', 'converted', 'merged', 'abandoned')),
currency TEXT NOT NULL DEFAULT 'USD',
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CHECK (user_id IS NOT NULL OR session_token IS NOT NULL)
);
-- A user has at most one active cart.
CREATE UNIQUE INDEX uq_one_active_cart ON carts (user_id)
WHERE status = 'active' AND user_id IS NOT NULL;
-- Abandoned-cart emails and cleanup scan only active carts by age.
CREATE INDEX idx_carts_stale ON carts (updated_at) WHERE status = 'active';
CREATE TABLE cart_lines (
cart_id UUID NOT NULL REFERENCES carts(id) ON DELETE CASCADE,
variant_id BIGINT NOT NULL REFERENCES product_variants(id),
quantity INT NOT NULL CHECK (quantity BETWEEN 1 AND 99),
added_price_cents INT NOT NULL, -- to show "price dropped since you added"
added_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY (cart_id, variant_id)
);
Add to cart is an upsert, so double-clicks and retries are harmless:
INSERT INTO cart_lines (cart_id, variant_id, quantity, added_price_cents)
VALUES ($cart, $variant, $qty, $price)
ON CONFLICT (cart_id, variant_id)
DO UPDATE SET quantity = LEAST(cart_lines.quantity + EXCLUDED.quantity, 99);
Merging a guest cart at login, in one transaction:
INSERT INTO cart_lines (cart_id, variant_id, quantity, added_price_cents, added_at)
SELECT $user_cart, variant_id, quantity, added_price_cents, added_at
FROM cart_lines WHERE cart_id = $guest_cart
ON CONFLICT (cart_id, variant_id)
DO UPDATE SET quantity = LEAST(cart_lines.quantity + EXCLUDED.quantity, 99);
UPDATE carts SET status = 'merged', updated_at = NOW() WHERE id = $guest_cart;
Design notes:
- Carts do not reserve stock. Reserving on add-to-cart lets bots and indecisive shoppers lock up inventory. Reserve at checkout, with a short expiry. Show "only 2 left" from the availability query instead.
- Prices in the cart are advisory.
added_price_centsis for messaging only; checkout always re-reads the current price (and validates promotions) and snapshots it intoorder_items. - Carts are disposable and write-heavy. At high volume they are the first piece to leave Postgres for Redis (a hash per cart with a TTL) or DynamoDB, keyed by cart id. Orders stay in Postgres.
Price History
product_variants.price_cents is the current price. Finance, customer support ("I was charged more than the page said"), and regulations on advertised discounts (some jurisdictions require "was" prices to reflect the lowest price of the last 30 days) all need every price a variant has had. A temporal table with a non-overlap guarantee:
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE price_history (
variant_id BIGINT NOT NULL REFERENCES product_variants(id),
currency TEXT NOT NULL DEFAULT 'USD',
price_cents INT NOT NULL CHECK (price_cents >= 0),
valid_during TSTZRANGE NOT NULL,
reason TEXT, -- 'repricing', 'sale:BF2026', 'correction'
changed_by BIGINT REFERENCES users(id),
-- No two price rows for the same variant and currency may overlap in time.
EXCLUDE USING gist (variant_id WITH =, currency WITH =, valid_during WITH &&)
);
The exclusion constraint is the interesting part: it is a uniqueness constraint generalised from "equal" to "overlapping", backed by a GiST index (which is why btree_gist is needed for the = on plain columns). It makes "two prices at once" impossible, even under concurrent repricing jobs. The same GiST index serves Q9:
-- Price of a variant at a given moment
SELECT price_cents FROM price_history
WHERE variant_id = $1 AND currency = 'USD' AND valid_during @> $at::timestamptz;
-- Lowest price in the 30 days before a sale starts (for "was" price rules)
SELECT MIN(price_cents) FROM price_history
WHERE variant_id = $1 AND currency = 'USD'
AND valid_during && tstzrange($sale_start - INTERVAL '30 days', $sale_start);
Changing a price closes the open range and opens a new one, in one transaction, and refreshes the cached current price:
BEGIN;
UPDATE price_history
SET valid_during = tstzrange(lower(valid_during), NOW())
WHERE variant_id = $1 AND currency = 'USD' AND upper_inf(valid_during);
INSERT INTO price_history (variant_id, currency, price_cents, valid_during, reason, changed_by)
VALUES ($1, 'USD', $new_price, tstzrange(NOW(), NULL), $reason, $admin);
UPDATE product_variants SET price_cents = $new_price WHERE id = $1;
COMMIT;
Scheduled sales insert future ranges ahead of time (tstzrange('2026-11-27', '2026-12-01')); a job flips the cached price_cents when a range starts. Promotions that depend on the cart (buy two, get one) are not prices; model them as a separate promotions table evaluated at checkout, and record the applied discount on the order.
Performance Considerations
| Concern | Solution |
|---|---|
| Product search | Full-text index: CREATE INDEX idx_products_search ON products USING GIN (to_tsvector('english', name || ' ' || COALESCE(description, ''))) |
| Inventory races | UPDATE ... WHERE quantity - reserved >= $qty is atomic in a single statement |
| Order counting | Maintain order_count on users via trigger or application logic |
| Review aggregates | Materialized view or denormalized avg_rating/review_count on products |
| Category tree | Recursive CTE for nested categories; or materialized path (/electronics/phones/smartphones) |
| Soft deletes | is_active flag on products instead of DELETE; orders still reference the product |
| Listing sort without a Sort node | Composite (category_id, created_at DESC) partial index plus keyset pagination |
| Hot SKU during flash sales | Short reservation transaction, split counters, or a Redis admission gate |
| Webhook storms | payment_events unique key absorbs replays; process asynchronously |
| Abandoned carts and expired holds | Partial indexes on the live subset; SKIP LOCKED sweepers |
A note on the full-text row: the query must use exactly the same expression as the index (to_tsvector('english', name || ' ' || COALESCE(description, ''))), or the planner cannot match it. A generated column (search_tsv tsvector GENERATED ALWAYS AS (...) STORED) with a GIN index on it avoids that pitfall.
Schema Design Decisions
| Decision | Rationale |
|---|---|
| Cents not dollars | Avoids float rounding; all math is integer arithmetic |
| Snapshots in order_items | Orders are immutable records of what was purchased |
| JSONB for addresses in orders | Address at order time is frozen; user may later change their address |
| JSONB for variant attributes | Product attributes vary wildly (shoes have size, laptops have RAM); JSONB is flexible |
| Separate inventory table | Decouples stock from product catalog; supports multi-warehouse |
| order_events for audit trail | Every status change is logged; enables debugging and customer support |
| ENUM for statuses | Database enforces valid state values; faster than text comparison |
| Reservation ledger | Holds have an owner and an expiry, so abandoned checkouts release stock |
| Idempotency key per checkout | Retries return the same order instead of creating a second one |
| Payment attempts as rows | Declines and retries are history, not overwrites |
| Temporal price table with exclusion constraint | Every historical price is answerable and overlaps are impossible |
| Transition table plus trigger | Illegal state moves fail in the database, and events are always logged |
Evolution as It Scales
The schema above runs a surprisingly large store on one PostgreSQL primary. It changes shape in stages, and each stage is driven by a measured bottleneck, not by anticipation. See Database Scaling for the general ladder.
| Stage | Symptom | Change |
|---|---|---|
| 1. Single primary | Nothing hurts yet | Good indexes, connection pooling (Connection Pooling), cache PDPs at the CDN |
| 2. Read replicas | Catalog reads dominate CPU | Route catalog and order-history reads to replicas; keep checkout, inventory and "just placed" order reads on the primary to avoid replica lag surprises (Replication) |
| 3. Search engine | Facets, typo tolerance, relevance, sort by price | Catalog indexed into Elasticsearch/OpenSearch via change data capture; Postgres stays the source of truth (Elasticsearch Internals) |
| 4. Carts and sessions out | Cart writes churn the WAL and bloat tables | Carts to Redis or DynamoDB with TTLs; merge into an order at checkout |
| 5. Partitioning | orders, order_items, order_events, payment_events reach billions of rows; vacuum and index maintenance lag | Range-partition by created_at (monthly); detach and archive old partitions to object storage or the warehouse |
| 6. Service split | Inventory contention and team boundaries | Inventory and pricing become their own services and databases; checkout coordinates with a saga |
| 7. Sharding | One primary cannot absorb order writes | Shard orders by user_id |
Partitioning orders. Declarative range partitioning keeps recent data hot and makes archiving a metadata operation:
CREATE TABLE orders_p (LIKE orders INCLUDING DEFAULTS INCLUDING CONSTRAINTS)
PARTITION BY RANGE (created_at);
CREATE TABLE orders_2026_11 PARTITION OF orders_p
FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
-- Later: ALTER TABLE orders_p DETACH PARTITION orders_2024_01 CONCURRENTLY;
The catch: on a partitioned table, every primary key and unique constraint must include the partition key. PRIMARY KEY (id) becomes PRIMARY KEY (id, created_at), and UNIQUE (user_id, idempotency_key) can no longer be enforced globally. Keep the idempotency guarantee in the unpartitioned idempotency_keys table, and have foreign keys from order_items carry (order_id, order_created_at) or be dropped in favour of application checks. This is the kind of trade-off worth stating in a design review.
Choosing a shard key. The candidates and what each breaks:
| Shard key | Local (single shard) | Cross-shard | Verdict |
|---|---|---|---|
user_id | Checkout, order history, carts, addresses | Ops queue by status, merchant reports | Best for B2C. Customer-facing paths are single-shard; ops queries go to replicas or the warehouse |
order_id | Single-order lookups | "My orders" becomes scatter-gather | Poor; the most common read fans out |
merchant_id (marketplaces) | Merchant dashboards | Customer order history across merchants | Good for marketplaces, paired with a per-user index table |
sku | Inventory updates | Every multi-item order | Only for the inventory service, never for orders |
Notice that inventory and orders want different keys, which is one more reason they end up in different services. A checkout then reserves stock in the inventory service (keyed by SKU), creates the order in the order service (keyed by user), and compensates if either fails. That is the saga pattern; see Sharding and Distributed Transactions. Order ids should be generated so they are unique across shards and roughly time-ordered; see ID Generation.
Archiving. Orders older than the return window plus a buffer (say 18 months) are read rarely, mostly by support. Move them to cheaper storage but keep a lookup path, because "find my order from 2023" does happen. Analytics should never query the OLTP primary; ship changes to a warehouse.
Common Mistakes
- Floating-point money.
0.1 + 0.2is not0.3. Integers in minor units, orNUMERIC, always. Store the currency next to every amount. - No snapshot on order lines. Joining order lines to the live
product_variants.price_centssilently rewrites every past invoice when a price changes. - Read-then-write stock checks. Oversells under concurrency. Use the conditional
UPDATE. - Optimistic locking on the hottest row. Fine for profiles, catastrophic for a flash-sale SKU.
- Calling the payment gateway inside a database transaction. Locks are held for a network round trip; one slow gateway stalls checkout.
- No idempotency on checkout or webhooks. Retries create duplicate orders; replayed webhooks double-ship.
- Reserving stock when items are added to the cart. Bots and window shoppers drain inventory.
- Reservations without expiry. Abandoned checkouts hold stock forever.
- Status columns with no transition rules. An ENUM stops
'shiped'but notdelivered → pending. - Redundant indexes. Indexing a column that already has a
UNIQUEconstraint doubles write cost for nothing. - Single-column indexes for sorted, filtered lists.
(user_id)instead of(user_id, created_at DESC)forces a sort of every row. OFFSETpagination on deep pages. Page 500 reads 10,000 rows to return 20.- Hard-deleting products. Breaks foreign keys from order lines and reviews; deactivate instead.
- Running analytics on the primary. One "revenue by category for the year" query can starve checkout.
Interview Walkthrough: "Design the Schema for an Online Store"
A good answer follows the same order as this page. Here is how it sounds in a 45-minute interview.
1. Clarify scope (3 minutes). "Single seller or marketplace? Do guests check out? Multiple warehouses? Multiple currencies? I will assume a single seller, guest checkout allowed, a few warehouses, one currency per order, and about half a million orders a day with 20x peaks."
2. State the access patterns (5 minutes). List the hot reads (PDP, listing, my orders) and the critical writes (checkout, payment confirmation). Say out loud that the catalog is read-heavy and cacheable while checkout is low-volume but must be correct.
3. Entities and relationships (7 minutes). Users and addresses; categories, products, variants (one row per sellable SKU); inventory per variant per warehouse; carts and cart lines; orders, order lines with captured prices, payments as attempts, order events. Sketch the ER diagram. Call out the two "historical fact" decisions: snapshotting prices and addresses.
4. Tables and keys (10 minutes). Write the DDL for the important tables: inventory with CHECK (quantity >= 0), CHECK (reserved <= quantity) and UNIQUE (variant_id, warehouse); orders with an ENUM status and a total-consistency CHECK; order_items with the snapshot columns. Integer cents.
5. Indexes from queries (5 minutes). For each hot query name the index and its column order: (user_id, created_at DESC) for order history, (category_id, created_at DESC) WHERE is_active for listings, partial (status, created_at) for the ops queue. Mention keyset pagination.
6. Concurrency (10 minutes). This is where interviews are won. Explain the oversell race, the conditional UPDATE, lock ordering for multi-item orders, reservation expiry with a SKIP LOCKED sweeper, the flash-sale hot row and its mitigations, the state-machine compare-and-set, and idempotency keys for checkout, gateway calls and webhooks.
7. Scale and evolution (5 minutes). Replicas for catalog reads, search engine for listings and facets, carts to Redis, monthly partitions for orders with archiving, and eventually sharding orders by user_id while inventory becomes its own service. Mention the saga needed once orders and inventory live apart.
Likely follow-ups and crisp answers:
- "How do you prevent two people buying the last item?" One conditional
UPDATEthat checks and decrements together; the second transaction re-evaluates after the first commits and matches zero rows. - "What if payment succeeds but your server crashes before marking the order paid?" The gateway webhook (deduplicated by
payment_events) or a reconciliation job polling the gateway completes the transition; the compare-and-set makes the late update safe. - "Why not store the cart in the orders table with status 'cart'?" Carts are high-churn and mostly abandoned; mixing them in bloats the most important table and its indexes, and they want different storage as you scale.
- "Where does tax live?" Computed at checkout by a tax service, snapshotted per line and per order, never recomputed for past orders.
Where to Go Next
- OLTP Database Design: the general principles this page applies to one domain
- Transactions, ACID & Concurrent Updates and Isolation Levels: the theory behind the inventory and state-machine patterns
- Indexing Deep Dive: composite order, partial, covering and GiST indexes in depth
- Sharding: shard keys, scatter-gather and sagas
- E-Commerce System Design and Payment System: the services around this schema
- Social Media Schema Design, SaaS Multi-Tenant Schema Design, Chat Application Schema Design: the same method on other domains