PostgreSQL version 15 is on its way and we get a better and better look, what will be included in the future release. So it’s time to have a look at some new future features

1. Create privilege on public schema removed

Until today with PostgreSQL 14 it is possible for everybody to write into the public schema per default. With PostgreSQL 15 this won’t be possible anymore. The public schema is owned by the “pg_database_owner” now. Let’s do a short test.

postgres=# create user test;
CREATE ROLE
postgres=# du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test      |                                                            | {}

postgres=# c postgres test
You are now connected to database "postgres" as user "test".
postgres=> create table t1 (a int);
ERROR:  permission denied for schema public
LINE 1: create table t1 (a int);
                     ^
postgres=

If you want to be able again to write into the public schema, the permission has to be granted again explicitly.

2. Extended pg_basebackup compression

There are some extensions coming for pg_basebackup. Especially the compression improves. First of all –compress is now able to accept a compression method and an (optional) compression level using e.g. gzip:9. Futhermore –compress accepts client-gzip and server-gzip as compression method to define where to compress the backup. Another plus is that the compression can also be used with –format=p now. This gives you the opportunity to compress your pg_basebackup on server side and extract it again on client side automatically. Especially for slow network connections this can be a benefit. Let’s have a short look at the new –compress syntax.

[email protected]:/u99/backup2/ [PG15] pg_basebackup --help | grep -A 1 compress
  -z, --gzip             compress tar output
  -Z, --compress=[{client|server}-]METHOD[:DETAIL]
                         compress on client or server as specified
  -Z, --compress=none    do not compress tar output

To create a backup it looks like this now. Really simple and easy to use.

[email protected]:/u99/backup2/ [PG15] pg_basebackup -h localhost -p 5438 -Ft --compress=client-gzip:9 --pgdata=/u99/backup2/ -Xs
[email protected]:/u99/backup2/ [PG15] ll
total 3136
-rw------- 1 postgres postgres  136487 Mar 28 15:49 backup_manifest
-rw------- 1 postgres postgres 3050084 Mar 28 15:49 base.tar.gz
-rw------- 1 postgres postgres   17649 Mar 28 15:49 pg_wal.tar.gz
[email protected]:/u99/backup2/ [PG15]

Or with lz4 (available for client or server side compression):

[email protected]:/u99/backup2/ [PG15] pg_basebackup -h localhost -p 5438 -Ft --compress=lz4:9 --pgdata=/u99/backup2/ -Xs
[email protected]:/u99/backup2/ [PG15] ll
total 20096
-rw------- 1 postgres postgres   136487 Mar 28 15:18 backup_manifest
-rw------- 1 postgres postgres  3657232 Mar 28 15:18 base.tar.lz4
-rw------- 1 postgres postgres 16779264 Mar 28 15:18 pg_wal.tar

3. New role: pg_checkpointer

Until PostgreSQL 14 only the superuser(s) were allowed to execute CHECKPOINT commands. Starting with PostgreSQL 15 there is a new role called pg_checkpointer. Once you grant that role to an user, it is able to execute CHECKPOINT commands.

postgres=# create user checky;
CREATE ROLE
postgres=# c postgres checky
You are now connected to database "postgres" as user "checky".
postgres=> checkpoint;
ERROR:  must be superuser or have privileges of pg_checkpointer to do CHECKPOINT
postgres=> c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# grant pg_checkpointer to checky;
GRANT ROLE
postgres=# c postgres checky
You are now connected to database "postgres" as user "checky".
postgres=> checkpoint;
CHECKPOINT
postgres=>

4. MERGE command

MERGE gives you the opportunity to execute one SQL statement that INSERT/UPDATE/DELETE rows in regular and partitioned tables and more. There is some overhead if you use it as a single SQL command, because you need a lot of WHEN / THEN expressions.

Let’s have a look at this new feature using a simple example with two table which are similar, but there are some more entries.

dvdrental=# select * from category;
 category_id |    name     |     last_update
-------------+-------------+---------------------
           1 | Action      | 2006-02-15 09:46:27
           2 | Animation   | 2006-02-15 09:46:27
           3 | Children    | 2006-02-15 09:46:27
           4 | Classics    | 2006-02-15 09:46:27
           5 | Comedy      | 2006-02-15 09:46:27
           6 | Documentary | 2006-02-15 09:46:27
           7 | Drama       | 2006-02-15 09:46:27
           8 | Family      | 2006-02-15 09:46:27
           9 | Foreign     | 2006-02-15 09:46:27
          10 | Games       | 2006-02-15 09:46:27
          11 | Horror      | 2006-02-15 09:46:27
          12 | Music       | 2006-02-15 09:46:27
          13 | New         | 2006-02-15 09:46:27
          14 | Sci-Fi      | 2006-02-15 09:46:27
          15 | Sports      | 2006-02-15 09:46:27
          16 | Travel      | 2006-02-15 09:46:27
(16 rows)

dvdrental=# select * from category_new;
 category_id |    name     |        last_update
-------------+-------------+----------------------------
           1 | Action      | 2006-02-15 09:46:27
           2 | Animation   | 2006-02-15 09:46:27
           3 | Children    | 2006-02-15 09:46:27
           4 | Classics    | 2006-02-15 09:46:27
           5 | Comedy      | 2006-02-15 09:46:27
           6 | Documentary | 2006-02-15 09:46:27
           7 | Drama       | 2006-02-15 09:46:27
           8 | Family      | 2006-02-15 09:46:27
           9 | Foreign     | 2006-02-15 09:46:27
          10 | Games       | 2006-02-15 09:46:27
          11 | Horror      | 2006-02-15 09:46:27
          12 | Music       | 2006-02-15 09:46:27 
          13 | Biography   | 2022-04-12 11:53:34.986878
          14 | Sci-Fi      | 2006-02-15 09:46:27
          15 | Sports      | 2006-02-15 09:46:27
          16 | Travel      | 2006-02-15 09:46:27
          17 | Dramedy     | 2022-04-12 11:48:49.559058
          18 | Love        | 2022-04-12 11:49:32.072536
(17 rows)

dvdrental=# MERGE INTO category AS c
USING category_new AS n
ON c.category_id = n.category_id
WHEN MATCHED AND c.name = n.name  THEN
  DO NOTHING
WHEN MATCHED AND c.name  n.name THEN
  UPDATE SET name=n.name
WHEN NOT MATCHED THEN
  INSERT VALUES (n.category_id, n.name, n.last_update)
;
MERGE 17
dvdrental=# 

Once the MERGE command is done, select the original table again, to see if it added and updated everything as planned:

dvdrental=# select * from category order by 1;
 category_id |    name     |        last_update
-------------+-------------+----------------------------
           1 | Action      | 2006-02-15 09:46:27
           2 | Animation   | 2006-02-15 09:46:27
           3 | Children    | 2006-02-15 09:46:27
           4 | Classics    | 2006-02-15 09:46:27
           5 | Comedy      | 2006-02-15 09:46:27
           6 | Documentary | 2006-02-15 09:46:27
           7 | Drama       | 2006-02-15 09:46:27
           8 | Family      | 2006-02-15 09:46:27
           9 | Foreign     | 2006-02-15 09:46:27
          10 | Games       | 2006-02-15 09:46:27
          11 | Horror      | 2006-02-15 09:46:27
          12 | Music       | 2006-02-15 09:46:27
          13 | Biography   | 2022-04-12 13:42:26.187381
          14 | Sci-Fi      | 2006-02-15 09:46:27
          15 | Sports      | 2006-02-15 09:46:27
          16 | Travel      | 2006-02-15 09:46:27
          17 | Dramedy     | 2022-04-12 11:48:49.559058
          18 | Love        | 2022-04-12 11:49:32.072536
(18 rows)

Merge does not support foreign tables or updatable views. Maybe this will come later, if there is need for it. But for the moment it is not planned.

Conclusion

These are only a few new features coming with PostgreSQL Version 15. Much more new stuff to follow. Especially with replication there is a lot more to check out and also in cases of security PostgreSQL improves with every new release.


Thumbnail [60x60]
by
Julia Gugel