Why upgrade to PostgreSQL 12 ?
PostgreSQL 12 provides significant performance and maintenance enhancements to its indexing system and to partitioning. PostgreSQL 12 introduces the ability to run queries over JSON documents using JSON path expressions defined in the SQL/JSON standard. Such queries may utilize the existing indexing mechanisms for documents stored in the JSONB format to efficiently retrieve data. PostgreSQL 12 extends its support of ICU collations by allowing users to define “nondeterministic collations” that can, for example, allow case-insensitive or accent-insensitive comparisons. PostgreSQL 12 enhancements include notable improvements to query performance, particularly over larger data sets, and overall space utilization. This release provides application developers with new capabilities such as SQL/JSON path expression support, optimizations for how common table expression (WITH) queries are executed, and generated columns
The following are top three most interesting features introduced in PostgreSQL 12 :
1. Much better indexing for performance and optimal space management in PostgreSQL 12 – Why we worry so much about indexing in Database Systems ? All of us know very well that large amount data Can’t technically fit well in the main memory. When you have more number of keys, You will eventually end-up reading more from disk compared to main memory and Disk access time is very high compared to main memory access time. We use B-tree indexes to reduce the number of disk accesses. B-tree is a data structure that store data in its node in sorted order. B-tree stores data in a way that each node accommodate keys in ascending order. B-tree uses an array of entries for a single node and having reference to child node for each of these entries. We spend significant amount of time to reclaim the storage occupied by dead tuples and this happen due to PostgreSQL indexes bloat, which take up extra storage in the disk. Thanks to PostgreSQL 12, We have now much better B-tree indexing which can reduce up to 40% in space utilization and overall gain in the query performance and that means we have now both faster WRITEs and READs. PostgreSQL 12 introduces the ability to rebuild indexes without blocking writes to an index via the REINDEX CONCURRENTLY command, allowing users to avoid downtime scenarios for lengthy index rebuilds.
2. ALTER TABLE ATTACH PARTITION without blocking queries – In PostgreSQL, Every lock has queue. If transaction T2 tries to acquire a lock that is already held by transaction T1 with a conflicting lock level, then transaction T1 will wait in the lock queue. Now something interesting happens: if another transaction T3 comes in, then it will not only have to check for conflict with T1, but also with transaction T2, and any other transaction in the lock queue. So even if your DDL command can run very quickly, it might be in a queue for a long time waiting for queries to finish, and queries that start after it will be blocked behind it. PostgreSQL support partitioning, The partitioning is about splitting logically one large table into several pieces. Partitioning improves query performance. The PostgreSQL partitioning substitutes for leading columns of indexes, reducing index size and making it more likely that the heavily-used parts of the indexes fit in memory. Till PostgreSQL 11, During INSERTs into a partitioned table, the every partition of respective table was locked and it doesn’t even matter if it received a new record or not, At a large data operations scale with larger number of partitions this could become a serious bottleneck. Starting from PostgreSQL 12, When we are inserting a row, only the related partition will be locked. This results in much better performance at higher partition counts, especially when inserting just 1 row at a time.
3. JSON Path support in Postgres 12 – The JSON data-type was introduced in PostgreSQL-9.2 and from there PostgreSQL commitment to JSON data management has increased significantly. The SQL:2016 standard introduced JSON and various ways to query JSON values, The major addition came-up in PostgreSQL-9.4 with the addition of JSONB data-type. JSONB is an advanced version of JSON data-type which stores the JSON data in binary format. PostgreSQL 12 support JSON Path, The JSON Path in PostgreSQL is implemented as jsonpath data type, which is actually the binary representation of parsed SQL/JSON path expression. The main task of the path language is to specify the parts (the projection) of JSON data to be retrieved by path engine for the SQL/JSON query functions. PostgreSQL 12 introduces the ability to run queries over JSON documents using JSON path expressions defined in the SQL/JSON standard. Such queries may utilize the existing indexing mechanisms for documents stored in the JSONB format to efficiently retrieve data.
References
- Gentle Guide to JSONPATH in PostgreSQL – https://github.com/obartunov/sqljsondoc/blob/master/jsonpath.md
- PostgreSQL release notes – https://www.postgresql.org/about/news/1976/
Book for an no obligation consulting with MinervaDB PostgreSQL Team
