PostgreSQL

What it is

PostgreSQL (often called Postgres) is a mature, open-source object-relational database. It started in 1986 at UC Berkeley as POSTGRES and was renamed to PostgreSQL in 1996 when SQL support was added. Three decades later, it remains the default choice for new applications that need ACID transactions, complex queries, and a feature-rich SQL engine.

Why people use it

• SQL standard compliance. PostgreSQL implements more of the SQL standard than any other database, including window functions, recursive CTEs, and full ANSI SQL semantics.
• Extensible. The extension system (CREATE EXTENSION) lets you add PostGIS for geo, pgvector for embeddings, TimescaleDB for time-series, or write your own. This single feature lets Postgres absorb workloads that used to require separate specialized databases.
• JSONB. First-class binary JSON support with indexing, making Postgres viable for semi-structured workloads that would have gone to MongoDB a decade ago.
• MVCC concurrency. Multi-Version Concurrency Control means readers never block writers and vice versa. This is the foundation of Postgres’s reliability under heavy concurrent load.
• Rich type system. Arrays, ranges, custom types, enums, UUID, network address types, and full-text search vectors — all native.

When to use PostgreSQL

• Default choice for new OLTP applications.
• Complex queries with joins, CTEs, and window functions.
• Geospatial workloads (with PostGIS).
• Vector similarity search at small-to-medium scale (with pgvector, up to roughly 10M vectors).
• Mixed OLTP and analytical workloads in the same database.
• When you want a single database for as long as possible.

When not to use PostgreSQL

• Pure key-value workloads at very high throughput. Redis or DragonflyDB are simpler and faster.
• Petabyte-scale analytics. ClickHouse, BigQuery, or Snowflake are purpose-built for this.
• Globally distributed writes with low latency. CockroachDB, YugabyteDB, or Spanner handle this better than vanilla Postgres.
• Embedded use cases. SQLite is the right answer.

Notable trade-offs

• Write scaling. Postgres scales reads well via replicas, but write scaling requires sharding. You have to build it yourself, or adopt Citus or Neon. MySQL has historically been easier to shard.
• MVCC bloat. Frequent updates create dead tuples that need autovacuum to clean up. Misconfigured autovacuum on write-heavy workloads is a common source of production incidents.
• Replication. Streaming replication is solid; logical replication has rough edges. Synchronous replication has tradeoffs that need careful tuning.
• Conservative defaults. The out-of-the-box postgresql.conf is tuned for a small VPS, not modern hardware. You will likely tune shared_buffers, work_mem, effective_cache_size, and max_connections on day one.

Ecosystem

The PostgreSQL extension ecosystem has spawned several commercial products covered separately in this directory: TimescaleDB (time-series), Citus (distributed Postgres), Supabase (Postgres + auth + realtime), and Neon (serverless Postgres with branching). The Postgres wire protocol has also become a de facto standard, spoken even by non-Postgres products like CockroachDB and YugabyteDB.