PostgreSQL
A powerful, open-source relational database. PostgreSQL (Postgres) is ACID-compliant, supports complex queries, JSON, full-text search, and extensions. The default choice for new SQL databases. Reliable, feature-rich, and free. Used by Instagram, Discord, and many large apps.
Michael Stonebraker · Led the POSTGRES project at UC Berkeley in 1986
- Default choice for relational data.
- Apps needing JSON + SQL.
- Geospatial (PostGIS).
- Vector search (pgvector).
- Pure key-value workloads (use Redis).
- Massive write scale (use DynamoDB/Cassandra).
- Document-only with no relations (use MongoDB).
Definition
PostgreSQL is an open-source, ACID-compliant relational database. It supports SQL, JSON/JSONB, arrays, custom types, full-text search, geospatial (PostGIS), and extensions. MVCC for concurrent transactions. Logical/physical replication. Strong SQL standards compliance.
Why it exists
To provide a free, reliable, feature-rich relational database that rivals commercial databases (Oracle, SQL Server) with advanced features (JSON, extensions, geospatial).
How it works
Define tables with typed columns. Insert/query with SQL. JSONB stores unstructured data with indexing. Indexes (B-tree, GIN, GiST) speed queries. MVCC lets readers not block writers. WAL enables replication and point-in-time recovery. Extensions add features (PostGIS, pgvector).
Scaling limits
Single primary (writes don't scale horizontally without sharding). Connection limits (use pooling — PgBouncer). Vacuum overhead. Large tables need partitioning. Read replicas help reads but not writes.
Cost shape
Free and open source. Managed (RDS, Aurora, Supabase) charges per usage/storage/IO.
Alternatives
- MySQL — Simpler, faster for simple reads, less feature-rich.
- SQLite — Embedded, no server, great for local/small apps.
Failure modes
- Vacuum bloat.
- Connection exhaustion.
- Missing indexes causing slow queries.
- Single-point-of-failure without replication.