Top New Features in PostgreSQL 14 database

Postgres 14 new features

PostgreSQL 14 has more than 200 improvements and new features that help developers and administrators.

• Postgres 14 onwards, stored procedures can return data via OUT parameters.
• Security change is moving to SCRAM as the default authentication after SCRAM-SHA-256 authentication. Changed the default of the password_encryption server parameter to scram-sha-256.
• Simplifies the process of assigning write-only and read-only capabilities to users on tables, views, and schemas by introducing two predefined roles, pg_write_all_data, and pg_read_all_data.
• PostgreSQL 14 added support for LZ4 compression for TOAST.
• Performance improvements have been made for partitioned tables, logical replication, and vacuuming.
• Add command-line utility pg_amcheck to simplify running contrib/amcheck tests on many relations
• Allow column names in the WHERE clause of ON CONFLICT to be table-qualified.
• VACUUM automatically becomes more aggressive, and skips inessential cleanup, if the database starts to approach a transaction ID wraparound condition.
• Added a predefined role pg_database_owner that contains only the current database's owner.
• Allows pg_hba.conf and pg_ident.conf records to span multiple lines. A backslash at the end of a line allows record contents to be continued on the next line.
• Add an optional timeout parameter to pg_terminate_backend().
• Add OR REPLACE option for CREATE TRIGGER, to allow pre-existing triggers to be conditionally replaced.
• Allow partitions to be detached in a non-blocking manner.  ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY, and FINALIZE.
• Allow REFRESH MATERIALIZED VIEW to use parallelism.
• Allow publications to be more easily added to and removed from a subscription. ALTER SUBSCRIPTION ... ADD/DROP PUBLICATION. This avoids having to specify all publications to add/remove entries.
• Added string_to_table() function to split a string on delimiters.

New Server Parameters in Postgres 14

• Added server parameter idle_session_timeout to close idle sessions.
• New server parameter client_connection_check_interval allows control over whether loss of connection is checked for intra-query, and long-running queries are canceled if the client disconnects.
• Added new read-only server parameter in_hot_standby, to allow clients to easily detect whether they are connected to a hot standby server.
• Added server parameter huge_page_size to control the size of huge pages used on Linux.
• Added server parameter log_recovery_conflict_waits to report long recovery conflict wait times.
• Added server parameter debug_discard_caches to control cache flushing for test purposes.
• New server parameter compute_query_id, with default value auto, will automatically enable query id computation when this extension is loaded.

New System Views in PostgreSQL 14

• pg_stat_progress_copy to report COPY progress.
• pg_stat_wal to report WAL activity.
• pg_stat_statements_info system view to show pg_stat_statements activity.
• pg_stat_replication_slots to report replication slot activity.
• pg_backend_memory_contexts to report session memory usage.

Deprecated/Removed

• Removed the ! operator in Postgres14

Related PostgreSQL Articles: pg_dumpall tool in Postgres   New Features in PostgreSQL 15 database