Welcome to End Point’s blog

Ongoing observations by End Point people

New Features in PostgreSQL 9.5

The new PostgreSQL 9.5 release has a bunch of great features. I describe below the ones I find most interesting.


UPSERT is simply a combination of INSERT and UPDATE. This works like this: if a row exists, then update it, if it doesn't exist, create it.

Before Postgres 9.5 when I wanted to insert or update a row, I had to write this:

INSERT INTO test(username, login)
SELECT 'hey', 'ho ho ho'
WHERE NOT EXISTS (SELECT 42 FROM test WHERE username='hey');

UPDATE test SET login='ho ho ho' WHERE username='hey' AND login <> 'ho ho ho';

Which was a little bit problematic. You need to make two queries, and both can have quite complicated WHERE clauses.

In PostgreSQL 9.5 there is much simpler version:

INSERT INTO test(username, login) VALUES ('hey', 'ho ho ho')
ON CONFLICT (username)
DO UPDATE SET login='ho ho ho';

The only requirement is that there should be a UNIQUE constraint on a column which should fail while inserting a row.

The version above makes the UPDATE when the INSERT fails. There is also another form of the UPSERT query, which I used in this blog post. You can just ignore the INSERT failure:

INSERT INTO test(username, login) VALUES ('hey', 'ho ho ho')
ON CONFLICT (username)

Switching Tables to Logged and Unlogged

PostgreSQL keeps a transaction write ahead log, which helps restore the database after a crash, and is used in replication, but it comes with some overhead, as additional information must be stored on disk.

In PostgreSQL 9.5 you can simply switch a table from logged to unlogged. The unlogged version can be much faster when filling it with data, processing it etc. However at the end of such operations it might be good to make it a normal logged table. Now it is simple:


JSONB Operators and Functions

This is the binary JSON type, and these new functions allow us to perform more operations without having to convert our data first to the slower, non-binary JSON alternative.

Now you can remove a key from a JSONB value:

SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb || '{"x": 1, "y": 2, "c": 42}'::jsonb;

 {"b": 2, "c": 3}

And merge JSONB values (the last value's keys overwrite the first's one):

SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb || '{"x": 1, "y": 2, "c": 42}'::jsonb;

 {"a": 1, "b": 2, "c": 42, "x": 1, "y": 2}

And we have the nice jsonb_pretty() function which instead of this:

SELECT jsonb_set('{"name": "James", "contact": {"phone": "01234 567890",
                   "fax": "01987 543210"}}'::jsonb,
                   '{contact,phone}', '"07900 112233"'::jsonb);

 {"name": "James", "contact": {"fax": "01987 543210", "phone": "07900 112233"}}

prints this:

SELECT jsonb_pretty(jsonb_set('{"name": "James", "contact": {"phone": "01234 567890",
                   "fax": "01987 543210"}}'::jsonb,
                   '{contact,phone}', '"07900 112233"'::jsonb));

  {                              ↵
      "name": "James",           ↵
      "contact": {               ↵
          "fax": "01987 543210", ↵
          "phone": "07900 112233"↵
      }                          ↵

More Information

There are more nice features in the new PostgreSQL 9.5. You can read the full list at'snewinPostgreSQL9.5


Ravi Krishna said...

WOuldn't UNLOGGED table affect replication since replication comes out of logs only.

David Christensen said...


You are correct, that UNLOGGED tables are not replayed on the replica (since the WAL representing any data changes is omitted, the definition of the UNLOGGED TABLE). This is the design tradeoff you make when choosing to make some tables UNLOGGED; you have to be willing to forgo any data that lives in it in either replication or if the server were to crash.



Ravi Krishna said...

I am curious to know what happens if a table was initially a logged one and then altered to a nonlogged one. what will happen to that table when the slave gets promoted as primary after some time.

Daniel van der Meulen said...

Your first and last jsonb examples are not correct.

David Christensen said...

Hi Ravi,

When you change a table from LOGGED to UNLOGGED, it will give you an error message when you try to access it on the active standby:

ERROR: cannot access temporary or unlogged relations during recovery

When you promote that standby to master, it should show up as an empty table.