How do you decide between a SQL and a NoSQL database for a new product?

I start from the access patterns. If the data is relational and I need joins and transactions, such as orders and payments, I use a relational database. If records are nested objects read as a whole, a document store fits. If the only question is "value for this key", a key-value store is simple and fast. If I am unsure, I start with PostgreSQL, since it scales a long way. The trade-off is flexibility and strong guarantees against ease of spreading writes across many machines.

How does an index speed up a query, and what does it cost?

An index is a sorted structure, usually a B-tree, from column values to row locations. Instead of scanning every row, the database reads a few index pages to find the row, so lookups grow very slowly with table size. The cost is extra disk space and extra work on every insert, update and delete of the indexed column, because the index must stay sorted. So index the columns you filter, join or sort on, and no more.

Design a database schema for a library that lends books to members.

Tables: members, books (or titles and physical copies), and loans linking a member to a copy with borrowed and due dates and a returned date. Loans resolve the many-to-many relationship. Primary keys on each table, foreign keys from loans to members and copies, and indexes on the columns used by the main queries, such as loans by member and overdue loans by due date. I would keep it normalised, since the data is small and relational. The trade-off is that listing a member's loans needs a join, which is cheap with the right indexes.

When would you denormalise a schema, and what do you risk?

Denormalise when a read is hot and a join is measurably too slow, for example copying the customer name into each order so the list of orders needs no join, or storing a counter instead of counting rows. The risk is that copies can disagree: when the source fact changes, every copy must be updated, or you accept slightly stale data. Start normalised, measure, and denormalise deliberately with a plan to keep copies in sync.

A page that used to load in 100 ms now takes 5 seconds as the data has grown. How do you find and fix it?

Find the slow query from logs or monitoring, then run EXPLAIN to see the plan. A sequential scan on a big table usually means a missing index on the filtered, joined or sorted column. Add the index and check the plan again. Also look for N+1 queries, selecting more columns or rows than needed, and missing limits. If reads are still heavy, add caching or read replicas. The trade-off of a new index is slower writes and more storage.

Databases and Data Modelling

Why you need to think about your database, and when to choose which

Almost every system keeps its important data in a database, and that choice is hard to undo later. A database stores data safely on disk, finds it again quickly, and (for many kinds) keeps it correct when several things change at once. A bad choice or a bad design shows up as slow queries, wrong data or painful changes.

Getting it right gives you two things interviewers always ask about:

Correct, durable data: what you save stays saved, and related records stay consistent.
Fast queries at scale: the right structure and indexes keep reads quick as the data grows from thousands to millions of rows.

Choose a relational (SQL) database when:

Your data has relationships: customers, orders, products that refer to each other.
You need transactions: several changes that must all happen or none, such as moving money.
You want to ask flexible questions later with joins, filters and totals.

Choose a document, key-value or other NoSQL store when:

Records are nested and vary in shape, and you usually read one whole record (a document store).
You only look things up by one key at very high speed (a key-value store).
You have a special shape such as a graph of connections, a time series, or a huge write volume that one machine cannot take.

Skip the fancy choice when:

You are small and unsure: a single relational database such as PostgreSQL is a safe default for most products.
You pick NoSQL only because it "scales": check what you really need, because modern relational databases scale a long way.

Try it

Choose how the database looks up a row, how big the table is, and what to do. Press play and watch how many pages it reads.

Finding a row with and without an index

Step 1 of 5 · Press Space to play, arrow keys to step

Lookup:

Table size:

Do:

Table scan: rows checked

TableWorking...

A table with 1,000 rows. There is no index, so the database must scan the table row by row. Assume about 100 rows fit on a page. Press play.

rows:: 1,000
rows looked at:: 0
pages read:: 0

Teaching model: about 100 rows fit on a page and about 100 keys fit on an index page. Real databases differ, but the shape is the same.

Speed

Things to try:

No index on a table of 1,000,000 rows, Find a row near the end: the database scans most of the table, about 9,000 pages. Switch to With an index: 4 page reads.
Keep the index and compare table sizes. For 20, 1,000 and 1,000,000 rows, the index needs 1, 2 and 3 levels. A table 50,000 times bigger needs only two more steps.
Find a row that does not exist with no index: the scan reads every row, 10,000 pages for a million rows. A miss is the most expensive scan.
Insert a new row with and without an index. The index costs a second write on every insert, because it must stay sorted. Reads are faster and writes are slower.

Types of database

Type	How data is stored	Good when	Weakness
Relational (SQL)	Tables of rows and columns, linked by keys, queried with SQL	Relationships, transactions, flexible queries	Harder to spread writes across many machines; schema changes need care
Document	JSON-like documents, often nested	Records that vary, read as a whole	Joins across documents are awkward; easy to duplicate data
Key-value	One key to one value	Caches, sessions, simple lookups at huge speed	You can only ask by key
Wide-column	Rows grouped by a key, with flexible columns, spread across many machines	Very large write volumes, known access patterns	You must design tables around queries; limited ad hoc queries
Graph	Nodes and relationships	Questions about connections: friends of friends, routes	A niche; not the best for plain tables

Other special-purpose stores exist, for example time-series databases for measurements over time and search engines for text search.

Normalised vs denormalised schemas

Normalised: each fact is stored once, and other tables refer to it by key. A customer's address lives in the customers table, and orders point at the customer. Updates are simple and there are no copies to disagree. Reading an order and its customer needs a join.
Denormalised: facts are copied to where they are read, for example the customer's name stored inside every order document. Reads are cheaper and need no join. But when the name changes, every copy must be updated.

Start normalised. Denormalise on purpose, for a measured read problem, and know how you will keep the copies in sync. Document stores lean denormalised by design.

How an index works

An index is a separate, sorted structure that maps a column's values to where the rows are, usually a B-tree. Without it the database must check rows one by one (a scan). With it, the database walks a short tree of pages and jumps straight to the row.

The query says WHERE customer_id = 42.
The database reads the index root page, which says which child page covers 42.
It follows one page per level, a few reads even for millions of rows.
The last index page holds a pointer to the table page with the row, and the database reads it.

Rules to remember:

Primary keys and UNIQUE columns are indexed automatically in most databases.
Index the columns you filter, join or sort on, such as foreign keys.
An index has a cost: extra disk space, and an extra write on every insert, update or delete. Do not index everything.
A composite index on (a, b) helps queries on a, or on a and b. It does not help a query on b alone, so column order matters.
Check with EXPLAIN. It shows whether the database really uses the index.

Relational vs document vs key-value

Concern	Relational (SQL)	Document	Key-value
Relationships and joins	Strong	Weak, usually copied	None
Transactions across records	Strong	Limited, varies by product	Usually single key only
Flexible shape	Needs a schema change	Easy	Easy
Flexible ad hoc queries	Yes	Some	No, by key only
Raw lookup speed	Good	Good	Excellent
Best for	Orders, accounts, anything related	Profiles, catalogues, content	Caches, sessions, counters

A relational database is the safe default. Use a document store when the record is naturally one nested object, and a key-value store when the question is always "give me the value for this key".

Avoiding a new single point of failure

The database is usually the most important single point of failure, because every app server depends on it. Protect it:

Backups, and test restoring them. A backup you have never restored is a hope, not a plan.
Replication to a standby that can take over (see the Replication and Sharding lesson).
Limits on connections and slow queries, so one bad query cannot take everyone down.
Do not share one database between unrelated services, or one team's bad query becomes everyone's outage.

The trade-off: more copies and more machines to run, in return for survival after a failure.

Common mistakes

No index on a column you filter or join on. The query scans the table and slows down as data grows. Add the index and check with EXPLAIN.
Too many indexes. Each one slows every write. Keep only the ones your queries use.
Storing money as floating point. It loses precision. Use whole cents or a decimal type.
The N+1 query problem. One query for a list, then one more query per item. Fetch them together with a join or a batch.
Picking NoSQL for the label. If your data is relational, you will rebuild joins by hand. Start from your access patterns.
Never testing restores. Backups that fail on the day you need them are common. Practise restoring.

How to talk about databases in an interview

Start from the access patterns: "the main queries are these three", then pick the store and the shape of the data.
Say why SQL or NoSQL, using relationships, transactions and query flexibility, not fashion.
Name the indexes you would add and what they cost on writes.
Mention unprompted: backups, replication, and what happens when the database is slow or down.
Name the trade-off: for example, denormalising for fast reads at the cost of keeping copies in sync.

Where you meet databases in real systems

Every app with accounts, orders or content has one. Here are three drawn out, the first with configuration you can run, then more examples.

Example 1: An orders table with an index

An online shop stores customers and orders. "Show all orders for this customer" is the query that runs constantly, so the customer_id column on orders needs an index. Open the example below to see the picture and the SQL. A document-style version of the same order is included to show the other approach.

Config: tables, an index, and a way to check it is usedSetup 1 small scriptCode no code changes

The query planner chooses a plan. With an index on the filtered column it walks the index to the matching rows. Without one it must read the whole table.

-- PostgreSQL: two related tables, one index, and a way to check the index is used.

-- A customer has many orders (one-to-many). The foreign key keeps orders pointing at real customers.
CREATE TABLE customers (
    id     bigint PRIMARY KEY,                  -- a primary key is indexed automatically
    email  text NOT NULL UNIQUE,                -- UNIQUE also creates an index on email
    name   text NOT NULL
);

CREATE TABLE orders (
    id          bigint PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers (id),
    total_cents integer NOT NULL CHECK (total_cents >= 0),  -- whole cents, never floating point money
    created_at  timestamptz NOT NULL DEFAULT now()
);

-- Without this, "all orders for one customer" scans the whole orders table.
CREATE INDEX orders_customer_id_idx ON orders (customer_id);

-- Check the plan. EXPLAIN shows what the database intends to do. Look for "Index Scan" or
-- "Bitmap Index Scan" instead of "Seq Scan" on a large table.
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

The PostgreSQL script was run in a real PostgreSQL 16 container: it created the tables and index, and EXPLAIN on 200,000 rows reported a Bitmap Index Scan on the new index. The SQLite script was run with Python's built-in SQLite and showed the plan change from SCAN to SEARCH ... USING INDEX. The JSON document was checked to be valid JSON. Names and values are examples.

Example 2: A normalised schema

Customers, orders and order items are stored in separate tables and joined by keys. Each fact lives once, so changing a customer's name is a single update.

One customer has many orders, and one order has many items. Each arrow is a key that links a row to the row it belongs to, so each fact is stored once.

Example 3: A query that slows down as data grows

A query that scans is fine on a small table and slow on a large one. Teams find the culprit by looking at slow-query logs and plans, then add the index or change the query.

Measure first. Read the plan, add the missing index or change the query, and check the plan again. Fixing one slow query often removes the need for more servers.

More real-world scenarios

4. Banks and payments

Moving money needs a transaction: take from one account and add to another, or do neither. Relational databases are built to guarantee that, even if the server fails in the middle.

5. Online shops

Products, customers, orders and stock are related and need consistency, so shops usually keep them in a relational database. Product search is often handled by a separate search engine fed from it.

6. User profiles and content

A profile or an article is naturally one nested object that is read whole. A document database stores it as it is used, and different profiles can have different fields.

7. Sessions and counters

A login session or a page-view counter is read and written by a single key, very often. A key-value store is a natural fit and keeps these off the main database.

8. Time series and monitoring

Sensors and servers produce a measurement every few seconds, forever. Databases built for time series store these compactly and make "average over the last hour" quick.

9. Social graphs and recommendations

Questions like "who are the friends of my friends" are about relationships. Graph databases make following connections cheap, while a relational database needs repeated joins.

10. Analytics and reporting

A report that adds up a billion rows works best in a store that keeps each column together (a columnar warehouse). Teams copy data from the live database into one so reports do not slow the shop.

Tools and managed services

Kind	Name	Notes
Open source	PostgreSQL, MySQL, SQLite	Relational databases. SQLite runs inside the app with no server
Open source	MongoDB	Document database
Open source	Redis	Key-value store
Open source	Apache Cassandra	Wide-column database for very large write volumes
AWS	Amazon RDS, Amazon Aurora	Managed relational databases
AWS	Amazon DynamoDB	Managed key-value and document database
Google Cloud	Cloud SQL, Cloud Spanner	Managed relational databases
Google Cloud	Firestore, Bigtable	Managed document and wide-column databases
Azure	Azure SQL Database	Managed relational database
Azure	Azure Cosmos DB	Managed multi-model NoSQL database

The takeaway

Whenever a design has data that must be stored safely and queried quickly, the first box you draw is a database chosen from the access pattern, with the right indexes and a tested backup.