PostgreSQL

PostgreSQL is the Swiss Army knife of databases. It handles relational data with ACID transactions, stores JSON documents, supports full-text search, and can even do basic time-series workloads. In system design interviews, it's the safe default. Understanding its capabilities and limits lets you make confident decisions about when it's enough and when you need something else.

Why PostgreSQL Is the Default

When you draw a database in a system design interview, PostgreSQL should be your first instinct unless you have a specific reason to choose something else. Here's why:

In interviews, saying "I'll use PostgreSQL" is never wrong for a primary data store. The interviewer expects you to know when it's *not enough*. That's what the rest of this lesson covers.

Indexing in PostgreSQL

PostgreSQL uses B-tree indexes by default, the same structure we covered in Database Fundamentals. But it supports several other index types, and knowing when to use each one is a valuable interview skill.

Index Types

Composite Indexes

A composite index covers multiple columns in a specific order. The order matters: an index on (user_id, created_at) efficiently serves "get all tweets by user X sorted by time" but NOT "get all tweets from today regardless of user."

The rule of thumb: put the column you filter with equality first, then the column you sort or range-filter on second. WHERE user_id = ? ORDER BY created_at DESC → index on (user_id, created_at).

Concurrency: MVCC

PostgreSQL uses MVCC (Multi-Version Concurrency Control) to handle concurrent access. Instead of locking rows when a transaction reads them, PostgreSQL keeps multiple versions of each row. Each transaction sees a snapshot of the database as of its start time.

This means readers never block writers and writers never block readers, a huge advantage for read-heavy workloads. The trade-off is that old row versions accumulate and must be cleaned up by a background process called VACUUM. In high-write workloads, VACUUM can become a bottleneck.

In interviews, MVCC usually comes up when discussing concurrent access: "PostgreSQL uses MVCC, so our read-heavy feed queries won't block the write path. But with our high write volume, we should monitor VACUUM performance."

JSONB: The Best of Both Worlds

PostgreSQL's JSONB type lets you store and query JSON documents with full indexing support. You get document-store flexibility inside a relational database, so you don't need MongoDB for semi-structured data.

When to use JSONB: for fields with variable structure, like user preferences or event metadata. Store the core relational data in regular columns and the flexible data in a JSONB column. You get relational integrity for the important stuff and schema flexibility for the rest.

Full-Text Search

PostgreSQL has built-in full-text search using tsvector (text search vector) and tsquery types with GIN indexes. It supports stemming, ranking, phrase matching, and multiple languages.

When it's enough: searching within a single table with moderate data volume (millions of rows). Product search, article search, user search by name.

When you need Elasticsearch: complex multi-field search across many tables, fuzzy matching at scale, faceted search, or search-as-you-type with sub-50ms latency on billions of documents. For most interview problems, PostgreSQL's built-in search is sufficient. Mention Elasticsearch only when the scale or complexity demands it.

Scaling PostgreSQL

A single PostgreSQL instance can handle more than most people think: up to ~10K reads/sec and ~5K writes/sec on modern hardware, with databases up to several TB. But eventually you'll need to scale beyond a single node.

Scaling Reads

Add read replicas. PostgreSQL's streaming replication sends WAL changes to replicas in near-real-time. Route writes to the primary and reads to replicas. This scales read throughput linearly. Three replicas give you roughly 3x read capacity.

The catch is that replicas are eventually consistent. There's a small replication lag, typically under a second. For most interview problems, this is fine. If a user creates a post and immediately reads their own timeline, route that specific read to the primary (read-your-own-writes consistency).

Scaling Writes

Read replicas don't help with write throughput, since all writes still go to a single primary. When the primary becomes the bottleneck, you have two options:

Vertical scaling. Get a bigger machine. PostgreSQL scales well vertically. Going from 8 to 32 cores and adding more RAM can 4x your write capacity. It's the simplest approach and should be your first recommendation.

Sharding. Split data across multiple PostgreSQL instances by a partition key (e.g., user_id). Each shard handles its own subset of writes. The downside is that cross-shard queries and transactions become complex or impossible. Only recommend sharding when vertical scaling isn't enough.

When to Reach for Something Else