PostgreSQL 15 New Features (Part – 1)

PostgreSQL 15 New Features in detail (Part – 1): General, Configuration & Administration

1. Improved performance for sorts exceeding working memory
   

   Sorts for data sets that exceed work_mem now use a batch sorting algorithm that uses more output streams and improves performance.

   We created a sample table called test with two columns (an ‘Id’ column, and a text column called ‘name’) and populated with 10 million records for this test. 

   When we executed a select on this table with a sort clause on the ‘name’ column, we observed that the query execution time in pg-15 (15.5 seconds) is indeed less when compared to the query execution time in pg-14(89.7 seconds).

   The following statements were used for table creation and data population on both pg-14 and pg-15:

   logical=# create table test(id int , name varchar);

   CREATE TABLE

   logical=# insert into test select generate_series(1,10000000),md5(generate_series(1,10000000)::text);

   INSERT 0 10000000

   Performance testing in PostgreSQL 15: 

   logical=# explain analyze select * from test order by name ;

         QUERY PLAN

   —————————————————————————–

    Gather Merge  (cost=811991.86..1784268.27 rows=8333216 width=37) (actual time=9245.117..15159.681 rows=10000000 loops=1)

      Workers Planned: 2

      Workers Launched: 2

      ->  Sort  (cost=810991.83..821408.35 rows=4166608 width=37) (actual time=9180.457..10819.035 rows=3333333 loops=3)

            Sort Key: name

            Sort Method: external merge  Disk: 155400kB

            Worker 0:  Sort Method: external merge  Disk: 147624kB

            Worker 1:  Sort Method: external merge  Disk: 157176kB

            ->  Parallel Seq Scan on test  (cost=0.00..125000.08 rows=4166608 width=37) (actual time=0.021..482.660 rows=3333333 loops=3)

   Planning Time: 1.464 ms
   Execution Time: 15427.398 ms
   

   (11 rows)
   

   Performance testing in PostgreSQL 14:

   example=# explain analyze select * from test order by name ;

   QUERY PLAN

   —————————————————————————–

    Gather Merge  (cost=856262.40..1885277.78 rows=8819516 width=36) (actual time=61296.715..88931.115 rows=10000000 loops=1)

      Workers Planned: 2

      Workers Launched: 2

      ->  Sort  (cost=855262.38..866286.77 rows=4409758 width=36) (actual time=61117.409..65908.421 rows=3333333 loops=3)

            Sort Key: name

            Sort Method: external merge  Disk: 155488kB

            Worker 0:  Sort Method: external merge  Disk: 153152kB

            Worker 1:  Sort Method: external merge  Disk: 151392kB

            ->  Parallel Seq Scan on test  (cost=0.00..127431.58 rows=4409758 width=36) (actual time=0.285..1926.893 rows=3333333 loops=3)

    Planning Time: 7.000 ms

    Execution Time: 89674.825 ms

   (11 rows)

2. Privileges for setting configuration parameters
   

   Unprivileged users can now be granted permission to change server variables via SET and ALTER SYSTEM.

   How is this useful?
   
   Only privileged users could change the configuration parameters up until the previous version (i.e., pg14). In pg15 version, privileged users can grant permission to unprivileged users to SET and ALTER specific configuration parameters via the ALTER SYSTEM command. Privileged users also have the option of controlling which specific configuration parameters the unprivileged users can modify, and the unprivileged users’ access to modify the configuration parameters would then be limited to such granted parameters only.

   This feature could help decrease the turn-around time during application development & test cycles by minimizing the dependency on DBAs.
   
   Is it chained?

   This feature is not chained, i.e., an Unprivileged user who has been given SET or ALTER SYSTEM privileges cannot share or extend the same access to another unprivileged user.

   Examples:

   [postgres@localhost backups]$ psql -U sudheer -d postgres

   psql (15.0)

   postgres=> alter system set wal_level = replica;

   ERROR:  permission denied to set parameter “wal_level”

   postgres=> exit

   [postgres@localhost backups]$ psql

   psql (15.0)

   postgres=# GRANT ALTER SYSTEM ON PARAMETER wal_level to sudheer;

   GRANT

   postgres=# exit

   [postgres@localhost backups]$ psql -U sudheer -d postgres

   psql (15.0)

   postgres=> alter system set wal_level = replica;

   ALTER SYSTEM

   [postgres@localhost backups]$ psql -U sudheer -d postgres

   psql (15.0)

   postgres=> show wal_level;

    wal_level 

   ———–

    replica

   (1 row)

   postgres=> GRANT ALTER SYSTEM ON PARAMETER wal_level to testuser;

   WARNING:  no privileges were granted for “wal_level”

   GRANT

   postgres=> exit

   [postgres@localhost backups]$ psql -U testuser -d postgres

   psql (15.0)

   postgres=> alter system set wal_level = logical;

   ERROR:  permission denied to set parameter “wal_level”

   postgres=>

3. Alter Table… Set Access Method
   

   It is a feature that allows an alternative implementation for how data in a table should be stored. Until PostgreSQL 11, access methods were provided for index data, to choose different storage methods such as B-Tree or hash, but no similar mechanism was available for tables.

   PostgreSQL 12 introduced this feature, so now access methods can be implemented for tables as well as indexes, allowing the selection of different table storage mechanisms.

   PostgreSQL 15 allows users to change the table access method using the “ALTER TABLE”, this will re-write the table to a different storage method.

   Example:

   postgres=# ALTER TABLE employee SET ACCESS METHOD heap;

   ALTER TABLE

4. UNIQUE NULLS NOT DISTINCT
   

   Up until now, the Postgres database would not enforce the unique constraint on NULL values, and allowed multiple records with NULL values even on a column that has a UNIQUE constraint defined.

   In pg15, we now have the option to enforce uniqueness across all records, and allow only one record with a NULL value. This can be achieved by adding the NULLS NOT DISTINCT clause when defining the unique constraints / indexes.

   Example:

   postgres@localhost data]$ psql -d test -U test

   psql (15.0)

   test=# create table emp (id int UNIQUE NULLS NOT DISTINCT, name varchar(50));

   CREATE TABLE

   test=# insert into emp values (1,’aaa’);

   INSERT 0 1

   test=# insert into emp values (null,’aaa’);

   INSERT 0 1

   test=# insert into emp values (2,’bbb’);

   INSERT 0 1

   test=# insert into emp values (null,’bbb’);

   ERROR:  duplicate key value violates unique constraint “emp_id_key”

   DETAIL:  Key (id)=(null) already exists.

5. PSQL: \dconfig
   

   The psql client now includes a new command ‘\dconfig’ for inspecting and finding the values of configuration parameters.

   

   Adding the + symbol to the \dconfig command displays extended information on the configuration parameters such as Type, Context and Access Privileges.

   We can find the output of \dconfig+ command in PostgreSQL 15 in the following screenshot: