PostgreSQL 14 New Features – Cool little things

I will be honest with this – I wasn’t sure about doing 100% justice to the 9-days & 9-part blog series to commemorate one of the biggest Festivals in India – Navratri

Well, today, it’s a Wrap-up of the PostgreSQL 14 New Features 9-part blog series and we are elated to have had the opportunity to do this. A Big Shout to the PostgreSQL Global Development Group for PG14.

We have had multiple topics covered in this blog series, around:

• Performance
• New configuration params
• Security
• DBA & Developer features

Today, let’s take a look at some of the Cool Little things introduced as part of PostgreSQL 14.

psql

I have been a big fan of psql – the CLI utility that comes installed with PostgreSQL. It is simple, feature-rich, and just works! Call me a nuts-n-bots guy, heck yeah!

\dt – Lists the TOAST tables and their Indexes

postgres=# CREATE TABLE messages (id serial PRIMARY KEY, msg_text text);
CREATE TABLE
postgres=# SELECT reltoastrelid::regclass FROM pg_class WHERE relname = 'messages';
      reltoastrelid      
-------------------------
 pg_toast.pg_toast_24735
(1 row)

postgres=# \dt+ pg_toast.pg_toast_24735
                                            List of relations
  Schema  |      Name      |    Type     |  Owner   | Persistence | Access method |  Size   | Description 
----------+----------------+-------------+----------+-------------+---------------+---------+-------------
 pg_toast | pg_toast_24735 | TOAST table | postgres | permanent   | heap          | 0 bytes | 
(1 row)

\dX A new shorts to list Extended statistics. Till Postgres 13, you would have polled pg_stats_ext for Extended statistics.

CREATE TABLE t1 (a int, b int);
INSERT INTO t1 SELECT i/100, i/500 FROM generate_series(1,1000000) s(i);
CREATE STATISTICS s1 (dependencies) ON a, b FROM t1;
\dX
                  List of extended statistics
 Schema | Name |  Definition  | Ndistinct | Dependencies | MCV 
--------+------+--------------+-----------+--------------+-----
 public | s1   | a, b FROM t1 |           | defined      | 
(1 row)

There are a few additional improvements to TAB completion in psql, which is always handy when navigating in a CLI.

FDW Functions

postgres_fdw has two new functions:
postgres_fdw_disconnect() – discards the cached connections to the specified foreign server
postgres_fdw_disconnect_all() – discards all the cached connections.

Note: Connections used in the current transaction are not closed and a warning message is emitted.

TRUNCATE command accepts foreign tables as the target to truncate. Using postgres_fdw, you can issue TRUNCATE command to foreign servers.

Data Loading

Regular expressions can now use \D, \S, and \W inside of a bracketed character class, e.g. [\Da-fA-F]* will match characters that are not hexadecimal digits.

Also, invent “word” as an allowed character class name, thus “\w” is now equivalent to “[[:word:]]” outside brackets, or “[:word:]” within brackets.

The numeric data type can now store the values infinity and -infinity for consistency with floating-point data types. Add infinities that behave the same as they do in the floating-point data types. Aside from any intrinsic usefulness, these may have, this closes an important gap in our ability to convert floating values to numeric and/or replace float-based APIs with numeric.

Faster COPY FROM encoding conversion/verification in larger chunks. This gives a small performance gain, by reducing the number of calls to the conversion/verification function, and letting it work with larger inputs.

JSON

In my earlier blog on Developer features in PostgreSQL 14, I have mentioned quite a few interesting features of JSON. and how it lets you access JSON data using subscripts.

Another feature in PG14 is the new JSONB syntax, which makes it easy to update deep JSON values –

UPDATE table SET some_jsonb_column['person']['bio']['age'] = '99';

JSONB capabilities in Postgres are amazing, but the syntax can be slightly tricky – for example, I’m forever mixing up -> and ->>. This new syntax feels far more intuitive.

Troubleshooting tweaks

Reduced need for AS. Queries like SELECT 1 SELECT now work! Before, you had to say SELECT 1 AS SELECT. There are still 39 special keywords where you have to say AS if you want to use them as a column label, but PostgreSQL 13 required AS for all 451 keywords.

Indexes now track the associated collation version when they are created or rebuilt, and warn when used if the version doesn’t match.

Well, that’s a WRAP!

Our Gaja Wishing you all a Happy Dussehra and Vijayadashami

Thanks for all your time and support, Postgres Community.