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.
postgres@pgdebian:/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.
postgres@pgdebian:/u99/backup2/ [PG15] pg_basebackup -h localhost -p 5438 -Ft --compress=client-gzip:9 --pgdata=/u99/backup2/ -Xs postgres@pgdebian:/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 postgres@pgdebian:/u99/backup2/ [PG15]
Or with lz4 (available for client or server side compression):
postgres@pgdebian:/u99/backup2/ [PG15] pg_basebackup -h localhost -p 5438 -Ft --compress=lz4:9 --pgdata=/u99/backup2/ -Xs postgres@pgdebian:/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]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/GWI_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)