On this page

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.

OperationSteady ratePeak rateRead or writeLatency targetNotes
Product detail page (PDP)3,000/s30,000/sReadp99 < 50 msMostly served from cache/CDN; DB sees cache misses
Category listing1,500/s15,000/sReadp99 < 100 msFilter by category, sort by newness/price, paginate
Free-text search800/s8,000/sReadp99 < 150 msBelongs in a search engine, not Postgres, at this scale
Add/update cart line200/s3,000/sWritep99 < 50 msVery high abandon rate; cheap, disposable data
Checkout (create order + reserve stock)6/s100/sWritep99 < 300 msMust never oversell, must never double charge
Flash-sale checkout on one SKU02,000/sWriteBest effortThe single hottest row in the system
Payment webhook6/s100/sWriteAsyncGateways retry, so duplicates are normal
Order history ("My orders")300/s2,000/sReadp99 < 100 msPer-user, newest first, paginated
Ops dashboard: orders by status20/s50/sReadp99 < 500 ms"Paid but not shipped for 48 h"
Stock adjustments (warehouse feeds)50/s500/sWriteAsyncBatch-friendly
Price changes5/s5,000/sWriteAsyncBulk repricing before a sale

Three things fall out of this table immediately:

  1. 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.
  2. 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.
  3. 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 queryIndex it needs
Q1Product by slugUNIQUE (slug) on products
Q2Active products in a category, newest first, page of 20(category_id, created_at DESC) WHERE is_active
Q3Variants and available stock for a product(product_id) on variants, UNIQUE (variant_id, warehouse) on inventory
Q4A user's orders, newest first(user_id, created_at DESC) on orders
Q5Line items for an order(order_id) on order_items
Q6Payment by gateway transaction id (webhooks)UNIQUE (gateway, gateway_txn_id)
Q7Open orders by status, oldest first (ops)partial (status, created_at) on non-terminal statuses
Q8Expired stock reservations to releasepartial (expires_at) WHERE status = 'held'
Q9Price of a variant at a point in timeGiST 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_lines is the junction and carries quantity. An order and a variant are M:N; order_items is 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_history all 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 original idx_orders_user is 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 supersedes idx_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

StepStatementRows touchedLocks
1INSERT INTO idempotency_keys ... ON CONFLICT DO NOTHING1new row
2INSERT INTO orders ... RETURNING id1new row
3INSERT INTO order_items (multi-row)N linesnew rows
4UPDATE inventory ... WHERE quantity - reserved >= qty per line, in variant_id orderNrow lock per SKU until commit
5INSERT INTO inventory_reservations (multi-row)Nnew rows
6UPDATE carts SET status = 'converted'1row lock
7COMMITall 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:

  1. Keep the transaction tiny. Reserve stock in its own short transaction before creating the order rows, and do nothing slow while holding the lock.
  2. Split the counter. Store the SKU's stock as 10 rows (bucket 0 to 9) and have each checkout try a random bucket, falling back to others. Ten rows means ten independent locks. Total availability is SUM over buckets.
  3. Gate in front of the database. Keep a Redis counter (DECRBY in 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.
  4. 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 RETURNING tells 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_key with UNIQUE (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_cents is for messaging only; checkout always re-reads the current price (and validates promotions) and snapshots it into order_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

ConcernSolution
Product searchFull-text index: CREATE INDEX idx_products_search ON products USING GIN (to_tsvector('english', name || ' ' || COALESCE(description, '')))
Inventory racesUPDATE ... WHERE quantity - reserved >= $qty is atomic in a single statement
Order countingMaintain order_count on users via trigger or application logic
Review aggregatesMaterialized view or denormalized avg_rating/review_count on products
Category treeRecursive CTE for nested categories; or materialized path (/electronics/phones/smartphones)
Soft deletesis_active flag on products instead of DELETE; orders still reference the product
Listing sort without a Sort nodeComposite (category_id, created_at DESC) partial index plus keyset pagination
Hot SKU during flash salesShort reservation transaction, split counters, or a Redis admission gate
Webhook stormspayment_events unique key absorbs replays; process asynchronously
Abandoned carts and expired holdsPartial 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

DecisionRationale
Cents not dollarsAvoids float rounding; all math is integer arithmetic
Snapshots in order_itemsOrders are immutable records of what was purchased
JSONB for addresses in ordersAddress at order time is frozen; user may later change their address
JSONB for variant attributesProduct attributes vary wildly (shoes have size, laptops have RAM); JSONB is flexible
Separate inventory tableDecouples stock from product catalog; supports multi-warehouse
order_events for audit trailEvery status change is logged; enables debugging and customer support
ENUM for statusesDatabase enforces valid state values; faster than text comparison
Reservation ledgerHolds have an owner and an expiry, so abandoned checkouts release stock
Idempotency key per checkoutRetries return the same order instead of creating a second one
Payment attempts as rowsDeclines and retries are history, not overwrites
Temporal price table with exclusion constraintEvery historical price is answerable and overlaps are impossible
Transition table plus triggerIllegal 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.

StageSymptomChange
1. Single primaryNothing hurts yetGood indexes, connection pooling (Connection Pooling), cache PDPs at the CDN
2. Read replicasCatalog reads dominate CPURoute 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 engineFacets, typo tolerance, relevance, sort by priceCatalog indexed into Elasticsearch/OpenSearch via change data capture; Postgres stays the source of truth (Elasticsearch Internals)
4. Carts and sessions outCart writes churn the WAL and bloat tablesCarts to Redis or DynamoDB with TTLs; merge into an order at checkout
5. Partitioningorders, order_items, order_events, payment_events reach billions of rows; vacuum and index maintenance lagRange-partition by created_at (monthly); detach and archive old partitions to object storage or the warehouse
6. Service splitInventory contention and team boundariesInventory and pricing become their own services and databases; checkout coordinates with a saga
7. ShardingOne primary cannot absorb order writesShard 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 keyLocal (single shard)Cross-shardVerdict
user_idCheckout, order history, carts, addressesOps queue by status, merchant reportsBest for B2C. Customer-facing paths are single-shard; ops queries go to replicas or the warehouse
order_idSingle-order lookups"My orders" becomes scatter-gatherPoor; the most common read fans out
merchant_id (marketplaces)Merchant dashboardsCustomer order history across merchantsGood for marketplaces, paired with a per-user index table
skuInventory updatesEvery multi-item orderOnly 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

  1. Floating-point money. 0.1 + 0.2 is not 0.3. Integers in minor units, or NUMERIC, always. Store the currency next to every amount.
  2. No snapshot on order lines. Joining order lines to the live product_variants.price_cents silently rewrites every past invoice when a price changes.
  3. Read-then-write stock checks. Oversells under concurrency. Use the conditional UPDATE.
  4. Optimistic locking on the hottest row. Fine for profiles, catastrophic for a flash-sale SKU.
  5. Calling the payment gateway inside a database transaction. Locks are held for a network round trip; one slow gateway stalls checkout.
  6. No idempotency on checkout or webhooks. Retries create duplicate orders; replayed webhooks double-ship.
  7. Reserving stock when items are added to the cart. Bots and window shoppers drain inventory.
  8. Reservations without expiry. Abandoned checkouts hold stock forever.
  9. Status columns with no transition rules. An ENUM stops 'shiped' but not delivered → pending.
  10. Redundant indexes. Indexing a column that already has a UNIQUE constraint doubles write cost for nothing.
  11. Single-column indexes for sorted, filtered lists. (user_id) instead of (user_id, created_at DESC) forces a sort of every row.
  12. OFFSET pagination on deep pages. Page 500 reads 10,000 rows to return 20.
  13. Hard-deleting products. Breaks foreign keys from order lines and reviews; deactivate instead.
  14. 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 UPDATE that 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