The Developer Space

Developer's Cafe

  • Cloud
  • Database
  • Open Source
  • Programming
  • Web Dev
  • Mobile
  • Security
  • QuickRef
  • Home
  • Database
  • PostgreSQL 12: New Features and Enhancements

PostgreSQL 12: New Features and Enhancements

Shameel Ahmed - CodeProject, Database, PostgreSQL
September 16, 2019September 21, 2019 No Comments
0 0
0 0
Read Time8 Minute, 8 Second

Table of Contents

  • Introduction
  • New Features & Enhancements
    • CTE Enhancements
    • Generated Columns
      • The SQL Server Syntax for Computed Columns
      • So how is a Generated column different from a normal column with a DEFAULT clause?
    • Re-Index Improvements
    • JIT Compilation enabled by default
    • Query Parallelism Improvements
    • PL/pgSQL Enhancements
    • SQL/JSON Path Queries
    • Partitioning Improvements
    • Collation Improvements
    • MCV statistics now supports multiple columns
  • Migrating from older versions
  • References
  • More Reading
  • Share

Introduction

PostgreSQL 12 is the next major release of the world’s most popular and feature-rich open source database. The stable version of PostgreSQL 12 is scheduled to be released in late 2019. Beta 4 of version 12 was released on 12th Sep 2019.

This article walks you through the summary of most important new features. enhancements and breaking changes in PostgreSQL 12.

New Features & Enhancements

CTE Enhancements

In PostgreSQL, CTEs are optimization fences (outer query restrictions are not passed on to CTEs) and the database evaluates the query inside the CTE and caches the results (i.e., materialized results) and outer WHERE clauses are applied later when the outer query is processed, which means either a full table scan or a full index seek is performed and results in horrible performance for large tables.

WITH AllUsers AS (SELECT * FROM Users)
SELECT * FROM AllUsers WHERE Id = 100;  

To overcome this, you rewrite this query in PostgreSQL as: 

WITH UserRecord AS (SELECT * FROM Users WHERE Id = 100)
SELECT * FROM UserRecord;  

PostgreSQL 12 addresses this problem by introducing query optimizer hints to enable us to control if the CTE should be materialized or not: MATERIALIZED, NOT MATERIALIZED. So, the query can be rewritten as follows to achieve better performance.

WITH AllUsers AS NOT MATERIALIZED (SELECT * FROM Users)
SELECT * FROM AllUsers WHERE Id = 100;  

This effectively in-lines the CTE expression and results in significant performance improvement.

Generated Columns

SQL Server and Oracle have long had Computed Columns and the lack of computed columns in PostgreSQL made migrations from other Databases quite difficult. PostgreSQL 12 introduces Generated Columns to address this problem.

A generated column is to columns what a view is to a table. The value of the column is always computed from other columns in the table. A generated column can either be virtual or stored. The values for virtual columns are computed on the fly during query time and they don’t take storage space. The values for stored columns are pre-computed and stored as part of table data. PostgreSQL currently implements only stored generated columns.

The GENERATED ALWAYS AS clause is used to create Generated columns

CREATE TABLE Employee (
    ...,
    dob timestamp,
    age integer GENERATED ALWAYS AS (date_part('year', CURRENT_TIME - dob)) STORED
);

In PostgreSQL 11.x and older, the only way to do this was to declare age as a normal column and write a trigger to populate the column during inserts and updates

CREATE OR REPLACE FUNCTION calc_age() RETURNS TRIGGER AS $body$
BEGIN
  NEW.age := date_part('year', CURRENT_TIME - NEW.dob::timestamp);
  RETURN NEW;
END; 
$body$ LANGUAGE plpgsql;

The SQL Server Syntax for Computed Columns

Contrast this to SQL Server syntax. There is no special keyword in SQL Server to declare a computed column. You just specify the expression that makes us the computed column after the AS clause.

-- SQL Server Syntax
CREATE TABLE Employee (
    ...,
    dob datetime,
    age AS DATEDIFF(year,dob,GETDATE()) PERSISTED
);

Note the PERSISTED clause which is the equivalent of PostgreSQL’s STORED clause. SQL Server also supports non-persisted computed columns, you just don’t specify the PERSISTED clause.

So how is a Generated column different from a normal column with a DEFAULT clause?

  1. The column default is evaluated once when the row is first inserted if no other value was provided; a generated column is updated whenever the row changes and cannot be overridden.
  2. A column with DEFAULT constraint can be given a value in an INSERT or UPDATE statement. Generated columns cannot be given values, they’re always computed.
  3. A column default cannot refer to other columns of the table, whereas a generated columns is specifically meant to do so.
  4. A column default can use volatile functions, for example random() or current_time, generated columns cannot.

Re-Index Improvements

REINDEX CONCURRENTLY can rebuild an index without blocking writes to its table. The CONCURRENTLY clause was earlier available only in the CREATE INDEX statement.

CREATE TABLE test (x INTEGER);
INSERT INTO test SELECT generate_series(1, 1000000);
CREATE INDEX i_test ON test (x);
REINDEX INDEXCONCURRENTLY i_test;

In previous version of PostgreSQL, REINDEX on large tables frequently caused blocking writes and severe performance issues.

JIT Compilation enabled by default

Enable Just-in-Time (JIT) compilation by default, if the server has been built with support for it. Note that this support is not built by default, but has to be selected explicitly while configuring the build. This is especially useful for data warehouse queries.

Query Parallelism Improvements

Allow parallelized queries when in SERIALIZABLE isolation mode. Previously, parallelism was disabled when in this mode.

PL/pgSQL Enhancements

Allow SQL commands and variables with the same names as those commands to be used in the same PL/pgSQL function. For example, allow a variable called comment to exist in a function that calls the COMMENT SQL command. Previously this combination caused a parse error.

SQL/JSON Path Queries

PostgreSQL 12 now allows execution of JSON path queries per the SQL/JSON specification in the SQL:2016 standard. JSON path expressions let you evaluate a variety of arithmetic expressions and functions and compare values within JSON documents, similar to XPath expressions for XML. These expressions can be accelerated with GIN indexes which improves the execution of lookups across JSON data.

Partitioning Improvements

There is significant performance improvement in PostgreSQL 12 when processing tables with thousands of partitions for command that only need to use a subset of all the available partitions. The INSERT and COPY into a partitioned table also perform significantly faster compared to previous versions. ATTACH PARTITION can now be performed without blocking concurrent queries on the partitioned table. Partitioned tables can now be referenced as foreign keys. Partition bounds can now be expressions.

Collation Improvements

PostgreSQL 12 now supports case-insensitive and accent-insensitive comparisons for ICU provided collations, also known as “nondeterministic collations“. When used, these collations can provide convenience for comparisons and sorts, but can also lead to a performance penalty as a collation may need to make additional checks on a string.

MCV statistics now supports multiple columns

The CREATE STATISTICS command allows most-common-value statistics for multiple columns; previously only a single correlation value was recorded for multiple columns.

CREATE STATISTICS stts3 (mcv) ON state, city FROM zipcodes;
ANALYZE zipcodes;

Migrating from older versions

Some changes in PostgreSQL 12 may affect compatibility with previous releases.

  1. recovery.conf is no longer used, and the server will not start if that file exists. recovery.signal and standby.signal files are now used to switch into non-primary mode. trigger_file has been renamed to promote_trigger_file. The standby_mode setting has been removed.
  2. In new btree indexes, the maximum index entry length is reduced by eight bytes, to improve handling of duplicate entries. This means that a REINDEX operation on an index pg_upgrade’d from a previous release could potentially fail.
  3. DROP IF EXISTS FUNCTION/PROCEDURE/AGGREGATE/ROUTINE to generate an error if no argument list is supplied and there are multiple matching objects

References

https://www.postgresql.org/docs/12/release-12.html

https://www.postgresql.org/developer/roadmap/

https://www.postgresql.org/docs/12/ddl-generated-columns.html

More Reading

Migrating your SQL Server Workloads to PostgreSQL
PostgreSQL finally gets Stored Procedures
Check PostgreSQL Edition

Share

Facebook
Twitter
LinkedIn
Email

Post navigation

PostgreSQL finally gets Stored Procedures
Generated Columns in PostgreSQL

Related Articles

Migrate your SQL Server Workloads to PostgreSQL: Quick Reference: Second Edition

Book: Migrate your SQL Server Workloads to PostgreSQL: Quick Reference – Second Edition

Shameel Ahmed
October 18, 2022October 22, 2022 1 Comment

How to fix Redis Error “ERR DEBUG command not allowed.”

Shameel Ahmed
July 4, 2022July 5, 2022 No Comments

Migrate MySQL table data to MongoDB collections using Python

Shameel Ahmed
June 18, 2021June 25, 2021 1 Comment

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%
(Add your review)

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Contents

  • Introduction
  • New Features & Enhancements
    • CTE Enhancements
    • Generated Columns
      • The SQL Server Syntax for Computed Columns
      • So how is a Generated column different from a normal column with a DEFAULT clause?
    • Re-Index Improvements
    • JIT Compilation enabled by default
    • Query Parallelism Improvements
    • PL/pgSQL Enhancements
    • SQL/JSON Path Queries
    • Partitioning Improvements
    • Collation Improvements
    • MCV statistics now supports multiple columns
  • Migrating from older versions
  • References
  • More Reading
  • Share

Categories

.NET Architecture Artificial Intelligence ASP.NET AWS Azure Books C# Career Cloud CodeProject Conversational Bots Database Data Security Facade IDEs Java Mobile MongoDB MySQL Open Source Patterns PostgreSQL Programming Python Redis Security SQL Server Tools Uncategorized Web Development Windows Phone

Recent Posts

  • How to build a REST API using Amazon API Gateway to invoke OpenAI APIs March 11, 2023
  • How to fix “‘StreamingBody’ object is not subscriptable” in AWS Lambda boto3 February 12, 2023
  • How to invoke OpenAI APIs from AWS Lambda functions February 5, 2023
  • Developer to Architect Series (Red Hat Enable Architect) January 16, 2023
  • Can ChatGPT replace Google Search? January 11, 2023

Archives

  • March 2023 (1)
  • February 2023 (2)
  • January 2023 (2)
  • December 2022 (1)
  • October 2022 (1)
  • July 2022 (2)
  • February 2022 (1)
  • November 2021 (1)
  • July 2021 (1)
  • June 2021 (1)
  • September 2020 (1)
  • May 2020 (2)
  • April 2020 (1)
  • October 2019 (1)
  • September 2019 (4)
  • July 2019 (2)
  • May 2018 (1)
  • September 2017 (1)
  • April 2017 (1)
  • April 2014 (1)
  • August 2011 (1)
  • June 2009 (1)
Copyright 2022. The Developer Space | Theme: OMag by LilyTurf Themes
  • DbStudio
  • Re:Link
  • shameel.net
  • Privacy Policy
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept”, you consent to the use of ALL the cookies.
Do not sell my personal information.
Cookie settingsACCEPT
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are as essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
SAVE & ACCEPT