PostgreSQL

PostgreSQL
Name	PostgreSQL
Developer	PostgreSQL Global Development Group
Released	08 July 1996
Latest release version	17.2
Latest release date	08 February 2024
Operating system	Cross-platform
Genre	Relational database management system
License	PostgreSQL License (permissive free software license)
Website	https://www.postgresql.org/

PostgreSQL, often known simply as Postgres, is a powerful, open-source object-relational database management system (ORDBMS) with a strong reputation for reliability, feature robustness, and performance. It emphasizes extensibility and compliance with technical standards, supporting both SQL (Structured Query Language) and JSON (JavaScript Object Notation) querying. The system is developed by a worldwide team of volunteers, known as the PostgreSQL Global Development Group, and is released under a license similar to the BSD licenses.

History

The project's origins trace back to the Ingres database developed at the University of California, Berkeley in the 1970s. In 1986, a team led by Michael Stonebraker began work on Postgres, aiming to add support for complex data types. The project concluded at Berkeley in 1994, but its open-source code was taken up by Andrew Yu and Jolly Chen, who replaced the original QUEL query language with SQL, creating Postgres95. In 1996, the project was renamed to reflect its new SQL capabilities, and its stewardship was formalized by the establishment of the PostgreSQL Global Development Group. Major milestones include the introduction of features like Write-ahead logging (WAL) in 2000 and support for Microsoft Windows in 2005.

Features

It is renowned for its advanced feature set, which includes full ACID compliance, sophisticated locking mechanisms, and multi-version concurrency control (MVCC). It supports a wide array of data types, including advanced geometric data types and network address types, and allows for the creation of custom types. Key features include comprehensive support for JSON and related binary format JSONB, powerful indexing options like Generalized Search Tree (GiST) and Space-partitioning tree (SP-GiST), and built-in synchronous replication. The system also provides extensive support for stored procedures and functions in languages such as PL/pgSQL, Python (via PL/Python), and Perl.

Architecture

The system employs a process per connection model, where each client connection is handled by a separate backend process spawned from the main postmaster daemon. This architecture enhances stability by isolating sessions. Core to its durability is the Write-ahead logging (WAL) system, which ensures data integrity. Data is stored in a cluster of databases within a single file system directory, with tables and indexes organized into heap files. The query optimizer is cost-based and can handle complex queries involving multiple join methods and sophisticated query planning. The shared buffers in memory manage caching, working in concert with the operating system's page cache.

Administration and development tools

Administration is commonly performed using the command-line tool psql, which provides an interactive terminal. Popular graphical user interface (GUI) administration clients include pgAdmin, which is the official project offering, and DBeaver. For database migration and schema management, tools like Liquibase and Flyway are often used. Monitoring and performance tuning can be accomplished with utilities such as pg_stat_statements and external systems like Prometheus with the PostgreSQL Exporter. Connection pooling is frequently handled by PgBouncer.

Distributions and variants

While the core software is available from the official project, several companies and organizations provide packaged distributions, support, and proprietary variants. Notable commercial distributions and managed services include those from EnterpriseDB (which offers EDB Postgres Advanced Server), Amazon Web Services (Amazon Aurora PostgreSQL-compatible edition), and Microsoft (Azure Database for PostgreSQL). Other significant cloud offerings come from Google Cloud (Cloud SQL) and IBM (IBM Db2). Specialized forks and variants exist for specific use cases, such as TimescaleDB for time-series data and Greenplum Database for massively parallel processing (MPP) and data warehousing.

See also

* MySQL * MariaDB * SQLite * Oracle Database * Microsoft SQL Server

Category:Free database management systems Category:Object-relational database management systems Category:Software using the PostgreSQL license