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.
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.
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.
Allows efficient execution of remote queries involving extension operators. Again, great for sharding scenarios.
Together with the new "remote_apply" setting for the "synchronous_commit" parameter user have power to create “mirrored” multi-machine clusters. Awesome feature!
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.
Meaning order of your “search words” is respected.
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.
This would enable canceling running queries and terminating sessions of other database users. More built-in roles to be expected in future.
The specified operations are carried out in the order in which the options are given, and then psql terminates.
This is useful after getting an unexpected error
With those two pgbench changes one can now build and test "close to real life" testcases faster and easier.
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
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!
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:)
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.
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.