PostgreSQL 17's JSON_TABLE and VACUUM Overhaul: What's Changed?

JSON_TABLE: Expanding Semi-Structured Data into Relational Tables

A change has been added that intuitively solves a long-standing challenge when storing JSON data in the database. Until now, to extract and query values inside JSON objects or arrays in PostgreSQL, you had to rely on calling jsonb\_array\_elements and chaining arrow operators together. This not only made the code lengthy but also reduced readability, making maintenance difficult.

PostgreSQL 17 now natively supports the standard SQL/JSON JSON\_TABLE function. You can now expand JSON arrays into the rows and columns of a standard relational table within a single query, without complex function chaining.

Here is an example of JSON\_TABLE usage executable in a PostgreSQL 17 environment.

You can now handle tasks that previously required fetching JSON data to the application layer and parsing it with loops, all within a single database query.

VACUUM Memory Structure: Reducing the Burden of Large-Scale Table Management

VACUUM, the most challenging management task in database operations, has also been significantly improved. High memory usage and disk I/O bottlenecks occurring during the cleanup of dead tuples and index reordering have been a long-standing pain point.

PostgreSQL 17 has redesigned the internal memory management structure of VACUUM. The memory consumption for tracking target tuples for deletion has been reduced to up to 1/20th of the previous level. The 1GB cap on maintenance\_work\_mem that existed in previous versions has also been removed. Even in large tables with hundreds of millions of records, cleanup is completed with a stable memory state without unnecessarily splitting index scans multiple times.

Streaming I/O and Query Planner Optimization

Performance improvements in the query execution engine are also worth noting. A new streaming I/O interface has been introduced to pre-read necessary blocks faster when performing sequential scans or gathering statistics.

Query planner optimization rules have also been expanded. B-tree index traversal speed has been improved when multiple search targets are provided in an IN clause, and correlated IN subqueries are now automatically converted to joins at the execution stage to reduce delays caused by repeated calculations. The performance gains are noticeable in aggregation queries dealing with large data or complex conditional statements.

Which Projects Should Adopt This?

If your service stores external API responses or logs in JSONB format and frequently extracts and processes them using SQL, you will gain direct benefits in terms of code conciseness and performance. Since the database treats JSON as a virtual table, the data preprocessing burden on your backend services can be significantly reduced.

It is also suitable for high-volume production environments that have periodically experienced I/O delays during VACUUM due to massive table sizes. By alleviating issues where index cleanup was delayed due to memory constraints, it helps maintain stable system resources. We highly recommend adoption for teams building new architectures or preparing to upgrade from PostgreSQL 14 or 15 versions.