Relational Databases and ACID
A relational database organizes data into tables, each with a fixed schema of typed columns. Rows in one table reference rows in another via foreign keys. The database enforces these relationships and guarantees the correctness of every operation through a property set known as ACID.
ACID is not just a marketing term. It is a concrete set of guarantees that make financial systems, inventory management, and any workload where correctness is non-negotiable possible to build reliably.
Atomicity
All operations in a transaction succeed or all fail. If a bank transfer debits account A but then crashes before crediting account B, the debit is rolled back. No partial writes.
Example: Transferring money: debit + credit is one atomic unit.
Consistency
A transaction takes the database from one valid state to another. Constraints, triggers, and cascades are enforced. You cannot commit data that violates a foreign key constraint.
Example: Cannot insert a comment for a post_id that does not exist.
Isolation
Concurrent transactions behave as if they ran serially. One transaction cannot see uncommitted changes from another. Isolation levels (read committed, serializable) tune the trade-off with performance.
Example: Two users buying the last ticket: only one succeeds.
Durability
Once a transaction is committed, it survives crashes. Data is written to disk and flushed before the commit is acknowledged. The write-ahead log (WAL) enables crash recovery.
Example: Power loss after COMMIT does not lose the transaction.
How the relational model works
Normalized schema: users, posts, comments
Each piece of data lives in exactly one place. Foreign keys link tables. JOINs assemble the full picture at query time.
When SQL is the right choice
Financial systems
Account balances, transfers, ledgers. ACID guarantees mean money cannot vanish or be double-spent.
Complex reporting
SQL lets you write arbitrary queries across tables at query time, without pre-designing access patterns.
Highly relational data
If your data naturally forms a graph of entities with many-to-many relationships, the relational model fits cleanly.
Strict schema required
Regulated industries (healthcare, finance) often require enforced data types and constraints at the database level.
NoSQL: Four Different Models
NoSQL is not a single technology. It is a category of databases that reject the relational model in favor of data structures better suited to specific access patterns. Document, key-value, columnar, and graph databases each make fundamentally different trade-offs.
The explorer below shows how the same social post data would be stored and queried in three different paradigms. Switch between them to see how the data model shapes what queries are easy and what becomes hard.
CREATE TABLE users ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE NOT NULL ); CREATE TABLE posts ( id SERIAL PRIMARY KEY, user_id INT REFERENCES users(id), content TEXT, created_at TIMESTAMPTZ DEFAULT NOW() ); CREATE TABLE comments ( id SERIAL PRIMARY KEY, post_id INT REFERENCES posts(id), user_id INT REFERENCES users(id), content TEXT );
JSON-like documents grouped into collections. Each document is self-contained and can have a different structure.
Strengths
Rich nested data, flexible schema, fast reads when the document matches the query shape.
Weaknesses
Data duplication across documents. Cross-document transactions are complex.
Best for: User profiles, product catalogs, CMS content, mobile app backends.
A giant dictionary. Every value is stored and retrieved by its exact key. Values can be strings, hashes, lists, sets, or sorted sets.
Strengths
Extremely fast O(1) reads and writes. Atomic operations. TTL support. Horizontal scaling via consistent hashing.
Weaknesses
No query language. You must know the key. Cannot filter or aggregate without maintaining secondary index keys manually.
Best for: Session storage, caching, rate limiting, leaderboards, pub/sub messaging.
Data is stored column by column rather than row by row. Each row can have different columns. Optimized for writing and reading large volumes of data across many rows.
Strengths
Linear write scalability across nodes. Excellent for time-series and append-heavy workloads. Efficient compression of column data.
Weaknesses
Query patterns must be designed at schema time. No JOINs. Data modeling is access-pattern-driven and complex.
Best for: IoT telemetry, analytics, time-series metrics, event logs, anything written at massive scale.
Nodes (entities) and edges (relationships) as first-class citizens. Traversing relationships is as fast as a lookup, regardless of graph size.
Strengths
Natural fit for highly connected data. Relationship queries that would require many JOINs in SQL are single traversals.
Weaknesses
Not general-purpose. Poor fit for tabular or document data. Smaller ecosystem and fewer hosted options.
Best for: Social graphs, fraud detection, recommendation engines, knowledge graphs, network topology.
ACID vs BASE: Consistency Trade-offs
ACID databases prioritize correctness. Every transaction is guaranteed to leave the database in a valid, consistent state. NoSQL databases often trade some of that correctness for scale and availability, following a model called BASE.
BASE is not a downgrade from ACID. It is a different set of trade-offs appropriate for different workloads. A social media like count does not need ACID guarantees. A bank transfer does.
Basically Available
The system always responds to requests, even if the response contains stale or partial data. Availability is prioritized over correctness in network partition scenarios.
vs ACID: SQL databases may block or fail a query to preserve consistency.
Soft state
The state of the system can change over time, even without new input, as replicas converge. The data you read now may differ from the data you read in 100ms.
vs ACID: Once committed, SQL data is stable, reads return the same value.
Eventually consistent
Given no new writes, all replicas will eventually converge to the same value. The window of inconsistency is typically milliseconds to seconds, not permanent.
vs ACID: SQL isolation ensures all clients see the same data at the same time.
The line between ACID and BASE has blurred considerably. MongoDB, DynamoDB, and Cassandra all now offer tunable consistency or opt-in transactions. The real question is not "SQL or NoSQL" but "what consistency guarantees does this workload require, and at what performance cost?"
Consistency guarantees by system
| Database | Model | Notes |
|---|---|---|
| PostgreSQL | ACID | Full serializable transactions. Row-level locking. WAL-based durability. |
| MySQL (InnoDB) | ACID | ACID on the InnoDB engine. MyISAM lacks transaction support. |
| CockroachDB | ACID | Distributed ACID via multi-version concurrency control and Raft consensus. |
| MongoDB | BASE (tunable) | Eventually consistent by default. Supports multi-document ACID transactions since v4.0. |
| Cassandra | BASE | Tunable consistency levels (ONE, QUORUM, ALL). Trade availability for stronger guarantees. |
| DynamoDB | BASE (tunable) | Eventually consistent reads by default. Strongly consistent reads available at higher cost. |
Choosing the Right Database
The right database is determined by your access patterns, not by what is popular or what your team already knows. That said, PostgreSQL is an excellent default: it handles most workloads well, supports JSON columns for flexibility, has excellent tooling, and adds ACID at no extra cost.
Reach for a NoSQL database when you have a specific reason, not out of a desire to use the newest technology. The wrong database for your access patterns creates pain that compounds over years.
Decision guide
| Question | SQL answer | NoSQL answer |
|---|---|---|
| Do you need ACID transactions? | Yes, use SQL | Only if the DB supports them (MongoDB 4+, DynamoDB Transactions) |
| Is your schema fixed or known in advance? | Yes, relational model enforces it | No, document stores allow schema-per-document flexibility |
| Do you need complex, ad-hoc queries? | Yes, SQL handles arbitrary joins and aggregations | Only for pre-designed access patterns |
| Do you need massive write throughput (>100k/s)? | Hard, vertical scaling has limits | Yes, Cassandra, DynamoDB scale writes linearly |
| Is your data highly hierarchical/nested? | Awkward, requires multiple tables and joins | Natural fit for document stores |
| Do you need full-text search? | Basic, use Postgres tsvector or add Elasticsearch | MongoDB Atlas Search, Elasticsearch (a document store) |
Common use cases and their databases
ACID is non-negotiable. Money must not be double-spent or lost.
Flexible schema. Each user can have different optional fields.
TTL-based eviction, O(1) reads. Purpose-built for ephemeral data.
Variable attributes per product. High read volume.
Massive write throughput, time-ordered data, cheap storage.
Friend-of-a-friend queries are traversals, not joins.
Inventory, payment, and order state all need ACID.
Sorted sets provide O(log n) ranked reads with atomic updates.
Either works. Choose by team familiarity and query patterns.
Columnar storage with vectorized execution for aggregations.
Many mature systems use multiple databases: PostgreSQL for transactional data, Redis for caching and sessions, Elasticsearch for full-text search, and S3 for blob storage. This is called polyglot persistence. It adds operational complexity, so only add a second database when the first genuinely cannot do the job.