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
