PostgreSQL is one of the world's most advanced open source database systems, and it has many features that are widely used by developers and system administrators alike. Starting with PostgreSQL 10, many new features have been added to PostgreSQL, which contribute greatly to the success of this exceptional open source product. In this book, many of these cool features will be covered and discussed in great detail.

In this chapter, you will be introduced to PostgreSQL and its cool new features available in PostgreSQL 10.0 and beyond. All relevant new functionalities will be covered in detail. Given the sheer number of changes made to the code and given the size of the PostgreSQL project, this list of features is of course by far not complete, so I tried to focus on the most important aspects relevant to most people.

The features outlined in this chapter will be split into the following categories:

• Database administration
• SQL and developer related
• Backup, recovery, and replication
• Performance-related topics

PostgreSQL 10.0 has been released in late 2017 and is the first version that follows the new numbering scheme introduced by the PostgreSQL community. From now on, the way major releases are done will change and therefore, the next major version after PostgreSQL 10.0 will not be 10.1 but PostgreSQL 11. Versions 10.1 and 10.2 are merely service releases and will only contain bug fixes.

PostgreSQL 10.0 has many new features that can help the administrator reduce work and make systems more robust.

One of these features that makes life easier for administrators is related to additional information in pg_stat_activity.

Before PostgreSQL 10.0, pg_stat_activity only contained information about normal backend processes serving end users (connections). However, this has changed. Since PostgreSQL 10.0, a lot more information is exposed. It is possible to figure out what these other system processes are doing.

The following listing shows the content of pg_stat_activity on an idle database instance:

test=# \x 
Expanded display is on. 

test=# SELECT pid, wait_event_type, wait_event, backend_type  
   FROM  pg_stat_activity ;
 
-[ RECORD 1 ]---+-------------------- 
pid             | 12159 
wait_event_type | Activity 
wait_event      | AutoVacuumMain 
backend_type    | autovacuum launcher 
-[ RECORD 2 ]---+-------------------- 
pid             | 12161 
wait_event_type | Activity 
wait_event      | LogicalLauncherMain 
backend_type    | background worker 
-[ RECORD 3 ]---+-------------------- 
pid             | 12628 
wait_event_type |  
wait_event      |  
backend_type    | client backend 
-[ RECORD 4 ]---+-------------------- 
pid             | 12156 
wait_event_type | Activity 
wait_event      | BgWriterMain 
backend_type    | background writer 
-[ RECORD 5 ]---+-------------------- 
pid             | 12155 
wait_event_type | Activity 
wait_event      | CheckpointerMain 
backend_type    | checkpointer 
-[ RECORD 6 ]---+-------------------- 
pid             | 12157 
wait_event_type | Activity 
wait_event      | WalWriterMain 
backend_type    | walwriter 

What you see here is that every server process is listed. It will allow you to gain some insights into what is happening the server.

Most people use passwords to connect to the database and manage security. Traditionally, people utilized md5. However, md5 is not safe anymore and therefore new authentication methods are needed. Starting with version 10.0, PostgreSQL supports SCRAM-SHA-256, which is far safer than the previous authentication method.

The old way of doing it is still supported. However, it is strongly recommended to move to SCRAM-SHA-256 in favor of md5.

The introduction of PostgreSQL also saw the introduction of logical replication, which has not been in the core before.

Since version 8.0, PostgreSQL has supported binary replication (also often referred to as WAL-shipping). The ability to distribute transaction log ( WAL) has been improved steadily over the years.

With the introduction of PostgreSQL 10.0, a new feature has been added to PostgreSQL—Logical replication. How does it work? Logical replication allows you to publish a set of tables on one server and ask other servers to subscribe to the changes.

To publish data, the new CREATE PUBLICATION command has been introduced:

test=# \h CREATE PUBLICATION  
Command:  CREATE PUBLICATION 
Description: define a new publication 
Syntax: 
CREATE PUBLICATION name 
    [ FOR TABLE [ ONLY ] table_name [ * ] [, ...] 
      | FOR ALL TABLES ] 
    [ WITH ( publication_parameter [= value] [, ... ] ) ] 

Once the data has been published, remote servers can subscribe to these changes and receive information about what has happened to those published data sets:

test=# \h CREATE SUBSCRIPTION  
Command:  CREATE SUBSCRIPTION 
Description: define a new subscription 
Syntax: 
CREATE SUBSCRIPTION subscription_name 
    CONNECTION 'conninfo' 
    PUBLICATION publication_name [, ...] 
    [ WITH ( subscription_parameter [= value] [, ... ] ) ] 

CREATE SUBSCRIPTION is used on the slave side to attach to these changes. The beauty of the concept is that a server can publish one set of tables while subscribing to some other tables at the same time—there is no such thing as always master or always slave anymore. Logical replication allows you to flexibly distribute data.

PostgreSQL has offered support to synchronous replication for quite some time now. Traditionally, only one server could act as a synchronous standby. This has changed. In PostgreSQL 10.0, the community has introduced quorum COMMITs. The idea is actually quite simple. Suppose you want five out of seven servers to confirm a transaction before the master returns a COMMIT. This is exactly what a quorum COMMIT does. It gives the developers and administrators a chance to define what COMMIT does in a more fine-grained way.

To configure quorum COMMITs, the syntax of synchronous_standby_names has been extended. Here are two simple examples:

synchronous_standby_names = ANY 1 (s1, s2)  
synchronous_standby_names = ANY 2 (s1, s2, s3)  

There have been talks about introducing partitioning to PostgreSQL for years. However, big, important features take time to implement and this is especially true if you are aiming for a good, extensible, and future-proof implementation. In PostgreSQL 10.0, table partitioning has finally made it to the PostgreSQL core. Of course, the implementation is far from complete, and a lot of work has to be done in the future to add even more features. However, support for partitioning is important and will definitely be one of the most desirable things in PostgreSQL 10.0.

As of now, partitioning is able to:

• Automatically create proper child constraints
• Route changes made to the parent table to the child table

However, as stated earlier, there are still a couple of missing features that have not been addressed yet. Here are some of the more important things:

• Create child tables automatically in case data comes in, which is not covered by partitioning criteria yet
• No support for hash partitioning
• Move updated rows that no longer match the partition
• Handle partitions in parallel

The roadmap for PostgreSQL 11.0 already suggests that many of these things might be supported in the next release.

CREATE STATISTICS is definitely one of my personal favorite features of PostgreSQL 10.0 because it allows consultants to help customers in many real-world situations. So, what is it all about? When you run SQL, the optimizer has to come up with clever decisions to speed up your queries. However, to do so, it has to rely heavily on estimates to figure out how much data a certain clause or a certain operation returns. Before version 10.0, PostgreSQL only had information about individual columns. Let's look at an example:

SELECT * FROM car WHERE vendor = 'Ford' AND model = 'Mini Clubman'; 

In version 9.6, PostgreSQL checks which fraction of the table matches Ford and which fraction matches Mini Clubman. Then, it would try to guess how many rows match both criteria. Remember, PostgreSQL 9.6 only has information about each column—it does not know that these columns are actually related. Therefore, it will simply multiply the odds of finding Ford with the odds of finding Mini Clubman and use this number. However, Ford does not produce a Mini Clubman instance—only BMW does. Therefore, the estimate is wrong. The same cross column correlation problem can happen in other cases too. The number of rows returned by a join might not be clear and the number of groups returned by a GROUP BY clause might be an issue.

Consider the following example:

SELECT gender, age, count(*) FROM children_born GROUP BY gender, age 

The number of children born to people of a certain age will definitely depend on their age. The likelihood that some 30 year old women will have children is pretty high and therefore there will be a count. However, if you happen to be 98, you might not be so lucky and it is pretty unrealistic to have a baby, especially if you are a man (men tend to not give birth to children).

CREATE STATISTICS will give the optimizer a chance to gain deeper insights into what is going on by storing multivariate statistics. The idea is to help the optimizer handle functional dependencies.

PostgreSQL 9.6 was the first version supporting parallel queries in their most basic form. Of course, not all parts of the server are fully parallel yet. Therefore, it is an ongoing effort to speed up even more operations than before. PostgreSQL 10.0 is a major step towards even more parallelism as a lot more operations can now benefit from multi-core systems.

Indexes are a key area of improvement and will benefit greatly from additional features introduced into PostgreSQL 10.0. There is now full support for parallel b-tree scans as well as for bitmap scans. For now, only b-tree indexes can benefit from parallelism but this will most likely change in future releases too, to ensure that all types of indexes can enjoy an even better performance.

In addition to indexing, the PostgreSQL community has also worked hard to introduce support for parallel merge joins and to allow for more procedures to run in parallel. Some of the latest blog posts from the PostgreSQL community already suggest that many new features related to parallelism are in the pipeline for PostgreSQL 11.0.

When a PostgreSQL database is created, the administrator can choose the encoding, which should be used to store the data. Basically, the configuration decides which characters exist and in which order they are displayed. Here is an example—de_AT@UTF-8. In this case, we will use Unicode characters, which will be displayed in an Austrian sort order (Austrians speak some sort of German). So, de_AT will define the order in which the data will be sorted.

To achieve this kind of sorting, PostgreSQL relies heavily on the operating system. The trouble is that if the sort order of characters changes in the operating system for some reason (maybe because of a bug or because of some other reason), PostgreSQL will have troubles with its indexes. A normal b-tree index is basically a sorted list, and if the sort order changes, naturally, there is a problem.

The introduction of the ICU library is supposed to fix this problem. ICU offers stronger promises than the operating system and is, therefore, more suitable for long-term storage of data. With the introduction of PostgreSQL 10.0, ICU encodings can be enabled.

In PostgreSQL 10.0, a lot of functionalities have been added that allow people to run even more professional applications even faster and more efficiently. All areas of the database server have been improved and many new professional features have been added. In the future, even more improvements will be made. Of course, the changes listed in this chapter are by far not complete because many small changes were made.