PostgreSQL

PostgreSQL, also called Postgres, is an open-source object-relational database management system. It combines SQL queries and relational constraints with extensibility: applications can add data types, functions, operators and index methods. Its origins are in the POSTGRES research project at the University of California, Berkeley.^[1][2] PostgreSQL is distributed under the PostgreSQL License, a permissive license allowing use, modification and redistribution subject to its notice requirements.^[3]

This article describes the database software, with technical behavior referenced to PostgreSQL 18. Extensions and commercial hosting services are separate from the core system.

Research origins and naming

Michael Stonebraker led the Berkeley POSTGRES project, whose implementation began in 1986. Stonebraker and Lawrence A. Rowe's 1986 paper, The Design of POSTGRES, proposed a successor to INGRES that would retain the relational model while accommodating complex objects and user-defined types, operators and access methods. The design also investigated rule processing and changes to crash recovery. These were research goals for the original system, not a specification of present-day PostgreSQL.^[2][4]

The Berkeley project ended with POSTGRES 4.2. In 1994, Andrew Yu and Jolly Chen added an SQL interpreter; the resulting open-source descendant was released as Postgres95. In 1996, the project adopted the name PostgreSQL and resumed the earlier version-number sequence at 6.0. Postgres remains an official alternative name.^[2]

Architecture and data model

PostgreSQL uses a client/server architecture. The server program, postgres, manages database files and executes requests. Clients can be command-line tools, graphical interfaces or applications using database drivers. A client and server may run on different machines; a client-side file path therefore does not necessarily identify a file accessible to the server. The server handles concurrent connections through separate backend processes.^[5]

Relational constraints express rules that the database checks when data changes. A primary key identifies rows uniquely and disallows null key values. Foreign keys require referencing values to correspond to values in a referenced table, subject to their null and referential-action rules. NOT NULL, UNIQUE and CHECK constraints express other restrictions. For example, a document table can require a unique document identifier and a non-null title without relying on every application to repeat those checks. Constraints enforce the rules actually declared in the schema; they cannot establish that a stored claim or model output is factually correct.^[6]

The object-relational description refers to facilities beyond a fixed set of scalar columns and operators. PostgreSQL permits user-defined types and functions, and extensions can add specialized behavior. It remains a relational database: adding a specialized type does not remove SQL joins, constraints or transactions.^[1]

Transactions and concurrent access

A transaction groups database operations into a unit. BEGIN starts an explicit transaction block; COMMIT completes it, while ROLLBACK cancels its database changes. PostgreSQL also executes individual statements within transactions when an application has not opened an explicit block. Savepoints allow part of a transaction to be rolled back without discarding earlier work in the same block.^[7]

PostgreSQL uses multiversion concurrency control, or MVCC, to provide snapshots of data to concurrent queries. A query can read a visible version of a row while another transaction changes it, rather than reading that transaction's incomplete modification.^[8] MVCC does not mean the database is lock-free. Conflicting updates can wait on row locks, and operations holding an ACCESS EXCLUSIVE table lock can block ordinary reads. Deadlocks remain possible.^[9]

Isolation levels

PostgreSQL's default isolation level is Read Committed. The isolation choices differ in what concurrent activity a transaction can observe:^[10]

Sequence changes are an important exception to ordinary rollback expectations. Advancing a sequence is visible to other transactions and is not undone when the requesting transaction aborts. Consequently, a sequence-backed identifier should not be interpreted as a promise of gapless numbering.^[10]

Logging and durability

Write-ahead logging, or WAL, records changes before the corresponding changed data pages reach permanent storage. Recovery can replay logged changes after a crash, so PostgreSQL does not have to flush every modified table and index page at each commit.^[11]

Durability depends on configuration. With asynchronous commit, the server can acknowledge a transaction before its WAL has reached durable storage; a crash can then lose recent acknowledged transactions. This differs from disabling fsync, which can permit database corruption after an operating-system or hardware crash. These settings are not interchangeable performance switches.^[12]

JSON and document-shaped data

PostgreSQL supports both json and jsonb columns. json preserves the supplied textual representation, including whitespace and object-key order. jsonb stores a decomposed representation and supports indexing, but does not preserve those textual details or duplicate object keys; when a key is repeated, the last value is retained.^[13]

JSON columns can coexist with relational columns. An application might keep a document's identifier and owner in ordinary columns while placing variable metadata in jsonb. A predictable metadata structure still makes queries easier to maintain. Large JSON documents also remain subject to row-level concurrency: updating part of a stored document acquires a lock on the row containing it.^[13]

Indexes and query plans

PostgreSQL provides several index methods for different operators and data structures. The default for CREATE INDEX is B-tree. An index's usefulness depends on the query and its operator class, not simply on whether a column has an index.^[14]

EXPLAIN shows the query planner's chosen scans, joins and estimated costs. Those costs are planner units, not measured milliseconds. EXPLAIN ANALYZE actually runs the statement and adds observed execution statistics. It is therefore not a harmless preview for a modifying statement: its side effects occur unless appropriately contained. Comparing estimated and observed row counts can reveal why a plan behaves differently from expectations.^[15]

Text search and AI retrieval

PostgreSQL includes full-text search in the core system. It parses text into tokens, normalizes them into lexemes and can rank matching documents. tsvector represents a processed document; tsquery represents a processed search query. The @@ operator tests a match. Text-search configurations control language-dependent processing, including stop words and normalization. This is a lexical information retrieval mechanism rather than an embedding-based semantic search model.^[16]

pgvector is a separate extension that adds vector similarity search. It supports exact nearest-neighbor queries and optional HNSW or IVFFlat indexes for approximate nearest-neighbor search. Approximate indexes trade recall for speed and can change which neighbors a query returns. Vector storage and search do not themselves generate embeddings; the extension's documentation shows loading vectors generated elsewhere.^[17]

A retrieval-augmented generation application can use relational fields to record document ownership and a text index to select candidate passages. This is an example use of database facilities, not a built-in PostgreSQL RAG pipeline. Selecting a retrieval method does not remove the need to enforce access restrictions on the documents returned to an application.^[6][16][18]

Extensions and hosting

An extension packages related database objects so PostgreSQL can manage them as a unit. CREATE EXTENSION loads those objects into a database from files installed on the server. Extensions can include SQL definitions and compiled libraries. The packaging mechanism also affects backup behavior: pg_dump normally records the extension creation command rather than separate definitions for each member object. Restoring the dump therefore requires the appropriate extension files on the destination server.^[19]

This distinction matters when moving an application. A schema that uses an extension depends on more than the PostgreSQL major version. The destination must provide the required extension and a compatible installation; copying SQL that references an unavailable type or function is insufficient.^[19]

Commercial companies offer PostgreSQL hosting and support contracts, but those services are not the PostgreSQL software project itself.^[20] Features advertised by a hosting provider should be attributed to that service rather than presented as universal PostgreSQL behavior.

Permissions and application security

Database roles control privileges. A role needs the LOGIN attribute to be used as the initial role for a connection. A superuser bypasses permission checks other than the right to log in, so the PostgreSQL documentation advises performing most work without superuser privileges.^[21]

Row-level security can restrict which rows a role may read or modify. Enabling it on a table without an applicable policy produces a default-deny result for normal access. However, superusers and roles with BYPASSRLS bypass these policies, and table owners normally do so unless forced to obey them. Testing a policy only as the owner can therefore give a misleading impression of what an application user will see. Row security also does not govern whole-table operations such as TRUNCATE.^[18]

Client libraries can send parameter values separately from SQL text. PostgreSQL's libpq, for example, provides PQexecParams for this purpose. Treating untrusted values as parameters avoids manually constructing SQL string literals; dynamically selected identifiers, such as table names, require separate handling. Parameterization is not a substitute for restricting which database operations an application account is allowed to perform.^[22][21]

Maintenance, backup and replication

Vacuuming and statistics

Updates and deletes leave older row versions that may still be visible to other transactions. Once they are no longer needed, ordinary VACUUM makes their space reusable. It generally does not return that space to the operating system, apart from certain empty pages at a table's end. VACUUM FULL rewrites a table and can return more space, but requires an exclusive lock and additional temporary disk space. Autovacuum does not run VACUUM FULL.^[23]

Routine maintenance also prevents transaction-ID wraparound problems and maintains the visibility map. ANALYZE gathers statistics for query planning; autovacuum can schedule both vacuuming and analysis. A busy installation may need adjusted maintenance settings rather than assuming the defaults will suit every workload.^[23]

Recovery methods

pg_dump produces a logical backup of one database. Its snapshot is internally consistent, and ordinary reads and writes can continue during the dump, although operations requiring exclusive locks can conflict. A single-database dump does not include cluster-wide roles and tablespaces; pg_dumpall handles those objects as well as databases.^[24]

Point-in-time recovery uses a base backup and a continuous sequence of archived WAL. Replay can stop at a selected recovery point. A logical dump cannot replace the base backup for WAL replay. Recovery depends on retaining the needed archive sequence, so a backup plan must account for both the base backup and its associated WAL.^[25]

Replication tradeoffs

Streaming replication sends WAL to standby servers and is asynchronous by default. A promoted standby can lack transactions committed on the former primary if they had not yet reached it. Synchronous replication can wait for standby confirmation, with the exact durability behavior depending on the selected settings. This adds latency and can leave commits waiting when the required standby acknowledgments are unavailable.^[26]

Logical replication instead publishes changes to selected data objects and applies them through subscriptions. It can support selective replication and replication across major versions.^[27] It does not automatically replicate schema changes or sequence state in PostgreSQL 18. A migration or failover plan must handle those separately.^[28]

Versions and upgrades

The PostgreSQL project's policy provides five years of support for each major version. Minor releases deliver fixes within a major series, including security fixes. The project recommends running the current minor release for the chosen major version and reading the release notes before upgrading.^[29]

Minor updates retain the major version's data-storage format. Major upgrades can change that format and introduce application-visible incompatibilities. Supported approaches include dump and restore, pg_upgrade, and replication-based migration. Extension compatibility and intervening release notes remain part of upgrade planning even when skipping directly across major versions.^[30]

References

1. ^1 ^2PostgreSQL Global Development Group. What Is PostgreSQL?. PostgreSQL 18 documentation.
2. ^1 ^2 ^3PostgreSQL Global Development Group. A Brief History of PostgreSQL. PostgreSQL 18 documentation.
3. ^PostgreSQL Global Development Group. License.
4. ^Michael Stonebraker and Lawrence A. Rowe. The Design of POSTGRES. ACM SIGMOD, 1986, pp. 340-355.
5. ^PostgreSQL Global Development Group. Architectural Fundamentals. PostgreSQL 18 documentation.
6. ^1 ^2PostgreSQL Global Development Group. Constraints. PostgreSQL 18 documentation.
7. ^PostgreSQL Global Development Group. Transactions. PostgreSQL 18 documentation.
8. ^PostgreSQL Global Development Group. Concurrency Control: Introduction. PostgreSQL 18 documentation.
9. ^PostgreSQL Global Development Group. Explicit Locking. PostgreSQL 18 documentation.
10. ^1 ^2PostgreSQL Global Development Group. Transaction Isolation. PostgreSQL 18 documentation.
11. ^PostgreSQL Global Development Group. Write-Ahead Logging. PostgreSQL 18 documentation.
12. ^PostgreSQL Global Development Group. Asynchronous Commit. PostgreSQL 18 documentation.
13. ^1 ^2PostgreSQL Global Development Group. JSON Types. PostgreSQL 18 documentation.
14. ^PostgreSQL Global Development Group. Index Types. PostgreSQL 18 documentation.
15. ^PostgreSQL Global Development Group. EXPLAIN. PostgreSQL 18 documentation.
16. ^1 ^2PostgreSQL Global Development Group. Full Text Search: Introduction. PostgreSQL 18 documentation.
17. ^pgvector contributors. pgvector README. Project repository; accessed September 27, 2026.
18. ^1 ^2PostgreSQL Global Development Group. Row Security Policies. PostgreSQL 18 documentation.
19. ^1 ^2PostgreSQL Global Development Group. Packaging Related Objects into an Extension. PostgreSQL 18 documentation.
20. ^PostgreSQL Global Development Group. Hosting Providers.
21. ^1 ^2PostgreSQL Global Development Group. Role Attributes. PostgreSQL 18 documentation.
22. ^PostgreSQL Global Development Group. Command Execution Functions. PostgreSQL 18 documentation.
23. ^1 ^2PostgreSQL Global Development Group. Routine Vacuuming. PostgreSQL 18 documentation.
24. ^PostgreSQL Global Development Group. SQL Dump. PostgreSQL 18 documentation.
25. ^PostgreSQL Global Development Group. Continuous Archiving and Point-in-Time Recovery. PostgreSQL 18 documentation.
26. ^PostgreSQL Global Development Group. Log-Shipping Standby Servers. PostgreSQL 18 documentation.
27. ^PostgreSQL Global Development Group. Logical Replication. PostgreSQL 18 documentation.
28. ^PostgreSQL Global Development Group. Logical Replication: Restrictions. PostgreSQL 18 documentation.
29. ^PostgreSQL Global Development Group. Versioning Policy.
30. ^PostgreSQL Global Development Group. Upgrading a PostgreSQL Cluster. PostgreSQL 18 documentation.