New in PostgreSQL 14: What every developer should know

Summary

PostgreSQL 14 introduces several enhancements to improve application performance, data manipulation, query troubleshooting, and database security. Developers can traverse nested JSON data structures using subscripting and store noncontiguous values using multirange data types in single operations. Applications relying on libpq can leverage client-side query pipelining to send multiple queries across high-latency networks without waiting for individual responses, while parallel execution now supports foreign data wrappers, parallel sequential scans, and materialized view refreshes. For observability, query IDs are integrated into live execution tracking via pg_stat_activity, EXPLAIN VERBOSE outputs, and log files. Security upgrades include predefined read-only roles such as pg_read_all_data alongside SCRAM-SHA-256 enabled as the default authentication mechanism.

Context

Developers and database administrators face challenges including managing complex nested JSON, handling disjointed data ranges without multiple inserts, overcoming network latency during batch operations, correlating query monitoring metrics across system components, and maintaining secure password hashing.

Approach / What changed

PostgreSQL 14 adds native JSON subscripting syntax, multirange column types like tsmultirange, libpq client-side query pipelining, system-wide query identifier tracking via compute_query_id, built-in read-only roles, and default SCRAM-SHA-256 password hashing.

Takeaways

• PostgreSQL 14 enables JSON key traversal via subscripting syntax and permits noncontiguous data mapping through multirange types such as tsmultirange.
• Client-side query pipelining in libpq allows multiple queries to be dispatched simultaneously over a single connection, reducing latency overhead.
• Query identifiers can be enabled via compute_query_id to monitor live queries in pg_stat_activity, log files, and EXPLAIN VERBOSE outputs.

Related reading

• Supabase · Aug 2, 2020

  Continuous PostgreSQL Backups using WAL-G

  Continuous PostgreSQL backups require coordinating physical base snapshots with ongoing write-ahead log archiving to support point-in-time recovery. The open-source WAL-G utility simplifies this workflow by managing backup pushes and fetches to cloud storage services such as Amazon S3. In a typical setup on Ubuntu with PostgreSQL 12, envdir supplies AWS credentials and storage prefixes to WAL-G commands embedded directly in the PostgreSQL archive configuration. Regular base backups can be automated via cron jobs, which minimizes the volume of WAL archives that must be replayed during a restore. When recovering to a new instance, administrators fetch the latest base backup, define a restore command with targeted recovery timestamps in the configuration, and initiate replay using a recovery signal file.

  Angelico de los Reyes

• Supabase · Nov 18, 2020

  Postgres Views

  Postgres views serve as query shortcuts that execute underlying SQL statements upon retrieval without generating new tables or persisting duplicate data. By encapsulating complex multi-table joins, standard views provide query consistency across applications, simplify repetitive calls, improve logical schema organization, and enhance security by restricting sensitive columns. In contrast, materialized views physically store query results on disk, dramatically reducing read latency for heavy queries spanning millions of rows. Because materialized views introduce the trade-off of stale data, administrators must periodically run the refresh command based on workload tolerances for use cases like analytics and internal dashboards. Materialized views should not substitute query optimization, as underlying query efficiency remains essential.