{"id":3324,"date":"2025-12-25T19:00:41","date_gmt":"2025-12-25T19:00:41","guid":{"rendered":"https:\/\/bynatree.com\/?p=2927"},"modified":"2025-12-25T19:00:41","modified_gmt":"2025-12-25T19:00:41","slug":"new-features-added-in-postgresql-18","status":"publish","type":"post","link":"https:\/\/divaind.com\/ie1\/2025\/12\/25\/new-features-added-in-postgresql-18\/","title":{"rendered":"New Features added in PostgreSQL 18"},"content":{"rendered":"<h1><b>PostgreSQL 18: Revolutionary Changes in Database Performance and Functionality-\u00a0<\/b><\/h1>\n<blockquote><p><span style=\"font-weight: 400;\"><a href=\"https:\/\/www.postgresql.org\/\">PostgreSQL 18<\/a>, <a href=\"https:\/\/www.postgresql.org\/about\/news\/postgresql-18-released-3142\/\">released<\/a> on <a href=\"https:\/\/database.news\/\">September 25, 2025,<\/a> is one of the most significant updates in years. It brings major improvements to I\/O, query processing, and data management, offering enhanced performance and powerful new features for developers, <a href=\"https:\/\/divaind.com\/ie1\/services\/remote-database-administrator\/\">DBAs<\/a>, and <a href=\"http:\/\/bynatree.com\">organizations<\/a>.<\/span><\/p><\/blockquote>\n<h2><b>These are the Game-Changing Performance Enhancements in <span style=\"font-weight: 400;\"><a href=\"https:\/\/www.postgresql.org\/\">PostgreSQL 18<\/a><\/span><\/b><\/h2>\n<ul>\n<li aria-level=\"1\"><b>Asynchronous I\/O Subsystem: The Most Significant Addition<\/b><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">PostgreSQL 18 introduces a powerful new asynchronous I\/O (AIO) subsystem, allowing parallel data reads and reducing wait times. This marks a major shift from the previous synchronous I\/O, significantly improving performance<\/span><\/p>\n<ul>\n<li aria-level=\"1\"><b>Key Benefits of Asynchronous I\/O:<\/b><\/li>\n<\/ul>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Increased I\/O Throughput<\/b><span style=\"font-weight: 400;\">: Multiple read requests can be queued and processed simultaneously<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Reduced Latency<\/b><span style=\"font-weight: 400;\">: I\/O operations no longer block query execution<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Better Resource Utilization<\/b><span style=\"font-weight: 400;\">: More efficient use of storage subsystems<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Enhanced Performance<\/b><span style=\"font-weight: 400;\">: Particularly beneficial for sequential scans, bitmap heap scans, and vacuum operations<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li aria-level=\"1\"><b>Configuration Options:<\/b><\/li>\n<\/ul>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>io_method<\/b><span style=\"font-weight: 400;\">: Controls the I\/O method (enables AIO)<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>io_combine_limit<\/b><span style=\"font-weight: 400;\"> and <\/span><b>io_max_combine_limit<\/b><span style=\"font-weight: 400;\">: Control I\/O operation combining<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>pg_aios<\/b><span style=\"font-weight: 400;\">: New system view to monitor asynchronous I\/O file handles<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h3><b>Asynchronous I\/O Configuration in PostgreSQL 18<\/b><\/h3>\n<p><b>Check the current I\/O method<\/b><\/p>\n<pre class=\"theme:solarized-light lang:default decode:true \">testdb=# SHOW io_method;\n(1 row)\nio_method\n-----------\nworker<\/pre>\n<ul>\n<li><b>Enable asynchronous I\/O<\/b><\/li>\n<\/ul>\n<pre class=\"theme:solarized-light lang:default decode:true\">testdb=# SHOW io_combine_limit;\nio_combine_limit\n------------------\n128kB\n(1 row)\ntestdb=# SHOW io_max_combine_limit;\nio_max_combine_limit\n----------------------\n128kB\n(1 row)<\/pre>\n<ul>\n<li aria-level=\"1\"><b>Monitor asynchronous I\/O handles<\/b><\/li>\n<\/ul>\n<pre class=\"theme:solarized-light lang:default decode:true\">testdb=# SELECT * FROM pg_aios LIMIT 5;\npid | io_id | io_generation | state | operation | off | length | target | handle_data_len | raw_result | result | target_desc | f_sync | f_localmem | f_buffer\ned\n-----+-------+---------------+-------+-----------+-----+--------+--------+-----------------+------------+--------+-------------+--------+------------+---------\n---\n(0 rows)<\/pre>\n<ul>\n<li aria-level=\"1\"><b>Parallel GIN Index Building:<\/b><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">PostgreSQL 18 adds parallel builds for GIN indexes, speeding up index creation for JSON and full-text search data.<\/span><\/p>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">JSON and JSONB data<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Full-text search columns<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Array data types<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Other complex data structure<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li aria-level=\"1\"><b>Parallel GIN Index Building<\/b><\/li>\n<\/ul>\n<pre class=\"theme:solarized-light lang:default decode:true\">testdb=# CREATE TABLE docs(id serial, content jsonb);\nCREATE TABLE\ntestdb=# INSERT INTO docs (content)\ntestdb-# VALUES ('{\"key\": \"value\", \"count\": 10}'::jsonb);\nINSERT 0 1\ntestdb=# SELECT jsonb_build_object('text', md5(random()::text)) FROM generate_series(1, 50000);\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 jsonb_build_object\n----------------------------------------------\n{\"text\": \"75ea8a8788ab2f5817cb68499ae581f0\"}\n{\"text\": \"46932c038a966472df32fa4937e8aa65\"}\n{\"text\": \"d479ddb89fd7d1d9355bc723632350f1\"}\n{\"text\": \"722227f05df276d241849e376f473b77\"}<\/pre>\n<ul>\n<li aria-level=\"1\"><b>Parallel GIN index creation<\/b><\/li>\n<\/ul>\n<p style=\"padding-left: 80px;\"><b>Set the Session Parallelism<\/b><\/p>\n<pre class=\"theme:solarized-light lang:default decode:true\">testdb=# SET max_parallel_maintenance_workers = 4;\nSET<\/pre>\n<p style=\"padding-left: 80px;\"><b>\u00a0 Create Parallel GIN index\u00a0<\/b><\/p>\n<pre class=\"theme:solarized-light lang:default decode:true\">testdb=# CREATE INDEX gin_docs_idx ON docs USING gin (content jsonb_path_ops);\nCREATE INDEX<\/pre>\n<p style=\"padding-left: 80px;\"><b> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 &#8211;\u00a0 Verify in pg_stat_progress_create_index<\/b><\/p>\n<pre class=\"theme:solarized-light lang:default decode:true \">testdb=# SELECT * FROM pg_stat_progress_create_index WHERE command = 'CREATE INDEX';\npid | datid | datname | relid | index_relid | command | phase | lockers_total | lockers_done | current_locker_pid | blocks_total | blocks_done | tuples_total\n| tuples_done | partitions_total | partitions_done\n-----+-------+---------+-------+-------------+---------+-------+---------------+--------------+--------------------+--------------+-------------+--------------\n+-------------+------------------+-----------------\n(0 rows)<\/pre>\n<p>&nbsp;<\/p>\n<ul>\n<li aria-level=\"1\"><b>Advanced Query Optimization Improvements in<span style=\"font-weight: 400;\"> <a href=\"https:\/\/www.postgresql.org\/\">PostgreSQL 18<\/a>,<\/span><\/b><\/li>\n<\/ul>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Self-Join Elimination<\/b><span style=\"font-weight: 400;\">: PostgreSQL 18 can automatically remove unnecessary table self-joins, simplifying query execution plans and improving performance.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>\u00a0Enhanced OR-Clause Processing<\/b><span style=\"font-weight: 400;\">: PostgreSQL 18&#8217;s optimizer now turns OR clauses into arrays, making index use faster and queries more efficient.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>\u00a0Right Semi Join Support<\/b><span style=\"font-weight: 400;\">: PostgreSQL 18 adds Hash Right Semi Join, letting the planner choose which table to hash based on size, improving semi-join performance.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>\u00a0Skip Scans for B-tree Indexes<\/b><span style=\"font-weight: 400;\">: Multi-column B-tree indexes can now be used by queries that only reference the second or later indexed columns through skip scan functionality.<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li aria-level=\"1\"><b>Self-Join Elimination Example<\/b><\/li>\n<\/ul>\n<pre class=\"theme:solarized-light lang:default decode:true\">testdb=# INSERT INTO employees VALUES (1, NULL, 'CEO'), (2, 1, 'Manager'), (3, 2, 'Developer');\nINSERT 0 3\ntestdb=# EXPLAIN SELECT e1.name, e2.name AS manager FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id;\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 QUERY PLAN\n-----------------------------------------------------------------------------\nMerge Join\u00a0 (cost=166.75..280.75 rows=7200 width=64)\n\u00a0 Merge Cond: (e1.manager_id = e2.id)\n\u00a0 -&gt;\u00a0 Sort\u00a0 (cost=83.37..86.37 rows=1200 width=36)\n\u00a0 \u00a0 \u00a0 \u00a0 Sort Key: e1.manager_id\n\u00a0 \u00a0 \u00a0 \u00a0 -&gt;\u00a0 Seq Scan on employees e1\u00a0 (cost=0.00..22.00 rows=1200 width=36)\n\u00a0 -&gt;\u00a0 Sort\u00a0 (cost=83.37..86.37 rows=1200 width=36)\n\u00a0 \u00a0 \u00a0 \u00a0 Sort Key: e2.id\n\u00a0 \u00a0 \u00a0 \u00a0 -&gt;\u00a0 Seq Scan on employees e2\u00a0 (cost=0.00..22.00 rows=1200 width=36)\n(8 rows)<\/pre>\n<ul>\n<li aria-level=\"1\"><b>Skip Scan on Multi-column Index<\/b><\/li>\n<\/ul>\n<pre class=\"theme:solarized-light lang:default decode:true\">testdb=# CREATE TABLE orders(order_id int, customer_id int, order_date date);\nCREATE TABLE\ntestdb=# CREATE INDEX orders_idx ON orders(customer_id, order_date);\nCREATE INDEX<\/pre>\n<ul>\n<li aria-level=\"1\"><b>Query using skip scan (only order_date)<\/b><\/li>\n<\/ul>\n<pre class=\"theme:solarized-light lang:default decode:true \">testdb=# EXPLAIN SELECT * FROM orders WHERE order_date &gt; CURRENT_DATE - INTERVAL '30 days';\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 QUERY PLAN\n----------------------------------------------------------------------------\nBitmap Heap Scan on orders\u00a0 (cost=19.63..41.53 rows=680 width=12)\n\u00a0 Recheck Cond: (order_date &gt; (CURRENT_DATE - '30 days'::interval))\n\u00a0 -&gt;\u00a0 Bitmap Index Scan on orders_idx\u00a0 (cost=0.00..19.46 rows=680 width=0)\n\u00a0 \u00a0 \u00a0 \u00a0 Index Cond: (order_date &gt; (CURRENT_DATE - '30 days'::interval))\n(4 rows)<\/pre>\n<h2><b>These are the Advanced Data Management Features in PostgreSQL 18<\/b><\/h2>\n<ul>\n<li aria-level=\"1\"><b>Virtual Generated Columns:<\/b><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">PostgreSQL 18 introduces virtual generated columns as the default behavior, representing a significant shift from the previous stored-only approach:<\/span><\/p>\n<ul>\n<li aria-level=\"1\"><b>Virtual vs. Stored Generated Columns:<\/b><\/li>\n<\/ul>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Virtual<\/b><span style=\"font-weight: 400;\">: Values are computed when the column is read (default in PostgreSQL 18)<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Stored<\/b><span style=\"font-weight: 400;\">: Values are computed and stored when the row is written (available via STORED option)<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li aria-level=\"1\"><b>Benefits of Virtual Generated Columns:<\/b><\/li>\n<\/ul>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Reduced storage requirements<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Always up-to-date values without maintenance overhead<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Better performance for columns that are read infrequently<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ol>\n<li><b> Virtual Generated Columns<\/b><\/li>\n<\/ol>\n<pre class=\"theme:solarized-light lang:default decode:true\">testdb=# CREATE TABLE sales (\ntestdb(# \u00a0 id serial,\ntestdb(# \u00a0 price numeric,\ntestdb(# \u00a0 quantity int,\ntestdb(# \u00a0 total numeric GENERATED ALWAYS AS (price * quantity) VIRTUAL\ntestdb(# );\nCREATE TABLE\ntestdb=# INSERT INTO sales (price, quantity) VALUES (100, 5), (200, 2);\nINSERT 0 2\ntestdb=# SELECT * FROM sales;\nid | price | quantity | total\n----+-------+----------+-------\n\u00a0 1 | \u00a0 100 |\u00a0 \u00a0 \u00a0 \u00a0 5 | \u00a0 500\n\u00a0 2 | \u00a0 200 |\u00a0 \u00a0 \u00a0 \u00a0 2 | \u00a0 400\n(2 rows)<\/pre>\n<ul>\n<li aria-level=\"1\"><b>Enhanced UUID Support:<\/b><\/li>\n<\/ul>\n<p><b>UUIDv7 Generation<\/b><span style=\"font-weight: 400;\">: PostgreSQL 18introduces <\/span><b>uuidv7(<\/b><b>)<\/b><span style=\"font-weight: 400;\">, a new time-sortable UUID version that provides globally unique, non-predictable IDs ideal for ordered data storage and retrieval:\u00a0<\/span><\/p>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Time-series data<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Distributed systems requiring ordered identifiers<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Primary keys where chronological ordering is beneficial<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p><b>Time-sortable UUID: <\/b><b>ChatGPT said:<\/b><\/p>\n<p><b>Time-sortable UUIDs: <\/b><span style=\"font-weight: 400;\">It<\/span> <span style=\"font-weight: 400;\">include a time-based component, allowing efficient sorting and improving database performance compared to random UUIDs like UUIDv4<\/span><\/p>\n<ul>\n<li aria-level=\"1\"><b>uuidv7() example<\/b><\/li>\n<\/ul>\n<pre class=\"theme:solarized-light lang:default decode:true \">testdb=# SELECT uuidv7();\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 uuidv7\n--------------------------------------\n0199ca8c-692d-769d-812c-5f9054168fa1\n(1 row)<\/pre>\n<p>&nbsp;<\/p>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Time-sortable UUID example<\/b><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\">\n<pre class=\"theme:solarized-light lang:default decode:true\">testdb=# SELECT uuidv7() AS time_sortable_uuid;\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 time_sortable_uuid\n--------------------------------------\n0199ca8d-cbd0-7c4a-9afb-97a5266c2505\n(1 row)<\/pre>\n<p><b>Explicit UUIDv4 Generation<\/b><span style=\"font-weight: 400;\">: The <\/span><span style=\"font-weight: 400;\">UUIDv4 is a &#8220;purely random&#8221; 128-bit identifier, mostly generated from high-quality randomness, with a few bits set by the RFC 9562 standard to define version and variant.<\/span><\/li>\n<\/ol>\n<pre class=\"theme:solarized-light lang:default decode:true\">testdb=# SELECT uuidv4() AS random_uuid;\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 random_uuid\n--------------------------------------\n9945163f-616f-467e-9ece-fb110b2cb6e0\n(1 row)<\/pre>\n<ul>\n<li aria-level=\"1\"><b>Improved NOT NULL Constraint Management:<\/b><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">Improved <\/span><b>NOT NULL constraint management <\/b><span style=\"font-weight: 400;\">is active in recent PostgreSQL versions (particularly since PostgreSQL 11, with further enhancements in 17 and 18), focusing on two main areas:<\/span><\/p>\n<ol>\n<li style=\"list-style-type: none;\">\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Zero-downtime schema changes for large tables.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Query performance optimization by leveraging constraint knowledge.<\/span><\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<ul>\n<li aria-level=\"1\"><b>NOT NULL Constraint Improvements<\/b><\/li>\n<\/ul>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\">\n<pre class=\"theme:solarized-light lang:default decode:true \">testdb=# CREATE TABLE customers (\ntestdb(# \u00a0 id serial PRIMARY KEY,\ntestdb(# \u00a0 name text,\ntestdb(# \u00a0 email text\ntestdb(# );\nCREATE TABLE<\/pre>\n<p><b>Named NOT NULL Constraints<\/b><span style=\"font-weight: 400;\">: You can now assign names to NOT NULL constraints, improving error messages and constraint management.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>NOT VALID Support<\/b><span style=\"font-weight: 400;\">:<\/span><span style=\"font-weight: 400;\"> NOT NULL constraints can be marked as NOT VALID, allowing for non-blocking constraint addition on large tables.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Inheritance Control<\/b><span style=\"font-weight: 400;\">: <\/span><span style=\"font-weight: 400;\">NOT NULL constraints can now be made inheritable or non-inheritable on partitioned tables.<\/span><\/li>\n<\/ol>\n<ul>\n<li aria-level=\"1\"><b>Add NOT NULL constraint with a name and NOT VALID option<\/b><\/li>\n<\/ul>\n<pre class=\"theme:solarized-light lang:default decode:true\">testdb=# ALTER TABLE customers ADD CONSTRAINT nn_email CHECK (email IS NOT NULL) NOT VALID;\nALTER TABLE<\/pre>\n<ul>\n<li aria-level=\"1\"><b>Validate constraint later<\/b><\/li>\n<\/ul>\n<pre class=\"theme:solarized-light lang:default decode:true\">testdb=# ALTER TABLE customers VALIDATE CONSTRAINT nn_email;\nALTER TABLE<\/pre>\n<h2><b>Enhanced Developer Experience in <span style=\"font-weight: 400;\"><a href=\"https:\/\/www.postgresql.org\/\">PostgreSQL 18<\/a><\/span><\/b><\/h2>\n<ul>\n<li aria-level=\"1\"><b>RETURNING Clause Enhancements<\/b><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">The RETURNING clause now supports OLD\/NEW syntax, allowing developers to access both old and new values in DML operations:<\/span><\/p>\n<p><b>This feature is particularly useful for:<\/b><\/p>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">\u00a0Aduit trails<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">\u00a0Change tracking<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">\u00a0Data migration scenarios<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>RETURNING Clause with OLD\/NEW<\/b><\/li>\n<\/ul>\n<ul>\n<li aria-level=\"1\">\n<pre class=\"theme:solarized-light lang:default decode:true \">testdb=# CREATE TABLE employees_audit(id serial, name text, salary numeric);\nCREATE TABLE\ntestdb=# INSERT INTO employees_audit(name, salary) VALUES ('Alice', 50000);\nINSERT 0 1\ntestdb=#\ntestdb=# UPDATE employees_audit\ntestdb-# SET salary = salary * 1.1\ntestdb-# RETURNING old.salary AS old_salary, new.salary AS new_salary;\nold_salary | new_salary\n------------+------------\n\u00a0 \u00a0 \u00a0 50000 |\u00a0 \u00a0 55000.0\n(1 row)<\/pre>\n<p>&nbsp;<\/p>\n<p><b>Advanced JSON Functionality<\/b><\/li>\n<\/ul>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Improved NULL Handling<\/b><span style=\"font-weight: 400;\">: JSONB null values can now be cast to scalar types as NULL, improving flexibility in JSON data processing.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Enhanced Array Processing<\/b><span style=\"font-weight: 400;\">: New optional parameters in json{b}_strip_nulls() allow removal of null array elements.<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li aria-level=\"1\"><b>JSON Enhancements<\/b><\/li>\n<\/ul>\n<pre class=\"theme:solarized-light lang:default decode:true\">testdb=# CREATE TABLE json_test(data jsonb);\nCREATE TABLE\ntestdb=# INSERT INTO json_test VALUES ('{\"a\":1,\"b\":null,\"c\":[1,null,3]}');\nINSERT 0<\/pre>\n<ul>\n<li aria-level=\"1\"><b>Remove null array elements<\/b><\/li>\n<\/ul>\n<pre class=\"theme:solarized-light lang:default decode:true\">testdb=# SELECT jsonb_strip_nulls(data, true) FROM json_test;\n\u00a0 jsonb_strip_nulls\n-----------------------\n{\"a\": 1, \"c\": [1, 3]}\n(1 row)<\/pre>\n<ul>\n<li aria-level=\"1\"><b>NULL casting example<\/b><\/li>\n<li aria-level=\"1\">\n<pre class=\"theme:solarized-light lang:default decode:true \">testdb=# SELECT (data-&gt;&gt;'b')::int IS NULL AS null_cast_check FROM json_test;\nnull_cast_check\n-----------------\nt\n(1 row)<\/pre>\n<p><b>New Array Functions:<\/b><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>array_sort()<\/b><span style=\"font-weight: 400;\">: Sorts an array&#8217;s first dimension<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>array_reverse()<\/b><span style=\"font-weight: 400;\">: Reverses an array&#8217;s first dimension<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>reverse()<\/b><span style=\"font-weight: 400;\">: Reverses bytea bytes<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>New Array Functions<\/b><\/li>\n<\/ul>\n<pre class=\"theme:solarized-light lang:default decode:true\">testdb=# SELECT array_sort(ARRAY[5,2,9,1]) AS sorted,\n\u00a0 \u00a0 \u00a0 array_reverse(ARRAY[1,2,3,4]) AS reversed;\n\u00a0 sorted \u00a0 | reversed\n-----------+-----------\n{1,2,5,9} | {4,3,2,1}\n(1 row)\n\ntestdb=# SELECT reverse('PostgreSQL18'::bytea);\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 reverse\n----------------------------\n\\x38314c515365726774736f50\n(1 row)<\/pre>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Case Folding Support<\/b><span style=\"font-weight: 400;\">: The new casefold() function enables advanced, language-aware case-insensitive text matching beyond simple lower\/upper conversions.<\/span><\/li>\n<\/ol>\n<ul>\n<li aria-level=\"1\"><b>Case Folding<\/b><\/li>\n<\/ul>\n<pre class=\"theme:solarized-light lang:default decode:true\">testdb=# SELECT lower('Stra\u00dfe') AS folded_german,\ntestdb-#\u00a0 \u00a0 \u00a0 \u00a0 lower('\u0130stanbul') AS folded_turkish;\nfolded_german | folded_turkish\n---------------+----------------\nstra\u00dfe\u00a0 \u00a0 \u00a0 \u00a0 | istanbul\n(1 row)\n\nTime: 17.053 ms<\/pre>\n<h2><b><a href=\"https:\/\/en.wikipedia.org\/wiki\/Authentication\">Security and Authentication<\/a> Improvements <span style=\"font-weight: 400;\"><a href=\"https:\/\/www.postgresql.org\/\">PostgreSQL 18<\/a><\/span><\/b><\/h2>\n<ul>\n<li aria-level=\"1\"><b>OAuth (open-authorization) Authentication Support:<\/b><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">PostgreSQL 18 introduces built-in Open-Authorization authentication support, modernizing authentication capabilities:<\/span><\/p>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">OAuth authentication method in pg_hba.conf<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">libpq OAuth connection options<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Integration with modern identity providers<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li aria-level=\"1\"><b>Enhanced SSL\/TLS Configuration<\/b><\/li>\n<\/ul>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>TLS 1.3 Cipher Suites<\/b><span style=\"font-weight: 400;\">: The New ssl_tls13_ciphers parameter allows specification of multiple TLS 1.3 cipher suites.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Extended Curve Support<\/b><span style=\"font-weight: 400;\">: The ssl_groups parameter (renamed from ssl_ecdh_curve) now supports multiple elliptic curves, including X25519.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Security Key Improvements<\/b><span style=\"font-weight: 400;\">: Cancel request keys are now 256 bits, enhancing security when using wire protocol version 3.2.<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li aria-level=\"1\"><b>Deprecation of MD5 Passwords<\/b><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">PostgreSQL 18 officially deprecates MD5 password authentication:<\/span><\/p>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">CREATE ROLE and ALTER ROLE now emit warnings for MD5 passwords<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">MD5 support will be removed in a future major version<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Organizations should migrate to SCRAM-SHA-256 authentication<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h2><b>Monitoring and Observability in PostgreSQL 18<\/b><\/h2>\n<ul>\n<li aria-level=\"1\"><b>Enhanced Statistics Collection:<\/b>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Detailed I\/O Statistics<\/b><span style=\"font-weight: 400;\">: New columns in pg_stat_io report I\/O activity in bytes (read_bytes, write_bytes, extend_bytes).<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>WAL I\/O Monitoring<\/b><span style=\"font-weight: 400;\">: WAL receiver activity is now tracked in pg_stat_io with dedicated wait events.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Parallel Worker Statistics<\/b><span style=\"font-weight: 400;\">: New columns in pg_stat_database track parallel worker launch and execution metrics.<\/span><\/li>\n<\/ul>\n<\/li>\n<li aria-level=\"1\"><b>Enhanced I\/O Stats<\/b><\/li>\n<\/ul>\n<pre class=\"theme:solarized-light lang:default decode:true\">testdb=# SELECT backend_type,\ntestdb-#\u00a0 \u00a0 \u00a0 \u00a0 object,\ntestdb-#\u00a0 \u00a0 \u00a0 \u00a0 read_bytes,\ntestdb-#\u00a0 \u00a0 \u00a0 \u00a0 write_bytes\ntestdb-# FROM pg_stat_io\ntestdb-# WHERE read_bytes &gt; 0;\n\u00a0 backend_type\u00a0 \u00a0 |\u00a0 object\u00a0 | read_bytes | write_bytes\n-------------------+----------+------------+-------------\nclient backend\u00a0 \u00a0 | relation |\u00a0 \u00a0 5505024 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0\nautovacuum worker | relation | \u00a0 \u00a0 106496 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0\nstartup \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | wal\u00a0 \u00a0 \u00a0 |\u00a0 \u00a0 \u00a0 32768 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0\n(3 rows)\n\nTime: 65.021 ms<\/pre>\n<ul>\n<li aria-level=\"1\"><b>WAL I\/O Monitoring<\/b><\/li>\n<\/ul>\n<pre class=\"theme:solarized-light lang:default decode:true\">testdb=# SELECT *\ntestdb-# FROM pg_stat_io\ntestdb-# WHERE object = 'WAL';\nbackend_type | object | context | reads | read_bytes | read_time | writes | write_bytes | write_time | writebacks | writeback_time | extends | extend_bytes |\nextend_time | hits | evictions | reuses | fsyncs | fsync_time | stats_reset\n--------------+--------+---------+-------+------------+-----------+--------+-------------+------------+------------+----------------+---------+--------------+-\n------------+------+-----------+--------+--------+------------+-------------\n(0 rows)\n\nTime: 7.194 ms\ntestdb=# SELECT DISTINCT object FROM pg_stat_io;\n\u00a0 \u00a0 object\n---------------\nwal\ntemp relation\nrelation\n(3 rows)<\/pre>\n<ul>\n<li aria-level=\"1\"><b>Improved EXPLAIN Functionality:<\/b><\/li>\n<\/ul>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Automatic BUFFERS Output<\/b><span style=\"font-weight: 400;\">: EXPLAIN ANALYZE now automatically includes buffer usage information.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Enhanced Node Details<\/b><span style=\"font-weight: 400;\">: Additional memory and disk usage details for Material, Window Aggregate, and CTE nodes.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Fractional Row Counts<\/b><span style=\"font-weight: 400;\">: EXPLAIN now outputs fractional row counts for more accurate cost estimates.<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li aria-level=\"1\"><b>EXPLAIN Enhancements<\/b><\/li>\n<\/ul>\n<pre class=\"theme:solarized-light lang:default decode:true\">testdb=# EXPLAIN (ANALYZE, BUFFERS)\ntestdb-# SELECT * FROM sales WHERE total &gt; 100;\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 QUERY PLAN\n------------------------------------------------------------------------------------------------------\nSeq Scan on sales\u00a0 (cost=0.00..25.53 rows=270 width=72) (actual time=4.675..4.701 rows=2.00 loops=1)\n\u00a0 Filter: ((price * (quantity)::numeric) &gt; '100'::numeric)\n\u00a0 Buffers: shared hit=1\nPlanning Time: 7.938 ms\nExecution Time: 8.154 ms\n(5 rows)\n\nTime: 39.339 ms<\/pre>\n<h2><b>Replication and High Availability in PostgreSQL 18<\/b><\/h2>\n<ul>\n<li aria-level=\"1\"><b>Generated Column Replication<\/b><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">PostgreSQL 18 allows generated column values to be logically replicated, controlled by the new <\/span><b>publish_generated_columns<\/b><span style=\"font-weight: 400;\"> option..<\/span><\/p>\n<ul>\n<li aria-level=\"1\"><b>Generated Column Replication<\/b><\/li>\n<\/ul>\n<ul>\n<li aria-level=\"1\"><b>Enhanced Subscription Management:<\/b><\/li>\n<\/ul>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Default Streaming Mode<\/b><span style=\"font-weight: 400;\">: CREATE SUBSCRIPTION now defaults to parallel streaming mode instead of off.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Two-Phase Commit Flexibility<\/b><span style=\"font-weight: 400;\">: ALTER SUBSCRIPTION can now modify replication slot two-phase commit behavior.<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li aria-level=\"1\"><b>Improved Conflict Resolution<\/b><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">Enhanced logging and monitoring of conflicts during logical replication, with new columns in pg_stat_subscription_stats for detailed conflict tracking.<\/span><\/p>\n<h2><b>Administrative and Operational Improvements in PostgreSQL 18<\/b><\/h2>\n<ul>\n<li aria-level=\"1\"><b>Vacuum and Maintenance Enhancements:<\/b><\/li>\n<\/ul>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Eager Page Freezing<\/b><span style=\"font-weight: 400;\">: VACUUM can now freeze some all-visible pages even during normal operations, controlled by vacuum_max_eager_freeze_failure_rate.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Truncation Control<\/b><span style=\"font-weight: 400;\">: New vacuum_truncate server variable controls file truncation during VACUUM operations.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Enhanced Vacuum Statistics<\/b><span style=\"font-weight: 400;\">: Detailed timing information for vacuum and analysis operations, including delay time reporting.<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li aria-level=\"1\"><b>VACUUM Enhancements<\/b><\/li>\n<\/ul>\n<pre class=\"theme:solarized-light lang:default decode:true\">testdb=# VACUUM (VERBOSE) sales;\nINFO:\u00a0 vacuuming \"testdb.public.sales\"\nINFO:\u00a0 finished vacuuming \"testdb.public.sales\": index scans: 0\npages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0 eagerly scanned\ntuples: 0 removed, 2 remain, 0 are dead but not yet removable\nremovable cutoff: 807, which was 0 XIDs old when operation ended\nnew relfrozenxid: 789, which is 1 XIDs ahead of previous value\nfrozen: 0 pages from table (0.00% of total) had 0 tuples frozen\nvisibility map: 1 pages set all-visible, 0 pages set all-frozen (0 were all-visible)\nindex scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed\navg read rate: 0.000 MB\/s, avg write rate: 0.986 MB\/s\nbuffer usage: 20 hits, 0 reads, 6 dirtied\nWAL usage: 5 records, 6 full page images, 38403 bytes, 0 buffers full\nsystem usage: CPU: user: 0.04 s, system: 0.00 s, elapsed: 0.04 s\nINFO:\u00a0 vacuuming \"testdb.pg_toast.pg_toast_24665\"\nINFO:\u00a0 finished vacuuming \"testdb.pg_toast.pg_toast_24665\": index scans: 0\npages: 0 removed, 0 remain, 0 scanned (100.00% of total), 0 eagerly scanned\ntuples: 0 removed, 0 remain, 0 are dead but not yet removable\nremovable cutoff: 807, which was 0 XIDs old when operation ended\nnew relfrozenxid: 807, which is 19 XIDs ahead of previous value\nfrozen: 0 pages from table (100.00% of total) had 0 tuples frozen\nvisibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)\nindex scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed\navg read rate: 0.727 MB\/s, avg write rate: 0.727 MB\/s\nbuffer usage: 25 hits, 1 reads, 1 dirtied\nWAL usage: 2 records, 1 full page images, 7495 bytes, 0 buffers full\nsystem usage: CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s\nVACUUM\nTime: 75.587 ms<\/pre>\n<ul>\n<li aria-level=\"1\"><b>Adjust eager freeze rate<\/b><\/li>\n<\/ul>\n<pre class=\"theme:solarized-light lang:default decode:true\">testdb=# SET vacuum_max_eager_freeze_failure_rate = 0.05;\nSET\nTime: 6.117 ms\ntestdb=# SET vacuum_truncate = on;\nSET\nTime: 0.230 ms<\/pre>\n<ul>\n<li aria-level=\"1\"><b>Flexible Autovacuum Configuration:<\/b><\/li>\n<\/ul>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Worker Slot Management<\/b><span style=\"font-weight: 400;\">: New autovacuum_worker_slots parameter allows dynamic adjustment of autovacuum workers without server restart.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Threshold Configuration<\/b><span style=\"font-weight: 400;\">: autovacuum_vacuum_max_threshold allows specification of fixed dead tuple counts for triggering autovacuum.<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li aria-level=\"1\"><b>Autovacuum Worker Slots<\/b><\/li>\n<\/ul>\n<pre class=\"theme:solarized-light lang:default decode:true\">testdb=# SHOW autovacuum_worker_slots;\nautovacuum_worker_slots\n-------------------------\n16\n(1 row)\n\nTime: 7.151 ms<\/pre>\n<ul>\n<li aria-level=\"1\"><b>Backup and Recovery Improvements:<\/b><\/li>\n<\/ul>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>pg_combinebackup Enhancements<\/b><span style=\"font-weight: 400;\">: New &#8211;link option enables hard linking for faster backup combinations.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Tar Format Verification<\/b><span style=\"font-weight: 400;\">: pg_verifybackup now supports verification of tar-format backups.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Enhanced pg_upgrade<\/b><span style=\"font-weight: 400;\">: Statistics preservation during major version upgrades, parallel database checks, and new &#8211;swap mode for potentially faster upgrades.<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h2><b>Client and Tool Enhancements<\/b><\/h2>\n<ul>\n<li aria-level=\"1\"><b>psql Improvements<\/b><\/li>\n<\/ul>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Pipeline Query Support<\/b><span style=\"font-weight: 400;\">: New commands for issuing pipeline queries (\\startpipeline, \\syncpipeline, etc.).<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Enhanced Information Display<\/b><span style=\"font-weight: 400;\">: Improved \\conninfo output with tabular format and additional connection details.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Expanded Mode for Lists<\/b><span style=\"font-weight: 400;\">: All list commands can now use expanded mode with the x suffix. (\\x) command for expanded enable or disable. <\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li aria-level=\"1\"><b>Connection and Protocol Enhancements<\/b><\/li>\n<\/ul>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Protocol Version Control<\/b><span style=\"font-weight: 400;\">: New libpq parameters to specify minimum and maximum acceptable protocol versions.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Search Path Reporting<\/b><span style=\"font-weight: 400;\">: Changes to search_path are now reported to clients.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>SSL Key Logging<\/b><span style=\"font-weight: 400;\">: New sslkeylogfile parameter for debugging SSL connections.<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h2><b>Infrastructure and Platform Updates\u00a0<\/b><\/h2>\n<ul>\n<li aria-level=\"1\"><b>Build System and Dependencies:<\/b><\/li>\n<\/ul>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Meson on Windows<\/b><span style=\"font-weight: 400;\">: Meson builds are no longer experimental on Windows platforms.<\/span><\/li>\n<\/ul>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Updated Requirements<\/b><span style=\"font-weight: 400;\">:<\/span><\/li>\n<\/ol>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Minimum Python version: 3.6.8<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">OpenSSL: Version 1.1.1 or later required<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">LLVM: Version 14 or later if enabled<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li aria-level=\"1\"><b>NUMA (Non-Uniform Memory Access) Awareness:<\/b><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">New-with-libnuma configure option enables NUMA awareness, with supporting functions: It\u2019s introduced on PostgreSQL version 18, and it is valid for later versions.\u00a0<\/span><\/p>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">pg_numa_available(): Reports NUMA availability<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">pg_shmem_allocations_numa and pg_buffercache_numa: NUMA-aware system views<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h2><b>Migration and Compatibility Considerations\u00a0<\/b><\/h2>\n<ul>\n<li aria-level=\"1\"><b>Breaking Changes<\/b><\/li>\n<\/ul>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Data Checksums Default<\/b><span style=\"font-weight: 400;\">: initdb now enables data checksums by default. Use &#8211;no-data-checksums to disable.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Time Zone Handling<\/b><span style=\"font-weight: 400;\">: Session time zone abbreviations are now favored over the server timezone_abbreviations setting.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>VACUUM Behavior<\/b><span style=\"font-weight: 400;\">: VACUUM and ANALYZE now process inheritance children by default. Use the new ONLY option for previous behavior.<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li aria-level=\"1\"><b>Upgrade Path<\/b><\/li>\n<\/ul>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Statistics Preservation<\/b><span style=\"font-weight: 400;\">: pg_upgrade can now preserve optimizer statistics, reducing post-upgrade analysis time.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Parallel Checks<\/b><span style=\"font-weight: 400;\">: Database checks during upgrade can now run in parallel, significantly reducing upgrade time.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Checksum Compatibility<\/b><span style=\"font-weight: 400;\">: pg_upgrade requires matching checksum settings between clusters.<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h2><b>Performance Impact and Real-World Benefits in PostgreSQL 18<\/b><\/h2>\n<ul>\n<li aria-level=\"1\"><b>Expected Performance Improvements<\/b><\/li>\n<\/ul>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>I\/O-Bound Workloads<\/b><span style=\"font-weight: 400;\">: Applications with heavy read patterns will see the most dramatic improvements from asynchronous I\/O.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Index-Heavy Operations<\/b><span style=\"font-weight: 400;\">: Parallel GIN index builds and enhanced B-tree skip scans will significantly benefit search-intensive applications.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Large Table Maintenance<\/b><span style=\"font-weight: 400;\">: Improved vacuum strategies and parallel operations reduce maintenance windows.<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li aria-level=\"1\"><b>Industry-Specific Benefits<\/b><\/li>\n<\/ul>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Analytics and Reporting<\/b><span style=\"font-weight: 400;\">: Enhanced query optimization and parallel processing benefit OLAP workloads.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Full-Text Search<\/b><span style=\"font-weight: 400;\">: Parallel GIN index building and improved JSON handling enhance search applications.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Time-Series Data<\/b><span style=\"font-weight: 400;\">: UUIDv7 support and virtual generated columns optimize time-series workloads.<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h2><b>Looking Forward\u00a0<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">PostgreSQL 18 marks a key shift toward more efficient, scalable operations, with async I\/O boosting its competitiveness while keeping reliability and standards intact.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Key areas where PostgreSQL 18 excels:<\/span><\/p>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Performance<\/b><span style=\"font-weight: 400;\">: Asynchronous I\/O and query optimization improvements<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Scalability<\/b><span style=\"font-weight: 400;\">: Enhanced parallel processing and better resource utilization<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Developer Experience<\/b><span style=\"font-weight: 400;\">: Improved SQL features and better tooling<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Operations<\/b><span style=\"font-weight: 400;\">: Enhanced monitoring and flexible maintenance options<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Security<\/b><span style=\"font-weight: 400;\">: Modern authentication methods and improved SSL\/TLS support<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h2><b>Conclusion\u00a0<\/b><\/h2>\n<blockquote><p><span style=\"font-weight: 400;\"><a href=\"https:\/\/www.postgresql.org\/about\/news\/postgresql-18-released-3142\/\">PostgreSQL 18<\/a> delivers on the promise of modern database performance while maintaining PostgreSQL&#8217;s commitment to reliability and standards compliance. The introduction of asynchronous I\/O, enhanced query optimization, and improved developer features make this release a compelling upgrade for organizations of all sizes.<\/span><\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>PostgreSQL 18: Revolutionary Changes in Database Performance and Functionality-\u00a0 PostgreSQL 18, released on September 25, 2025, is one of the most significant updates in years. It brings major improvements to I\/O, query processing, and data management, offering enhanced performance and powerful new features for developers, DBAs, and organizations. These are the Game-Changing Performance Enhancements in&hellip;<\/p>\n","protected":false},"author":1,"featured_media":3329,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[69,85,105,126,131,139,174,179,236,264,268,297,304,337],"class_list":["post-3324","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-asynchronous","tag-bynatreedatasolutions","tag-database","tag-databaseinnovation","tag-datadriven","tag-dba-community","tag-io-performance","tag-json","tag-parallel-gin-index","tag-postgres-update","tag-postgresql-18","tag-query-optimization","tag-remote-dba","tag-techupdate","category-28","description-off"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/3324","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/comments?post=3324"}],"version-history":[{"count":0,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/3324\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media\/3329"}],"wp:attachment":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media?parent=3324"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/categories?post=3324"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/tags?post=3324"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}