Key Technology: PostgreSQL and Relational Databases

For most designs, the right default database is a relational one, and PostgreSQL is the one interviewers most often assume. It offers transactions, rich queries, strong consistency and a mature ecosystem, and it scales much further than the "SQL does not scale" folklore suggests. This chapter covers how it works well enough to reason about performance, scaling and failure, with the discipline of choosing the simplest step that meets the numbers.

Details vary by version, so where a specific behaviour or number matters, treat it as something to confirm against the documentation of the version you would use.

1. Why a relational database is the default

• Transactions with ACID guarantees, so multi-row updates are all-or-nothing.
• A flexible query language. New questions can be answered with new queries and indexes, without redesigning the storage.
• Integrity constraints. Primary keys, unique constraints, foreign keys and checks enforce correctness in the database, not only in application code.
• Maturity. Tooling, monitoring, backup and expertise are widespread.

The honest limits are a single primary for writes, and scaling that needs deliberate work beyond one machine. A good answer states a reason to leave a relational database, such as a write volume or data size that the numbers show cannot be handled, and does not leave it by reflex.

2. How a write works

Understanding the write path explains durability, replication and performance.

1. The change is first appended to the write-ahead log (WAL), a sequential, durable record of every change. When the log record is flushed to disk, the transaction can be acknowledged as committed.
2. The change is applied to data pages in memory, and flushed to the table files later, in the background.
3. After a crash, the database replays the WAL to recover committed changes. That is why a commit is durable even though the table files were not yet updated.
4. Replicas receive the same WAL stream and replay it, so they become copies of the primary.

Sequential WAL writes make commits fast, and the WAL is also the foundation of replication, point-in-time recovery and change data capture.

3. Concurrency: MVCC and isolation

PostgreSQL uses multi-version concurrency control (MVCC). Rather than locking rows for readers, an update creates a new version of the row, and each transaction sees a consistent snapshot. The result:

• Readers do not block writers, and writers do not block readers.
• Old row versions pile up and must be cleaned by vacuum, a background process. If it falls behind, tables bloat, and long-running transactions can prevent cleanup. Monitoring vacuum is a real operational concern.

Isolation levels. The default is read committed: each statement sees data committed before it began. Repeatable read gives a stable snapshot for the whole transaction, and serializable prevents all anomalies, at some cost and with possible retries on conflict. Explain the anomaly each level prevents, as in the consistency chapter, and note that write skew requires serializable or explicit locking.

Row locks and avoiding lost updates. For read-modify-write on the same row, use an atomic update (UPDATE ... SET qty = qty - 1), a row lock (SELECT ... FOR UPDATE), or optimistic checking with a version column. For queue-like tables, a lock that skips rows already locked by other workers lets many workers claim distinct rows without blocking each other.

4. Indexes

An index lets the database find rows without scanning the table. PostgreSQL offers several types:

Rules worth stating:

• Each index speeds reads and slows writes, and costs space.
• A composite index on (a, b) helps queries that filter on a, or on a and b, but not on b alone. Put the equality columns first and the range column last.
• A covering index includes the extra columns a query needs, so the table need not be read.
• A partial index covers only rows matching a condition, such as unprocessed jobs, and stays small.
• Look at the plan with EXPLAIN and EXPLAIN ANALYZE. A sequential scan on a large table in a hot query is the usual suspect.
• Low-selectivity columns, such as a boolean, rarely benefit from an index by themselves.

5. Scaling a relational database

Present scaling as a ladder, and climb only as high as the numbers force.

Step 1: query and index tuning. Most slow systems have missing indexes, queries that return too much, or N+1 query patterns. Fix these first. It costs nothing in architecture.

Step 2: connection pooling. Each PostgreSQL connection is a process with real memory cost, so thousands of direct connections hurt. A connection pooler between the application and the database multiplexes many client connections onto a small number of server connections.

Step 3: read replicas and caching. Send read-only queries to replicas, and put a cache in front for the hottest reads. Replication is asynchronous by default, so replicas lag. Handle read-your-writes by reading from the primary after a write, or by waiting for a replica to catch up.

Step 4: partition large tables. Declarative partitioning splits one logical table into several physical ones by range (often time), list or hash. Queries that filter on the partition key touch only the relevant partitions, and old partitions can be dropped instantly, which makes retention cheap. This stays inside one server.

Step 5: shard across servers. Split data across independent databases by a shard key. It removes the single-primary limit, and it makes cross-shard queries, joins and transactions hard. There are extensions and managed offerings that help, and you can shard in the application. Choose the key from the dominant query, as in the storage chapter.

Vertical scaling (a bigger machine) fits between steps and is often the cheapest way to buy time. A single well-configured server can handle a surprising amount: tens of thousands of simple queries per second and terabytes of data, depending on the workload. Treat figures like these as starting points to benchmark.

6. Replication and high availability

Streaming replication sends WAL to replicas. Modes:

• Asynchronous (default): the primary does not wait. Fast, and a primary failure can lose the last few transactions.
• Synchronous: the primary waits for at least one replica to confirm. No acknowledged transaction is lost on failover, at the cost of latency, and a slow replica can stall commits. Many setups require confirmation from one of several candidate replicas.

Failover. When the primary fails, a replica is promoted. You need failure detection, a fencing mechanism that stops the old primary from accepting writes if it returns (to prevent split brain), and a way for applications to find the new primary, usually through a virtual address or a connection proxy. Managed services and failover tools handle this, and you should say that you would use one, not hand-build it.

Backups and recovery. Take base backups and archive the WAL, which allows point-in-time recovery: restore to any moment, such as just before a bad deployment. Test restores, because an untested backup is a hope.

Logical replication and change data capture. The WAL can be decoded into row-level changes and streamed to other systems, such as a search index, a cache or a warehouse. This is the standard way to avoid dual writes.

7. Data modelling points that come up

• Normalise first, denormalise for a measured reason. Normalisation avoids update anomalies, and denormalisation speeds specific reads at the cost of keeping copies consistent.
• Choose primary keys deliberately. Auto-increment integers are compact and ordered, but expose volume and are awkward across shards. Random identifiers are shard-friendly and scatter inserts across the index, which can hurt write locality. Time-ordered identifiers give both uniqueness and locality.
• JSON columns. A flexible jsonb column with a GIN index suits semi-structured attributes, but queries on deep structure are slower and less checked than regular columns.
• Full-text and geospatial search exist in the database and can be enough for modest needs before adding a dedicated search engine.
• Money and exact numbers. Use exact numeric types or integers of the smallest unit, not floating point.
• Schema changes. Make them backward compatible in steps: add the column, deploy code that writes both, backfill, switch reads, remove the old column later. Some operations take locks that block writes on large tables, so know which ones and use the online alternatives.

8. Failure modes and operations

• Connection exhaustion. Too many connections degrade the server. Use pooling and limits.
• Long-running transactions. They hold locks, block vacuum and bloat tables. Set timeouts and monitor them.
• Lock contention and deadlocks. Access rows in a consistent order, keep transactions short, and retry on deadlock errors.
• Replication lag. Monitor it. Lag makes replica reads stale and delays failover.
• Table and index bloat, vacuum lag. Monitor and tune autovacuum, especially on update-heavy tables.
• Disk full. WAL archiving failures or a stuck replica can cause the WAL to fill the disk. Alert on disk and WAL growth.
• A bad query. A single unindexed query on a large table can saturate the server. Use statement timeouts and track slow queries.

9. Interview questions and model answers

Q: Why would you choose PostgreSQL? I need transactions, relationships and flexible queries, and the data and write volume fit a single primary with replicas for a long time. It enforces integrity in the database, and I can add caching, replicas and partitioning before considering sharding.

Q: Your read-heavy relational database is slow. What do you do? Look at the slow queries and plans first, add or fix indexes, and pool connections. Then add read replicas and a cache for hot reads, handling replication lag with read-your-writes where it matters.

Q: When do you shard? When one primary cannot take the write volume or the data size even after tuning, vertical scaling and partitioning. I choose the shard key from the dominant query and avoid cross-shard transactions by design.

Q: How do you avoid two users buying the last item? An atomic conditional update, such as decrementing stock only where it is positive, or a row lock before the check, so the database decides one winner.

Q: What does the WAL do for you? It makes commits durable and fast through sequential writes, enables crash recovery by replay, and drives replication, point-in-time recovery and change data capture.

Q: How do you make schema changes safely? In backward-compatible steps across several deployments, avoiding operations that take long locks on large tables.

10. Common mistakes

• Reaching for sharding or a different database before tuning queries and indexes.
• Opening a database connection per request with no pooling.
• An index on every column, slowing writes for no gain.
• Treating replicas as always current.
• Long-running transactions that block cleanup.
• Floating point for money.
• Assuming failover is free of data loss with asynchronous replication.