PostgreSQL Principles, Architecture, and a Comparison with MySQL
*Summary of a Chinese forum post on zhichai.net — PostgreSQL: a powerful, highly extensible, free and open-source full-stack database solution.*
PostgreSQL Overview
PostgreSQL originates from the 1986 Berkeley POSTGRES project and is a powerful open-source relational database management system, known for its stability, flexibility, rich feature set, and community support.
Key characteristics:
- Open source and free
- Highly extensible
- Powerful SQL capabilities
- Data integrity
- Security
- Multi-version concurrency control (MVCC)
- Extensibility: custom data types, operators, functions, and index methods; functionality can be enhanced via extension plugins.
- Strict ACID compliance: fully ensures consistency, atomicity, isolation, and durability.
- Rich data types: JSON/JSONB, arrays, range types, geometric types, and more.
- Open-source community driven: an active developer community with continuous improvement, under the PostgreSQL License.
- Reliability and stability: years of development and testing, strong community support, widely used in mission-critical systems.
- Advanced features: complex queries, foreign keys, triggers, views, window functions, and other advanced database capabilities.
- Extensibility: a rich plugin ecosystem including PostGIS, TimescaleDB, Citus, and more.
- Community ecosystem: an active open-source community with extensive documentation and resources.
PostgreSQL is a full-stack database that can serve many roles: OLTP, OLAP, stream processing, geospatial data, time-series data, and more.
PostgreSQL Architecture and Principles
Client/Server Architecture
PostgreSQL uses a C/S architecture: clients connect over the network to the server to perform database operations. Each connection is handled by a separate independent process, providing better isolation.
Multi-Version Concurrency Control (MVCC)
MVCC maintains multiple versions of data so that read and write operations do not block each other. Every transaction sees a consistent data snapshot, avoiding read-write conflicts.
Query Processing Pipeline
Parse → Optimize → Execute
The query processor parses SQL statements, the query optimizer selects the best execution plan, and the execution engine runs the query and returns results.
Storage Engine
PostgreSQL uses a unified storage engine that supports transactions, foreign keys, and row-level locking, ensuring data consistency and integrity. It also supports advanced storage features such as tablespaces and partitioned tables.
PostgreSQL Design Philosophy
PostgreSQL vs MySQL Comparison
| Feature | PostgreSQL | MySQL | |---|---|---| | Architecture | Multi-process; one independent process per connection | Multi-threaded; multiple threads within a single process | | Data types | Rich: JSON/JSONB, arrays, range types, geometric types, etc. | Basic: integers, floats, strings; limited advanced type support | | JSON support | Powerful: JSONB binary storage with GIN indexes, efficient queries | Basic JSON type with limited functionality and weaker indexing | | Geospatial | PostGIS extension with 300+ spatial functions, very capable | Basic spatial data types, ~20 spatial functions, limited | | Transactions | Strict ACID; default REPEATABLE READ isolation | InnoDB supports ACID; default REPEATABLE READ but implemented differently | | Use cases | Complex analytics, high consistency, geospatial applications | Simple queries, high concurrency, web applications |