PostgreSQL
Extensible relational engine with MVCC, a strong type system and a rich extension ecosystem — the default choice for transactional data.
What it is
An open-source relational database with multi-version concurrency control, a cost-based planner and an extension system that lets third-party code add index types, data types and background workers without forking the server. Licensed under the permissive PostgreSQL License.
Architecture
Process per connection, with backends sharing memory through shared_buffers. Every change is
written to a write-ahead log before the data page is flushed, which is what makes crash recovery,
streaming replication and point-in-time recovery possible. Auxiliary processes handle checkpointing,
background writing and autovacuum.
Best use cases
- Transactional application state where correctness and constraints matter.
- Mixed read/write workloads up to what a single well-provisioned node handles — a far higher ceiling than most teams assume.
- Geospatial workloads through PostGIS.
- Time-series data through TimescaleDB.
- Moderate analytics over operational data, with partitioning and parallel query.
When not to use it
- Write volume exceeding one node, without application-level sharding.
- High-cardinality analytical scans over billions of rows — a column store is the right tool.
- As a cache, or as a very high-throughput queue.
Data model
Typed tables with constraints, plus first-class jsonb, arrays, ranges, uuid, network types and
user-defined composite types — all indexable.
Consistency and transactions
Fully ACID on a single node. Implements READ COMMITTED (default), REPEATABLE READ and
SERIALIZABLE; the last uses Serializable Snapshot Isolation and requires the application to retry
SQLSTATE 40001.
Scaling model
Vertical first; read replicas for read scaling; partitioning to reduce per-query work; sharding at the application level or via extensions for write scaling.
Replication
Physical streaming replication for high availability, logical replication for major-version upgrades and selective data movement. Synchronous commit is configurable per transaction.
Backup and recovery
pg_dump for portable logical dumps; physical base backups plus archived WAL (pgBackRest, Barman)
for point-in-time recovery.
Monitoring
pg_stat_activity, pg_stat_statements, pg_stat_replication, pg_stat_user_tables and
pg_stat_archiver. See PostgreSQL Monitoring.
Common mistakes
Production checklist
The full list is in PostgreSQL Production Checklist.