PostgreSQL ends up at the center of so many systems that it is easy to treat it as a boring default. This chapter is valuable because it restores respect for why it gets chosen there so often.
In real work, it helps frame PostgreSQL through MVCC, WAL, extensibility, indexes, and execution plans, meaning through the properties that actually define a reliable OLTP core rather than through familiar SQL syntax alone.
In interviews and architecture discussions, the chapter is strongest when you can explain a Postgres decision not by habit, but by the combination of transactional rigor, query power, and a well-understood operating model.
Practical value of this chapter
Transactional core
Use PostgreSQL as OLTP backbone when ACID guarantees, expressive queries, and predictable consistency matter.
Indexes and planner
Design schema together with index strategy and plan analysis, not as a separate afterthought.
Operational stability
Treat autovacuum, bloat control, WAL archiving, and replication as first-class architecture concerns.
Interview articulation
Justify Postgres choice via data integrity, query power, and known operational trade-offs.
Source
PostgreSQL
History, MVCC, replication, extensibility and the PostgreSQL ecosystem.
PostgreSQL is a free, open-source object-relational DBMS with a focus on extensibility, rich data types, and reliable transaction processing.
History: key milestones
Ingres -> POSTGRES
PostgreSQL evolved from the Ingres project at UC Berkeley and the POSTGRES system.
Postgres95
Postgres95 added an SQL interpreter and gave the database a modern direction.
PostgreSQL
The project was renamed PostgreSQL, and release 6.0 was released in January 1997.
8.0: Windows and PITR
The 8.0 branch brought native Windows support and point-in-time recovery.
9.0: streaming replication
Streaming replication appears, which makes HA scenarios much more practical.
10: logical replication
The new versioning scheme and built-in logical replication expand migration and integration scenarios.
16: mature modern branch
The 14-16 Series enhances performance, concurrency, and replication performance under heavy workloads.
17: release focused on vacuum and logical replication
PostgreSQL 17 improves throughput and planning, significantly reduces VACUUM memory footprint, and simplifies HA scenarios with logical replication.
PostgreSQL specifics
Object-relational DBMS
PostgreSQL is a free open-source object-relational DBMS.
MVCC and isolation levels
MVCC provides snapshots of data to transactions and supports serializable snapshot isolation.
Rich types and indexes
JSON/JSONB, arrays, ranges, extensible types and indexes (GiST, GIN, SP-GiST, BRIN).
WAL-based replication
Built-in replication uses WAL and supports async and synchronous modes.
PostgreSQL architecture by layers
The architecture shows the layers: from drivers and query scheduler to MVCC, WAL and replication.
Key features
PostgreSQL is known for strong extensibility, a rich type system, and a broad extension ecosystem.
Extensibility
Rich data types
Ecosystem
DDL vs DML: how the request goes
DDL changes the structure and metadata, DML works with data. Below is a visualization of the key stages for both types of requests.
How a request flows through PostgreSQL
Comparing the execution chain for DDL (schema) and DML (data)
Active step
1. Parse + plan
The planner chooses an efficient plan and indexes.
Data operations
- DML works with data and indexes without changing schema.
- MVCC enables concurrent access without read locks.
- Replication behavior depends on WAL mode and settings.
Source
MySQL
License, LAMP stack and changes around MySQL.
PostgreSQL vs MySQL
Data model
PostgreSQL: Object-relational, extensible types and functions.
MySQL: Relational DBMS, often used in the LAMP stack.
License and management
PostgreSQL: Permissive PostgreSQL License and development through PGDG.
MySQL: GPL + commercial licenses; ownership through Sun and Oracle.
Competitiveness and integrity
PostgreSQL: MVCC and serializable snapshot isolation.
MySQL: InnoDB is the default engine with transactions and foreign keys.
Ecosystem
PostgreSQL: An ecosystem of extensions and derivative solutions.
MySQL: Strong web ecosystem and rich history of use in LAMP.
Why PostgreSQL is often chosen over MySQL
Practical interpretation based on PostgreSQL properties:
- PostgreSQL's extensible architecture and rich set of data types make it suitable for complex domain models.
- MVCC and advanced isolation levels simplify competitive scenarios under high loads.
- Built-in WAL-based replication provides a clear path to scaling reads.
- The permissive license and strong derivative ecosystem encourage the choice of PostgreSQL.
Related chapters
- Database Selection Framework - How to position PostgreSQL among other data stores based on workload shape, consistency needs, and operational constraints.
- MySQL: history, engines and scaling - A practical comparison of the two main OLTP paths and their architectural/operational trade-offs.
- PostgreSQL from the inside (short summary) - Deeper coverage of MVCC, WAL, locking, and indexing internals that drive real production behavior.
- Replication and sharding - Scaling and availability practice: read replicas, failover models, shard strategy, and rebalancing.
- Designing Data-Intensive Applications (short summary) - Conceptual foundation for transactions, replication, and consistency decisions in system design.
- Introduction to Data Storage - How storage decisions map to API contracts and architecture evolution as systems and teams scale.