# PostgreSQL

PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance. It supports ACID transactions, advanced data types, and runs on all major operating systems.

PostgreSQL ( POHST-gres-kew-EL), also known as Postgres, is a free and open-source relational database management system (RDBMS) that emphasizes extensibility and SQL compliance. The database features transactions with atomicity, consistency, isolation, and durability (ACID) properties, along with automatically updatable views, materialized views, triggers, foreign keys, and stored procedures. It is supported on all major operating systems, including Windows, Linux, macOS, FreeBSD, and OpenBSD, and handles a range of workloads from single machines to data warehouses, data lakes, or web services with many concurrent users.

The PostgreSQL Global Development Group focuses only on developing a database engine and closely related components. This core is technically what comprises PostgreSQL itself, but an extensive developer community and ecosystem provides other important feature sets that might traditionally be provided by a proprietary software vendor. These include special-purpose database engine features, such as those needed to support a geospatial or temporal database, or features that emulate other database products. Also available from third parties are a wide variety of user and machine interface features, such as graphical user interfaces or load balancing and high availability toolsets. The large third-party PostgreSQL support network of people, companies, products, and projects, even though not part of the PostgreSQL Development Group, is essential to the database engine's adoption and use and makes up the PostgreSQL ecosystem writ large.

## History

### Ingres and University POSTGRES (1982-1994)

PostgreSQL evolved from the Ingres project at the University of California, Berkeley. In 1982, the leader of the Ingres team, Michael Stonebraker, left Berkeley to make a proprietary version of Ingres. He returned to Berkeley in 1985 and began a post-Ingres project to address the problems with contemporary database systems that had become increasingly clear during the early 1980s. He won the Turing Award in 2014 for these and other projects, and for techniques pioneered in them.

The new project, POSTGRES, aimed to add the fewest features needed to completely support data types. These features included the ability to define types and to fully describe relationships, something used widely but maintained entirely by the user. In POSTGRES, the database understood relationships and could retrieve information in related tables in a natural way using rules. POSTGRES used many of the ideas of Ingres but not its code.

Starting in 1986, published papers described the basis of the system, and a prototype version was shown at the 1988 ACM SIGMOD Conference. The team released version 1 to a small number of users in June 1989, followed by version 2 with a rewritten rules system in June 1990. Version 3, released in 1991, again rewrote the rules system and added support for multiple storage managers and an improved query engine. By 1993, the number of users began to overwhelm the project with requests for support and features. After releasing version 4.2 on June 30, 1994, primarily a cleanup, the project ended. Berkeley released POSTGRES under an MIT License variant, which enabled other developers to use the code for any use. At the time, POSTGRES used an Ingres-influenced POSTQUEL query language interpreter, which could be interactively used with a console application named monitor.

### Postgres95 (1994-1996)

In 1994, Berkeley graduate students Andrew Yu and Jolly Chen replaced the POSTQUEL query language interpreter with one for the SQL query language, creating Postgres95. The monitor console was also replaced by psql. Yu and Chen announced the first version (0.01) to beta testers on May 5, 1995. Version 1.0 of Postgres95 was announced on September 5, 1995, with a more liberal license that enabled the software to be freely modifiable.

On July 8, 1996, Marc Fournier at Hub.org Networking Services provided the first non-university development server for the open-source development effort. With the participation of Bruce Momjian and Vadim B. Mikheev, work began to stabilize the code inherited from Berkeley.

### PostgreSQL (1996-present)

In 1996, the project was renamed to PostgreSQL to reflect its support for SQL. The online presence at the website PostgreSQL.org began on October 22, 1996. The first PostgreSQL release formed version 6.0 on January 29, 1997. Since then, developers and volunteers around the world have maintained the software as The PostgreSQL Global Development Group.

The project continues to make releases available under its free and open-source software PostgreSQL License. Code comes from contributions from proprietary vendors, support companies, and open-source programmers.

PostgreSQL 18, released on September 25, 2025, introduced a new asynchronous I/O (AIO) subsystem, enabling database users to perform concurrent I/O tasks like readahead and sequential scan and demonstrating up to a 3x performance improvement for storage reads in some workloads. The release also added support for planner statistics to persist across major-version upgrades, reducing the performance degradation that previously occurred while statistics were rebuilt after an upgrade. As of mid-2026, PostgreSQL 19 was in beta testing, with a general release anticipated later in the year.

## Multiversion Concurrency Control (MVCC)

PostgreSQL manages concurrency through multiversion concurrency control (MVCC), which gives each transaction a "snapshot" of the database, allowing changes to be made without affecting other transactions. This largely eliminates the need for read locks and ensures the database maintains ACID principles. PostgreSQL offers four levels of transaction isolation: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Because PostgreSQL is immune to dirty reads, requesting a Read Uncommitted transaction isolation level provides Read Committed instead. PostgreSQL supports full serializability via the serializable snapshot isolation (SSI) method. The PostgreSQL MVCC implementation is prone to performance issues that require tuning when under a heavy write load that updates existing rows.

## Storage and Replication

### Replication

PostgreSQL includes built-in binary replication based on shipping the changes (write-ahead logs, or WAL) to replica nodes asynchronously, with the ability to run read-only queries against these replicated nodes. This allows splitting read traffic among multiple nodes efficiently. Earlier replication software that allowed similar read scaling normally relied on trigger-based solutions, which were less robust. Synchronous replication is also available, ensuring that a transaction is committed only after it has been written to at least one standby server, providing stronger durability guarantees at the cost of higher latency.

### Storage and Indexing

PostgreSQL supports a variety of storage and indexing techniques. The default heap storage organizes rows in pages, and multiple index types are available, including B-tree, hash, GiST, SP-GiST, GIN, and BRIN. These indexes support efficient querying for different data types and access patterns. Table partitioning, introduced in earlier versions and enhanced over time, allows large tables to be split into smaller, more manageable pieces, improving query performance and maintenance operations. The system also supports tablespaces, allowing administrators to control physical storage placement on disk.

## Extensibility and Data Types

A defining feature of PostgreSQL is its extensibility. Users can define custom data types, operators, index methods, and functions. This extensibility has led to the development of specialized extensions such as PostGIS for geospatial data, which adds support for geographic objects and spatial queries. Other extensions provide full-text search capabilities, key-value store functionality, and integration with external systems. The extension mechanism allows third-party developers to add features without modifying the core database engine, contributing to the rich PostgreSQL ecosystem.

PostgreSQL natively supports a wide range of data types, including numeric, string, date/time, boolean, and binary data. It also includes support for arrays, JSON and JSONB (binary JSON), XML, and network address types. The JSONB type, in particular, allows for efficient storage and querying of semi-structured data, making PostgreSQL a viable option for document-oriented workloads alongside traditional relational data.

## SQL Compliance and Features

PostgreSQL is known for its high degree of SQL compliance. It implements a large portion of the SQL standard, including complex queries, subqueries, window functions, common table expressions (CTEs), and recursive queries. The database supports foreign keys, check constraints, unique constraints, and primary keys to enforce data integrity. Triggers, which can be written in several procedural languages, allow automatic execution of functions in response to database events.

Stored procedures and functions can be written in multiple languages, including PL/pgSQL (the built-in procedural language), PL/Python, PL/Perl, and PL/Tcl. This flexibility enables developers to implement business logic directly in the database. PostgreSQL also supports user-defined functions written in C, allowing for high-performance custom operations. The system includes a sophisticated query planner and optimizer that can handle complex join orders and use statistics to choose efficient execution plans.

## Community and Ecosystem

The PostgreSQL Global Development Group coordinates the development of the core database engine, but the broader ecosystem is vast. Numerous companies offer commercial support, consulting, and training for PostgreSQL. Cloud providers, including [amazon-web-services](https://www.wikiprompt.org/wiki/amazon-web-services), [azure](https://www.wikiprompt.org/wiki/azure), and [google-cloud](https://www.wikiprompt.org/wiki/google-cloud), offer managed PostgreSQL services, making it easy to deploy and scale databases in the cloud. These services handle operational tasks like backups, patching, and high availability, while providing integration with other cloud-native tools.

Third-party tools enhance usability and functionality. Graphical user interfaces like pgAdmin provide visual management and query tools. High availability and load balancing solutions, such as Patroni and repmgr, automate failover and cluster management. Backup tools like pgBackRest and Barman offer advanced backup and recovery features. The ecosystem also includes connection poolers like PgBouncer, which improve performance for applications with many concurrent connections.

## Use Cases and Adoption

PostgreSQL is used across a wide range of industries and applications. It serves as the primary database for web applications, mobile backends, and enterprise systems. Its support for JSONB makes it attractive for applications that need a hybrid relational and document store. The geospatial capabilities provided by PostGIS are widely used in location-based services and geographic information systems (GIS).

In the realm of [artificial-intelligence](https://www.wikiprompt.org/wiki/artificial-intelligence) and [machine-learning](https://www.wikiprompt.org/wiki/machine-learning), PostgreSQL is increasingly used as a data store for training and inference pipelines. Extensions like pgvector allow for efficient storage and querying of vector embeddings, which are essential for [large-language-model](https://www.wikiprompt.org/wiki/large-language-model) applications and [generative-ai](https://www.wikiprompt.org/wiki/generative-ai) systems. This enables developers to build semantic search and recommendation systems directly within the database, reducing the need for separate vector databases. The database's robustness, extensibility, and open-source license have made it a popular choice for organizations ranging from startups to large enterprises.

## Licensing and Governance

The PostgreSQL License is a permissive free and open-source license, similar to the MIT License. It allows users to use, modify, and distribute the software for any purpose, including commercial applications, with minimal restrictions. The license requires that copyright notices be retained and that the software is provided without warranty.

The project is governed by a community of developers and users, with a core team overseeing major decisions. Development is driven by mailing lists and an annual release cycle, with major versions typically released in the fall. The governance model emphasizes transparency and consensus, with contributions from individuals and companies worldwide. This open governance has fostered a stable and innovative development process, ensuring PostgreSQL remains a leading database technology.

---
Source: https://www.wikiprompt.org/wiki/postgres
License: CC BY-SA 4.0 (https://creativecommons.org/licenses/by-sa/4.0/)
Last updated: 2026-09-12T22:21:05.960262+00:00