Short answers to what readers ask most about this topic.
ACID vs BASE with Postgres examples: what each ACID letter protects, why BASE is a pun and not a standard, and how to choose per feature.
Matthews Wong
Software Engineer
Key Takeaway
ACID is four guarantees a database transaction gives you: atomic, consistent, isolated and durable writes, so money moves all-or-nothing and survives a crash. BASE, meaning basically available, soft state, eventually consistent, is an informal label, not a standard, for designs that trade those guarantees for availability. Use ACID for money and stock, BASE for counters and feeds.
Two UPDATE statements and one crash between them is enough to make 200000 rupiah disappear from a ledger. Nothing in the database complains, because each statement was valid on its own. That small gap is the whole reason ACID exists, and it is the best way to understand what BASE gives up.
This post walks each ACID letter with a PostgreSQL failure you can reproduce, treats BASE honestly as a design vocabulary rather than a specification, and ends with a per-feature checklist. Every behaviour claimed about PostgreSQL and DynamoDB comes from their official documentation, and every number below is arithmetic you can check by hand.
Atomicity is the easiest letter to watch fail. Sari has 500000 and Budi has 100000, so the system holds 600000 in total. A transfer of 200000 is two UPDATE statements, and the script below runs it twice: once as bare statements, once inside BEGIN and COMMIT. PostgreSQL treats every statement outside a transaction as its own implicit transaction, so in the first version the debit commits on its own.
CREATE TABLE accounts (
id int PRIMARY KEY,
owner text NOT NULL,
balance numeric(12,2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'Sari', 500000), (2, 'Budi', 100000);
-- Total money in the system: 500000 + 100000 = 600000
-- Wrong: two autocommit statements. Each one is its own transaction.
UPDATE accounts SET balance = balance - 200000 WHERE id = 1; -- committed
-- ...the connection drops or the app crashes right here...
UPDATE accounts SET balance = balance + 200000 WHERE id = 2; -- never runs
-- Sari 300000 + Budi 100000 = 400000. 200000 has vanished.
-- Right: one transaction. Both updates commit together or neither does.
BEGIN;
UPDATE accounts SET balance = balance - 200000 WHERE id = 1;
UPDATE accounts SET balance = balance + 200000 WHERE id = 2;
COMMIT;
-- A crash before COMMIT leaves Sari 500000 + Budi 100000 = 600000.In the wrong version, a crash between the statements leaves 300000 plus 100000, which is 400000, and nobody can say where 200000 went. In the right version the same crash leaves 600000, because a transaction that never committed simply never happened. The PostgreSQL tutorial states the requirement directly: if something goes wrong partway through, none of the steps executed so far may take effect.
A driver that sends each query on its own gives you one transaction per query. Two back-to-back calls on a connection pool can even run on two different connections. Check that your service layer really opens BEGIN, runs both updates on the same connection, and issues ROLLBACK on the error path.
No, and this mix-up causes half the bad ACID-versus-BASE arguments. In ACID, consistency means a transaction takes the database from one state that satisfies its rules to another. The rules are the constraints you declared: NOT NULL, CHECK, UNIQUE, FOREIGN KEY. In the example, CHECK (balance >= 0) refuses an overdraft, and the violation aborts the whole transaction, including any credit that had not yet run.
BEGIN;
UPDATE accounts SET balance = balance - 700000 WHERE id = 1;
-- ERROR: new row for relation "accounts" violates check constraint
-- "accounts_balance_check"
-- The transaction is now aborted: every later statement is refused
-- until you ROLLBACK, so a half-applied transfer cannot be committed.
ROLLBACK;
-- What the database will NOT check for you: any rule you never declared.
-- "A user may not transfer to their own account" is not enforced by this
-- schema. Declare it (CHECK, FOREIGN KEY, UNIQUE) or enforce it in code.The consequence is that the database enforces only the invariants you wrote down. I prefer to push rules such as non-negative stock into the schema rather than into service code, because a constraint holds for every writer, including a migration script someone runs late at night. Consistency in the replica-agreement sense is a different question, and the sibling post on eventual consistency explained covers it.
Isolation is about concurrent transactions seeing each other's half-finished work. PostgreSQL's default level is Read Committed, where, per the docs, two successive SELECT commands in one transaction can see different data if another transaction commits in between. That is why the read-then-write pattern in the code loses money: both cashiers read 500000, both write 400000, and the final balance is 100000 higher than the correct 500000 minus 100000 minus 100000, which is 300000.
-- Sari's balance is 500000. Two cashiers each take 100000 at the same time.
-- Expected result: 500000 - 100000 - 100000 = 300000.
-- Wrong: read in the application, compute there, write the result back.
-- Session A: SELECT balance FROM accounts WHERE id = 1; -- 500000
-- Session B: SELECT balance FROM accounts WHERE id = 1; -- 500000
-- Session A: UPDATE accounts SET balance = 400000 WHERE id = 1; COMMIT;
-- Session B: UPDATE accounts SET balance = 400000 WHERE id = 1; COMMIT;
-- Final balance: 400000. One withdrawal was silently lost.
-- Right: let the database do the arithmetic on the current row.
UPDATE accounts SET balance = balance - 100000 WHERE id = 1;
-- Under Read Committed the second session waits for the first to commit,
-- then re-reads the row and subtracts from 400000. Final balance: 300000.
-- Or, when you must read first and then decide, lock the row you read:
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- ...application logic...
UPDATE accounts SET balance = 300000 WHERE id = 1;
COMMIT;The fix is usually a better statement rather than a higher isolation level. Arithmetic inside the UPDATE, or a row lock with SELECT FOR UPDATE, closes this particular gap. If you do raise the level to Repeatable Read or Serializable, the docs say the application must be prepared to retry the whole transaction after a serialization failure, reported with SQLSTATE 40001. What each level permits is covered in the post on database transaction isolation levels explained, so I will not repeat that table here.
Search your codebase for the pattern: select a balance, compute in the application, update with a literal. Each match is a lost update waiting for two concurrent requests. Replace it with relative SQL such as balance = balance - 100000, with a CHECK constraint behind it.
Durability means a committed transaction survives a crash. The PostgreSQL docs describe the mechanism: all the updates of a transaction are logged in permanent storage before the transaction is reported complete. With the default synchronous_commit setting of on, COMMIT waits for the local flush of the write-ahead log to disk before it returns success.
SHOW synchronous_commit; -- on (the default): COMMIT waits for the WAL flush
SHOW wal_writer_delay; -- 200ms by default
-- Relax durability for ONE low-value transaction, not the whole server.
-- Worst case: this write is lost in a crash, up to 3 x 200ms = 600ms later.
-- The database stays consistent; the write behaves as if it had aborted.
BEGIN;
SET LOCAL synchronous_commit = off;
INSERT INTO page_views (path, viewed_at) VALUES ('/blog/acid-vs-base', now());
COMMIT;
-- Never do this on a database you cannot rebuild from elsewhere:
-- fsync = off (a crash can cause unrecoverable data corruption)That setting can be loosened per transaction, which is a small piece of BASE thinking inside an ACID database. With synchronous_commit off, the docs say a crash may lose recently reported commits for up to three times wal_writer_delay, which is 3 x 200 ms = 600 ms at the default, yet the database stays consistent, as if those transactions had aborted. Turning fsync off is a different thing: the docs warn of unrecoverable data corruption after a crash. Use the first for page-view logs and never the second for data you cannot rebuild.
BASE stands for Basically Available, Soft state, Eventually consistent. Dan Pritchett introduced it in a 2008 ACM Queue article titled BASE: An Acid Alternative, and the name is a chemistry pun: a base is the opposite of an acid. It describes a design style, not a specification. I know of no standards body that defines it, and nothing certifies a database as BASE-compliant the way an engine can be judged on its ACID behaviour.
The three letters are loose. Basically available means the system keeps answering during a partial failure, possibly with stale data. Soft state means data can change over time without new input, because replicas are still converging. Eventually consistent means that if writes stop, every replica ends up with the same value. The post on eventual consistency explained covers how that convergence works; here the point is the trade.
| Question | ACID | BASE |
|---|---|---|
| What does it promise? | A transaction is all-or-nothing and, once committed, permanent | The system keeps answering, and replicas converge once writes stop |
| During a failure | Rolls back or refuses the write | Accepts the write and may serve stale data |
| Read right after a write | Sees the committed value on the primary | May see the old value for a while |
| Where the cost lands | Locks, conflict checks and retries at write time | Reconciliation and duplicate handling later |
| Typical fit | Balances, stock, orders, invoices | Counters, feeds, caches, search indexes |
| Formal status | Named properties the engine enforces | A label from a 2008 article, no specification |
Read the table as two ends of a dial, not two camps. The right-hand column describes intent, and a single product can sit in either column depending on which operation you call.
Almost every production stack does, often inside one database. Three common shapes:
So a store is rarely ACID or BASE as a whole. The useful question is which operation you are calling and what that operation promises.
Choose per feature, by what a wrong answer costs. Ask these four questions in order for each write path:
A carwash point of sale shows the split. The payment row and the stock decrement for a wax product are core. The dashboard counter for today's washes and the receipt message to the customer are edges. This is a worked example, not a measurement.
Keep the money-moving truth in one transactional store and let everything else hang off it through events. The code below writes the order, the stock decrement and an outbox row in one transaction, and a separate worker publishes afterwards. Because the event row commits atomically with the data, you cannot have an order without its event or an event without its order.
-- ACID core: the order, the stock decrement and the outbox row commit together.
BEGIN;
INSERT INTO orders (id, customer_id, total) VALUES (1042, 7, 85000);
UPDATE stock SET qty = qty - 1 WHERE sku = 'WAX-01' AND qty > 0;
-- 0 rows updated means sold out: ROLLBACK here, nothing leaks out.
INSERT INTO outbox (aggregate_id, topic, payload)
VALUES (1042, 'order.created', '{"orderId":1042}');
COMMIT;
-- BASE edge: a separate worker drains the outbox. Delivery is at-least-once,
-- so every consumer (receipt message, dashboard counter, search index) must
-- tolerate seeing the same event twice.
SELECT id, topic, payload FROM outbox
WHERE sent_at IS NULL ORDER BY id LIMIT 50
FOR UPDATE SKIP LOCKED;
-- publish each row, then: UPDATE outbox SET sent_at = now() WHERE id = ANY(...)The edge pays in at-least-once delivery, so consumers must be idempotent, and dashboards lag by however long the worker takes. Accept that lag explicitly. The transactional outbox post covers Postgres in more depth, and the saga post covers what to do when the core itself spans several services.
ACID and BASE are not rival camps but two promises you can make per write. Wrap anything that moves money or stock in a transaction, declare invariants as constraints, prefer relative updates over read-modify-write, and let counters, feeds and caches trail behind through an outbox. When someone asks which one your database is, ask which operation they mean.