In this section, you will learn what PostgreSQL is, what is new in version 12 (and version 13), and how to install and run this great open source database.
PostgreSQL is a well-known open-source relational database, and its motto states what the project intends to be: the most advanced open-source database in the world.
The main qualities that attract masses of new users every year and keep current users enthusiastic about their projects are its rock-solid stability, scalability, and safeness, as well as the features that an enterprise-level database management system provides.
But PostgreSQL is not just a database; it has grown to be a whole ecosystem of extensions, tools, and languages tied together by communities spread around the world.
PostgreSQL is an open-source project and is fully developed in the open-source world. That means that there is no single entity in charge of the project and the result is that PostgreSQL is not a commercial product. In other words, PostgreSQL belongs to everyone, and anyone can contribute to it. Thanks to a very permissive BSD-style license, PostgreSQL can be used in any project or scenario, either open or closed source.
Of course, contributing to a project of that size requires experience in software development, database concepts, and, of course, a positive attitude to open source and collaborative efforts. But it does also mean that PostgreSQL will continue to live pretty much forever without the risk of a single company going out of business and sinking with the database.
PostgreSQL 12 is the latest release of this great database, and at the time of writing, efforts for PostgreSQL 13 have already begun. This book will focus on PostgreSQL, starting from the basics and moving toward the most exciting and complex tasks (such as replicating your datasets to prevent disasters). Of course, given that PostgreSQL is a project of this size with so many features, a single book cannot cover it all in detail, so our aim is to introduce the whole set of qualities PostgreSQL provides to you, giving practical use cases and examples, as well as external resources to help you learn more about particular aspects.
This book covers PostgreSQL 12 and 13, but the concepts explained in this book can apply also to later versions (as well as to previous ones when the same features are present). At the time of writing, PostgreSQL 12 is the stable release, while PostgreSQL 13 is in its second beta public release and is expected, therefore, to be stable enough for testing its features.
This chapter will introduce you to this great open source database starting from the project history and goals, which is very important to help you decide whether you want to use PostgreSQL in the first place. You will learn basic PostgreSQL terminology, which is very important to help you search the documentation and understand the main error messages, in case you need to. Finally, you will see how to install PostgreSQL in different ways so that you will get a basic knowledge of how to install it on different platforms and contexts.
The following topics are covered in this chapter:
- PostgreSQL at a glance
- Exploring PostgreSQL terminology
- Installing PostgreSQL 12 or higher
Technical requirements
You can find the code for this chapter at the following GitHub repository: https://github.com/PacktPublishing/Learn-PostgreSQL.
PostgreSQL at a glance
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.
ACID is an acronym of the properties used to indicate that the database engine provides atomicity, consistency, isolation, and durability. Atomicity means that a complex database operation is processed as a single instruction even when it is made up of different operations. Consistency means that the data within the database is always kept consistent and that is it is not corrupted due to partially performed operations. Isolation allows the database to handle concurrency in the "right way"âthat is, without having corrupted data from interleaved changes. Lastly, durability means that the database engine is supposed to protect the data it contains, even in the case of software and hardware failures, as much as it can.
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.
The PostgreSQL project has a very rich and extensive set of a mailing lists that range from general topics to very specific details. It is a good habit to search for problems and solutions on the mailing list archives; see the web page at https://www.postgresql.org/list/ to get a better idea.
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 wit...