The PostgreSQL development community is buzzing with excitement as the latest updates from PostgreSQL 18 continue to unfold. The January CommitFest has introduced several significant enhancements aimed at improving monitoring capabilities and adding new features that will benefit database administrators and developers alike.

For those interested in the ongoing development of PostgreSQL 18, previous reviews of CommitFests can be found here: 2024-07, 2024-09, 2024-11.

EXPLAIN (Analyze): Buffers Now Shown by Default

One of the more noteworthy changes stems from a detailed discussion within the community regarding the output of the EXPLAIN ANALYZE command. The decision was made to display buffer information by default upon execution. This marks a departure from prior versions where users had to manually enable this information.

As an example, running the command:

EXPLAIN (analyze, costs off) SELECT * FROM bookings;

will yield output similar to the following:

QUERY PLAN ------------------------------------------------------------------------ Seq Scan on bookings (actual time=0.086..119.142 rows=2111110 loops=1) Buffers: shared hit=160 read=13287 Planning: Buffers: shared hit=57 Planning Time: 0.543 ms Execution Time: 180.270 ms (6 rows)

While many users welcomed this update, a part of the commit message noted some discord regarding this change, with suggestions for a rollback if significant resistance arises before the official release.

For further insights, refer to Waiting for PostgreSQL 18 Enable BUFFERS with EXPLAIN ANALYZE by default by Hubert depesz Lubaczewski.

New Columns in pg_stat_io View

In another significant enhancement, the pg_stat_io view has been updated with three new columns: read_bytes, write_bytes, and extend_bytes. These columns are essential as they provide metrics for the volumes of read, write, and extend operations in bytes, making it easier for users to track I/O performance.

Previously, users had to multiply the op_bytes column by the respective operation counts to derive these metrics. Now, with dedicated columns for volume tracking, the op_bytes column has been removed for greater clarity.

This enhancement enables the tracking of additional I/O operations unrelated to the buffer cache, particularly Write-Ahead Logging (WAL) operations, where the volume of a transaction doesn't correlate with page size.

The introduction of the io_combine_limit parameter in PostgreSQL 17 allows for the grouping of read operations into larger chunks, accommodating various types of scans like sequential scans and analyses. To illustrate, one could run a small script to test the io_combine_limit as follows:

$ cat pg18/pg_stat_io.sql

Enhanced I/O Tracking for WAL Operations

Thanks to recent patches, I/O statistics can now be monitored independently from page sizes, which opens up the possibility of tracking WAL statistics in the pg_stat_io view. This tracking occurs across various processes and is categorized into two contexts: normal operations with existing WAL segments and operations involving the initialization of a new WAL segment.

Heres an example of how to query these statistics:

SELECT backend_type, object, context, writes, write_bytes, pg_size_pretty(round(write_bytes/writes)) bytes_per_op FROM pg_stat_io WHERE object = 'wal' AND writes > 0;

Visibility and Statistics Updates

Further improvements include the addition of new columns in the pg_stat_all_tables view, which now tracks total vacuum and analyze times per table. This enhancement gives users insights into performance, allowing them to see which tables consume the most time during these maintenance processes. A sample query could look like this:

SELECT relname, vacuum_count, total_vacuum_time, total_autovacuum_time FROM pg_stat_all_tables ORDER BY total_autovacuum_time + total_vacuum_time DESC LIMIT 1;

Moreover, the pg_visibility extension has been updated to display visibility map statistics post-vacuum, providing greater transparency on how many pages are marked as all-visible during this process.

Improvements to Password Security and UUID Generation

Security enhancements are a priority in this update as well. With the deprecation of the MD5 password encryption method, users are cautioned against its use in future releases. PostgreSQL 18 still supports MD5 but warns users to transition to stronger encryption methods.

Additionally, a new UUID generation function has been introduced. The uuidv7 function sequentially generates UUIDs based on Unix-epoch timestamps, facilitating ordered generation of unique identifiers, which is particularly useful for logs and time-sensitive applications.

Lastly, the ability to set a minimum password length has also been incorporated, ensuring that all new passwords meet security requirements.

As PostgreSQL 18 continues to evolve, these updates promise to enhance functionality, security, and performance for users worldwide. Stay tuned for more updates as we approach the final release of this version!