PostgreSQL
PostgreSQL is an open-source relational database known for reliability, standards compliance, and depth of features. It stores data in tables, enforces integrity with constraints, guarantees ACID transactions, and extends far beyond plain SQL with JSONB, full-text search, and a huge extension ecosystem.
Originating as POSTGRES at UC Berkeley in 1986 and gaining SQL in 1996, "Postgres" has grown into arguably the most advanced open-source database, often the best default for a new app. It scales from a laptop to large production systems and underpins platforms like Supabase, Neon, and AWS RDS. When in doubt, it's hard to go wrong starting here.
TL;DR
- A relational database with full ACID transactions and strong consistency.
- MVCC lets readers and writers work concurrently without blocking.
- Rich features: JSONB, CTEs, window functions, full-text search, extensions.
- Connections are processes — use a connection pool (PgBouncer).
- Great default for most apps; managed options reduce the ops burden.
Quick Example
A transaction shows the ACID guarantee — both updates commit together, or neither does:
Core Concepts
- Tables, keys, constraints — structured rows with primary/foreign keys and integrity rules.
- Indexes — B-tree (default), plus GIN/GiST/BRIN for JSON, full-text, and ranges.
- ACID transactions — work either fully commits or fully rolls back.
- MVCC — Multi-Version Concurrency Control: readers don't block writers and vice versa.
- WAL — the Write-Ahead Log underpins durability and replication.
- Query planner — chooses an execution plan from table statistics (inspect with
EXPLAIN ANALYZE).
💡
NULLmeans unknown, not zero or empty. Compare withIS NULL, never= NULL.
Architecture in brief
A postmaster process listens for connections and forks a backend process per connection (which is why pooling matters). Backends share memory (shared buffers, WAL buffers, lock tables); changes are written to the WAL before data files, and autovacuum reclaims space from dead row versions in the background.
Deployment Options
Indexing & Performance
- Index the columns in
WHERE/JOIN/ORDER BY(and foreign keys); index types include B-tree, GIN/GiST (JSONB, full-text), and BRIN (large append-only tables). - Profile with
EXPLAIN ANALYZE; watch for Seq Scans on large tables, and keep statistics fresh. - Pool connections — Postgres forks a process per connection, so hundreds of direct connections exhaust memory; use PgBouncer/pgcat.
- Common bottlenecks: missing indexes, connection exhaustion, lock contention from long transactions, and table bloat (let autovacuum run).
See Database Indexing and Query Optimization.
Extensions & JSONB
PostgreSQL's extensibility is a major differentiator:
- JSONB — store and index semi-structured data alongside relational tables.
- PostGIS (geospatial), pgvector (AI embeddings), pg_trgm (fuzzy search), pg_cron, TimescaleDB.
Security
- Row Level Security (RLS) — per-row access policies enforced by the database (the core of multi-tenant Postgres and Supabase).
- TLS for connections in transit;
pg_hba.confcontrols who can connect from where. - Least-privilege roles — separate app, migration, and admin roles; audit with
pgAudit.
Comparison
See MySQL, MongoDB, and SQLite.
Best Practices
- Index foreign keys and queried columns; avoid over-indexing (each index slows writes).
- Pool connections (PgBouncer) for any real web app.
- Always use parameterized queries (SQL Injection).
- Use RLS for multi-tenant access; keep statistics fresh and let autovacuum run.
Common Mistakes
Comparing to NULL with =
No connection pooling
Over-indexing
FAQ
PostgreSQL or MySQL?
PostgreSQL is the stronger default for complex queries, JSON, extensions, and strict correctness. MySQL is a fine choice for simpler, read-heavy web apps or where your hosting/team already favors it. For greenfield projects, many teams default to Postgres.
What is MVCC?
Multi-Version Concurrency Control keeps multiple versions of a row so readers see a consistent snapshot without blocking writers, and writers don't block readers. It's why Postgres handles concurrent load well — at the cost of needing vacuum to clean up old row versions.
Do I really need a connection pool?
For any real web app, yes. Postgres allocates a process per connection, so hundreds of direct connections exhaust memory and max_connections. A pooler (PgBouncer) lets many clients share a small set of backend connections.
Should I store JSON or use normalized tables?
Use normalized tables for relational, queried-on data; use JSONB for genuinely variable or document-shaped fields. Postgres lets you mix both and index JSONB, so you don't have to choose one globally.
How do I scale PostgreSQL?
Vertically first (bigger instance), then read replicas for read-heavy load, a cache (Redis) for hot reads, and Citus or partitioning for very large datasets. Serverless options (Neon, Aurora Serverless) change the operational model.
PostgreSQL Deep Dives
- pgvector — Vector similarity search and embeddings in Postgres
- Row-Level Security (RLS) — Per-row access control and multi-tenancy
- JSONB in PostgreSQL — Document storage, operators, and GIN indexing
- PostgreSQL Full-Text Search — Native search with tsvector/tsquery
- PostgreSQL Performance Tuning — EXPLAIN, autovacuum, and config
- PostgreSQL Extensions — The ecosystem that makes Postgres a platform
- PostgreSQL Partitioning — Scaling very large tables
- PostgreSQL Connection Pooling — Serving many clients with PgBouncer
Related Topics
- SQL Fundamentals — The query language
- MySQL — The other popular RDBMS
- Database Indexing — Making queries fast
- Database Transactions — ACID in depth
- Database Replication — Scaling reads and HA
- Supabase — Postgres-as-a-platform
- PostgreSQL Indexes — B-tree, GIN, GiST, BRIN, and when to use each
- PostgreSQL Functions — PL/pgSQL, triggers, and server-side logic
- PostgreSQL Security — Roles, privileges, and hardening