Features
- ACID compliance with MVCC concurrency
- JSON/JSONB for document-style storage
- Full-text search, GIS (PostGIS), and vector extensions
- Advanced indexing (B-tree, GIN, GiST, BRIN)
Pros
- Most feature-rich open-source relational database
- Extensible with custom types, functions, and extensions
- Excellent for complex queries and data integrity
Cons
- More complex to tune than MySQL for simple workloads
- Replication setup is more involved than MySQL
- Higher memory usage than lightweight alternatives
Overview
PostgreSQL is the world’s most advanced open-source relational database, known for its reliability, data integrity, and extensibility. It supports both SQL (relational) and JSON (non-relational) querying, making it versatile enough for virtually any application.
PostgreSQL’s extension ecosystem is one of its greatest strengths. PostGIS adds geographic queries, pgvector enables AI vector search, pg_cron handles scheduled tasks, and hundreds of other extensions add specialized functionality. This extensibility has made PostgreSQL the default database choice for modern web applications.
When to Use
PostgreSQL is the right default for most web applications. Choose it when you need strong data integrity, complex queries, full-text search, or extensions like PostGIS or pgvector. It’s the best general-purpose database for applications that may need to scale in unpredictable ways.
Getting Started
# Docker quickstart
docker run -d --name postgres \
-e POSTGRES_PASSWORD=secret \
-p 5432:5432 postgres:16
# Connect
psql postgresql://postgres:secret@localhost:5432CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL
);