PostgreSQL® 14: PostgreSQL Is Still a Teenager

Performance Improvements in PostgreSQL 14

  • REFRESH MATERIALIZED VIEW
  • RETURN QUERY
  • Queries to foreign data wrappers
  • ALTER TABLE DETACH PARTITION CONCURRENTLY FINALIZE
  • Allow index commands using CONCURRENTLY to avoid waiting for the completion of other operations using CONCURRENTLY
  • CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY no longer limit the dead row removal of other relations.

Visibility Improvements

  • Status of a COPY operation in pg_stat_progress_copy
  • Status of the write ahead log is in pg_stat_wal
  • Status of replication is now in pg_stat_replication_slots
  • Status of the backend query handling is in pg_backend_memory_contexts, which you may also force into the log using pg_log_backend_memory_contexts
  • New columns in pg_stat_database
  • Added the archiver to pg_stat_activity
  • Added lock wait start time to pg_locks
  • Added a view in information_schema called routine_column_usage to track columns referenced by function and procedure default expressions.
  • Added %P to log_line_prefix to see the group leader PID
  • Added server parameter log_recovery_conflict_waits to report long recovery conflict wait times
  • Added function pg_get_wal_replay_pause_state() to report the recovery state
  • Added function pg_xact_commit_timestamp_origin() to return the commit timestamp and replication origin of the specified transaction
  • Added the replication origin to the record returned by pg_last_committed_xact()
  • pg_stat_statements tracks top and nested statements separately
  • Added cumulative -verbose (-vvv) flags to several utilities for screen logging

Other Improvements in PostgreSQL 14

What Will PostgreSQL Be When it Grows Up?

--

--

Managed platform for open source technologies including Apache Cassandra, Apache Kafka, Apache ZooKeepere, Redis, Elasticsearch and PostgreSQL

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Instaclustr

Instaclustr

Managed platform for open source technologies including Apache Cassandra, Apache Kafka, Apache ZooKeepere, Redis, Elasticsearch and PostgreSQL