PostgreSQL

PostgreSQL is an open-source relational database that has been in continuous development since 1996. It supports the full relational model - tables, joins, foreign keys, constraints, transactions - and implements it with strong standards compliance and a wide feature set that most competitors do not match.

What sets PostgreSQL apart from MySQL or SQLite is the depth of its capabilities: window functions, common table expressions (CTEs), JSONB with index support, full-text search, geospatial queries via PostGIS, row-level security, custom data types, and a mature extension ecosystem. The same engine handles simple CRUD workloads and complex analytical queries.

Why it matters in Engineering: PostgreSQL scales further than most teams will ever need. Teams frequently migrate to distributed databases like Cassandra or MongoDB to solve problems that PostgreSQL could have handled with proper indexing and query optimization. It is actively maintained, has a large community, and managed versions - AWS RDS, Supabase, Neon - handle operational overhead. For most backend systems, PostgreSQL should be the default choice until there is a specific technical reason to pick something else.

Core Concepts

ACID Transactions
Every write is atomic, consistent, isolated, and durable. Either the full transaction commits or none of it does. This guarantee is non-negotiable for financial or inventory data.

Indexes
B-tree indexes are the default. PostgreSQL also supports GIN (for JSONB and full-text search), GiST (for geometric types), BRIN (for large sequential tables), and partial indexes. Choosing the right index type matters for query performance.

WAL (Write-Ahead Log)
All changes are written to the WAL before being applied to data files. WAL enables crash recovery and powers streaming replication to read replicas.

JSONB
Binary JSON storage with full index support. Lets you store semi-structured data inside a relational database without sacrificing query performance. A practical middle ground between a rigid schema and a document database.

Replication
Streaming replication to read replicas distributes read load. Logical replication supports more fine-grained use cases like syncing specific tables or feeding change data capture pipelines.