Following the recent launch of PostgreSQL 18 in Cloud SQL, we’re taking a closer look at the new features and performance enhancements now available across four key areas:
- Performance
- Observability
- Developer experience
- Tooling
In this blog post, we explore these areas in depth, providing valuable insights and examples to guide you in leveraging PostgreSQL 18 on Cloud SQL.
Performance improvements
Asynchronous I/O (AIO)
PostgreSQL 18 introduces an asynchronous I/O (AIO) subsystem. This new subsystem increases I/O throughput and reduces I/O latency. According to postgresql.org “PostgreSQL 18 improves performance for workloads of all sizes through a new I/O subsystem that has demonstrated up to 3× performance improvements when reading from storage, and also increases the number of queries that can use indexes”. This means that read-intensive operations like large SELECT queries or VACUUM operations can perform a lot better..
To monitor AIO activity, execute the following query:
select * from pg_aios;
Parallel GIN Index Builds
GIN (Generalized Inverted Index) indexes, commonly used for JSONB and full-text search, can now be built in parallel, significantly reducing the time required for index creation on large datasets.
The following is the sample query to create GIN index:
CREATE INDEX idx_documents_fts
ON documents
USING GIN (to_tsvector('english', title || ' ' || body));
This means faster setup times for applications relying heavily on JSON data or full-text search capabilities.
Enhanced Observability
Understanding the internal workings of your database is crucial for performance tuning and troubleshooting. PostgreSQL 18 introduces new tools and views that provide deeper insights into I/O behavior and database activity.
pg_stat_io View
The new pg_stat_io system view in PostgreSQL 18 now provides granular visibility into I/O performance metrics, including byte-level reporting. This allows DBAs and developers to pinpoint bottlenecks by seeing buffer read/write counts, sync vs. async I/O breakdowns, and device-level stats, now with added `read_bytes`, `write_bytes`, and `extend_bytes` columns.
For more details, you can refer to the official documentation on pg_stat_io.
Monitoring Asynchronous I/O (AIO) Activity with pg_aios
PostgreSQL 18 introduces the pg_aios view, a crucial addition to observability, specifically designed to monitor the new Asynchronous I/O (AIO) subsystem. This view provides real-time insights into the performance and efficiency of AIO operations within your database.
By default, the pg_aios view can be read only by superusers or roles with privileges of the pg_read_all_stats role.
PostgreSQL 18’s Enhanced EXPLAIN
PostgreSQL 18 significantly upgrades the EXPLAIN command, offering richer and more precise insights into query execution.
Key improvements include
- Memory and disk usage details to Material, Window Aggregate, and common table expression nodes to EXPLAIN output, showing resource consumption more clearly
For example.,
-> Materialize (cost=52065.45..53732.20 rows=333350 width=4) (actual time=44.587..46.118 rows=9999.00 loops=1)
Output: a.value
Storage: Memory Maximum Storage: 17kB
..
..
2. It explicitly reports disabled nodes
postgres=> SET enable_seqscan = off ;
SET
postgres=> EXPLAIN SELECT * FROM large_numbers;
QUERY PLAN
------------------------------------------------------------------
Seq Scan on large_numbers (cost=0.00..14425.00 rows=1000000 width=8)
Disabled: true
(2 rows)
It makes the EXPLAIN output a more authentic and direct representation of the query planner’s decision-making process when faced with explicit constraints or disabled features.
3. EXPLAIN ANALYZE now defaults to including BUFFERS output for immediate I/O insights. Furthermore,, and adds “WAL Buffers Full” information to EXPLAIN (WAL).
Developer Experience
Virtual Generated Columns
Traditionally, when you created a GENERATED ALWAYS AS column, its value was computed and stored on disk whenever a row was inserted or updated. This meant:
- Increased storage space: The calculated value took up physical space.
- Slower writes: The calculation had to happen during INSERT or UPDATE operations, adding overhead to write performance.
With PostgreSQL 18, the default behavior for generated columns shifts to virtual. This has several implications:
- Values Generated on Read (Default Behavior):
- The generated column’s value is computed dynamically every time you query (read) it.
- It is not stored on disk.
- This saves storage space and reduces the overhead during write operations (e.g., INSERT, UPDATE), making them faster.
Support for UUIDv7
Native support for UUIDv7 provides universally unique identifiers that are also chronologically sortable. This is a significant improvement for indexing performance, as sequential inserts reduce B-tree page splits compared to random UUIDs.
postgres=> CREATE TABLE events (
event_id UUID DEFAULT uuidv7() PRIMARY KEY,
event_data TEXT,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE TABLE
postgres=> INSERT INTO events (event_data) VALUES ('User logged in');
INSERT 0 1
postgres=> select event_id, event_data from events;
event_id | event_data
--------------------------------------+----------------
01994464-2ea5-7510-a739-44911d204f9d | User logged in
(1 row)
postgres=>
Queries ordering by event_id will implicitly order by creation time, leveraging efficient index scans.
Enhanced RETURNING Clause
The RETURNING clause is enhanced to allow more flexible access to both old and new values in DML operations, making it easier to track changes and perform audits.
Example: To see both the original and updated email address after an UPDATE:
postgres=>
postgres=> select * from users;
user_id | email
---------+-----------------
1 | abc@example.com
(1 row)
postgres=>
postgres=> UPDATE users
SET email = 'new_email@example.com'
WHERE user_id=1
RETURNING OLD.email AS previous_email, NEW.email AS current_email;
previous_email | current_email
-----------------+-----------------------
abc@example.com | new_email@example.com
(1 row)
UPDATE 1
Tooling
Enhanced pg_dump and pg_restore
PostgreSQL 18 enhances the pg_dump/pg_restore utility with new, highly specific options for controlling what data elements are restored from a backup archive. The flags are:
- — with-data / — no-data: Explicitly include or exclude the actual table rows (the data).
- — with-schema / — no-schema: Explicitly include or exclude the database structure (tables, indexes, views, functions, etc.).
- — with-statistics / — no-statistics: Explicitly include or exclude the optimizer statistics collected by ANALYZE, a crucial feature for ensuring good query performance immediately post-restore.
Summary
In summary, PostgreSQL 18 introduces significant advancements in performance, observability, developer experience, and tooling. These enhancements are designed to streamline your database operations and improve database management capabilities. Refer to the official release notes for a complete list of new features and detailed information. We are excited to make PostgreSQL 18 available in Cloud SQL!
We encourage you to try Cloud SQL PostgreSQL 18 today and experience the benefits of these powerful updates. For more information about Cloud SQL, explore the Cloud SQL for PostgreSQL Managed Database section. To create your Cloud SQL PostgreSQL 18 instance, click here.
What’s new in PostgreSQL 18, now available in Cloud SQL was originally published in Google Cloud – Community on Medium, where people are continuing the conversation by highlighting and responding to this story.
Source Credit: https://medium.com/google-cloud/whats-new-in-postgresql-18-now-available-in-cloud-sql-1ebb362e080a?source=rss—-e52cf94d98af—4
About the Author
