SQL vs NoSQL: How to Choose a Database

Choosing how to store data is one of the most important design decisions, because data is the hardest part to change later.

Relational databases#

Relational databases, such as PostgreSQL and MySQL, store data in tables with fixed columns, and connect them with keys. You query them with SQL.

They provide ACID transactions:

These suit money, orders, and anything with relationships. Modern PostgreSQL handles far more than many beginners assume, and for most projects it is a good first choice.

NoSQL families#

Many NoSQL systems follow BASE (basically available, soft state, eventually consistent), giving up some guarantees to scale out easily.

SQL or NoSQL?#

In interviews, the good answer is not "NoSQL scales better". Say what the access pattern is, and why a database fits it.

Large systems often use several, known as polyglot persistence: orders in PostgreSQL, sessions in Redis, search in Elasticsearch, and files in object storage.

Indexes#

An index is a sorted structure that speeds up lookups, like the index in a book. Without one, the database scans every row:

import bisect
import time

rows = list(range(0, 2_000_000, 2))
target = 1_999_998

start = time.perf_counter()
found = target in rows
scan = time.perf_counter() - start

start = time.perf_counter()
pos = bisect.bisect_left(rows, target)
indexed = time.perf_counter() - start

print("Both found it:", found and rows[pos] == target)
print("Index was faster:", indexed < scan)

steps = 0
low, high = 0, len(rows)
while low < high:
    mid = (low + high) // 2
    steps += 1
    if rows[mid] < target:
        low = mid + 1
    else:
        high = mid
print(f"Rows: {len(rows):,}. Full scan checks up to {len(rows):,}. Binary search took {steps} steps.")

Both found it: True
Index was faster: True
Rows: 1,000,000. Full scan checks up to 1,000,000. Binary search took 20 steps.

Real databases use B-trees, which work on the same idea. Indexes make writes a little slower, and they take space, so index what your queries need. See SQL indexes.

Normalisation and denormalisation#

Normalised data avoids duplication, and uses joins. Denormalised data copies fields to make reads fast. At scale, joins get expensive, so read-heavy systems often store a precomputed copy, accepting that it must be kept up to date.

Normalised:    posts(id, user_id, text)  +  users(id, name)
Denormalised:  posts(id, user_id, user_name, text)

Common mistakes#

Practice#

import math

print(math.ceil(math.log2(1_000_000_000)))

30

Key takeaway#

Next: Database Replication.