Intermediate 3 min · March 05, 2026

SQL vs NoSQL — Financial Transactions Require ACID

Three production incidents in 4 months from a NoSQL migration: double-charges, analytics errors, schema bugs.

Naren · Founder

Plain-English first. Then code. Then the interview question.

⚡Quick Answer

SQL databases store data in structured tables with fixed schemas and support complex queries with JOIN — use for relational, transactional data
NoSQL databases use flexible schemas (documents, key-value, wide-column, graph) — use for high-velocity writes, variable data shapes, or massive horizontal scale
The decision driver: what shape is your data and how do you access it — not 'which is faster'
SQL: strong consistency (ACID), complex queries, well-understood tooling, vertical scaling
NoSQL: eventual consistency by default, simple access patterns, horizontal scaling by design
Biggest mistake: choosing NoSQL because it 'scales better' without defining the access pattern first

Every application you've ever used — Instagram, your bank's website, Spotify, Uber — is powered by a database. The choice of which type of database to use isn't just a technical detail. It's an architectural decision that shapes how your app scales, how fast it reads and writes data, and how painful future changes will be.

For most of the web's history, the answer was always SQL. Relational databases have decades of tooling, predictable behaviour, and robust consistency guarantees. The rise of NoSQL in the late 2000s introduced new trade-offs: more flexible schemas, built-in horizontal scaling, and access patterns that map directly to how applications actually query data.

This guide gives you the mental framework to make the right choice for your project — not based on hype, but based on your actual data shape and access patterns.

SQL Databases — The Relational Model and What It Gives You

SQL (Structured Query Language) databases organise data in tables with rows and columns. Every row in a table has the same columns. Every column has a defined data type. Foreign keys connect related tables. This structure is called the relational model and it was designed specifically to eliminate data redundancy and ensure consistency.

The relational model's biggest advantage is the query language. SQL lets you ask complex questions across multiple tables with JOINs, filter with WHERE, aggregate with GROUP BY, and sort and page with ORDER BY / LIMIT. These operations are built into the database engine and run efficiently regardless of which application framework is on top.

The transactional guarantees (ACID) mean that multiple related operations are atomic — either all of them succeed or none of them do. A bank transfer either moves money from both accounts or neither — no partial state. These guarantees are the reason relational databases have powered financial systems, e-commerce, and enterprise software for decades.

NoSQL Databases — Four Types, Four Use Cases

NoSQL is not one thing — it is a category that covers four fundamentally different database types, each optimised for a specific access pattern.

Document databases (MongoDB, CouchDB) store data as JSON-like documents. Each document can have different fields. Ideal when your data shape genuinely varies per record — an e-commerce product with 3 attributes for a book and 50 attributes for a TV.

Key-value stores (Redis, DynamoDB) store a value at a key. Lookups are O(1). No complex querying. Ideal for sessions, caches, leaderboards, and any access pattern that is always 'get the thing by ID.'

Wide-column databases (Cassandra, HBase) store data in column families, partitioned by a key. Optimised for high-velocity writes and large-scale time-series or event data. Access is always by partition key — complex joins are not supported.

Graph databases (Neo4j) store nodes and relationships. Ideal for social networks, recommendation engines, and any domain where the relationships between entities are the primary data.

Making the Decision — The Two-Question Framework

Most database selection debates waste time on the wrong questions. The right framework reduces to two questions.

Question 1: What shape is your data? If your data is relational — entities with well-defined foreign key relationships — SQL is almost always the right choice. If your data is genuinely variable per record (product attributes, user-generated content, configuration objects), a document store might be appropriate.

Question 2: What are your access patterns? If you query by many different attributes, use complex filters, and need aggregations — SQL and its query planner are built for this. If you always access data by a single key (user_id, device_id, session_token) and need extreme throughput — NoSQL is appropriate.

The secondary question: what scale are you actually at? Most applications operate at a scale where PostgreSQL with proper indexes handles the workload comfortably. The decision to add NoSQL should be driven by a measured problem — a specific query that cannot be made fast enough in PostgreSQL — not by anticipated future scale.

Factor	PostgreSQL (SQL)	MongoDB (Document)	Redis (Key-Value)	Cassandra (Wide-Column)
Schema	Fixed, enforced	Flexible per document	No schema	Fixed column families
Consistency	Strong (ACID)	Tunable (ACID available)	Strong for single ops	Eventual (tunable)
Query model	SQL — complex joins, aggregates	Document queries, limited joins	Key lookup only	Partition key + clustering
Write throughput	10K–100K/s	50K–200K/s	1M+/s	100K–1M+/s
Horizontal scale	Complex (Citus, sharding)	Built-in sharding	Redis Cluster	Native linear scale
Best for	Transactions, complex queries	Variable-schema content	Cache, sessions, real-time	Time-series, event streams

Key Takeaways

SQL: fixed schema, ACID transactions, complex queries — the correct default for relational, transactional data.
NoSQL covers four distinct types: document, key-value, wide-column, and graph — each optimised for a specific access pattern.
The two decision questions: what shape is your data, and how do you access it? — not which is faster.
Start with PostgreSQL. Add each additional database only when you have a specific measured problem it cannot solve.

Common Mistakes to Avoid

Choosing NoSQL because it scales without defining the access pattern
Symptom: The NoSQL application requires frequent cross-collection joins or multi-document transactions — operations that NoSQL makes expensive or impossible
Fix: Write down your top 5 most frequent queries before making any database decision. If they require joins, complex aggregates, or multi-row atomic updates, SQL is almost always the correct choice.
Using a document database for strongly relational data
Symptom: The application code spends significant time performing joins in application code across multiple collections; schema drift causes deserialization failures as documents diverge in shape
Fix: Relational data with foreign key relationships belongs in a relational database. If you find yourself writing application-level joins, migrate the affected collections to PostgreSQL.
Adding NoSQL to a system that has not measured a specific bottleneck
Symptom: The team has two databases to maintain but no measurable performance improvement; operational complexity has increased without a corresponding benefit
Fix: Profile PostgreSQL first. Add indexes, tune queries, add read replicas. Only add a second database when you have a specific measured query that cannot be made fast enough in PostgreSQL with proper indexing.

Interview Questions on This Topic

QWhen would you choose a NoSQL database over PostgreSQL for a new feature?Mid-levelReveal
When the access pattern maps poorly to the relational model and well to the NoSQL database's strengths. Specifically: a Redis key-value store for session management and caching (always access by token — no joins needed); Cassandra or DynamoDB for high-velocity event streams or user activity feeds that are always queried by a single partition key (user_id, device_id) with a time range; MongoDB when the data schema genuinely varies per record (product catalog with different attributes per product type). I would not choose NoSQL speculatively for anticipated scale — PostgreSQL handles most workloads at production scale, and the operational cost of an additional database must be justified by a measured problem.
QWhat does eventual consistency mean and when is it acceptable?Mid-levelReveal
Eventual consistency means that after a write, all nodes in the distributed database will eventually reflect the new value — but there is a window (typically milliseconds to seconds) where different nodes may return different results. It is acceptable when the business domain tolerates brief inconsistencies: a social media like count showing 1,247 instead of 1,248 for 200ms harms nothing. It is not acceptable when strong consistency is required: a bank balance or inventory count must be accurate at the moment of query — 'eventually accurate' is not sufficient. The business domain determines acceptability. Financial, medical, inventory, and compliance data almost always require strong consistency.
QDesign the database architecture for a ride-sharing app. What databases would you use and why?SeniorReveal
PostgreSQL for the transactional core: user accounts, driver profiles, trip history, and payments all require ACID transactions and relational integrity. A payment must atomically deduct from one balance and credit another — strong consistency is non-negotiable. Redis for real-time driver location: GPS pings come in thousands of times per second from active drivers. Redis geospatial commands (GEOADD, GEORADIUS) store millions of coordinates and return nearby drivers in sub-millisecond. This data is ephemeral — only the current location matters, not history. Cassandra for trip event streams: GPS coordinates sampled every few seconds for every active trip produce high-velocity time-series data. Cassandra partitioned by trip_id handles this write volume and allows efficient time-range queries on trip data. Elasticsearch for trip search: full-text search across trip notes, addresses, and driver names requires inverted index search that relational databases handle poorly at scale.

Frequently Asked Questions

Is NoSQL faster than SQL?

It depends on the access pattern. A Redis key-value lookup is faster than a SQL query for the same operation. A Cassandra wide-column read with a known partition key is faster than a SQL JOIN. But a complex analytical query with multiple JOINs, GROUP BY, and ORDER BY will run faster in PostgreSQL (which is optimised for it) than in MongoDB (which is not). Speed depends on the match between access pattern and database type, not on SQL vs NoSQL as categories.

Can MongoDB replace PostgreSQL?

For some domains, yes. For others, no. MongoDB is appropriate for variable-schema content, high-velocity document writes, and applications that always access data by a document ID or a simple field. It is not appropriate for financial transactions requiring multi-document atomicity, complex analytical queries requiring joins across collections, or any domain where relational integrity between entities is a requirement.

What is polyglot persistence?

Using multiple database types in the same application, each chosen for a specific domain and access pattern. For example: PostgreSQL for transactional data, Redis for caching and real-time features, Cassandra for event streams. The trade-off is operational complexity — each additional database requires monitoring, backup, and on-call procedures. Polyglot persistence is appropriate at scale where the performance gains justify the overhead.

🔥

That's NoSQL. Mark it forged?

3 min read · try the examples if you haven't