Why should you care?

PostgresVerse Flipkart-style shop must never oversell GPU stock during flash sale.

See it live — copy this example

Run in pgAdmin or psql.

CREATE TABLE products (
  product_id bigserial PRIMARY KEY,
  sku text UNIQUE,
  stock_qty int NOT NULL CHECK (stock_qty >= 0)
);
CREATE TABLE carts (
  cart_id uuid PRIMARY KEY,
  customer_id bigint,
  updated_at timestamptz DEFAULT now()
);
CREATE TABLE cart_items (
  cart_id uuid REFERENCES carts(cart_id),
  product_id bigint REFERENCES products(product_id),
  qty int CHECK (qty > 0),
  PRIMARY KEY (cart_id, product_id)
);

What happened?

• Normalized cart model.
• stock_qty CHECK prevents negative inventory at DB.
• Checkout transaction locks product row FOR UPDATE before decrement.

Practice next

1. Create products with low stock_qty test SKU.
2. Add cart items in transaction.
3. Simulate two checkouts; second waits or fails on stock.
4. Add order_status enum and orders table from cart merge.
5. Index products(sku) for barcode scan API.

Remember

Cart normalized; checkout is one transaction. FOR UPDATE on stock hot rows. SKU unique across catalog.

PostgresVerse flash sale

100 concurrent buy clicks on 10 GPUs; locking prevents oversell.

Outcome: Angry customers avoided; support load manageable.