Back to all posts
PostgreSQL System Design E-Commerce

Architecting a Scalable Multi-Seller E-Commerce Database in PostgreSQL for FujiCommerce

A deep dive into the schema design and concurrency control behind FujiCommerce's multi-seller marketplace — and the exact locking strategy that stopped two customers from buying the same unit of inventory.

FujiCommerce is a multi-seller digital commerce platform: independent sellers list products, manage their own inventory, and fulfill orders through a shared storefront and checkout. The brief was simple to state and hard to deliver — scalable backend architecture that lets hundreds of sellers write to overlapping inventory and order tables at the same time, without ever selling a unit that doesn't exist.

The application layer is vanilla PHP 8.2 — no framework — talking to the database through PDO. The database itself started life on MySQL and was migrated to PostgreSQL once the concurrency requirements around row-level locking and transactional guarantees outgrew what the original schema could offer. This is the case study on how that database was redesigned in PostgreSQL: the schema, the concurrency model, and the specific race condition that surfaced in production load testing before it ever reached a real customer.

The Concurrency Challenge

A single-seller store can get away with naive stock checks — read the quantity, check it's greater than zero, decrement it. In a high-concurrency multi-seller environment that pattern fails constantly. During a flash sale simulation we ran against a staging clone of FujiCommerce, two checkout requests for the last unit of a limited-stock item both read quantity = 1 before either request wrote back. Both succeeded. We oversold.

The challenge wasn't just "prevent overselling." It was preventing overselling without serializing every write to the inventory table, which would have collapsed throughput the moment checkout traffic scaled past a few hundred concurrent requests. The database needed to:

  • Guarantee that stock decrements are atomic per SKU, even under concurrent checkout attempts.
  • Isolate sellers from each other's write load — a hot product from Seller A should not lock rows belonging to Seller B.
  • Keep order writes and inventory writes consistent, so a failed payment never leaves stock permanently decremented.

Database Schema Design

The schema separates identity (who owns what) from availability (what's sellable right now), which is what makes seller-level isolation possible. Simplified, the core tables look like this:

schema.sqlPostgreSQL
CREATE TABLE sellers (
    id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    business_name   text NOT NULL,
    status          text NOT NULL DEFAULT 'active',
    created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE products (
    id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    seller_id       uuid NOT NULL REFERENCES sellers(id),
    sku             text NOT NULL,
    title           text NOT NULL,
    price_cents     integer NOT NULL CHECK (price_cents >= 0),
    UNIQUE (seller_id, sku)
);

-- Inventory is isolated per seller+product, so a hot
-- row for Seller A never contends with Seller B's rows.
CREATE TABLE inventory (
    product_id      uuid PRIMARY KEY REFERENCES products(id),
    quantity        integer NOT NULL CHECK (quantity >= 0),
    reserved        integer NOT NULL DEFAULT 0 CHECK (reserved >= 0),
    updated_at      timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
    id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    buyer_id        uuid NOT NULL,
    status          text NOT NULL DEFAULT 'pending',
    created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
    id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id        uuid NOT NULL REFERENCES orders(id),
    product_id      uuid NOT NULL REFERENCES products(id),
    seller_id       uuid NOT NULL REFERENCES sellers(id),
    quantity        integer NOT NULL CHECK (quantity > 0),
    unit_price_cents integer NOT NULL
);

Two decisions here matter more than they look:

  • One inventory row per product. No shared "global stock pool" table that every seller's checkout path touches — that would have been a single hot contention point at scale.
  • A reserved column separate from quantity, so stock can be held during payment processing without being permanently committed or permanently lost if a payment fails.

Solving Race Conditions

The overselling bug came from a classic read-then-write gap: the application read the quantity in one query, decided stock was available, then issued a separate UPDATE in a second query. Between those two queries, another request could read the same stale value. The fix is to make the check and the decrement a single atomic statement, and let PostgreSQL's row locking do the serialization for you — scoped to exactly one row, not the whole table.

reserve_stock.sqlPostgreSQL
-- Atomic reserve: only succeeds if enough stock exists.
-- The UPDATE itself acquires the row lock, closing the
-- read-then-write gap that caused the overselling bug.
UPDATE inventory
SET reserved = reserved + 1,
    updated_at = now()
WHERE product_id = $1
  AND (quantity - reserved) >= 1
RETURNING product_id;

-- If RETURNING yields zero rows, stock wasn't available —
-- the application treats this as "sold out", not an error.

This single-statement UPDATE ... WHERE ... RETURNING pattern works because PostgreSQL evaluates the WHERE clause and takes the row lock as one atomic operation — no other transaction can sneak a write in between the check and the update, because there is no gap between them. For the rarer case where a batch process needs to read and reason about a row before writing it, I use an explicit SELECT ... FOR UPDATE to hold the lock across the transaction:

OrderService.phpPHP 8.2
<?php

declare(strict_types=1);

final class OrderService
{
    public function __construct(private readonly PDO $db) {}

    public function reserveAndCreateOrder(string $productId, int $qty): Order
    {
        $this->db->beginTransaction();

        try {
            // Row-level lock: blocks concurrent reservations for
            // THIS product only — other products remain unaffected.
            $stmt = $this->db->prepare(
                'SELECT (quantity - reserved) AS available
                 FROM inventory WHERE product_id = :id FOR UPDATE'
            );
            $stmt->execute(['id' => $productId]);
            $available = $stmt->fetchColumn();

            if ($available === false || $available < $qty) {
                throw new InsufficientStockException($productId);
            }

            $update = $this->db->prepare(
                'UPDATE inventory SET reserved = reserved + :qty WHERE product_id = :id'
            );
            $update->execute(['qty' => $qty, 'id' => $productId]);

            $order = $this->insertPendingOrder($productId, $qty);

            $this->db->commit();
            return $order;
        } catch (Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }
}

On the API layer, the same request path wraps this behind a thin idempotency check, so a retried checkout request (double-click, flaky network) never reserves stock twice for the same cart:

checkout.phpPHP 8.2
<?php

declare(strict_types=1);

header('Content-Type: application/json');

$input = json_decode(file_get_contents('php://input'), true);
['productId' => $productId, 'quantity' => $quantity, 'idempotencyKey' => $key] = $input;

// Unique constraint on idempotency_key does the
// deduplication — no distributed lock required.
$stmt = $pdo->prepare('SELECT order_id FROM idempotency_keys WHERE key = :key');
$stmt->execute(['key' => $key]);

if ($existingOrderId = $stmt->fetchColumn()) {
    http_response_code(200);
    echo json_encode(['orderId' => $existingOrderId]);
    exit;
}

try {
    $order = $orderService->reserveAndCreateOrder($productId, (int) $quantity);

    $insert = $pdo->prepare(
        'INSERT INTO idempotency_keys (key, order_id) VALUES (:key, :order_id)'
    );
    $insert->execute(['key' => $key, 'order_id' => $order->id]);

    http_response_code(201);
    echo json_encode($order);
} catch (InsufficientStockException $e) {
    http_response_code(409);
    echo json_encode(['error' => 'Sold out']);
}

Under repeated load testing with 500 concurrent checkout attempts against a single limited-stock SKU, this combination — per-row locking scoped to one product, a separate reserved counter, and idempotency keys at the API boundary — eliminated the overselling bug entirely while keeping checkout throughput for other products completely unaffected, since locks never escalate beyond the single row being written.

The lesson that generalizes beyond FujiCommerce: concurrency bugs in commerce systems are almost always a gap between a read and a write. Close the gap with one atomic statement before reaching for heavier tools like advisory locks or serializable isolation.

What I'd evaluate next

  • Partitioning order_items by seller for sellers with very high order volume.
  • A background sweeper that releases reserved stock for orders that never reach payment confirmation within a timeout window.
  • Read replicas for seller-facing analytics dashboards, so reporting queries never compete with checkout write load.

If you're building — or hiring for — a high-concurrency fintech system or a multi-seller commerce platform and want to talk through the schema and concurrency trade-offs for your own case, get in touch.

More engineering notes Contact me