Best of PostgreSQL 9.6 for the Developer

10.2016 / Category: / Tags:

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.

0 0 votes
Article Rating
Notify of
Newest Most Voted
Inline Feedbacks
View all comments
Alessandro Costa
Alessandro Costa
7 years ago

For development Postgres sucks! It has been a hell! For example it's not possible to move a field in a table! Gurus told me... you stupid! make a view on top of the table! Nice... But for any little modification to the table I have to drop and recreate the views! Now I switched to MySql and it works like a charm!

7 years ago

Well, I can agree so far that there's no perfect system, but many on the other hand would say that Postgres is the best for developers as it's the most extensible - custom PL languages, extensions, operators, types. But in this concrete column re-ordering case - it's a pretty difficult feature, there's no direct support for that wether in Oracle nor MSSQL and most people would tell you that physical column ordering should not matter for the application anyways as one can re-order columns in a SELECT statement:)

Hans-Jürgen Schönig
Hans-Jürgen Schönig
7 years ago

well, the point is: if you want columns to be in a certain order ... list them in the SELECT-clause in the order you need. doing SELECT * is usually a bad idea because the next DDL coming around the corner will kill you.

Derek Bartron
Derek Bartron
7 years ago

I only use Postgres in development. And I love it. Especially with the JSONB type they added. And I agree, Postgres doesn't suck just because your programming is lazy.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram