Thursday, December 18, 2014

PostgreSQL 9.4 and beyond

PostgreSQL 9.4 is released today, and once again my main input has been in the JSON area. We've introduced a new spiffy JSON type called "jsonb" that makes performing operations on JSON much, much faster, because it's not stored as plain text, but rather decomposed into the structures and scalar values that go to make up JSON. Much of the credit for this goes to Oleg Bartunov and Teodor Sigaev, the original authors of the hstore module, who created a "nested hstore" module that we eventually based jsonb on. Credit also goes to Alexander Korotkov and Peter Geoghegan, especially for their indexing work, which will make many operations super fast. My role was to make sure that all (or almost all) of the operations that are available for the original json type are also available for the jsonb type.

There have been some benchmark tests that suggest that our jsonb is faster in many respects that other database engines that use json as their native storage.

The nice thing about this is that you can now have a highly performing mixture of traditional relational data and semi-structured treeish data with proper transactional semantics, all in one engine. That's a very attractive proposition for a lot of people looking to design new data stores.

My other JSON contribution was to add a number of functions that allow the construction of arbitrarily complex json documents, with data from the database. This will be particularly useful for clients that expect json in a particular, non-regular shape, and is based on the needs of some actual json-using customers.

Already committed for 9.5, to be released some time next year, are jsonb versions of those json functions we didn't provide jsonb versions of in 9.4, and functions to allow the stripping of object fields with 'null' as the value from json and jsonb values (this can result in much more compact storage in some cases, with no significant semantic loss).

I made a few other contributions to 9.4, but they are not worth mentioning in detail.

I'm now looking for new PostgreSQL projects, not involving JSON. I'm not sure what those should be. For the most part my contributions over the last 12 years have tended not to be in  the area that involves the planning and execution of queries, so maybe it's time for me to dive into that pool. But to do what I don't yet know.


  1. >I'm now looking for new PostgreSQL projects

    What about implementing the support of SQL ASSERTION's in PostgreSQL. It would really raise PostgreSQL above the other SQL DBMS's.

  2. SQL Assertions have been discussed in the past. I'm not sure how possible they are.

  3. Thanks for all your work; we rely heavily on the JSON functionality in Postgres, and I can't wait to start using jsonb.

  4. >SQL Assertions have been discussed in the past.
    I imagine that the main counterargument is performance. But first, lest acknowledge that there are different kinds of systems with different requirements. Not all have billions of rows, thousands of parallel users and tens of distributed computers.

    There are prototype relational (not SQL) systems, created by only single developer that have implemented assertion-like general constraints. Therefore I assume it is technically possible to achieve. It seems to be (sadly) that it just has not been the focus of the DBMS-development community.

    In my opinion it would be a great productivity boost if instead of writing A4 page-long procedural code, one could achieve the same result by writing only couple of lines. Exclusion constraints are a good example in this regard. Not all systems require/want this but there are many systems that would benefit a lot.

  5. SQL Assertions might be nice, but they aren't something I really want to work on, TBH.