ACID vs BASE: What These Actually Mean for Your Product Decisions

# ACID vs BASE: What These Actually Mean for Your Product Decisions

TL;DR: ACID (Atomicity, Consistency, Isolation, Durability) guarantees that every transaction leaves your data correct and readable by everyone immediately — pick it for money, inventory counts, and anything where two people seeing different answers is a bug. BASE (Basically Available, Soft state, Eventual consistency) trades that immediacy for availability and scale — pick it for feeds, counters, and anything where a stale read for a few hundred milliseconds is a shrug, not an incident.

Most engineers can recite the acronyms. Fewer can say, without hedging, which one their likes_count column or their wallet_balance table actually needs — and mixing them up is how you end up with double-charged customers or a "trending" feed that takes 30 seconds to update. This is a decision about failure modes, not a database feature checklist.

What the acronyms are actually promising

ACID is a guarantee about a single transaction:

• Atomicity — the transaction happens completely or not at all.
• Consistency — the database moves from one valid state to another (constraints, foreign keys, and invariants hold).
• Isolation — concurrent transactions don't see each other's half-finished work.
• Durability — once committed, it survives a crash.

BASE is a guarantee about a distributed system under load or partition:

• Basically Available — the system responds, even if a node is down or a network partition is happening.
• Soft state — the data can change over time even without new writes, as replicas catch up.
• Eventual consistency — if you stop writing, all replicas will converge to the same value, but not necessarily right now.

These aren't opposites on a single dial. ACID is about correctness inside one transaction; BASE is about what a distributed system does when part of it is unreachable. The reason they get compared at all is the CAP theorem: under a network partition, you choose consistency (reject/queue the write until it's safe) or availability (accept the write and reconcile later). ACID systems generally choose consistency; BASE systems choose availability.

Scenario 1: Payments need ACID, full stop

A funds transfer is the canonical case where "basically available" is not good enough. If a transfer debits one account and the credit to the other account is lost because of a crash mid-write, you have a support ticket and possibly a regulatory problem, not an eventually-consistent inconvenience.

This is why relational databases like PostgreSQL wrap transfers in a single atomic transaction and, where needed, escalate the isolation level to prevent anomalies like write skew — where two concurrent transactions each read a valid state, each independently decide their change is safe, and together break an invariant (for example, two withdrawals from different accounts that share one overdraft limit). PostgreSQL's default isolation level is Read Committed; for money-moving logic you typically want Serializable or explicit row locking instead. (PostgreSQL docs: Transaction Isolation)

-- Transfer $50 from account A to account B, atomically.
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

UPDATE accounts
SET balance = balance - 50
WHERE id = 'acct_a' AND balance >= 50;

-- If the debit affected zero rows, the balance check failed — bail out.
-- (Application code checks the row count here before continuing.)

UPDATE accounts
SET balance = balance + 50
WHERE id = 'acct_b';

COMMIT;
-- On a serialization failure (error code 40001), the application must
-- retry the whole transaction — Serializable isolation trades a small
-- amount of throughput for the guarantee that this race can't happen.

If either UPDATE fails, or the app crashes between them, COMMIT never happens and Postgres rolls back both changes — that's atomicity doing its job, and once COMMIT returns, durability means the transfer survives a crash. In this exact example, the conditional UPDATE ... WHERE balance >= 50 already locks the row and re-checks the balance, so two concurrent debits can't overdraw a single account even at Read Committed. Serializable earns its keep when the rule spans rows or depends on something you read earlier in the transaction — the write-skew cases above. It costs you retry-on-conflict logic, which is a fair trade for a ledger.

This is also why a sensible default is not to put the ledger itself on an eventually consistent store, even if the rest of the product runs on one — keep the transactional core on a database with real ACID guarantees, and feed everything else (search indexes, activity feeds, analytics) from it asynchronously.

Scenario 2: A social feed can tolerate BASE

Now take a "likes" counter or a home feed. If two users like a post at nearly the same instant and the count briefly shows 41 instead of 42 on one replica, nothing breaks. If a follower sees a new post 300ms later than another follower because a read hit a lagging replica, nobody notices. What actually matters for a feed is that it stays available — under load, during a regional blip, during a deploy — and that it eventually shows the same thing everywhere.

This is the exact trade the BASE model is built for, and it's why systems like Amazon DynamoDB default to eventually consistent reads: they're cheaper and lower-latency, and you opt into strongly consistent reads only where you actually need them.

// DynamoDB GetItem request for a post's like count.
// Default consistency — cheaper, lower latency, may lag behind
// the most recent write by a short interval.
{
  "TableName": "Posts",
  "Key": { "postId": { "S": "post_9182" } }
}
// Add "ConsistentRead": true only for paths that truly need it
// (e.g. showing a user their own just-created post) — eventually
// consistent reads cost half as much as strongly consistent ones.

Eventually consistent reads are DynamoDB's default for tables and Local Secondary Indexes, and they cost half as much as strongly consistent reads; all reads from Global Secondary Indexes and DynamoDB Streams are eventually consistent, with no strong option, which is worth knowing before you architect a "read your own write" flow through a GSI. (AWS docs: DynamoDB read consistency)

Apache Cassandra makes the same trade explicit and tunable per request, rather than baking in one default: you choose a consistency level like ONE (fast, least consistent) or QUORUM (majority of replicas, stronger) for each read and write, and the relationship R + W > RF (replication factor) tells you whether a given combination is strongly consistent. (Baeldung: Cassandra Consistency Levels) The consistency level isn't part of the CQL statement itself — the old USING CONSISTENCY clause was removed in CQL 3 — so you set it on the statement in your driver, or with the CONSISTENCY command in cqlsh (cqlsh docs):

-- cqlsh: applies to the statements that follow
CONSISTENCY ONE;

-- Likes as a counter column (post_id text PRIMARY KEY, likes counter):
-- increment in place instead of read-modify-write.
UPDATE like_counts SET likes = likes + 1 WHERE post_id = 'post_9182';

-- Read it back — also ONE, because a stale like count for a
-- few hundred milliseconds costs nothing.
SELECT likes FROM like_counts WHERE post_id = 'post_9182';

It's not really "pick one database"

The more accurate framing: most real products need both models, applied to different data, sometimes in the same request. A checkout flow needs ACID for the payment and the inventory decrement, but can update "12 people viewed this in the last hour" with a BASE-style counter. Even MongoDB — a document database often associated with the BASE side of this conversation — has supported full multi-document ACID transactions since version 4.0 on replica sets and 4.2 on sharded clusters, specifically so teams don't have to bolt on a separate relational database just for the few write paths that need strict guarantees. (MongoDB: Transactions docs)

Getting this mapping wrong in either direction is expensive to unwind — forcing ACID everywhere caps your scale and adds latency where nobody needed the guarantee, while defaulting to BASE for financial or contractual data is the kind of decision that turns into a rebuild once you have real customers and real money moving through it. That's usually the point where teams bring in outside custom software engineering help to re-architect the data layer rather than patch around it — which is a more expensive path than deciding correctly the first time.

FAQ

Is a NoSQL database always BASE and a SQL database always ACID?

No. The SQL/NoSQL split and the ACID/BASE split are different axes. PostgreSQL and MySQL are relational and ACID. MongoDB is a document store (NoSQL) that also supports full ACID multi-document transactions. DynamoDB and Cassandra are NoSQL and default to BASE-style eventual consistency, but both let you request stronger consistency per-operation at a cost.

Can a single application use both models?

Yes, and most non-trivial products do — an ACID store (or ACID-scoped transactions within a multi-model database) for money, contracts, and inventory, and a BASE-tolerant store or configuration for feeds, counters, search indexes, and caches fed asynchronously from the source of truth.

What does eventual consistency actually cost me in practice?

Mainly two things: read-your-own-write bugs (a user who just posted doesn't see it yet on a lagging replica), and reconciliation logic for the rare cases where replicas genuinely disagree. Both are manageable if you decide upfront which flows need strong reads and pay for consistency only there.

Does the CAP theorem mean I have to choose consistency or availability for my whole system?

No — CAP applies per data path during an actual network partition, not as a single global setting. That's exactly why tunable-consistency systems like Cassandra, and multi-model databases like MongoDB, let you make the trade at the level of an individual read or write instead of for the whole application.

Sources

• PostgreSQL Documentation: Transaction Isolation
• PostgreSQL Documentation: Concurrency Control (MVCC)
• AWS Documentation: DynamoDB Read Consistency
• Baeldung: Consistency Levels in Cassandra
• Apache Cassandra Documentation: cqlsh
• MongoDB Documentation: Transactions
• IBM: What is the CAP theorem?