PostgreSQL: The Default You Should Reach For — FM India

If you only learn one database deeply, make it PostgreSQL. It's open source, free, battle-tested over decades, and it's the database the rest of this course (and the full-stack track) runs on. Everything you practised in the playground was real Postgres. Here's what makes it the default and where its edges are.

What it is

Postgres is a relational database: tables, rows, columns, schemas, keys, and SQL, exactly the model you've been using. What sets it apart isn't the basics (every relational database has those) but how far it stretches beyond them while staying correct and reliable.

The features that make it special

Functional · what it does

Rich SQL. Window functions, CTEs (WITH queries), and complex joins, so hard questions stay one query.
JSONB. Store and query JSON documents inside a relational table, so you get document-database flexibility without a second database.
Full-text search. Built-in ranked text search, often enough to skip Elasticsearch entirely.
Extensions. pgvector for AI similarity search, PostGIS for maps, and dozens more bolt straight on.

Non-functional · what it must survive

Correct by default. Strong transactions and constraints; it's hard to corrupt your data.
MVCC concurrency. Readers never block writers, so it stays responsive under load.
Reliable at scale. A single well-tuned Postgres handles huge traffic before you ever need to shard.
Everywhere. Managed by every cloud (RDS, Cloud SQL, Neon, Supabase) and runnable on a laptop.

One database, many shapes

The quietly radical thing about Postgres is that it covers jobs people reach for separate databases to do:

-- Relational, as you know it
SELECT name, city FROM customers WHERE city = 'Mumbai';

-- A JSON document column, queried like a document store
SELECT data->>'title' FROM pages WHERE data->>'status' = 'published';

-- Full-text search with ranking, no Elasticsearch needed
SELECT title FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('database & fast');

-- Vector similarity search for AI, via the pgvector extension
SELECT chunk FROM docs ORDER BY embedding <-> '[0.1, 0.2, ...]' LIMIT 5;

This is why the system-design advice keeps saying "start with Postgres." You can ship structured data, flexible JSON, search, and AI retrieval on one system you only have to learn, operate, and back up once.

When to use it

Use Postgres for basically any application's primary data store: anything with structure, relationships, or correctness needs (which is almost everything). It's the right first choice, and most teams never have a real reason to leave it.

When something else fits better

Postgres isn't the answer to literally everything

Reach elsewhere when: you need a pure cache or ephemeral counters at sub-millisecond speed (Redis), you're ingesting internet-scale write volumes a single node can't take (a wide-column store like Cassandra), your data is fundamentally graph traversal ("friends of friends of friends" at depth) (Neo4j), or you're doing heavy analytics over billions of rows (a column store like ClickHouse). Even then, many teams run these alongside Postgres, not instead of it.

Gotchas worth knowing early

A few things that bite newcomers, all covered deeper in the Postgres internals deep dive: updates create new row versions (so heavy-update tables need VACUUM to reclaim space), connections are relatively expensive (use a connection pooler in serverless setups), and a long-running transaction can quietly block cleanup and bloat your tables. None are dealbreakers; they're just the things to learn once.

The one-liner

Postgres is the default for a reason: relational correctness at its core, plus JSON, search, and vectors bolted on, all in one open-source system that scales further than most teams ever need. Start here, and only add another database when a specific job (cache, extreme scale, graph, analytics) genuinely demands it.

Test yourself

Questions· say the answer out loud before you open it. If you can't, the chapter isn't done.

QWhy is Postgres so often the recommended default?+

Because it's relational and correct at its core but also handles JSON documents, full-text search, and vector similarity in one system, so a single reliable database covers a huge range of needs. You learn, operate, and back up one thing, and it scales far before you need anything more.

QWhat does JSONB let you do?+

Store and query JSON documents inside a relational table, giving you document-database flexibility (varying, nested shapes) without running a separate document database. You can index and filter on fields inside the JSON alongside your normal columns.

QWhen should you reach for something other than Postgres?+

For a pure sub-millisecond cache or counters (Redis), internet-scale write volumes a single node can't handle (Cassandra), genuine deep graph traversal (Neo4j), or heavy analytics over billions of rows (ClickHouse). Often these run alongside Postgres rather than replacing it.

QWhat are a couple of Postgres gotchas to learn early?+

Updates create new row versions, so heavy-update tables rely on VACUUM to reclaim space; connections are relatively expensive, so serverless setups need a connection pooler; and a long-running transaction can block cleanup and bloat tables. All manageable once you know them.