PostgreSQL is getting faster! The most popular open-source relational database system has recently been upgraded. There have been significant changes like its previous updates, ranging from speed to security upgrades.

PostgreSQL 14 packs several features that developers have been waiting for a very long time. This update focuses primarily on improving performance and providing users with a faster, more secure, and user-friendly working environment.

If you have missed out on the huge change, no need to worry! Here, we will cover all the major features added to PostgreSQL 14 that developers should know.

What's new in PostgreSQL 14

Double Speed

1. The PostgreSQL 14 speed has doubled. The number of connections has significantly increased. This makes your work way faster and easier and straightforwardly manage heavy workloads. The table's index bloat has been reduced due to the B-tree indexes' major improvement.
2. The pipeline queries ability has grown. The high latency connections are accompanied by the database's improved performance (works with any advanced PostgreSQL database with 14 client versions). This is also valid for small wire operations workloads (e.g., INSERT, UPDATE, and DELETE).

Distributed Workloads Improvement

This upgrade allows streaming in-progress transactions to subscribers and boosts the large transactions applying. The logical decoding system's performance is enhanced, thus powering the logical replication.

Foreign data wrapper upgrade

The latest PostgreSQL release enables you to:

• use Foreign data wrappers to leverage query parallelism
• use Foreign data wrappers to insert bulk data on foreign tables
• import table partitions with the IMPORT FOREIGN SCHEMA directive.

This feature is implemented with postgres_fd.

Tighter Security

PostgreSQL has worked a lot of security issues out before releasing the 14th version and has come up with the following improvements:

• You can assign permission to users on tables, schemas, and views.
• You get the possibility to allow authentications, such as write-only and read-only privileges.
• Your data becomes much more secure, and the chances of data decryption are minimal.
• The predefined roles have also been improved. pg_read_all_data allows the user only to view all the data. pg_write_all_data, in its turn, enables the user to edit the data.
• The updated password management system SCRAM-SHA-256 enables you to make standards-compliant authentication system default in PostgreSQL 14.

Better Support for JSON

Being a significant feature of PostgreSQL since version 9.2, JSON has always been one of the targets for improvement. Now you can:

• access JSON using subscripts
• use subscription frameworks to other nested data structures and the hstore data type
• introduce multi-range data types to write more straightforward queries for sophisticated ranges. Multi ranges are supported by the native range types (e.g. dates, times, and numbers) and other data types when extended.

Improved SQL Performance

PostgreSQL 14 significantly improves query planning with parallelism support. It comes with the following updates:

• Parallel queries execution using the RETURN QUERY command in PL/pgSQL. The parallel sequential scans' performance has increased exponentially.
• REFRESH MATERIALIZED VIEW for the execution of parallel queries.
• The improved performance for queries that use nested loops.
• Extended Statistics.
• Detect slow queries that have bad execution plans.
• Use the CREATE STATISTICS command to create objects.
• Analyze the data using the ANALYZE command, which now works way faster.
• Check the values in pg_statistic_ext_data.
• Enhancement of the Window Functions with incremental sorts.
• Stored Procedures can return the data using the OUT parameter, unlike being used solely for transaction control in a code in previous releases.
• daba_bin aligns or timestamps to a particular interval.
• SEARCH and CYCLE clauses for common table expressions order and cycle detection.

Administration Updates

Monitoring and observation is another focus of PostgreSQL 14. Check the updates here:

• Write-ahead-log (WAL) activity, COPY Commands, and Statistics on replication slots progress tracker.
• Query monitoring via compute_query_id command. Works with multiple PostgreSQL features (e.g. EXPLAIN VERBOSE, pg_stat_activity, etc.)
• Optimized vacuuming system that reduces the overhead from B-Trees. The new "emergency mode" prevents the transaction ID wraparound.
• Configurable TOAST system. The columns compression feature with the pglz compression retention option allows you to store more considerable data volume.
• You can set the default TOAST compression method for compressible columns' values or individual columns using the default_toast_compression (enum) command. Just access the CREATE TABLE or ALTER TABLE options.

How Can Developers Benefit From PostgreSQL 14

• Improved performance. Different features have upgraded various options to reach the maximum speed possible.
• Enhanced developers' experience. PostgreSQL team did a lot of work to improve existing features and add new valuable ones.

PostgreSQL 14 is full of exciting features to be used by developers for years to come. Hopefully, these new updates will help strengthen the PostgreSQL community.