SQL vs NoSQL for E-commerce: Orders, Stock, Catalogue and Search

This guide is for founders, developers and technical leads planning an e-commerce platform, a marketplace or a direct-to-consumer store who need to choose between SQL and NoSQL databases, or a mix. It works through the four workloads that decide the answer: orders and payments, stock, catalogue and search. By the end you will be able to choose a primary database, know where a second store earns its place, and sketch the core tables.

What an e-commerce database actually has to do

Online stores look simple from the outside and are demanding underneath. In one checkout, the system must reserve stock, calculate a total with taxes and discounts, take a payment through a gateway, create an order, send a confirmation and update the customer's history, and every one of those steps must agree with the others when something fails halfway.

At the same time, the product catalogue changes shape constantly. A shoe has sizes and colours, a laptop has processor and memory, a saree has fabric and length, and the marketing team wants to add a new attribute this week. Customers expect search that tolerates misspellings, filters by any attribute and returns results in under a second.

These are two different problems. The first needs consistency and structure; the second needs flexibility and speed. The SQL vs NoSQL debate for e-commerce is really about which problem your primary database should optimise for, and the answer is the first one, because money is involved.

Orders, stock and payments: why consistency wins

Consider two customers buying the last unit of a product within the same second. With a relational database, the checkout runs in a transaction: read the stock row with a lock, check the quantity, decrement it, insert the order, commit. The second transaction waits for the first, sees zero stock and fails cleanly. No overselling, no apology email, no refund.

Document databases have added multi-document transactions over the years and the mature ones handle this case correctly today. The difference is in the default. In a relational database every write is transactional and every relationship is enforced by a foreign key unless you turn those features off. In a document store you opt into transactions, define relationships in application code and check them yourself. Over hundreds of features and several developers, defaults matter more than capabilities.

Payments raise the stakes further. A payment record must reference exactly one order, an order total must equal the sum of its lines plus tax minus discounts, and a refund must never exceed what was captured. Constraints, check clauses and foreign keys express these rules once in the schema. Our payment gateway integration work always assumes a relational order and payment ledger, whatever the rest of the stack looks like.

• Keep relational: orders, order lines, payments, refunds, stock levels, stock movements, customers, addresses, coupons and their usage counts, tax rules.
• Can be flexible: product attributes, product content blocks, reviews, wishlists, browsing events, personalisation signals.
• Should be in a cache: sessions, active carts before checkout, rate limits, rendered fragments of category pages.

Catalogue flexibility: the case for NoSQL and the SQL answer

The catalogue is where document databases make their strongest argument. Each product is a document with whatever attributes it needs, categories can carry different attribute sets, and adding an attribute is a code change, not a migration. For a marketplace with thousands of sellers listing very different goods, this flexibility is real.

Relational databases answer with a hybrid schema. Fixed columns hold the fields every product has: SKU, name, price, tax class, status, brand. A JSONB column holds the variable attributes, indexed so that filtering by colour or screen size stays fast. Variants sit in their own table with their own stock rows, because a variant is a thing you sell and must be tracked exactly. This pattern gives you the flexibility of a document for attributes and the rigour of a table for anything with a price and a quantity.

The reason we usually recommend the SQL answer even for the catalogue is that the catalogue is not isolated. Prices feed order lines, stock rows feed availability, categories feed navigation and reporting. When products live in a separate document store, every one of those relationships crosses a system boundary and needs synchronisation code. For a comparison of the two engines in general, see our MongoDB vs PostgreSQL post.

Search: neither database is the search engine

Catalogue search is often the argument that pushes teams toward NoSQL, and it is usually a misdirected one. Neither a relational database nor a document store is a great full-text search engine at scale. Typo tolerance, synonyms, faceted counts across dozens of attributes, relevance tuning and merchandising rules are the job of a dedicated search engine, whether self-hosted or a hosted search service, fed from your primary database by an indexing job.

Below a certain size, PostgreSQL full-text search plus trigram indexes is enough. A store with under about 50,000 products and a few thousand searches an hour can run search from the primary database, with faceted filters served from indexed JSONB attributes. Above that, or when merchandising rules and analytics on search behaviour become important, add the search engine and treat it as a read-only projection of the catalogue that can be rebuilt at any time.

SQL vs NoSQL for e-commerce: comparison table

Workload	SQL (PostgreSQL, MySQL)	Document NoSQL	Recommendation
Orders and payments	Transactions and constraints by default	Transactions available, relationships enforced in code	SQL
Stock and reservations	Row locks prevent overselling simply	Possible with careful atomic updates	SQL
Product catalogue	Fixed columns plus JSONB for attributes	Natural fit for variable attributes	SQL with JSONB; document store for very heterogeneous marketplaces
Search and facets	Full-text and trigram indexes up to mid scale	Text indexes up to mid scale	Dedicated search engine above about 50,000 products
Carts and sessions	Works, but adds write load	Works well	In-memory cache, persisted to SQL at checkout
Reviews, wishlists, events	Fine in tables or JSONB	Fine as documents	Either; keep near the primary store
Reporting and finance	SQL joins and aggregates, every BI tool connects	Aggregation pipelines, export often needed	SQL
Schema changes	Migrations, versioned and reviewed	Application-managed, flexible	Depends on team discipline
Scaling reads	Read replicas and caching	Replica sets, sharding built in	Both scale well past typical store traffic

Hybrid patterns that work in production

Most stores above a certain size end up with more than one data store. The successful ones share a rule: exactly one system of record per fact, and every other store is a derived copy that can be rebuilt.

1. SQL plus cache: PostgreSQL or MySQL for everything durable, an in-memory cache for sessions, carts, rate limits and hot category pages. This is the starting point for nearly every store and it carries most of them a long way.
2. SQL plus search engine: the catalogue is indexed into a search engine by a job that runs on every product change. Search results return product IDs, and the page fetches prices and stock from SQL so that what the customer sees is always current.
3. SQL plus document store for content: rich product content, editorial pages and reviews live as documents managed by a content team, while the commercial record stays relational. The store's page renders from both, keyed by SKU.
4. Event stream for analytics: browsing, add-to-cart and checkout events go to an append-only store or warehouse for personalisation and reporting, never to the transactional database.

The anti-pattern is two systems of record: prices in the document store and also in SQL, stock in both, with code that tries to keep them aligned. That code is where overselling and wrong totals come from.

E-commerce database design: the core tables

A working relational design for a store has fewer tables than people expect. This is the skeleton we start from and extend per project.

• products, product_variants, product_attributes (JSONB), categories, product_categories: the catalogue, with variants as the sellable unit.
• stock_levels, stock_movements: current quantity per variant per location, and an append-only ledger of every change with a reason, which is what makes stock audits and reconciliation possible.
• customers, addresses, sessions_or_carts: identity and pre-checkout state, with carts persisted from the cache at checkout.
• orders, order_lines, order_status_history: the order as placed, frozen prices and names on each line, and every status change with a timestamp.
• payments, refunds, payment_events: a ledger keyed to the gateway's identifiers, including every webhook received, so any dispute can be traced.
• coupons, coupon_redemptions, tax_rates, shipping_rates: commercial rules with usage counts enforced by constraints.

Two rules keep this design honest. Order lines copy the price and product name at the moment of purchase, so later catalogue edits never change history. Stock is changed only through stock_movements, never by editing stock_levels directly, so every unit can be accounted for.

What JK Tech Hub has seen on real online stores

We build e-commerce platforms from Rajkot, India for retailers and manufacturers across India and for clients in the US, UK, UAE, Australia and Europe, and the pattern above comes from those projects. A kitchenware manufacturer in Rajkot selling direct to consumers runs on PostgreSQL with JSONB attributes and database full-text search, roughly 2,000 SKUs and a few hundred orders a day, and has needed nothing more in three years.

A multi-vendor marketplace for a client in the UAE started on a document database chosen for catalogue flexibility. Within the first year we were called in because stock counts drifted from reality during sales events and finance could not reconcile payouts. We moved orders, stock and payments to PostgreSQL over ten weeks, kept the document store for seller-managed product content, and added a search engine fed from both. Oversells stopped the week the transactional tables went live.

A textile brand in Surat with about 40,000 products hit the limit of database search when faceted filtering across fabric, weave, colour and price became the main way customers browsed. Adding a dedicated search engine as a rebuildable projection cut category page response times by more than half and left the relational core untouched. Our e-commerce development page covers the platforms we build and the stack behind them.

Our recommendation

Start with PostgreSQL as your system of record, JSONB for product attributes, an in-memory cache for carts and sessions, and database full-text search. Add a dedicated search engine when the catalogue passes roughly 50,000 products or when merchandising rules matter. Add a document store only for content that a separate team manages and that never carries a price or a quantity. Choose a document database as the primary store only for a marketplace whose listings are so varied that a shared schema is impossible, and even then keep orders and payments relational.

Related reading and next step

• E-commerce development services
• Payment gateway integration with a proper payment ledger
• MongoDB vs PostgreSQL: the engines compared in depth
• PostgreSQL development services
• Building for clients outside India

If you are planning a store or marketplace and want the database design reviewed before development starts, send the details through the contact page or on WhatsApp at +91 7265004040 and we will reply with a fixed quote within two working days.