PostgreSQL 10.0 appeals to the SQL faithful

The next big version of the open source database has relatively few features aimed at the NoSQL market and instead focuses on major SQL-centric changes

PostgreSQL 10.0 appeals to the SQL faithful

The next major release of the PostgreSQL open source database system, version 10, aims to satisfy the SQL faithful.

Over the last few years, conventional databases have rolled in more features inspired by or borrowed outright from NoSQL. PostgreSQL added such features in previous versions, but version 10 contains several major SQL-centric changes that lay the foundation for other, long-desired improvements.

Little changes, big steps

A blog post by Robert Haas, vice president and chief architect at EnterpriseDB, a commercial support and services company for PostgreSQL, itemizes the big changes. Standouts from that list include the following:

  • A faster query executor. In PostgreSQL, the executor performs the actual retrieval of rows for a given query. "Substantial parts" of the executor have been rewritten in version 10 to speed things up. This is worth mentioning first, since it's one of the changes likely to affect nearly every PostgreSQL user.
  • Better parallel queries. Parallelism is a must for modern data applications (and modern applications, period -- especially data apps). PostgreSQL already had the ability to perform many operations in parallel, but version 10 adds parallelism to many under-the-hood operations, like scanning indices for tables, so more and different kinds of queries can reap speed boosts.
  • Logical replication. Existing versions of PostgreSQL let you replicate the entire database or perform change capture, but among its many limitations was that it only worked on the database level. The new logical replication feature works on the table level and requires little work to set up.
  • Extended statistics. PostgreSQL can now gather a broader range of stats for queries on demand and, thus, potentially can generate more accurate plans for queries.

Much of what's offered in version 10 is useful on its own, but it's also a step toward more all-encompassing changes that have been a long time coming. With the rewritten query executor, for instance, there are plans to add just-in-time compilation to further speed up proceedings.

Another version 10 addition, transition tables, will make it possible to create what are called "automatically updated materialized views," a query that can automatically generate a view based on changes made to a given table.

More to come while you wait

Also notable about PostgreSQL 10 is how relatively few of the additions are aimed at the NoSQL market. The only significant exception: XMLTable, which enables users to query XML-formatted data as if it were a native table source and at high speed. But the vast majority of version 10's new features are aimed at improving life for existing PostgreSQL users.

PostgreSQL faces pressure, not merely from NoSQL-style storage systems but from cloud database-as-a-service systems and cloud data warehouses. The largest selling points of those services are not a more powerful feature set or even the convenience of being offered natively in the cloud, but that those services are intended to be zero-maintenance and strive for zero downtime as well.

PostgreSQL has a deserved reputation as highly polished and reliable, if somewhat conservative with its feature set. A downside of that approach is potentially powerful features take longer to introduce into PostgreSQL's codebase because the development team insists on getting it right the first time.

One feature not included because of such strictures is columnar storage, a way to offer features found in analytics databases such as Teradata or Vertica. MySQL spinoff MariaDB offers that feature, and while third-party add-ons for PostgreSQL support it, having native support for columnar storage would be welcomed by PostgreSQL's professional user base. But Haas has noted that adding support for columnar storage wouldn't work well without first making other, major changes under the hood.