PostgreSQL earned its position as the default database recommendation not by being the fastest at any one thing, but by being genuinely excellent at nearly everything — relational integrity, JSON storage, full-text search, and extensibility all in one system.
PostgreSQL is an open-source, object-relational database known for strict standards compliance, a mature transaction model (full ACID guarantees), and a feature set that's expanded well beyond traditional relational data — JSONB columns, array types, full-text search, and extensions like PostGIS for geospatial data. For most new projects without a specific reason to reach for something else, it's the sensible default.
Why PostgreSQL Matters (and When to Skip It)
Postgres gives you relational integrity (foreign keys, constraints, transactions) without sacrificing flexibility — JSONB columns let you store semi-structured data alongside strictly typed columns in the same table, which covers a lot of ground that used to require choosing between a relational and document database upfront.
Skip Postgres for workloads that are genuinely document-first with no relational structure at all (MongoDB may fit more naturally), or for use cases needing specialized data models Postgres doesn't handle natively at scale, like massive time-series ingestion (a dedicated time-series database) or graph traversal (a graph database).
Getting Started with PostgreSQL
A typical schema with relationships and a JSONB column for flexible metadata:
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT UNIQUE NOT NULL,
created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE posts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
title TEXT NOT NULL,
metadata JSONB DEFAULT '{}',
created_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_metadata ON posts USING GIN (metadata);
Querying JSONB with real indexing support:
SELECT * FROM posts WHERE metadata @> '{"featured": true}';
Core PostgreSQL Concepts Every Developer Should Know
Indexes are the single highest-leverage performance tool, and the default B-tree index isn't always the right choice. GIN indexes for JSONB and array columns, and full-text search columns, need their own index types to be fast:
CREATE INDEX idx_posts_search ON posts USING GIN (to_tsvector('english', title));
SELECT * FROM posts WHERE to_tsvector('english', title) @@ to_tsquery('database');
Transactions guarantee correctness across multi-step operations. Wrapping related writes in a transaction ensures they either all succeed or all roll back — critical for anything involving multiple related tables (like creating an order and decrementing inventory together):
BEGIN;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 'abc';
INSERT INTO orders (product_id, user_id) VALUES ('abc', 'user_1');
COMMIT;
EXPLAIN ANALYZE shows you exactly what a query is doing, and is the actual answer to "why is this query slow" — guessing at optimizations without it wastes time:
EXPLAIN ANALYZE SELECT * FROM posts WHERE user_id = 'xyz';
-- reveals whether it's using the index or doing a sequential scan
Connection pooling is necessary at any real scale. Postgres connections are relatively expensive to establish; a pooler like PgBouncer (or your ORM's built-in pooling) reuses connections across requests instead of opening a new one per query, which matters a lot under concurrent load.
Common PostgreSQL Mistakes and How to Fix Them
Mistake 1: no index on foreign key columns. Postgres doesn't automatically index foreign keys the way some databases do — a JOIN or WHERE on an unindexed foreign key column forces a full table scan. Fix: explicitly add an index on every foreign key column used in queries.
Mistake 2: using JSONB for data that's actually relational. Storing an array of related objects in a JSONB column when they should be a proper related table loses referential integrity, indexing efficiency, and the ability to query/join efficiently. Fix: use JSONB for genuinely flexible/sparse attributes, not as a substitute for normalized tables.
Mistake 3: not setting NOT NULL and constraints where they belong. Relying on application code to enforce data integrity instead of the database means a bug or a second application writing to the same database can insert bad data. Fix: push constraints (NOT NULL, CHECK, foreign keys, unique) into the schema wherever the invariant is genuinely required.
When Should You Use PostgreSQL Instead of MongoDB?
Use Postgres when your data has real relational structure, you need strong consistency guarantees, or you want the flexibility of JSONB without giving up relational integrity elsewhere in the schema. Use MongoDB when your data is genuinely document-shaped with minimal cross-document relationships and you want schema flexibility as the primary design principle, not the exception.
PostgreSQL in Production
Set up automated backups and test restoring from them — a backup you've never restored isn't a verified backup. Also monitor slow queries via pg_stat_statements from day one; catching a missing index during development is a five-minute fix, catching it after it's degrading production under load is a much worse conversation.
If you're deciding between Postgres and a NoSQL option for a new project without a specific reason to avoid relational data, default to Postgres — its JSONB support already covers most of the "but I need flexible schema" arguments for choosing something else.