PostgreSQL
IntermediateOpen source · since 1996 · Relational + Document
PostgreSQL is the world's most advanced open-source relational database. Full SQL compliance, JSONB, window functions, full-text search, CTEs, extensions, and ACID transactions — all in one battle-tested system trusted by Apple, Instagram, Spotify, and Reddit.
Why PostgreSQL
Beyond basic SQL
PostgreSQL gives you features most databases charge enterprise licensing for — completely free.
Full Atomicity, Consistency, Isolation, Durability. Your data is always in a valid state, even after crashes.
Running totals, rankings, moving averages — in pure SQL. No application-level aggregation needed.
Store semi-structured data with full GIN indexing. Query nested JSON as fast as regular columns.
Built-in Google-style text search with ranking, highlighting, and multilingual stemming. No Elasticsearch needed.
Traverse trees, org charts, and graphs directly in SQL using RECURSIVE CTEs. No procedural loops needed.
PostGIS for geo, TimescaleDB for time-series, pgvector for AI embeddings — PostgreSQL is extensible by design.
B-tree, Hash, GiST, SP-GiST, GIN, BRIN — the right index type for every data shape. Partial and expression indexes.
PostgreSQL licence is permissive — use it in any project, commercial or not, with no restrictions or royalties.
Getting Started
Installation & Setup
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo -u postgres psqlbrew install postgresql@16
brew services start postgresql@16
psql postgres# Download installer from postgresql.org/download/windows
# Or use Chocolatey:
choco install postgresql
psql -U postgresdocker run -d \
--name postgres \
-e POSTGRES_PASSWORD=secret \
-p 5432:5432 \
postgres:16
docker exec -it postgres psql -U postgresCheat Sheet
Quick Reference
Click any syntax to copy
| Concept | Syntax |
|---|---|
| Create table | CREATE TABLE t (id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL); |
| Add column | ALTER TABLE t ADD COLUMN col TEXT DEFAULT ''; |
| Add index | CREATE INDEX idx ON t (col); |
| Add foreign key | col INT REFERENCES other(id) ON DELETE CASCADE; |
| Create enum type | CREATE TYPE mood AS ENUM ('sad','ok','happy'); |
| Drop table | DROP TABLE IF EXISTS t CASCADE; |
Ready to go deeper?
The full tutorial covers DDL, DML, advanced queries, window functions, JSONB, full-text search, transactions, and EXPLAIN ANALYZE — with 12 hands-on examples.
The Story of PostgreSQL
Real history, real companies, and an honest look at where PostgreSQLshines and where it doesn't — not just a feature list.
Where it came from
PostgreSQL traces back to the POSTGRES project at UC Berkeley, led by database pioneer Michael Stonebraker starting in 1986, as a research successor to his earlier Ingres project. It gained SQL support and its current name in the mid-1990s and has been developed as free, open-source software by a global community ever since — with no single company controlling its direction.
How it’s actually used today
PostgreSQL is the primary database behind Instagram, Reddit, Skype and Spotify, chosen specifically for its combination of strict data-correctness guarantees and advanced features like full-text search and JSON support built directly into the database. Many managed cloud database services (Amazon RDS, Supabase, Neon) are built specifically around it.
Strengths & trade-offs
PostgreSQL's strict adherence to SQL standards and strong consistency guarantees make it an unusually safe choice for data where correctness really matters, like financial transactions — the trade-off is that it can require more careful tuning for extremely high write-throughput workloads than a database purpose-built for that scale, like Cassandra.
What to learn next
Learning how to design a proper relational schema — normalisation, foreign keys, indexes — is the natural depth to add after basic queries. From there, pairing PostgreSQL with a back-end framework in Python (Django) or Node.js (via an ORM) is the standard next step toward building a real application.