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.