PostgreSQL - System Design Guide

Overview

PostgreSQL is an open-source relational database known for correctness, standards compliance, and extensibility. It supports ACID transactions with MVCC (multi-version concurrency control), rich indexing (B-tree, GIN, GiST, BRIN), JSONB documents, full-text search, window functions, and extensions such as PostGIS and pgvector.

It is a common default for new products because it covers relational, document, geospatial, and even vector search use cases in one system. Scaling follows the usual path: indexes and tuning, connection pooling, read replicas via streaming replication, table partitioning, and sharding (for example with Citus) when needed.

A Swiss army knife with a very sharp main blade

Its core is an excellent relational database, and extensions add tools for maps, search, and vectors. You may still need specialized tools at extreme scale, but it handles an impressive range of jobs well.

When to use it

General-purpose transactional workloads.
Mixed relational and JSON data.
Geospatial (PostGIS), full-text, and vector similarity (pgvector) queries.
When correctness and rich SQL features matter.

Where it shows up in interviews

Primary database choice

Recognize it when: which database for the core product?

Design an e-commerce backend
Design a SaaS multi-tenant app

Geospatial and search in one DB

Recognize it when: nearby queries, text search, or embeddings.

Design Yelp nearby search
Design a RAG store with pgvector

Where it is used in real software

Instagram

Ran on sharded PostgreSQL, using schemas as logical shards to scale to hundreds of millions of users.

Supabase and Neon

Platforms built entirely around managed PostgreSQL, including branching and serverless scaling.

Amazon Aurora PostgreSQL

Separates compute from a distributed storage layer for fast replicas and failover.

Key terms

MVCC: Each transaction sees a snapshot; writers do not block readers.
WAL: Write-ahead log: changes are logged before being applied, enabling crash recovery and replication.
VACUUM: Reclaims space from old row versions left by MVCC.
JSONB + GIN: Binary JSON column with an inverted index for fast containment queries.
Streaming replication: Replicas replay the primary's WAL.

How it works, step by step

1
Write path
Change is written to the WAL and flushed on commit, then applied to pages in shared buffers.
2
Read path
The planner picks a plan; pages come from shared buffers or disk.
3
Concurrency
MVCC keeps multiple row versions; each transaction sees a consistent snapshot.
4
Replication
Replicas stream the WAL and can serve read queries.
5
Maintenance
Autovacuum cleans dead rows; ANALYZE updates planner statistics.

PostgreSQL index types

Choose by query pattern

Index	Good for	Example
B-tree	Equality, ranges, sorting	WHERE created_at > ... ORDER BY created_at
GIN	JSONB, arrays, full-text	WHERE tags @> '{sale}'
GiST / SP-GiST	Geometry, ranges, nearest neighbor	PostGIS ST_DWithin
BRIN	Huge append-only tables ordered by time	Logs by timestamp
HNSW (pgvector)	Vector similarity	ORDER BY embedding <=> $1 LIMIT 10

NOWIndex: B-tree | Good for: Equality, ranges, sorting | Example: WHERE created_at > ... ORDER BY created_at

The right index type often turns a multi-second query into milliseconds without new infrastructure.

Implementation

1-- JSONB attributes with a GIN index2CREATE TABLE products (3  id    BIGSERIAL PRIMARY KEY,4  name  TEXT NOT NULL,5  attrs JSONB NOT NULL DEFAULT '{}'6);7CREATE INDEX idx_products_attrs ON products USING GIN (attrs);8SELECT id, name FROM products WHERE attrs @> '{"color": "red", "size": "M"}';9 10-- Upsert11INSERT INTO inventory (sku, qty) VALUES ('sku-1', 5)12ON CONFLICT (sku) DO UPDATE SET qty = inventory.qty + EXCLUDED.qty;13 14-- Row locking for a job queue: workers never grab the same job15SELECT id FROM jobs WHERE status = 'queued'16ORDER BY created_at17FOR UPDATE SKIP LOCKED18LIMIT 10;19 20-- Partition a large table by month21CREATE TABLE events (id BIGINT, created_at TIMESTAMPTZ NOT NULL, payload JSONB)22PARTITION BY RANGE (created_at);23CREATE TABLE events_2026_09 PARTITION OF events FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

Complexity and performance

Indexed point query~0.1-1 ms

Hot data in memory.

Max connectionshundreds

Process per connection; use PgBouncer.

Replication lagms-seconds

Asynchronous by default.

Trade-offs

Process-per-connection model

Robust but memory-heavy; high connection counts need a pooler.

MVCC bloat

Heavy updates leave dead rows; autovacuum must keep up or tables and indexes bloat.

Variants and related techniques

Citus

Extension that shards tables across nodes for horizontal scale.

TimescaleDB

Time-series extension with automatic partitioning.

Aurora / AlloyDB

Cloud-native PostgreSQL-compatible engines with distributed storage.

Common mistakes

Thousands of direct connections.
Fix: Use PgBouncer or RDS Proxy.
Long-running transactions.
Fix: They block vacuum and hold locks; keep transactions short.
Creating indexes without CONCURRENTLY in production.
Fix: Plain CREATE INDEX locks writes on the table.

Interview questions

How does PostgreSQL let readers and writers work concurrently?

MVCC: updates create new row versions, and each transaction reads the snapshot that was visible when it started, so readers do not block writers and writers do not block readers.

How would you implement a job queue in PostgreSQL?

A jobs table with status and timestamps; workers claim jobs with SELECT ... FOR UPDATE SKIP LOCKED inside a transaction, which prevents two workers from taking the same job without blocking each other.

Practice problems

Problem	Difficulty	What it trains
Use EXPLAIN ANALYZE to fix a slow query	Easy	Indexes.
Design a multi-tenant schema in PostgreSQL	Medium	Tenant isolation and RLS.
Scale PostgreSQL to 50k writes/s	Hard	Partitioning and sharding.