As a relational database, PostgreSQL provides a lot of features, and it is quite difficult to "scare" a PostgreSQL instance. In fact, a single instance can contain more than 4 billion individual databases, each with unlimited total size and capacity for more than 1 billion tables, each containing 32 TB of data. Moreover, if there's any concern that those upper limits won't suffice, please consider that a single table can have 1,600 columns, each 1 GB in size, with an unlimited number of multi-column (up to 32 columns) indexes. In short, PostgreSQL can store much more data than you can possibly think of!
Therefore, there is no amount of data that PostgreSQL cannot handle, but of course, in order to perform well with certain big databases, you need to understand PostgreSQL and its features.
PostgreSQL is fully ACID-compliant and has a very strong foundation in data integrity and concurrency. It ships with a procedural language, named PL/pgSQL, which can be used to write reusable pieces of code, such as functions and routines, and it supports before and after triggers, views, materialized views, and partitioned tables.
PostgreSQL can be extended with other embedded languages, such as Perl, Python, Java, and even Bash! And if you think the database does not provide you with enough features, you can plug in extensions to obtain different behaviors and enhancements—for instance, geospatial references (GIS), scheduled jobs, esoteric data types, and utilities in general.
PostgreSQL runs on pretty much every operating system out there, including Linux, Unix, Mac OS X, and Microsoft Windows, and can even run on commodity hardware such as Raspberry Pi boards. There are also several cloud computing providers that list PostgreSQL in their software catalog.
Thanks to its extensive tuning mechanism, it can be adapted very well to the hosting platform. The community is responsible for keeping the database and documentation at a very high-quality level, and also the mailing lists and IRC channels are very responsive and a valuable source for solutions and ideas.
In the experience of the authors, there has never been a case where PostgreSQL has not been able to adapt to an application scenario.
A brief history of PostgreSQL
PostgreSQL takes its name from its ancestor: Ingres. Ingres was a relational database developed by professor Michael Stonebraker. In 1986, Professor Stonebraker started a post-Ingres project to develop new cool features in the database landscape and named this project POSTGRES (POST-Ingres). The project aimed to develop an object-relational database, where "object" means the user would have the capability to extend the database with their own objects, such as data types, functions, and so on.
In 1994, POSTGRES was released with version 4.2 and an MIT license, which opened up collaboration from other developers around the world. At that time, POSTGRES was using an internal query language named QUEL.
Two Berkeley students, Andrew Yu and Jolly Chen, replaced the QUEL query language with the hot and cool SQL language, and the feature was so innovative that the project changed its name to Postgre95 to emphasize the difference compared to other preceding versions.
Eventually, in 1996, the project gained a public server to host the code, and five developers, including Marc G. Fournier, Tom Lane, and Bruce Momjan, started the development of the new branded project named PostgreSQL. Since then, the project has been kept in good shape and up to date.
This also means that PostgreSQL has been developed for over 30 years, again emphasizing the solidity and openness of the project itself. If you are curious, it is also possible to dig into the source code down to the initial commit in the open source world:
$ git log `git rev-list --max-parents=0 HEAD`
commit d31084e9d1118b25fd16580d9d8c2924b5740dff
Author: Marc G. Fournier <[email protected]>
Date: Tue Jul 9 06:22:35 1996 +0000
Postgres95 1.01 Distribution - Virgin Sources
What's new in PostgreSQL 12?
PostgreSQL 12 was released on October 3, 2019. It includes a rich set of new features with regard to its predecessor versions, including the following:
- Several performance optimizations, ranging from inlining Common Table Expressions to huge table partition management and an improved user-defined statistic hint for multi-column selections
- A few administrative optimizations, including the concurrent rebuilding of indexes, off-line check-summing, and, most notably, reporting about maintenance processes' progress
- Security features including multi-factor authentication and TCP/IP encryption via GSSAPI
- Support for the SQL JSON path language
- Stored generated columns
PostgreSQL 12 also contains a set of changes aimed to make the database administrator (DBA)'s life easier—for instance, removing conflicting options and obsolete SQL terms and types. This emphasizes the fact that PostgreSQL developers do always take care of the database and its adherence to the current SQL standard.
What's new in PostgreSQL 13?
PostgreSQL 13 will contain a very rich set of optimizations under the hood, with particular regard to the following:
- Partitioning, which now includes the ability to execute before triggers on partitioned tables, the capability to prune partitions in particular edge cases to speed up query execution, and a better way to join partitions in queries (referred to as partition-wise joins).
- Replication, which can now work at the logical level even on partitioned tables, automatically publishing all the partitions. Also, there is now no automatic promotion of a server if it does not reach the specific target to recovery and a slave server can be promoted without cancelling any pending pause requests. It is worth noting that it is possible to change the settings of a streaming replication without having to restart the cluster, therefore having a no-downtime impact.
- Indexes, which are now more efficient in general for storing data and accepting operators with parameters.
- Statistics, with particular regard to improvements in the extended statistics, the data collected and used by the optimizer and a few changes in the monitoring catalogs.
There are a lot more changes that will be discussed in an appropriate chapter at the end of the book, but as usual, a new release of PostgreSQL contains performance improvements as well as security improvements and, as always, a better configuration system.
PostgreSQL release policy, version numbers, and life cycle
PostgreSQL developers release a new major release once per year, usually near October. A major release is a stable version that introduces new features and possible incompatibilities with previous versions. During its life cycle, a major release is constantly improved by means of minor releases, which are usually bug-fixing and maintenance releases.
The PostgreSQL version number identifies the major and minor release. Since PostgreSQL 10 (released in 2011), the version number is specified as major.minor; so, for instance, 12.0 indicates the first major release, 12, while 12.1 indicates the minor release, 1, of major release 12. In short, the greater the number, the more recent the version you are managing.
However, before PostgreSQL 10, the version number was made by three different groups of digits—brand.year.minor—where the brand is the main development topic (for instance, "replication"), the year represents the year of development of that brand, and the minor is the minor version. What is important to keep in mind is that the brand and year pair made a major number in PostgreSQL versions prior to 10. So, for instance, PostgreSQL 9.6.16 is the 16th minor release done on brand 9 during the 6th year of development, and therefore could be incompatible with 9.5.20 because the two major versions are 9.6 and 9.5.
But what does it mean, in a practical sense, that two major versions are possibly incompatible?
PostgreSQL stores its own data on the storage system, often the hard disk. This data is stored in binary format, for optimization of performances and space consumption, and this format could possibly change between major versions. This means that, while you are able to upgrade PostgreSQL between minor versions on the fly, you probably will have to dump and restore your database content between major version upgrades. As you will see in this book, PostgreSQL provides ad hoc tools to support you even in the worst case of a major upgrade with a lot of incompatibilities, but keep in mind that while a minor version upgrade is something you usually do without any ahead planning, a major version upgrade could imply downtime.
The recommendation, as for much other software, is to run the most recent version of PostgreSQL available to you: PostgreSQL developers put in a lot of effort in order to provide bug-free products, but new features could introduce new bugs, and regardless of the very extensive testing platform PostgreSQL has, it is software after all, and software could have bugs. Despite internal bugs, new releases also include fixes for security exploits and performance improvements, so it is a very good habit to keep up to date with your running PostgreSQL server.
Last but not least, not all PostgreSQL versions will live forever. PostgreSQL provides support and upgrades for 5 years after a new release is issued; after this length of time, a major release will reach its end of life (EOL) and PostgreSQL developers will no longer maintain it. This does not mean you cannot run an ancient version of PostgreSQL, it simply means this version will not get any upgrades from the official project and, therefore, will be out of date. As an example, since PostgreSQL 12 was released in 2019, it will reach its EOL in 2024.
With that in mind, we'll now introduce the main PostgreSQL terminology, as well as further useful-to-understand concepts.
