Just a few weeks back I wrote a article discussing many of the things that were likely to miss making the 9.4 PostgreSQL release. Since that post a few weeks ago the landscape has already changed, and much more for the positive.

The lesson here, is never count Postgres out. As Bruce discussed in a recent interview, Postgres is slow and steady, but much like the turtle can win the race.

So onto the actual features:

JSONB

JSON has existed for a while in Postgres. Though the JSON that exists today simply validates that your text is valid JSON, then goes on to store it in a text field. This is fine, but not overly performant. If you do need some flexibility of your schema and performance without much effort then hstore may already work for you today, you can of course read more on this in an old post comparing hstore to json.

But let’s assume you do want JSON and a full document store, which is perfectly reasonable. Your option today is still best with the JSON datatype. And if you’re retrieving full documents this is fine, however if you’re searching/filtering on values within those documents then you need to take advantage of some functional indexing. You can do this some of the built-in operators or with full JS in Postgres. This is a little more work, but also very possible to get good performance.

Finally, onto the perfect world, where JSON isn’t just text in your database. For some time there’s been a discussion around hstore and its future progress and of course the future of JSON in Postgres. These two worlds have finally heavily converged for PostgreSQL 9.4 giving you the best of both worlds. With what was known as hstore2, by The Russians under the covers, and collective efforts on JSONB (Binary representation of JSON) which included all the JSON interfaces you’d expect. We now have full document storage and awesome performance with little effort.

Digging in a little further, why does it matter that its a binary representation? Well under the covers building on the hstore functionality brings along some of the awesome index types in Postgres. Namely GIN and possibly in the future GIST. These indexes will automatically index all keys and values within a document, meaning you don’t have to manually create individual functional indexes. Oh and they’re fast and often small on disk as well.

Logical Decoding

Logical replication was another feature that I talked about that was likely missing. Here there isn’t the same positive news as JSONB, as there’s not a 100% usable feature available. Yet there is a big silver lining in it. Committed just over a week ago was logical decoding. This means that we can decode the WAL (Write-Ahead-Log) into logical changes. In layman’s terms this means something thats unreadable to anything but Postgres (and version dependent in cases) can be intrepretted to a series of INSERTs, UPDATEs, DELETEs, etc. With logical commands you could then start to get closer to cross version upgrades and eventually multi-master.

With this commit it doesn’t mean all the pieces are there in the core of Postgres today. What it does mean is the part thats required of the Postgres core is done. The rest of this, which includes sending the logical replication stream somewhere, and then having something apply it can be developed fully as an extension.

In Conclusion

Postgres 9.4 isn’t 100% complete yet, as the commitfest is still going on. You can follow along on the postgres hackers mailing list or on the commitfest app where you can follow specific patches or even chip in on reviewing. And of course I’ll do my best to continue to highlight useful features here and surface them on Postgres Weekly as well.