Why this matters
- "SQL vs NoSQL" interview questions test practical judgment, not memorised slogans about scale.
- VaultCommerce runs Postgres for money, Redis for sessions, and Elasticsearch for search — three stores, one platform.
- Wrong store choice shows up as schema gymnastics, eventual consistency bugs, or JOIN queries that never finish.
- Teams that default to one database for every microservice often rebuild when access patterns diverge.
What SQL databases optimise for
Postgres gives VaultCommerce multi-table transactions, foreign keys, and ad-hoc reporting in one engine:
BEGIN;
INSERT INTO orders (customer_id, total_cents) VALUES ('user-42', 15999);
INSERT INTO order_items (order_id, product_id, qty)
VALUES (currval('orders_id_seq'), 881, 1);
UPDATE products SET stock = stock - 1 WHERE id = 881 AND stock > 0;
COMMIT;
If stock hits zero, the whole block rolls back — no orphan order line, no oversell.
When VaultCommerce chooses SQL
- Money and inventory — ACID transactions across related rows.
- Ad-hoc analytics — Analysts write JOIN queries without redeploying application code.
- Referential integrity — Constraints reject bad data at the boundary.
What NoSQL stores optimise for
NoSQL is an umbrella, not a single product. VaultCommerce uses different NoSQL flavours for different jobs:
NoSQL in the VaultCommerce stack
- Redis (key-value) — Sub-millisecond session lookups:
GET session:token-xyz. - Elasticsearch (document/search) — Full-text product search with fuzzy matching and facets.
- DynamoDB (wide-column, later modules) — High-write order events keyed by customer partition.
Each trades some relational convenience for horizontal scale or specialised access speed.
Decision signals that actually matter
Ignore hype. Ask four questions before choosing:
Practical selection checklist
- Do multiple rows must commit or roll back together? → SQL transaction.
- Is the schema stable or wildly variable per record? → Document store may reduce migration churn.
- Will queries join across entity types? → Relational model keeps that natural.
- Is write volume per key so high that a single node saturates? → Consider partitioned NoSQL.
VaultCommerce's checkout path scores 1 and 3 heavily — Postgres wins. Product search scores full-text and facet requirements — Elasticsearch wins despite also storing product metadata in Postgres as source of truth.
Polyglot persistence in one request
A single "search and buy" user journey touches multiple stores:
// Simplified VaultCommerce search-then-checkout flow
ProductHit hit = searchClient.findByQuery("wireless headphones");
Product product = productRepo.findById(hit.getProductId()); // Postgres
cartService.addItem(sessionId, product); // Redis cart hash
Postgres remains authoritative for price and stock. Redis holds ephemeral cart state. Elasticsearch is a read-optimised index rebuilt from Postgres changes — not a second source of truth for payments.
Myths to discard
| Aspect | Myth | Reality at VaultCommerce |
|---|---|---|
| NoSQL = web scale | Only NoSQL scales | Postgres handles millions of orders with proper indexing and pooling |
| SQL = rigid | Schema changes are impossible | Flyway migrations evolve schema weekly |
| Pick one | One DB per company | Polyglot persistence per access pattern |
NoSQL = web scale
MythOnly NoSQL scalesReality at VaultCommercePostgres handles millions of orders with proper indexing and poolingSQL = rigid
MythSchema changes are impossibleReality at VaultCommerceFlyway migrations evolve schema weeklyPick one
MythOne DB per companyReality at VaultCommercePolyglot persistence per access pattern
Quick recall
Everything you need if you only revisit this box.
- SQL excels at transactional, relational workloads; NoSQL families optimise for scale, flexibility, or specialised queries.
- VaultCommerce uses Postgres for money, Redis for sessions, Elasticsearch for search — not one store for everything.
- Choose based on transactions, schema shape, join needs, and per-key write volume.
- Designate a single source of truth; other stores are indexes or caches.
- "Web scale" does not automatically mean NoSQL — tuned SQL handles most e-commerce OLTP.
Test yourself
Answer these before moving on — recall is what makes it stick.