The 10th version of the popular RDBMS PostgresSQL was released a few weeks ago. 21 years old, Postgres is popular among developers for its reliability, cost savings, maturity and of course being open source.
Postgres 10 brings several new features among which some are mostly exciting for developers. 10Clouds explained in details some of these features in a blog post. Native partitioning has now become simpler in Postgres 10 by eliminating the need for triggers in the master table. This means that creating partitioned tables is simpler and also that querying and inserting to these tables is now identical to a non-partitioned table from a developer’s point of view.
Multicolumn statistics is another area of improvement for Postgres 10. By correlating data from different columns, the query planner can now avoid some edge cases where it would believe that the where clause is more selective than it really is and slow down execution time by picking the wrong plan. This improvement in Postgres’s view is innovative in the SQL space.
Parallelism has improved with Postgres 10 as well. A developer can now use index and index-only scans, parallel merge joins and bitmap heap scans. Parallel queries are implemented via different workers so in some cases setup and teardown cost can outweigh parallelisation’s benefits. By default, parallel table scan can be enabled on tables greater than 8MB in size and indexes greater than 512KB using at most 8 workers, but these options can be configured as needed.
Another important new feature in Postgres 10 is full text search in JSON and JSONB type columns. Once we create a language specific full text index we can search directly for the values on JSON fields. Full text indexes on JSON columns are similar to any other column type so our queries will need to use the text search syntax with to_tsquery and to_tsvector functions.
Identity columns or auto-increment columns is also another area of improvement. The new implementation is a bit more verbose but on the other hand conforms to the SQL standard making migrations between different databases easier. On top of that, using Postgres 10 we no longer need to alter sequences to restart with from a different id, but we can alter the column and Postgres will identify that this column is a sequence, simplifying day-to-day operations in our database.
As expected, there are some backwards incompatible changes. Support for floating point timestamps has been dropped, pg_dump for versions less than 8 has been dropped and some defaults around replication and pg_basebackup tool have also been changed. Version numbering has changed from three-part to two-part, which developers' scripts should not be using anyway directly, but instead use something like the server_version_num that returns consistently sortable and comparable version numbers.