Postgres v16: 14 Cool New Features

PostgreSQL v16 contains many new features and enhancements.

Here are PG v16's 14 best new features:

1. Everybody's favorite:

   You no longer need an alias for subqueries in FROM
   This in an extension to the standard, but makes porting queries from Oracle easier.

 

2. Improve performance of vacuum freezing

   Have you ever suffered from massive anti-wraparound autovacuum runs? If possible, v16 will freeze all rows in a block if it has to modify it. That will hopefully reduce the impact of anti-wraparound autovacuum.

 

3. New system view 'pg_stat_io'

   can tell you which tables cause I/O. This is particularly useful if track_io_timing = on

 

4. Make the CREATEROLE property of a user secure

   Previously, you could not grant that to users, because they could easily abuse it to become superusers. Now it is safe.This was considered a CVE security problem by some, but not by PostgreSQL developers.

 

5. First tentative support for direct I/O via the 'debug_io_direct' parameter

   This is not for production use at the moment, but it probably will be in the future!

 

6. Regular expression matching for database and role names in 'pg_hba.conf'

   Such patterns have to start with a slash (/). This adds flexibility.

 

7. Enhanced support for ICU collations

   There is now support for ICU rules to create your customized sort order. Move to ICU collations soon. They avoid some tricky problems, and they will become the default in future versions.

 

8. Streaming replication standby servers can now be logical replication publisher servers

   That allows you to build even more crazy replication architectures!

 

9. Logical replication now allows cycles with disabled statements

   This is a first step to multi-master replication, but there is no conflict resolution yet. Previously the cycles led to an 'echo chamber problem' where the logical replication kept sending the same insert/update/delete statement back and forth. The new change allows you to disable sending statements that came in via logical replication, so the cycle can be stopped and the echo chamber is no more.

 

10. In COPY FROM, you can specify a string that makes PostgreSQL insert the column default value

    Any occurrence of the string in the input data will insert a NULL value.

 

11. Specify integer constants in hex, octal or binary

    Examples are 0x42F, 0o273 and 0b100101. See our blog on this.

 

12. Allow the client library 'libpq' to specify acceptable authentication methods

    This prevents attacks by impersonating the server and requesting insecure authentication methods like 'password'.

 

13. Use the lz4 and zstd compression methods for 'pg_dump'

    They perform much better than the default compression method!

 

Now these are only thirteen features, where is the last one?

It was the following:

• New predefined role 'pg_maintain'

  allows non-superusers to run VACUUM, ANALYZE, CLUSTER, REFRESH MATERIALIZED VIEW, REINDEX, and LOCK TABLE on all tables.

 

Unfortunately, that feature got reverted because it had problems. This brings me to the following topic:

Beta Testing PostgreSQL v16

🔆 Beta Testing for PostgreSQL 16 is on! Wouldn't it be nice to benefit from all the performance improvements that the latest PostgreSQL version has to offer? You can have that and still be reasonably sure that you are not hit by a bug if you start testing your applications with PostgreSQL v16 Beta 2 now. An added plus: if you discover a problem with v16 now, you will have the undivided attention of the PostgreSQL developers! By the time v16 comes out in fall, you will be confident that it works well for you. (But this is NOT for production systems!)
 
To read more about how PostgreSQL has changed, check out my notes to Kaarel Moppel's blog about 7 things that could be improved in PostgreSQL
 

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.