Best of PostgreSQL 9.6 for the Developer

Last week a new PostgreSQL major version with the number 9.6 was released and I already covered my favorite parts from the official release notes from DBA’s point of view in a blogpost here. Now I would like to look at the same list of changes from a different angle, transforming into a database developer in my mind 🙂 Some features of shared interest are of course re-listed, but mostly new stuff, so put on your dev-hat and enjoy.

My favourites from PostgreSQL 9.6 from a Dev’s point of view

• Parallel execution of sequential scans, joins and aggregates.

Needs enabling via “max_parallel_workers_per_gather” parameter, which can luckily be done on the user level, thus making per-query parallelization possible. But one thing to note here is that the total number worker processes is limited with “max_worker_processes” parameter so this might need increasing (default is 8) on good hardware when doing parallel queries from lots of concurrent sessions.

• postgres_fdw now supports remote joins, sorts, UPDATEs, and DELETEs

Transparent sharding ahoy! That was already possible with 9.5 (foreign table inheritance + exclusion constraints) but now things also perform well for the non-simplistic usecases. Great stuff.

• Allow extension-provided operators and functions to be sent for remote execution, if the extension is whitelisted in the foreign server's options

Allows efficient execution of remote queries involving extension operators. Again, great for sharding scenarios.

• Synchronous replication now allows multiple standby servers for increased reliability.

Together with the new "remote_apply" setting for the "synchronous_commit" parameter user have power to create “mirrored” multi-machine clusters. Awesome feature!

• A new index type called “Bloom filter”

A pilot feature for generic index access methods, this new index type enables Bloom filters in Postgres. Basically Bloom index can definitely tell you if your entry “is not” there, with “is there” being lossy and giving false-positives. More info on the algorithm here. Support only equality queries and “int” and “text” datatypes but is a lot more efficient than B-tree for multi-column query conditions. Contrib module.

• Phrase search for full-text search

Meaning order of your “search words” is respected.

• Substring similarity search for  pg_trgm

Previously only whole words could be fuzzy-compared with pg_trgm, now also inputs with best matching fractions can be determined with “word_similarity” function. Contrib module.

• Add jsonb_insert() function to insert a new element into a jsonb array, or a not-previously-existing key into a jsonb object
• Allow “pythonic” array slice specifiers, e.g. array_col[3:]
• postgres_fdw - support for declaring extensions supported by the foreign server so that for example pg_trgm and btree_gin/gist operations would be performed remotely. Huge performance wins possible here
• ALTER TABLE ADD COLUMN IF NOT EXISTS  - less errors in migration scripts 🙂
• New built-in role called “pg_signal_backend” to enable this previously superuser-only functionality.

This would enable canceling running queries and terminating sessions of other database users. More built-in roles to be expected in future.

• Add pg_size_bytes() function to convert human-readable size strings to numbers
• "psql" supports multiple -c and -f command-line options.

The specified operations are carried out in the order in which the options are given, and then psql terminates.

• “psql” - add errverbose command that shows the last server error at full verbosity

This is useful after getting an unexpected error

• "pgbench" - allow weighted script selection when running a test
• "pgbench" - collect statistics for each script in a multi-script run

With those two pgbench changes one can now build and test "close to real life" testcases faster and easier.