A beta for PostgreSQL 10

PostgreSQL version 10 had its first beta release on May 18, just in time for the annual PGCon developer conference. The latest annual release comes with a host of major features, including new versions of replication and partitioning, and enhanced parallel query. Version 10 includes 451 commits, nearly half a million lines of code and documentation, and over 150 new or changed features since version 9.6. The PostgreSQL community will find a lot to get excited about in this release, as the project has delivered a long list of enhancements to existing functionality. There's also a few features aimed at fulfilling new use cases, particularly in the "big data" industry sector.

Built-in logical replication

The built-in, single-master replication that has shipped with PostgreSQL since version 9.0 is known as "binary replication." This means that it replicates 8KB data pages, not logical database objects like tables or rows. This has a number of advantages, the biggest being easy administration. However, it has a few major disadvantages: you can never replicate less than your entire server instance, no writes of any kind are permitted on the replica, and you can't replicate between different PostgreSQL versions.

In contrast, logical replication does allow replicating individual tables, or between versions, because it replicates tables and rows. For several years, there have been a number of third-party projects for logical replication, including Slony-I, Londiste, and Bucardo. These systems are more involved to administer and have lower performance than many users need, though, so several developers have been working on a built-in logical replication system. This work has been led by developers working for the European consulting company 2nd Quadrant.

The new replication is designed to operate concurrently with, and be as similar as possible to, the existing binary replication. As such, it also uses a transaction log stream as the data transport, and requires features like replication slots that are already familiar to PostgreSQL database administrators. The primary new concept is the "pub/sub" (publication and subscription) model for tables or groups of tables. Each node in a cluster can be a publisher or a subscriber for each specific set of tables.

Suppose I decide I want to replicate just the fines and loans tables from my public library database to the billing system so that it can process amounts owed. I would create a publication from those two tables with one command. This command includes the word "ONLY" because we only want to replicate the tables named, not other tables linked to them:

    libdata=# CREATE PUBLICATION financials FOR TABLE ONLY loans, ONLY fines;
    CREATE PUBLICATION

Then, in the billing database, I would create two tables that looked identical to the tables I'm replicating, and have the same names. They can have additional columns and a few other differences. Particularly, since I'm not copying the patrons or books tables, I'll want to drop some foreign keys that the origin database has. I also need to create any special data types or other database artifacts required for those tables. Often the easiest way to do this is selective use of the pg_dump and pg_restore backup utilities:

    origin# pg_dump libdata -Fc -f /netshare/libdata.dump

    replica# pg_restore -d libdata -s -t loans -t fines /netshare/libdata.dump

Following that, I can start a subscription to those two tables:

    libdata=# CREATE SUBSCRIPTION financials
                  CONNECTION 'dbname=libdata user=postgres host=origin.example.com'
                  PUBLICATION financials;
    NOTICE:  synchronized table states
    NOTICE:  created replication slot "financials" on publisher
    CREATE SUBSCRIPTION

This will first copy a snapshot of the data currently in the tables, and then start catching up from the transaction log. Once it's caught up, you can check status in pg_stat_subscription:

    libdata=# select * from pg_stat_subscription;
    -[ RECORD 1 ]---------+---------------------
    subid                 | 16475
    subname               | financials
    pid                   | 167
    relid                 |
    received_lsn          | 0/1FBEAF0
    last_msg_send_time    | 2017-06-07 00:59:44
    last_msg_receipt_time | 2017-06-07 00:59:44
    latest_end_lsn        | 0/1FBEAF0
    latest_end_time       | 2017-06-07 00:59:44

Possibly the biggest benefit to this new logical replication is that it will make "upgrade by replication" feasible for a lot more users. Databases that aren't permitted to be down will be able to upgrade from PostgreSQL 10 to 11 using logical replication to a new cluster and failing over.

The other major replication feature involves PostgreSQL's synchronous replication. It now supports quorum commit, allowing administrators to define a specific number of nodes in a pool of replicas that must receive a transaction for it to be complete. This will allow PostgreSQL to be used in a similar way to several scalable, consensus-based database systems.

Native partitioning

The second aspect of PostgreSQL to get a major ease-of-use upgrade is table partitioning. Partitioning is used to split up a huge table into multiple sub-tables, improving maintenance, backup, and the performance of some queries. While PostgreSQL has had partitioning since version 8.0, the existing implementation has been difficult for administrators, requiring multiple steps and a lot of unintuitive SQL syntax, or installing PostgreSQL extensions.

The new partitioning feature uses a simple declarative syntax, as well as enforcing some sensible defaults. It currently supports both "list" and "range" partitioning. List partitioning is where each item in a list of potential values (such as weekdays or Australian territories) receives a partition and data related to that value goes in that partition. Range partitioning is when each partition holds a defined upper and lower limit, as one would use for dates or numeric values. For example, we might decide to partition the book_history table; that's probably a good idea since that table is liable to accumulate data forever. Since the table is essentially a log file, we'll range partition it, with one partition per month.

First, we create a "master" partition table, which will hold no data but forms a template for the rest of the partitions:

    libdata=# CREATE TABLE book_history (
                  book_id INTEGER NOT NULL,
                  status BOOK_STATUS NOT NULL,
                  period TSTZRANGE NOT NULL )
              PARTITION BY RANGE ( lower (period) );

Then we create several partitions, one per month:

    libdata=# CREATE TABLE book_history_2016_09
              PARTITION OF book_history
              FOR VALUES FROM ('2016-09-01 00:00:00') TO
	                      ('2016-10-01 00:00:00');
    CREATE TABLE
    libdata=# CREATE TABLE book_history_2016_08
              PARTITION OF book_history
              FOR VALUES FROM ('2016-08-01 00:00:00') TO
	                      ('2016-09-01 00:00:00');
    CREATE TABLE
    libdata=# CREATE TABLE book_history_2016_07
              PARTITION OF book_history
              FOR VALUES FROM ('2016-07-01 00:00:00') TO
	                      ('2016-09-01 00:00:00');
    ERROR:  partition "book_history_2016_07" would overlap \
    partition "book_history_2016_08"

As you can see, the system even prevents accidental overlap. New rows will automatically be stored in the correct partition, and SELECT queries will search the appropriate partitions. If we decide to sunset the log after 12 months, we can delete old data by dropping partitions:

    libdata=# DROP TABLE book_history_2016_05;

Since dropping partitions is just a file unlink operation, it is orders of magnitude faster than deleting thousands or millions of rows.

There's still some work to be done on the new partitioning. Contributor Yugo Nagata is already hard at work on a hash partitioning option. Others want to add automatic creation of partition constraints and keys. Eventually, PostgreSQL will also support automated creation of new partitions, and performance improvements in searching partitions. Regardless, the new version of partitioning will be accessible to many users who avoided the complexity of the prior implementation.

For upgrade compatibility, the old form of partitioning will still work in PostgreSQL for the foreseeable future.

More parallel query operations

Parallel query was introduced as a PostgreSQL feature in version 9.6. It allows a single query to make use of multiple processes and cores in order to speed up execution. Implementing parallelism has been a matter of parallelizing one query operation at a time across successive PostgreSQL releases. In this beta, the most common operations in read queries can all be distributed across multiple cores.

In version 9.6, full table scans, aggregates, nested loop joins, and hash joins could be executed in parallel. Version 10 has added three new parallel operations: btree index scans, bitmap scans, and merge joins. With the addition of these query operation types, most read queries can be executed in parallel. This means that users who generally have fewer database connections than cores (something that is common in analytics databases) can count on speeding up much of their database workload through parallelism.

For example, if we wanted to search financial transaction history by an indexed column, it can now be executed in one-quarter the time by using four parallel workers:

    accounts=# \timing
    Timing is on.
    accounts=# select bid, count(*) from account_history
      where delta > 1000 group by bid;
    ...
    Time: 324.903 ms

    accounts=# set max_parallel_workers_per_gather=4;
    SET
    Time: 0.822 ms
    accounts=# select bid, count(*) from account_history
    where delta > 1000 group by bid;
    ...
    Time: 72.864 ms

The project has added a few new configuration options for resource controls over the number of workers that PostgreSQL uses for various things, including parallel query.

Future work on parallel query is likely to include parallel bulk loading (in a Google Summer of Code project) and parallel utility commands, such as building indexes. The project will also work on parallel scan for other types of indexes, such as geographic GiST indexes and full-text GIN indexes. Parallel execution of write queries has also been discussed, but there are some fundamental technical hurdles to making it work.

JSON full-text search

PostgreSQL is known both for having powerful JSON features for a relational database and very good full-text search for a general-purpose database. In version 10, Dmitry Dolgov decided to combine both of these features in order to make JSON fields fully searchable, both as JSON and as human-readable text. Combined with other features and extensions, this makes PostgreSQL a superior option to dedicated document databases for some JSON use cases.

The new feature works with both text JSON and binary JSONB types. You can index your JSONB field using a full-text index. This involves converting the JSONB field to a tsvector, then creating an specific language full-text index on it:

    libdata=# CREATE INDEX bookdata_fts ON bookdata
                  USING gin (( to_tsvector('english',bookdata) ));
    CREATE INDEX

Once that's set up, you can do full-text searching against all of the values in your JSON documents:

    libdata=# SELECT bookdata -> 'title'
		  FROM bookdata
		  WHERE to_tsvector('english',bookdata) @@
			to_tsquery('duke');  
    --------------------------------------------------------
     "The Tattooed Duke"
     "She Tempts the Duke"
     "The Duke Is Mine"
     "What I Did For a Duke"

Combined with the JsQuery extension, this provides PostgreSQL with a full set of JSON search tools that rival dedicated JSON databases. Its community expects more JSON applications to switch to PostgreSQL, or to combine PostgreSQL with non-relational databases like MongoDB and Couchbase in hybrid infrastructure.

Other features

PostgreSQL 10 includes new and improved security features, chief among them support for SCRAM authentication. This provides a much more secure password authentication method than the prior MD5 hashing used by the libpq library. Also, contributor Stephen Frost has added restrictive row-level security policies, enabling tighter permissions management of high-security data. Previously, row-level security permissions were always permissive, which means that users could grant permissions on roles but not revoke them. Now, both actions are possible.

There are many other interesting features among the 150 added in this release. XMLTABLE permits manipulating XML data in the database like it was a SQL table. Multi-column correlation statistics introduces a first-in-the-industry method of dealing with a chronic database performance issue: estimating selectivity for conditions on multiple columns. Oracle database administrators will be happy to now find "latch wait time" statistics available in PostgreSQL monitoring. PostgreSQL has also added support for the global standard ICU library for language collations.

With all these major features, though, there's a larger-than-normal number of backward-incompatible changes in this release. The first such change is the version number, which now has just two components instead of three. There are also a number of changes to the client library that could break some drivers. Version 10 also drops support for some antiquated data types: floating-point timestamps and the "tsearch2" full-text search indexes.

Possibly the most disruptive change for database administrators is the global renaming of everything that was called "xlog" to "wal", including directories, filenames, and administrative functions. The two abbreviations — the former standing for "transaction log", and the latter for "write-ahead log" — have been used as somewhat confusing synonyms for some time. The developers made this change in order to deter the common data loss scenario when a user confuses the disposable activity logs with the essential transaction logs.

Final release, version 11, and more

Users can expect to see at least two more beta releases, and possibly more, before a final release toward the end of the year. The target release date is early September, but release dates have slipped several times over the last five years. There are quite a few open issues against the current beta, including some pieces of user-visible behavior that may change before release. Mostly, the developers want driver authors to get working on some of the new client features now, particularly supporting SCRAM authentication.

During beta testing, work on PostgreSQL 11 (the next version according to the new numbering scheme) has already started. Features that already have patches include Write Amplification Reduction Method (WARM), designed to combat some of the I/O issues that Uber complained about, Auto Prewarm to speed up database server restarts, cascading logical replication, automatic per-statement savepoints, and support for the recent SQL/JSON standard. More speculatively, there's some hope that some types of multi-master replication will be integrated with the mainline in that release.

Regardless of what's in future versions, PostgreSQL 10 looks like a landmark release for the project. Users will want to test it now because they will likely want to upgrade soon after the 10.0 release.

[Josh Berkus is a contributor to PostgreSQL. He works for Red Hat.]