February 2, 2023

SharewarePile.com – Latest Web Hosting News

Latest Web Development and Hosting News

PostgreSQL 13 – New Features [Master Guide]

PostgreSQL 13 – New Features [Master Guide]

PostgreSQL 13 Beta 1 has been released for testing.

postgres=# show server_version_num;
server_version_num
--------------------
130000
(1 row)

PostgreSQL 13 New Features

More than 160 new features have been added in PostgreSQL 13 compared to its previous versions.

This is a multi-post series on PostgreSQL 13 NF, so I would recommend you to subscribe to the mailer given down below so that you get notified whenever I release a new post in the series.

I will just compare the major difference between PostgreSQL 12 (12.3) and PostgreSQL 13 Beta 1 (13.0). However, I am not going to describe all the features here, I will list down the features which plays a significant role in performance improvement.

Hardware/Software used to test the PostgreSQL 13 New Features.

  • Operating System      : RHEL 6
  • Disk                            : SSD
  • PostgreSQL Versions :  v12.3 vs v13 Beta 1

New Feature 1: Incremental Sorting

PostgreSQL 13 in its release notes highlighted Incremental sorting as one of the main new features in PostgreSQL 13.

PostgreSQL 13’s incremental sorting, which accelerates sorting data when data that is sorted from earlier parts of a query are already sorted.

For example, you have an index on c1 and you need to sort dataset by c1, c2. Then incremental sort can help you because it wouldn’t sort the whole dataset, but sort individual groups whose have the same value of c1 instead. The incremental sort is extremely helpful when you have a LIMIT clause.

Lets us run the query the following query in PostgreSQL 12 and PostgreSQL 13 Beta 1.

select * from pgbench_accounts order by aid, bid; 

here aid has an index whereas bid doesn’t have one.

PostgreSQL 12.3

[postgres@dbapath06 ~]$ psql
psql (12.3)
Type "help" for help.

postgres=# SET work_mem TO '64kB';
SET
postgres=# explain analyze select * from pgbench_accounts order by aid, bid;
                                                                  QUERY PLAN
----------------------------------------------------------------------------------

 Gather Merge  (cost=60150.23..99041.91 rows=333334 width=97) 
                           (actual time=494.075..707.664 rows=400000 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Sort  (cost=59150.20..59566.87 rows=166667 width=97) 
                           (actual time=442.889..470.942 rows=133333 loops=3)
         Sort Key: aid, bid
         Sort Method: external merge  Disk: 16592kB
         Worker 0:  Sort Method: external merge  Disk: 13432kB
         Worker 1:  Sort Method: external merge  Disk: 11936kB
         ->  Parallel Seq Scan on pgbench_accounts  
                    (cost=0.00..8224.67 rows=166667 width=97) 
                         (actual time=0.030..37.491 rows=133333 loops=3)
 Planning Time: 0.052 ms
 Execution Time: 724.088 ms
(11 rows)

PostgreSQL 13 Beta 1

[postgres@postgreshelp ~]$ psql
psql (13beta1)
Type "help" for help.

postgres=# SET work_mem TO '64kB';
SET
postgres=# explain analyze select * from pgbench_accounts order by aid, bid;
                  QUERY PLAN
----------------------------------------------------------------------
 Incremental Sort  (cost=0.47..34957.42 rows=400000 width=97) 
          (actual time=0.208..219.769 rows=400000 loops=1)
   Sort Key: aid, bid
   Presorted Key: aid
   Full-sort Groups: 12500  Sort Method: quicksort  Average Memory: 29kB  
   Peak Memory: 29kB
   ->  Index Scan using pgbench_accounts_pkey on pgbench_accounts  
(cost=0.42..16957.42 rows=400000 width=97) (actual time=0.055..146.3
56 rows=400000 loops=1)
 Planning Time: 0.810 ms
 Execution Time: 234.531 ms
(7 rows)

By not sorting the entire table at once, but in blocks, it increases the chance that the sorted block will fit in memory, and it will be possible to use quicksort instead of the slower, more demanding external sort.

You can enable or disables the query planner’s use of incremental sort steps with the below parameter. The default is on.

enable_incrementalsort (boolean)

Stay tuned for more new feature test cases, you can do so by subscribing here







PostgreSQL 13 – New Features [Master Guide]

Words from postgreshelp

Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestions/feedback.

If you want to be updated with all our articles

please follow us on Facebook Twitter
Please subscribe to our newsletter.

 


Go to Source of this post
Author Of this post: postgreshelp
Title Of post: PostgreSQL 13 – New Features [Master Guide]
Author Link: {authorlink}