Postgres 14: It's The Little Things

A lot of years Postgres will have some big pillar or theme to the release. Often this is thought of after the fact. Everything that is committed is looked at and someone thinks, "This is the key thing to talk about." In Postgres 9.2 it was JSON, in 9.4 it was JSONB, in 10 it was logical replication, 12 was a broader performance theme. While I look forward to each of these big highlights, in each release I'm equally excited to browse through and pull out the small things that simply make my life better.

Postgres is, and for some time will continue to be, the first database I turn to. As Postgres focuses on the little things, it just deepens my commitment to it. Why look elsewhere when the bond just grows over time? So today I wanted to call some extra attention to those little things, the ones that don't get the spotlight, but simply make a developer's life better.

JSON syntax

Alright, I know I talked about this one before and it got more attention than I expected. JSON in Postgres is great, we all know that (you usually want to use JSONB). And now it is much more user friendly to query. Where previously you had to do something like:

SELECT *
FROM shirts
WHERE details->'attributes'->>'color' = 'neon yellow'
  AND details->'attributes'->>'size' = 'medium';

You can now use JSON subscripting:

SELECT *
FROM shirts
WHERE details['attributes']['color'] = '"neon yellow"'
  AND details['attributes']['size'] = '"medium"'

If you're curious on the details, read more on my earlier post.

Read only roles!

This one is small, but oh so useful. Have you ever wanted to give read-only access to your database? Did you find yourself mucking around with several different grants? And in the end were still unsure if 1. They could actually read what you intended, but also 2. The couldn't actually write/delete data.

With Postgres 14 there is 1 simple grant you can run:

GRANT pg_read_all_data TO janedoe;

There is also another new role you can use to match the database owner pg_database_owner. It's great seeing some of these things that required a deeper knowledge of Postgres catalog and roles getting more simple to simply work.

Psql

I've talked a lot over the years about how I'm a fan of psql - the CLI client that comes with Postgres. It's quite feature rich, more so than most CLI tools I know. It has the ability to customize it via a psqlrc, a lot of handy shortcuts, ability to format the output of a query with \x and \x auto to auto format it, and features like \watch to auto re-run a query every few seconds.

When I started going through the psql improvements in Postgres 14 it was great to see not just 1 new feature but a slew of them.

First, there's a nice improvement contributed by two of my colleagues to the shortcut \df to allow you to see function and operator arguments. This helps reduce the number of matches for overloaded entries.

Second, \dX will now give you more insights around extended statistics.

There are some nice improvements to tab completion as well, which is always handy when navigating in a CLI.

There is even more, and if you're a psql user I'd recommend you give the full list a read.

Query pipelining

If you use Postgres in most languages it's likely that the underlying driver is actually the C library libpq. Libpq is the library taking care of a lot of the heavy lifting of communicating and speaking the PostgreSQL protocol. It already has some support for a lot of the things other languages just inherit like parsing a URI connection string that is formatted: postgres://username:password@hostname:port. Or supporting multiple hosts, so that your client when it detects a failure can automatically try the secondary.

And now in PostgreSQL 14 there is this seemingly small update, pipeline mode, which, according to the docs, allows applications to send a query without having to read the result of the previously sent query.

Taking advantage of the pipeline mode, a client will wait less for the server, since multiple queries/results can be sent/received in a single network transaction. In a world of cloud systems and ORMs, this is actually a huge improvement as workloads tend to be broken up into a lot of little things being sent to the database.

This one I have to give a shoutout to my colleague jkatz who caught this one... in checking out all the great features we both missed this the first time through. Then as he dug in, it's really a great hidden gem.

No longer do you have a very tightly integrated vertical stack with complex stored procedures. Apps are very different these days, and each application framework that jumps on the bandwagon to support this will receive huge wins!

It just keeps getting better

Yes, there are also performance improvements in PostgreSQL 14, there are some connection scalability improvements, and I'm sure there is a whole category of things I've missed. Regardless of what excites you about Postgres, it continues to deliver and improve in almost all areas. For me though, those little things hold a special place in my heart. Postgres is a reliable trusted database, that just keeps getting better, here's to another exciting release even if it's due to the small things.