1 About PostgreSQL
PostgreSQL is the most feature-rich free and open-source database system. Originally developed in an academic environment, it has successfully united a large community of developers over the years. Nowadays, PostgreSQL offers everything that most customers need, and it is actively used all over the world to create high-load business-critical systems.
Some History
Modern PostgreSQL originates from the POSTGRES project, which was led by Michael Stonebraker, professor of the University of California, Berkeley. Before this work, Michael Stonebraker managed the development of INGRES, one of the first relational database systems. POSTGRES emerged as an effort to rethink previous work and address the limitations of INGRES’s rigid type system.
The project was started in 1985, and by 1988 a number of scientific articles had been published that described the data model, POSTQUEL query language (SQL was not an accepted standard at the time), and data storage structure.
POSTGRES is sometimes considered to be a so-called post-relational database system. The relational model had long been criticized for its restrictions, which were an inevitable trade-off for its strictness and simplicity As computer technologies were spreading in all spheres of life, new types of applications started to appear, and databases had to support custom data types and such features as inheritance or creating and managing complex objects.
The first version of this database system appeared in 1989. It was being improved and enhanced for several years, but in 1993, when version 4.2 was released, the project was shut down. However, despite its official cancellation, UC Berkeley alumni Andrew Yu and Jolly Chen revived the project and resumed its development in 1994, taking advantage of its liberal BSD license and open source. They replaced POSTQUEL query language with SQL, which had become a generally accepted standard by that time. The project was renamed to Postgres95.
In 1996, it became obvious that the Postgres95 name would not stand the test of time, and a new name was selected: PostgreSQL. This name reflects the connection both with the original POSTGRES project and the SQL adoption. This name may be quite hard to articulate, but nevertheless, we should pronounce it as “Post-Gres-Q-L” or simply “postgres,” but not as “postgre.”
The first PostgreSQL release had version 6.0, keeping the original numbering scheme. The project grew, and its management was taken over by at first a small group of active users and developers, which was named PostgreSQL Global Development Group.
Development
All the main decisions about developing and releasing new PostgreSQL versions are taken by the Core team, which consists of seven people at the moment.
Apart from occasional contributors, there is a core group of developers who make significant contributions to PostgreSQL. They are called major contributors. There is also a group of committers, who have the write access to the source code repository. The group’s membership evolves over time, with new developers joining the community while others move on. The current list of developers is published on PostgreSQL’s official website: postgresql.org/community/contributors.
The contribution of Russian developers into PostgreSQL is compelling. This is arguably the largest global open-source project with such a vast Russian representation.
Vadim Mikheev, a software programmer from Krasnoyarsk who used to be a member of the Core team, played an important role in PostgreSQL evolution and development. He created such key core features as multi-version concurrency control (MVCC), vacuum, write-ahead log (WAL), subqueries, triggers. Vadim is not involved with the project anymore.
In 2015, Oleg Bartunov, a professional astronomer and research scientist at Sternberg Astronomical Institute of Lomonosov Moscow State University, teamed up with Teodor Sigaev and Alexander Korotkov to start the Postgres Professional company, which is now the main talent foundry in Russia when it comes to database system development.
The main areas of their contribution are PostgreSQL localization (national encodings and Unicode support), full-text search, working with arrays and semi-structured data (hstore, json, jsonb), new index methods (GiST, SP-GiST, GIN and RUM, Bloom). They have also created a lot of popular extensions.
The PostgreSQL release cycle usually takes about a year. In this timeframe, the community receives patches with bug fixes, updates, and new features from contributors worldwide. Traditionally, all patches are discussed in the pgsql-hackers mailing list. If the community finds the idea useful, its implementation is correct, and the code passes a mandatory code review by other developers, the patch is included into the next release.
At some point (usually in spring, about half a year before the release), code stabilization is announced: all new features get postponed until the next version, only bug fixes and improvements for the already included patches are accepted. Within the release cycle, beta versions appear. Closer to the end of the release cycle a release candidate is built, and soon a new major version of PostgreSQL is released.
Major versions used to be defined by two numbers, but in 2017 it was decided to start using a single number. Thus, version 9.6 was followed by PostgreSQL 10, while the latest available version is PostgreSQL 17, which was released in September 2024.
As the new version is being prepared, developers can find and fix bugs in it. The most critical fixes are backported to the previous versions. The community usually releases updates quarterly; these minor versions accumulate such fixes. For example, version 12.5 contains bug fixes for the 12.4 release, while version 17.1 provides fixes for PostgreSQL 17.0.
Support
PostgreSQL Global Development Group supports major releases for five years. Both support and development are managed through mailing lists. A properly filed bug report has a high likelihood of being addressed quickly; bug fixes can be released in as little as 24 hours.
Aside from the community support, 24x7 commercial support for PostgreSQL is also provided by a number of companies in different countries, including Postgres Professional (www.postgrespro.com).
Current State
PostgreSQL is one of the most popular databases. Based on the solid foundation of academic development, over several decades PostgreSQL has evolved into an enterprise-level product that is now a real alternative to commercial databases. You can see it for yourself by looking at the key features of PostgreSQL 17, which is the latest released version right now.
Reliability and Stability
Reliability is especially important in enterprise-level applications that handle business-critical data. For this purpose, PostgreSQL provides support for hot standby servers, point-in-time recovery, different types of replication (synchronous, asynchronous, cascade).
Security
PostgreSQL supports secure SSL connections and provides various authentication methods, such as password authentication (including SCRAM), client certificates, and external authentication services (LDAP, RADIUS, PAM, Kerberos).
For user management and database access control, the following features are provided:
creating and managing new users and group roles
role- and group-based access control to database objects
row-level and column-level security
SELinux support via a built-in SE-PostgreSQL functionality (Mandatory Access Control)
Russian Federal Service for Technical and Export Control has certified a custom PostgreSQL version released by Postgres Professional for use in data processing systems for personal data and classified information.
Conformance to the SQL Standard
As the ANSI SQL standard is evolving, its support is constantly being added to PostgreSQL. This is true for all versions of the standard, from SQL-92 to the most recent SQL:2023. For example, PostgreSQL 17 fully supports all JSON operations as described in the SQL:2016 Standard.
In general, PostgreSQL provides a high rate of conformance to the SQL standard, supporting 170 out of 177 mandatory features and many optional ones.
Transaction Support
PostgreSQL provides full support for ACID properties and efficient transaction isolation based on the multi-version concurrency control (MVCC). This method avoids locking in all cases except for concurrent updates to the same row by different processes. Reading transactions never block writing ones, and writing never blocks reading.
This is true even for the serializable isolation level, which is the strictest one. Using an innovative Serializable Snapshot Isolation system, this level ensures that there are no serialization anomalies and guarantees that concurrent transaction execution produces the same result as sequential one.
For Application Developers
Application developers get a rich toolset for creating applications of any type:
Support for various server programming languages: built-in PL/pgSQL (which is closely integrated with SQL), C for performance-critical tasks, Perl, Python, Tcl, as well as JavaScript, Java, etc.
APIs to access the database from applications written in virtually any language, including the standard ODBC and JDBC APIs.
A rich set of database objects that allow you to efficiently implement the logic of any complexity on the server side: tables and indexes, sequences, integrity constraints, views and materialized views, partitioning, subqueries and WITH-queries (including recursive ones), aggregate and window functions, stored functions, triggers, etc.
A flexible full-text search system that supports a variety of languages, extended with efficient index access methods.
Semi-structured data typical of NoSQL: hstore (storage of key–value pairs), xml, json (represented as text or in a more robust jsonb binary format).
Foreign Data Wrappers. This feature allows adding new data sources as external tables by the SQL/MED standard. You can use any major database as an external data source. PostgreSQL provides full support for foreign data, including write access and distributed query execution.
Scalability and Performance
PostgreSQL takes advantage of the modern multi-core CPU architecture. Its performance grows almost linearly as the number of cores increases.
PostgreSQL can parallelize queries and some commands (such as index creation and vacuuming). In this mode, reads and joins are performed by several concurrent processes. JIT-compilation of queries can speed up operations thanks to better use of hardware resources. Each PostgreSQL version adds new parallelization features.
Horizontal scaling can rely on both physical and logical replication. It allows you to build PostgreSQL-based clusters to achieve high performance, fault tolerance, and geodistribution. Some examples of such systems are Citus (Citusdata), Postgres-BDR (2ndQuadrant), Multimaster and BiHA (Postgres Professional), Patroni (Zalando).
Query Planner
PostgreSQL relies on a cost-based query planner. Using the collected statistics and taking into account both disk operations and CPU time in its mathematical models, the planner can optimize even the most complex queries. It can use all access paths and join methods available in state-of-the-art commercial database systems.
Indexing
PostgreSQL provides various types of indexes. Apart from traditional B-trees, you can use many other access methods.
Hash, a hash-based index. Unlike B-trees, such indexes work only for equality checks, but in some cases they can prove to be more efficient and compact.
GiST, a generalized balanced search tree. This access method is used for the data that cannot be ordered. For example, R-trees index points on a plane and facilitate fast k-nearest neighbor (k-NN) searches or indexing overlapping intervals.
SP-GiST, a generalized non-balanced tree based on dividing the search space into non-intersecting nested partitions. For example, quad-trees for spatial data and radix trees for text strings.
GIN, a generalized inverted index used for compound multi-element values. It is mainly applied in full-text search to find documents that contain the words used in the search query. Another example is search for elements in data arrays.
RUM, an enhancement of the GIN method for full-text search. Available as an extension, this index type can speed up phrase search and return the results in the order of relevance without any additional computations.
BRIN, a compact structure that provides a trade-off between the index size and search efficiency. Such index is useful for huge clustered tables.
Bloom, an index based on the Bloom filter. Having a compact representation, this index can quickly filter out non-matching tuples, but the remaining ones have to be re-checked.
Many index types can be built upon both a single column and multiple columns. Regardless of the type, you can build indexes not only on columns, but also on arbitrary expressions. It is also possible to create partial indexes for specific sets of rows. Covering indexes can speed up queries, since all the required data is retrieved from the index itself, avoiding heap access.
The planner can use a bitmap scan, which allows combining several indexes together for faster access.
Cross-Platform Support
PostgreSQL runs both on Unix operating systems (including server and client Linux distributions, FreeBSD, Solaris, and macOS) and on Windows systems.
Its portable open-source C code allows building PostgreSQL on a variety of platforms, even if there is no package supported by the community.
Extensibility
One of the main advantages of PostgreSQL architecture is extensibility. Without changing the core system code, users can add various features, such as:
data types
functions and operators to support new data types
index and table access methods
server programming languages
foreign data wrappers
loadable extensions
Comprehensive extension support allows for the development of new features of any complexity, which can be installed on demand without modifying the PostgreSQL core. For example, the following complex systems are built as extensions:
CitusDB, which implements massively parallel query execution and data distribution between different PostgreSQL instances (sharding).
PostGIS, one of the most popular and powerful geoinformation data processing systems.
- TimescaleDB, which provides support for time-series data, including special partitioning and sharding.
The standard PostgreSQL 17 distribution alone includes about fifty extensions that have proved to be useful and reliable.
Availability
A liberal PostgreSQL license, which is similar to BSD and MIT licenses, allows unrestricted use of PostgreSQL; you may also modify PostgreSQL code without any limitations and integrate it into other products, including commercial and closed-source software.
Independence
PostgreSQL does not belong to any company; it is developed by the international community, which includes developers from all over the world. It means that systems using PostgreSQL do not depend on a particular vendor, thus keeping the investment safe in any circumstances.