Key Technologies
PostgreSQL
The relational default that does far more than people assume — ACID transactions, rich indexing, JSON, full-text and geo search — and how far one Postgres box really scales before you need anything exotic.
PostgreSQL is a relational database, and it should be your default. In a world of specialised stores it's easy to forget that a single well-tuned Postgres handles the vast majority of systems you'll design — with strict schemas, real transactions, joins, and a feature set that quietly covers jobs you'd otherwise reach for separate tools to do. The strongest system design instinct you can have is "Postgres, unless I can name a reason not to."
What you get by default
- ACID transactions. All-or-nothing multi-row writes, the foundation for anything involving money, inventory, or correctness.
- Joins and ad-hoc queries. Ask new questions of your data without redesigning the schema — the thing query-first NoSQL stores make expensive.
- A rich index toolbox. B-tree for the usual lookups, plus GIN for full-text and JSON, GiST for geospatial, partial and expression indexes for the tricky cases.
- Constraints. Unique, foreign key, check — the database refuses bad data even if the app has a bug.
It replaces several specialised tools
Before adding a new system, check whether Postgres already does it well enough:
| You might reach for… | Postgres has |
|---|---|
| Elasticsearch | Full-text search (tsvector + GIN), ranked |
| A geo database | PostGIS / GiST — "points near here" |
| MongoDB | jsonb columns, indexable and queryable |
| A separate queue | SELECT ... FOR UPDATE SKIP LOCKED as a job queue |
| A key-value cache | UNLOGGED tables, or just an indexed table |
At small-to-medium scale, one Postgres doing search, geo, JSON and your relational data is far simpler than running four systems. Add the specialised tool only when that feature becomes a core, large-scale workload.
How far it scales
Further than most people guess. A single modern Postgres instance comfortably holds hundreds of GB to several TB and serves thousands of writes and far more reads per second. Before anything exotic, you scale it the boring, effective way:
- Indexes — fix the slow queries first (usually the whole problem).
- A connection pooler (PgBouncer) — so many app instances share a small set of real connections.
- Read replicas — offload reads (see Scaling Reads).
- Partitioning — split a huge table into pieces on the same server.
- Only then consider sharding or a distributed flavour (Citus, CockroachDB).
Most systems never get past step 3.
What to watch
- Connections are finite. Each connection costs memory; a spike of app instances can exhaust
max_connections. Use a pooler. - Vacuum and bloat. Updates/deletes leave dead rows that autovacuum reclaims — healthy, but worth knowing exists at scale.
- Writes still go to one primary. Replicas scale reads, not writes. Genuinely write-bound workloads eventually need partitioning or sharding.
What to say in an interview
Make it the default and justify any departure:
"I'll use Postgres for the core data — I need transactions for payments and joins for the relationships, and it scales fine here with an index pass, a pooler, and read replicas. I'd lean on its
jsonbfor the flexible fields and full-text search for the catalogue instead of adding Mongo or Elasticsearch, unless search became a core, large-scale feature — then I'd index into Elasticsearch off the Postgres source of truth."
Next up: Flink — processing streams in real time.