Relational databases and PostgreSQL

Most product designs start with one relational database, and many finish with one. PostgreSQL is the one to know well, because the properties you rely on (transactions, constraints, indexes) are the same in MySQL and most others.

Crux: choose Postgres for the guarantees (transactions, uniqueness, joins), and scale it in order: indexes, then read replicas, then partitioning, then sharding.

What it is and why it is the default

A relational database stores data in tables of rows and columns, and you query it with SQL. Its value is not the table. It is what the database promises.

• ACID transactions. A group of changes either all happen or none do (atomicity). Constraints always hold (consistency). Concurrent transactions do not see each other's half-done work (isolation). Committed data survives a crash (durability).
• Constraints. Primary keys, unique keys, foreign keys and checks are enforced by the database, not by hope in application code.
• Joins. Related data is kept once and combined at query time.
• A flexible query language. You can ask a new question later without changing how the data is stored. That matters when requirements move.

This is why the usual advice is to start here for product designs. Say the property you need ("an email must be unique", "the transfer must debit and credit together"), not "I like SQL".

How it works, at the level you must explain

Storage, indexes and the write-ahead log

• Rows live in pages on disk. Without an index, a query reads the whole table (a sequential scan).
• An index is a second structure that lets the database jump to rows. The default is a B-tree: sorted, so it serves equality, ranges and ordering. A hash index serves equality only. Other kinds exist (GIN for arrays, JSON and full text; GiST and the PostGIS extension for geographic data).
• Every index speeds up reads and slows down writes, because each write must update it too. Index the columns you filter and sort on. 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. Put the equality column first.
• Before changing data pages, Postgres writes the change to the write-ahead log (WAL). After a crash it replays the log. The same log is what ships to replicas.

Concurrency: MVCC and isolation

Postgres uses multi-version concurrency control (MVCC). A writer creates a new version of a row, and readers keep seeing the old version until the writer commits. So readers do not block writers. The cost is dead old versions, which a background process called vacuum cleans up.

The isolation level decides what a transaction can see.

• Read committed (the default): each statement sees data committed before it began.
• Repeatable read: the whole transaction sees one snapshot.
• Serializable: as if transactions ran one after another. Some will fail and must be retried.

For contention on one row, the usual tools are SELECT ... FOR UPDATE (lock the row), a version column (optimistic check), or a unique constraint that makes the second writer fail. These are covered again in the contention pattern.

Scaling, in order

• Indexes and query fixes. Cheapest and often enough. Look at the query plan (EXPLAIN).
• Read replicas. A primary takes all writes. It streams the WAL to replicas that serve reads. Replication is usually asynchronous, so a replica can be a little behind (replication lag). A user who just wrote something may not see it on a replica.
• Partitioning. One big table is split into smaller ones inside the same database, for example by month. Queries that filter on the partition key touch only some partitions, and old partitions can be dropped cheaply.
• Sharding. The data is split across several independent databases by a key. Postgres does not do this for you out of the box. You, or an extension or managed service, route each query to the right shard. Cross-shard joins and transactions become hard.

Which operations need which guarantee

Do not say "Postgres" as one block. Walk through the operations and give each the guarantee it needs. Take a library-lending app.

• Borrowing a book writes a loan row, lowers the available copies and logs the event. These three must succeed or fail together, so they go in one transaction. This is where the relational database earns its place.
• A member's "books borrowed this year" counter can be a little stale. Update it later, from a job, or compute it with a query. No transaction needed.
• Two members grabbing the last copy needs a rule: a lock on the copy row, or a unique constraint on (copy_id, returned_at IS NULL). Name the rule.

Matching each operation to a need shows judgment, which is what the interviewer is looking for.

Indexes: the details that get probed

• The planner picks an index only when it expects to beat a scan. On a column where most rows share a value (a status that is active for 95% of rows), it may ignore the index. Check with EXPLAIN.
• A partial index covers only some rows (WHERE returned_at IS NULL), which keeps it small when you only ever look up open loans.
• A covering index carries extra columns, so the query is answered from the index alone.
• Order matters in (member_id, borrowed_at): it serves "this member's loans, newest first" with no extra sort.
• Each index also costs disk and slows inserts. A table with ten indexes writes eleven structures per row.

Staying at the right depth

An interview asks for an architecture decision, not a lecture on page layout. Name MVCC, the WAL or vacuum in a sentence if it explains a trade-off (for example, why long transactions hurt). Then return to the design. The opposite error is a sweeping line such as "NoSQL scales better". Say which limit you expect to hit (write rate, size, availability across regions) and why indexes, replicas and partitioning will not cover it.

When to use it, trade-offs, numbers

• What: a single-writer relational database with strong transactional guarantees.
• Use when: you have related data, need uniqueness or multi-row transactions, and the access patterns may change. Orders, users, bookings, payments.
• Know: B-tree indexes, composite index order, MVCC, replication lag, and that sharding is the last rung.
• Careful: the write side scales up before it scales out. Do not say "we will shard" without naming the partition key and what it costs you.

Numbers worth holding as rough guides (they depend heavily on hardware and row size, so treat them as order of magnitude, and say that in the room):

• A well-tuned single primary on good hardware commonly handles thousands to low tens of thousands of simple writes per second. That is more than many designs need.
• Storage in the range of a few terabytes on one node is common. Past that, operations such as backup and vacuum get painful, and partitioning or sharding comes into view.
• Connections are expensive, since each is a process. Put a connection pool in front. Do not let every app instance open hundreds of connections.

Do the arithmetic from the estimation lesson first. If your peak is a few hundred writes per second, one primary is the honest answer.

Postgres or something else?

• MySQL: much the same story. Pick whichever you know. Postgres has richer types and extensions.
• DynamoDB or Cassandra: pick when you need to scale writes far past one primary, with simple known access patterns, and can give up joins and multi-row transactions.
• Elasticsearch: pick as an extra index for text search, not as the source of truth.

Common mistakes

• Saying "NoSQL for scale" without a number that one Postgres cannot meet.
• Adding read replicas and forgetting replication lag in read-your-own-write flows.
• Indexing every column, or indexing in the wrong order for a composite key.
• Storing large files in rows. Keep a URL, and put the file in blob storage.
• Sharding early, before indexes, replicas and partitioning are exhausted.

• Explaining storage internals for minutes when the question was about the design.
• Giving every operation the same strict treatment, when only some need a transaction.

Check yourself

• Name three guarantees a relational database gives you that a design can lean on.
• Why does a composite index on (a, b) not help a query on b alone?
• What does MVCC let readers and writers do, and what does it cost?
• What problem do read replicas introduce, and how would you handle it for a user who just wrote data?
• Put these in order: sharding, indexes, read replicas, partitioning.
• When is a single Postgres primary the right answer?
• Which operations in a lending app need a transaction, and which can tolerate stale data?
• When might the planner ignore an index you created?
• What is a partial index and when would you use one?