SQL vs NoSQL, the CAP Theorem and Consistency Models

Murad says: "Let's put everything in MongoDB, it scales." Leyla answers: "I would keep everything in PostgreSQL." Who is right? Both, halfway. Prowid Photos stores very different things: accounts and follows, photo files, like counters, sessions, search by captions. The question is not "SQL or NoSQL" but "what does each part need and what are we ready to sacrifice". In this lesson we look at the families of stores, ACID and BASE, the CAP theorem (and what a "partition" means there), consistency models and how to choose a store for each part of a system.

"SQL or NoSQL" is the wrong question

NoSQL is read as "not only SQL". It is not one kind of database but a scatter of families that appeared in the 2000s, when people needed to grow onto many servers, store flexible data and answer narrow queries fast. The borders are blurred: PostgreSQL stores JSON (jsonb), and MongoDB has transactions. The main families:

💡 Do not confuse wide-column (Cassandra) with analytical columnar databases (ClickHouse, BigQuery): those store data by columns and quickly compute aggregates over billions of rows. And photo files live in none of them: they go to S3 object storage.

Relational databases and ACID

Tables, a strict schema, SQL and the main trump card: ACID transactions. Atomicity (all changes apply or none), Consistency (constraints are not violated), Isolation (parallel transactions do not corrupt each other's data), Durability (what was confirmed survives a crash). The classic is transferring 100 AZN from Leyla to Murad:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- Leyla
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- Murad
COMMIT; -- both changes happen, or neither does

A relational database is ideal when data is connected, integrity matters and you need queries "any way you like". It grows vertically and with replicas; sharding, as we saw, is harder.

The other families by example

The document model keeps together what is read together, with no JOIN. The schema is flexible, but data gets duplicated and links between documents are the weak spot. You worked with MongoDB in the lesson "MongoDB: The Document Model and Getting Started":

const photo = {
  _id: 'p42',
  owner: { id: 7, name: 'Leyla' },
  caption: 'Baku at night',
  tags: ['baku', 'night', 'city'],
  exif: { camera: 'X100V', iso: 800 },
  comments: [
    { by: 'Murad', text: 'Great shot!' },
    { by: 'Aysel', text: 'Where is this?' },
  ],
};