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 <\/p>\n
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.<\/p>\n
\npostgres=# create user test;\nCREATE ROLE\npostgres=# du\n List of roles\n Role name | Attributes | Member of\n-----------+------------------------------------------------------------+-----------\n postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}\n test | | {}\n\npostgres=# c postgres test\nYou are now connected to database \"postgres\" as user \"test\".\npostgres=> create table t1 (a int);\nERROR: permission denied for schema public\nLINE 1: create table t1 (a int);\n ^\npostgres=\n<\/pre>\nIf you want to be able again to write into the public schema, the permission has to be granted again explicitly.<\/p>\n
2. Extended pg_basebackup compression <\/h3>\n
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.<\/p>\n
\npostgres@pgdebian:\/u99\/backup2\/ [PG15] pg_basebackup --help | grep -A 1 compress\n -z, --gzip compress tar output\n -Z, --compress=[{client|server}-]METHOD[:DETAIL]\n compress on client or server as specified\n -Z, --compress=none do not compress tar output\n<\/pre>\nTo create a backup it looks like this now. Really simple and easy to use.<\/p>\n
\npostgres@pgdebian:\/u99\/backup2\/ [PG15] pg_basebackup -h localhost -p 5438 -Ft --compress=client-gzip:9 --pgdata=\/u99\/backup2\/ -Xs\npostgres@pgdebian:\/u99\/backup2\/ [PG15] ll\ntotal 3136\n-rw------- 1 postgres postgres 136487 Mar 28 15:49 backup_manifest\n-rw------- 1 postgres postgres 3050084 Mar 28 15:49 base.tar.gz\n-rw------- 1 postgres postgres 17649 Mar 28 15:49 pg_wal.tar.gz\npostgres@pgdebian:\/u99\/backup2\/ [PG15]\n<\/pre>\nOr with lz4 (available for client or server side compression):<\/p>\n
\npostgres@pgdebian:\/u99\/backup2\/ [PG15] pg_basebackup -h localhost -p 5438 -Ft --compress=lz4:9 --pgdata=\/u99\/backup2\/ -Xs\npostgres@pgdebian:\/u99\/backup2\/ [PG15] ll\ntotal 20096\n-rw------- 1 postgres postgres 136487 Mar 28 15:18 backup_manifest\n-rw------- 1 postgres postgres 3657232 Mar 28 15:18 base.tar.lz4\n-rw------- 1 postgres postgres 16779264 Mar 28 15:18 pg_wal.tar\n<\/pre>\n 3. New role: pg_checkpointer <\/h3>\n
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.<\/p>\n
\npostgres=# create user checky;\nCREATE ROLE\npostgres=# c postgres checky\nYou are now connected to database \"postgres\" as user \"checky\".\npostgres=> checkpoint;\nERROR: must be superuser or have privileges of pg_checkpointer to do CHECKPOINT\npostgres=> c postgres postgres\nYou are now connected to database \"postgres\" as user \"postgres\".\npostgres=# grant pg_checkpointer to checky;\nGRANT ROLE\npostgres=# c postgres checky\nYou are now connected to database \"postgres\" as user \"checky\".\npostgres=> checkpoint;\nCHECKPOINT\npostgres=>\n<\/pre>\n 4. MERGE command<\/h3>\n
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. <\/p>\n
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.<\/p>\n
\ndvdrental=# select * from category;\n category_id | name | last_update\n-------------+-------------+---------------------\n 1 | Action | 2006-02-15 09:46:27\n 2 | Animation | 2006-02-15 09:46:27\n 3 | Children | 2006-02-15 09:46:27\n 4 | Classics | 2006-02-15 09:46:27\n 5 | Comedy | 2006-02-15 09:46:27\n 6 | Documentary | 2006-02-15 09:46:27\n 7 | Drama | 2006-02-15 09:46:27\n 8 | Family | 2006-02-15 09:46:27\n 9 | Foreign | 2006-02-15 09:46:27\n 10 | Games | 2006-02-15 09:46:27\n 11 | Horror | 2006-02-15 09:46:27\n 12 | Music | 2006-02-15 09:46:27\n 13 | New | 2006-02-15 09:46:27\n 14 | Sci-Fi | 2006-02-15 09:46:27\n 15 | Sports | 2006-02-15 09:46:27\n 16 | Travel | 2006-02-15 09:46:27\n(16 rows)\n\ndvdrental=# select * from category_new;\n category_id | name | last_update\n-------------+-------------+----------------------------\n 1 | Action | 2006-02-15 09:46:27\n 2 | Animation | 2006-02-15 09:46:27\n 3 | Children | 2006-02-15 09:46:27\n 4 | Classics | 2006-02-15 09:46:27\n 5 | Comedy | 2006-02-15 09:46:27\n 6 | Documentary | 2006-02-15 09:46:27\n 7 | Drama | 2006-02-15 09:46:27\n 8 | Family | 2006-02-15 09:46:27\n 9 | Foreign | 2006-02-15 09:46:27\n 10 | Games | 2006-02-15 09:46:27\n 11 | Horror | 2006-02-15 09:46:27\n 12 | Music | 2006-02-15 09:46:27 \n 13 | Biography | 2022-04-12 11:53:34.986878\n 14 | Sci-Fi | 2006-02-15 09:46:27\n 15 | Sports | 2006-02-15 09:46:27\n 16 | Travel | 2006-02-15 09:46:27\n 17 | Dramedy | 2022-04-12 11:48:49.559058\n 18 | Love | 2022-04-12 11:49:32.072536\n(17 rows)\n\ndvdrental=# MERGE INTO category AS c\nUSING category_new AS n\nON c.category_id = n.category_id\nWHEN MATCHED AND c.name = n.name THEN\n DO NOTHING\nWHEN MATCHED AND c.name n.name THEN\n UPDATE SET name=n.name\nWHEN NOT MATCHED THEN\n INSERT VALUES (n.category_id, n.name, n.last_update)\n;\nMERGE 17\ndvdrental=# \n<\/pre>\nOnce the MERGE command is done, select the original table again, to see if it added and updated everything as planned:<\/p>\n
\ndvdrental=# select * from category order by 1;\n category_id | name | last_update\n-------------+-------------+----------------------------\n 1 | Action | 2006-02-15 09:46:27\n 2 | Animation | 2006-02-15 09:46:27\n 3 | Children | 2006-02-15 09:46:27\n 4 | Classics | 2006-02-15 09:46:27\n 5 | Comedy | 2006-02-15 09:46:27\n 6 | Documentary | 2006-02-15 09:46:27\n 7 | Drama | 2006-02-15 09:46:27\n 8 | Family | 2006-02-15 09:46:27\n 9 | Foreign | 2006-02-15 09:46:27\n 10 | Games | 2006-02-15 09:46:27\n 11 | Horror | 2006-02-15 09:46:27\n 12 | Music | 2006-02-15 09:46:27\n 13 | Biography | 2022-04-12 13:42:26.187381\n 14 | Sci-Fi | 2006-02-15 09:46:27\n 15 | Sports | 2006-02-15 09:46:27\n 16 | Travel | 2006-02-15 09:46:27\n 17 | Dramedy | 2022-04-12 11:48:49.559058\n 18 | Love | 2022-04-12 11:49:32.072536\n(18 rows)\n\n<\/pre>\nMerge 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.<\/p>\n
Conclusion <\/h3>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,83],"tags":[77],"type_dbi":[],"class_list":["post-17161","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-postgresql","tag-postgresql"],"acf":[],"yoast_head":"\n
PostgreSQL 15: Some new features - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/postgresql-15-some-new-features\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 15: Some new features\" \/>\n<meta property=\"og:description\" content=\"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\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-15-some-new-features\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2022-04-12T10:55:38+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-09-10T15:30:25+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-15-some-new-features\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-15-some-new-features\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b\"},\"headline\":\"PostgreSQL 15: Some new features\",\"datePublished\":\"2022-04-12T10:55:38+00:00\",\"dateModified\":\"2024-09-10T15:30:25+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-15-some-new-features\/\"},\"wordCount\":445,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\",\"PostgreSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-15-some-new-features\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-15-some-new-features\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-15-some-new-features\/\",\"name\":\"PostgreSQL 15: Some new features - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2022-04-12T10:55:38+00:00\",\"dateModified\":\"2024-09-10T15:30:25+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-15-some-new-features\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-15-some-new-features\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-15-some-new-features\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 15: Some new features\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PostgreSQL 15: Some new features - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-some-new-features\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL 15: Some new features","og_description":"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","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-some-new-features\/","og_site_name":"dbi Blog","article_published_time":"2022-04-12T10:55:38+00:00","article_modified_time":"2024-09-10T15:30:25+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-some-new-features\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-some-new-features\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b"},"headline":"PostgreSQL 15: Some new features","datePublished":"2022-04-12T10:55:38+00:00","dateModified":"2024-09-10T15:30:25+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-some-new-features\/"},"wordCount":445,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring","PostgreSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-15-some-new-features\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-some-new-features\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-some-new-features\/","name":"PostgreSQL 15: Some new features - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2022-04-12T10:55:38+00:00","dateModified":"2024-09-10T15:30:25+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-some-new-features\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-15-some-new-features\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-some-new-features\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 15: Some new features"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17161","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=17161"}],"version-history":[{"count":1,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17161\/revisions"}],"predecessor-version":[{"id":34681,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17161\/revisions\/34681"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=17161"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=17161"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=17161"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=17161"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}