New Features added in PostgreSQL-18

PostgreSQL 18: Revolutionary Changes in Database Performance and Functionality-

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

Asynchronous I/O Subsystem: The Most Significant Addition

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

Key Benefits of Asynchronous I/O:

- Increased I/O Throughput: Multiple read requests can be queued and processed simultaneously
- Reduced Latency: I/O operations no longer block query execution
- Better Resource Utilization: More efficient use of storage subsystems
- Enhanced Performance: Particularly beneficial for sequential scans, bitmap heap scans, and vacuum operations

Configuration Options:

- io_method: Controls the I/O method (enables AIO)
- io_combine_limit and io_max_combine_limit: Control I/O operation combining
- pg_aios: New system view to monitor asynchronous I/O file handles

Asynchronous I/O Configuration

Check the current I/O method

 testdb=# SHOW io_method; (1 row) io_method ----------- worker

testdb=# SHOW io_method;

(1 row)

io_method

-----------

worker

Enable asynchronous I/O

 testdb=# SHOW io_combine_limit; io_combine_limit ------------------ 128kB (1 row) testdb=# SHOW io_max_combine_limit; io_max_combine_limit ---------------------- 128kB (1 row)

testdb=# SHOW io_combine_limit;

io_combine_limit

------------------

128kB

(1 row)

testdb=# SHOW io_max_combine_limit;

io_max_combine_limit

----------------------

128kB

(1 row)

Monitor asynchronous I/O handles

 testdb=# SELECT * FROM pg_aios LIMIT 5; pid | io_id | io_generation | state | operation | off | length | target | handle_data_len | raw_result | result | target_desc | f_sync | f_localmem | f_buffer ed -----+-------+---------------+-------+-----------+-----+--------+--------+-----------------+------------+--------+-------------+--------+------------+--------- --- (0 rows)

testdb=# SELECT * FROM pg_aios LIMIT 5;

-----+-------+---------------+-------+-----------+-----+--------+--------+-----------------+------------+--------+-------------+--------+------------+---------

---

(0 rows)

Parallel GIN Index Building:

PostgreSQL 18 adds parallel builds for GIN indexes, speeding up index creation for JSON and full-text search data.

- JSON and JSONB data
- Full-text search columns
- Array data types
- Other complex data structure

Parallel GIN Index Building

 testdb=# CREATE TABLE docs(id serial, content jsonb); CREATE TABLE testdb=# INSERT INTO docs (content) testdb-# VALUES ('{"key": "value", "count": 10}'::jsonb); INSERT 0 1 testdb=# SELECT jsonb_build_object('text', md5(random()::text)) FROM generate_series(1, 50000); jsonb_build_object ---------------------------------------------- {"text": "75ea8a8788ab2f5817cb68499ae581f0"} {"text": "46932c038a966472df32fa4937e8aa65"} {"text": "d479ddb89fd7d1d9355bc723632350f1"} {"text": "722227f05df276d241849e376f473b77"}

testdb=# CREATE TABLE docs(id serial, content jsonb);

CREATE TABLE

testdb=# INSERT INTO docs (content)

testdb-# VALUES ('{"key": "value", "count": 10}'::jsonb);

INSERT 0 1

testdb=# SELECT jsonb_build_object('text', md5(random()::text)) FROM generate_series(1, 50000);

jsonb_build_object

----------------------------------------------

{"text": "75ea8a8788ab2f5817cb68499ae581f0"}

{"text": "46932c038a966472df32fa4937e8aa65"}

{"text": "d479ddb89fd7d1d9355bc723632350f1"}

{"text": "722227f05df276d241849e376f473b77"}

Parallel GIN index creation

Set the Session Parallelism

 testdb=# SET max_parallel_maintenance_workers = 4; SET

1 2	testdb=# SET max_parallel_maintenance_workers = 4; SET

Create Parallel GIN index

 testdb=# CREATE INDEX gin_docs_idx ON docs USING gin (content jsonb_path_ops); CREATE INDEX

1 2	testdb=# CREATE INDEX gin_docs_idx ON docs USING gin (content jsonb_path_ops); CREATE INDEX

– Verify in pg_stat_progress_create_index

 testdb=# SELECT * FROM pg_stat_progress_create_index WHERE command = 'CREATE INDEX'; pid | datid | datname | relid | index_relid | command | phase | lockers_total | lockers_done | current_locker_pid | blocks_total | blocks_done | tuples_total | tuples_done | partitions_total | partitions_done -----+-------+---------+-------+-------------+---------+-------+---------------+--------------+--------------------+--------------+-------------+-------------- +-------------+------------------+----------------- (0 rows)

testdb=# SELECT * FROM pg_stat_progress_create_index WHERE command = 'CREATE INDEX';

| tuples_done | partitions_total | partitions_done

-----+-------+---------+-------+-------------+---------+-------+---------------+--------------+--------------------+--------------+-------------+--------------

+-------------+------------------+-----------------

(0 rows)

Advanced Query Optimization Improvements

- Self-Join Elimination: PostgreSQL 18 can automatically remove unnecessary table self-joins, simplifying query execution plans and improving performance.
- Enhanced OR-Clause Processing: PostgreSQL 18’s optimizer now turns OR clauses into arrays, making index use faster and queries more efficient.
- Right Semi Join Support: PostgreSQL 18 adds Hash Right Semi Join, letting the planner choose which table to hash based on size, improving semi-join performance.
- Skip Scans for B-tree Indexes: Multi-column B-tree indexes can now be used by queries that only reference the second or later indexed columns through skip scan functionality.

Self-Join Elimination Example

 testdb=# INSERT INTO employees VALUES (1, NULL, 'CEO'), (2, 1, 'Manager'), (3, 2, 'Developer'); INSERT 0 3 testdb=# EXPLAIN SELECT e1.name, e2.name AS manager FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id; QUERY PLAN ----------------------------------------------------------------------------- Merge Join (cost=166.75..280.75 rows=7200 width=64) Merge Cond: (e1.manager_id = e2.id) -> Sort (cost=83.37..86.37 rows=1200 width=36) Sort Key: e1.manager_id -> Seq Scan on employees e1 (cost=0.00..22.00 rows=1200 width=36) -> Sort (cost=83.37..86.37 rows=1200 width=36) Sort Key: e2.id -> Seq Scan on employees e2 (cost=0.00..22.00 rows=1200 width=36) (8 rows)

testdb=# INSERT INTO employees VALUES (1, NULL, 'CEO'), (2, 1, 'Manager'), (3, 2, 'Developer');

INSERT 0 3

testdb=# EXPLAIN SELECT e1.name, e2.name AS manager FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id;

QUERY PLAN

-----------------------------------------------------------------------------

Merge Join (cost=166.75..280.75 rows=7200 width=64)

Merge Cond: (e1.manager_id = e2.id)

-> Sort (cost=83.37..86.37 rows=1200 width=36)

Sort Key: e1.manager_id

-> Seq Scan on employees e1 (cost=0.00..22.00 rows=1200 width=36)

-> Sort (cost=83.37..86.37 rows=1200 width=36)

Sort Key: e2.id

-> Seq Scan on employees e2 (cost=0.00..22.00 rows=1200 width=36)

(8 rows)

Skip Scan on Multi-column Index

 testdb=# CREATE TABLE orders(order_id int, customer_id int, order_date date); CREATE TABLE testdb=# CREATE INDEX orders_idx ON orders(customer_id, order_date); CREATE INDEX

testdb=# CREATE TABLE orders(order_id int, customer_id int, order_date date);

CREATE TABLE

testdb=# CREATE INDEX orders_idx ON orders(customer_id, order_date);

CREATE INDEX

Query using skip scan (only order_date)

 testdb=# EXPLAIN SELECT * FROM orders WHERE order_date > CURRENT_DATE - INTERVAL '30 days'; QUERY PLAN ---------------------------------------------------------------------------- Bitmap Heap Scan on orders (cost=19.63..41.53 rows=680 width=12) Recheck Cond: (order_date > (CURRENT_DATE - '30 days'::interval)) -> Bitmap Index Scan on orders_idx (cost=0.00..19.46 rows=680 width=0) Index Cond: (order_date > (CURRENT_DATE - '30 days'::interval)) (4 rows)

testdb=# EXPLAIN SELECT * FROM orders WHERE order_date > CURRENT_DATE - INTERVAL '30 days';

QUERY PLAN

----------------------------------------------------------------------------

Bitmap Heap Scan on orders (cost=19.63..41.53 rows=680 width=12)

Recheck Cond: (order_date > (CURRENT_DATE - '30 days'::interval))

-> Bitmap Index Scan on orders_idx (cost=0.00..19.46 rows=680 width=0)

Index Cond: (order_date > (CURRENT_DATE - '30 days'::interval))

(4 rows)

These are the Advanced Data Management Features

Virtual Generated Columns:

PostgreSQL 18 introduces virtual generated columns as the default behavior, representing a significant shift from the previous stored-only approach:

Virtual vs. Stored Generated Columns:

- Virtual: Values are computed when the column is read (default in PostgreSQL 18)
- Stored: Values are computed and stored when the row is written (available via STORED option)

Benefits of Virtual Generated Columns:

- Reduced storage requirements
- Always up-to-date values without maintenance overhead
- Better performance for columns that are read infrequently

Virtual Generated Columns

 testdb=# CREATE TABLE sales ( testdb(# id serial, testdb(# price numeric, testdb(# quantity int, testdb(# total numeric GENERATED ALWAYS AS (price * quantity) VIRTUAL testdb(# ); CREATE TABLE testdb=# INSERT INTO sales (price, quantity) VALUES (100, 5), (200, 2); INSERT 0 2 testdb=# SELECT * FROM sales; id | price | quantity | total ----+-------+----------+------- 1 | 100 | 5 | 500 2 | 200 | 2 | 400 (2 rows)

testdb=# CREATE TABLE sales (

testdb(# id serial,

testdb(# price numeric,

testdb(# quantity int,

testdb(# total numeric GENERATED ALWAYS AS (price * quantity) VIRTUAL

testdb(# );

CREATE TABLE

testdb=# INSERT INTO sales (price, quantity) VALUES (100, 5), (200, 2);

INSERT 0 2

testdb=# SELECT * FROM sales;

id | price | quantity | total

----+-------+----------+-------

1 | 100 | 5 | 500

2 | 200 | 2 | 400

(2 rows)

Enhanced UUID Support:

UUIDv7 Generation: PostgreSQL 18introduces uuidv7(), a new time-sortable UUID version that provides globally unique, non-predictable IDs ideal for ordered data storage and retrieval:

- Time-series data
- Distributed systems requiring ordered identifiers
- Primary keys where chronological ordering is beneficial

Time-sortable UUID: ChatGPT said:

Time-sortable UUIDs: It include a time-based component, allowing efficient sorting and improving database performance compared to random UUIDs like UUIDv4

uuidv7() example

 testdb=# SELECT uuidv7(); uuidv7 -------------------------------------- 0199ca8c-692d-769d-812c-5f9054168fa1 (1 row)

testdb=# SELECT uuidv7();

uuidv7

--------------------------------------

0199ca8c-692d-769d-812c-5f9054168fa1

(1 row)

Time-sortable UUID example
testdb=# SELECT uuidv7() AS time_sortable_uuid; time_sortable_uuid -------------------------------------- 0199ca8d-cbd0-7c4a-9afb-97a5266c2505 (1 row)

1
2
3
4
5

testdb=# SELECT uuidv7() AS time_sortable_uuid;
time_sortable_uuid
--------------------------------------
0199ca8d-cbd0-7c4a-9afb-97a5266c2505
(1 row)

Explicit UUIDv4 Generation: The UUIDv4 is a “purely random” 128-bit identifier, mostly generated from high-quality randomness, with a few bits set by the RFC 9562 standard to define version and variant.

 testdb=# SELECT uuidv4() AS random_uuid; random_uuid -------------------------------------- 9945163f-616f-467e-9ece-fb110b2cb6e0 (1 row)

testdb=# SELECT uuidv4() AS random_uuid;

random_uuid

--------------------------------------

9945163f-616f-467e-9ece-fb110b2cb6e0

(1 row)

Improved NOT NULL Constraint Management:

Improved NOT NULL constraint management is active in recent PostgreSQL versions (particularly since PostgreSQL 11, with further enhancements in 17 and 18), focusing on two main areas:

1. Zero-downtime schema changes for large tables.
2. Query performance optimization by leveraging constraint knowledge.

NOT NULL Constraint Improvements

testdb=# CREATE TABLE customers ( testdb(# id serial PRIMARY KEY, testdb(# name text, testdb(# email text testdb(# ); CREATE TABLE

1
2
3
4
5
6

testdb=# CREATE TABLE customers (
testdb(# id serial PRIMARY KEY,
testdb(# name text,
testdb(# email text
testdb(# );
CREATE TABLE

Named NOT NULL Constraints: You can now assign names to NOT NULL constraints, improving error messages and constraint management.
NOT VALID Support: NOT NULL constraints can be marked as NOT VALID, allowing for non-blocking constraint addition on large tables.
Inheritance Control: NOT NULL constraints can now be made inheritable or non-inheritable on partitioned tables.

Add NOT NULL constraint with a name and NOT VALID option

 testdb=# ALTER TABLE customers ADD CONSTRAINT nn_email CHECK (email IS NOT NULL) NOT VALID; ALTER TABLE

1 2	testdb=# ALTER TABLE customers ADD CONSTRAINT nn_email CHECK (email IS NOT NULL) NOT VALID; ALTER TABLE

Validate constraint later

 testdb=# ALTER TABLE customers VALIDATE CONSTRAINT nn_email; ALTER TABLE

1 2	testdb=# ALTER TABLE customers VALIDATE CONSTRAINT nn_email; ALTER TABLE

Enhanced Developer Experience

RETURNING Clause Enhancements

The RETURNING clause now supports OLD/NEW syntax, allowing developers to access both old and new values in DML operations:

This feature is particularly useful for:

- Aduit trails
- Change tracking
- Data migration scenarios

RETURNING Clause with OLD/NEW

 testdb=# CREATE TABLE employees_audit(id serial, name text, salary numeric); CREATE TABLE testdb=# INSERT INTO employees_audit(name, salary) VALUES ('Alice', 50000); INSERT 0 1 testdb=# testdb=# UPDATE employees_audit testdb-# SET salary = salary * 1.1 testdb-# RETURNING old.salary AS old_salary, new.salary AS new_salary; old_salary | new_salary ------------+------------ 50000 | 55000.0 (1 row)

testdb=# CREATE TABLE employees_audit(id serial, name text, salary numeric);

CREATE TABLE

testdb=# INSERT INTO employees_audit(name, salary) VALUES ('Alice', 50000);

INSERT 0 1

testdb=#

testdb=# UPDATE employees_audit

testdb-# SET salary = salary * 1.1

testdb-# RETURNING old.salary AS old_salary, new.salary AS new_salary;

old_salary | new_salary

------------+------------

50000 | 55000.0

(1 row)

Advanced JSON Functionality

- Improved NULL Handling: JSONB null values can now be cast to scalar types as NULL, improving flexibility in JSON data processing.
- Enhanced Array Processing: New optional parameters in json{b}_strip_nulls() allow removal of null array elements.

JSON Enhancements

 testdb=# CREATE TABLE json_test(data jsonb); CREATE TABLE testdb=# INSERT INTO json_test VALUES ('{"a":1,"b":null,"c":[1,null,3]}'); INSERT 0

testdb=# CREATE TABLE json_test(data jsonb);

CREATE TABLE

testdb=# INSERT INTO json_test VALUES ('{"a":1,"b":null,"c":[1,null,3]}');

INSERT 0

Remove null array elements

 testdb=# SELECT jsonb_strip_nulls(data, true) FROM json_test; jsonb_strip_nulls ----------------------- {"a": 1, "c": [1, 3]} (1 row)

testdb=# SELECT jsonb_strip_nulls(data, true) FROM json_test;

jsonb_strip_nulls

-----------------------

{"a": 1, "c": [1, 3]}

(1 row)

NULL casting example
testdb=# SELECT (data->>'b')::int IS NULL AS null_cast_check FROM json_test; null_cast_check ----------------- t (1 row)

1
2
3
4
5

testdb=# SELECT (data->>'b')::int IS NULL AS null_cast_check FROM json_test;
null_cast_check
-----------------
t
(1 row)

New Array Functions:
- array_sort(): Sorts an array’s first dimension
- array_reverse(): Reverses an array’s first dimension
- reverse(): Reverses bytea bytes

New Array Functions

 testdb=# SELECT array_sort(ARRAY[5,2,9,1]) AS sorted, array_reverse(ARRAY[1,2,3,4]) AS reversed; sorted | reversed -----------+----------- {1,2,5,9} | {4,3,2,1} (1 row) testdb=# SELECT reverse('PostgreSQL18'::bytea); reverse ---------------------------- \x38314c515365726774736f50 (1 row)

testdb=# SELECT array_sort(ARRAY[5,2,9,1]) AS sorted,

array_reverse(ARRAY[1,2,3,4]) AS reversed;

sorted | reversed

-----------+-----------

{1,2,5,9} | {4,3,2,1}

(1 row)

testdb=# SELECT reverse('PostgreSQL18'::bytea);

reverse

----------------------------

\x38314c515365726774736f50

(1 row)

Case Folding Support: The new casefold() function enables advanced, language-aware case-insensitive text matching beyond simple lower/upper conversions.

Case Folding

 testdb=# SELECT lower('Straße') AS folded_german, testdb-# lower('İstanbul') AS folded_turkish; folded_german | folded_turkish ---------------+---------------- straße | istanbul (1 row) Time: 17.053 ms

testdb=# SELECT lower('Straße') AS folded_german,

testdb-# lower('İstanbul') AS folded_turkish;

folded_german | folded_turkish

---------------+----------------

straße | istanbul

(1 row)

Time: 17.053 ms

Security and Authentication Improvements

OAuth (open-authorization) Authentication Support:

PostgreSQL 18 introduces built-in Open-Authorization authentication support, modernizing authentication capabilities:

- OAuth authentication method in pg_hba.conf
- libpq OAuth connection options
- Integration with modern identity providers

Enhanced SSL/TLS Configuration

- TLS 1.3 Cipher Suites: The New ssl_tls13_ciphers parameter allows specification of multiple TLS 1.3 cipher suites.
- Extended Curve Support: The ssl_groups parameter (renamed from ssl_ecdh_curve) now supports multiple elliptic curves, including X25519.
- Security Key Improvements: Cancel request keys are now 256 bits, enhancing security when using wire protocol version 3.2.

Deprecation of MD5 Passwords

PostgreSQL 18 officially deprecates MD5 password authentication:

- CREATE ROLE and ALTER ROLE now emit warnings for MD5 passwords
- MD5 support will be removed in a future major version
- Organizations should migrate to SCRAM-SHA-256 authentication

Monitoring and Observability

Enhanced Statistics Collection:
- Detailed I/O Statistics: New columns in pg_stat_io report I/O activity in bytes (read_bytes, write_bytes, extend_bytes).
- WAL I/O Monitoring: WAL receiver activity is now tracked in pg_stat_io with dedicated wait events.
- Parallel Worker Statistics: New columns in pg_stat_database track parallel worker launch and execution metrics.
Enhanced I/O Stats

 testdb=# SELECT backend_type, testdb-# object, testdb-# read_bytes, testdb-# write_bytes testdb-# FROM pg_stat_io testdb-# WHERE read_bytes > 0; backend_type | object | read_bytes | write_bytes -------------------+----------+------------+------------- client backend | relation | 5505024 | 0 autovacuum worker | relation | 106496 | 0 startup | wal | 32768 | 0 (3 rows) Time: 65.021 ms

testdb=# SELECT backend_type,

testdb-# object,

testdb-# read_bytes,

testdb-# write_bytes

testdb-# FROM pg_stat_io

testdb-# WHERE read_bytes > 0;

backend_type | object | read_bytes | write_bytes

-------------------+----------+------------+-------------

client backend | relation | 5505024 | 0

autovacuum worker | relation | 106496 | 0

startup | wal | 32768 | 0

(3 rows)

Time: 65.021 ms

WAL I/O Monitoring

 testdb=# SELECT * testdb-# FROM pg_stat_io testdb-# WHERE object = 'WAL'; backend_type | object | context | reads | read_bytes | read_time | writes | write_bytes | write_time | writebacks | writeback_time | extends | extend_bytes | extend_time | hits | evictions | reuses | fsyncs | fsync_time | stats_reset --------------+--------+---------+-------+------------+-----------+--------+-------------+------------+------------+----------------+---------+--------------+- ------------+------+-----------+--------+--------+------------+------------- (0 rows) Time: 7.194 ms testdb=# SELECT DISTINCT object FROM pg_stat_io; object --------------- wal temp relation relation (3 rows)

testdb=# SELECT *

testdb-# FROM pg_stat_io

testdb-# WHERE object = 'WAL';

--------------+--------+---------+-------+------------+-----------+--------+-------------+------------+------------+----------------+---------+--------------+-

------------+------+-----------+--------+--------+------------+-------------

(0 rows)

Time: 7.194 ms

testdb=# SELECT DISTINCT object FROM pg_stat_io;

object

---------------

wal

temp relation

relation

(3 rows)

Improved EXPLAIN Functionality:

- Automatic BUFFERS Output: EXPLAIN ANALYZE now automatically includes buffer usage information.
- Enhanced Node Details: Additional memory and disk usage details for Material, Window Aggregate, and CTE nodes.
- Fractional Row Counts: EXPLAIN now outputs fractional row counts for more accurate cost estimates.

EXPLAIN Enhancements

 testdb=# EXPLAIN (ANALYZE, BUFFERS) testdb-# SELECT * FROM sales WHERE total > 100; QUERY PLAN ------------------------------------------------------------------------------------------------------ Seq Scan on sales (cost=0.00..25.53 rows=270 width=72) (actual time=4.675..4.701 rows=2.00 loops=1) Filter: ((price * (quantity)::numeric) > '100'::numeric) Buffers: shared hit=1 Planning Time: 7.938 ms Execution Time: 8.154 ms (5 rows) Time: 39.339 ms

testdb=# EXPLAIN (ANALYZE, BUFFERS)

testdb-# SELECT * FROM sales WHERE total > 100;

QUERY PLAN

------------------------------------------------------------------------------------------------------

Seq Scan on sales (cost=0.00..25.53 rows=270 width=72) (actual time=4.675..4.701 rows=2.00 loops=1)

Filter: ((price * (quantity)::numeric) > '100'::numeric)

Buffers: shared hit=1

Planning Time: 7.938 ms

Execution Time: 8.154 ms

(5 rows)

Time: 39.339 ms

Replication and High Availability

Generated Column Replication

PostgreSQL 18 allows generated column values to be logically replicated, controlled by the new publish_generated_columns option..

Generated Column Replication

Enhanced Subscription Management:

- Default Streaming Mode: CREATE SUBSCRIPTION now defaults to parallel streaming mode instead of off.
- Two-Phase Commit Flexibility: ALTER SUBSCRIPTION can now modify replication slot two-phase commit behavior.

Improved Conflict Resolution

Enhanced logging and monitoring of conflicts during logical replication, with new columns in pg_stat_subscription_stats for detailed conflict tracking.

Administrative and Operational Improvements

Vacuum and Maintenance Enhancements:

- Eager Page Freezing: VACUUM can now freeze some all-visible pages even during normal operations, controlled by vacuum_max_eager_freeze_failure_rate.
- Truncation Control: New vacuum_truncate server variable controls file truncation during VACUUM operations.
- Enhanced Vacuum Statistics: Detailed timing information for vacuum and analysis operations, including delay time reporting.

VACUUM Enhancements

 testdb=# VACUUM (VERBOSE) sales; INFO: vacuuming "testdb.public.sales" INFO: finished vacuuming "testdb.public.sales": index scans: 0 pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0 eagerly scanned tuples: 0 removed, 2 remain, 0 are dead but not yet removable removable cutoff: 807, which was 0 XIDs old when operation ended new relfrozenxid: 789, which is 1 XIDs ahead of previous value frozen: 0 pages from table (0.00% of total) had 0 tuples frozen visibility map: 1 pages set all-visible, 0 pages set all-frozen (0 were all-visible) index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed avg read rate: 0.000 MB/s, avg write rate: 0.986 MB/s buffer usage: 20 hits, 0 reads, 6 dirtied WAL usage: 5 records, 6 full page images, 38403 bytes, 0 buffers full system usage: CPU: user: 0.04 s, system: 0.00 s, elapsed: 0.04 s INFO: vacuuming "testdb.pg_toast.pg_toast_24665" INFO: finished vacuuming "testdb.pg_toast.pg_toast_24665": index scans: 0 pages: 0 removed, 0 remain, 0 scanned (100.00% of total), 0 eagerly scanned tuples: 0 removed, 0 remain, 0 are dead but not yet removable removable cutoff: 807, which was 0 XIDs old when operation ended new relfrozenxid: 807, which is 19 XIDs ahead of previous value frozen: 0 pages from table (100.00% of total) had 0 tuples frozen visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible) index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed avg read rate: 0.727 MB/s, avg write rate: 0.727 MB/s buffer usage: 25 hits, 1 reads, 1 dirtied WAL usage: 2 records, 1 full page images, 7495 bytes, 0 buffers full system usage: CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s VACUUM Time: 75.587 ms

testdb=# VACUUM (VERBOSE) sales;

INFO: vacuuming "testdb.public.sales"

INFO: finished vacuuming "testdb.public.sales": index scans: 0

pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0 eagerly scanned

tuples: 0 removed, 2 remain, 0 are dead but not yet removable

removable cutoff: 807, which was 0 XIDs old when operation ended

new relfrozenxid: 789, which is 1 XIDs ahead of previous value

frozen: 0 pages from table (0.00% of total) had 0 tuples frozen

visibility map: 1 pages set all-visible, 0 pages set all-frozen (0 were all-visible)

index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed

avg read rate: 0.000 MB/s, avg write rate: 0.986 MB/s

buffer usage: 20 hits, 0 reads, 6 dirtied

WAL usage: 5 records, 6 full page images, 38403 bytes, 0 buffers full

system usage: CPU: user: 0.04 s, system: 0.00 s, elapsed: 0.04 s

INFO: vacuuming "testdb.pg_toast.pg_toast_24665"

INFO: finished vacuuming "testdb.pg_toast.pg_toast_24665": index scans: 0

pages: 0 removed, 0 remain, 0 scanned (100.00% of total), 0 eagerly scanned

tuples: 0 removed, 0 remain, 0 are dead but not yet removable

removable cutoff: 807, which was 0 XIDs old when operation ended

new relfrozenxid: 807, which is 19 XIDs ahead of previous value

frozen: 0 pages from table (100.00% of total) had 0 tuples frozen

visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)

index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed

avg read rate: 0.727 MB/s, avg write rate: 0.727 MB/s

buffer usage: 25 hits, 1 reads, 1 dirtied

WAL usage: 2 records, 1 full page images, 7495 bytes, 0 buffers full

system usage: CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s

VACUUM

Time: 75.587 ms

Adjust eager freeze rate

 testdb=# SET vacuum_max_eager_freeze_failure_rate = 0.05; SET Time: 6.117 ms testdb=# SET vacuum_truncate = on; SET Time: 0.230 ms

testdb=# SET vacuum_max_eager_freeze_failure_rate = 0.05;

SET

Time: 6.117 ms

testdb=# SET vacuum_truncate = on;

SET

Time: 0.230 ms

Flexible Autovacuum Configuration:

- Worker Slot Management: New autovacuum_worker_slots parameter allows dynamic adjustment of autovacuum workers without server restart.
- Threshold Configuration: autovacuum_vacuum_max_threshold allows specification of fixed dead tuple counts for triggering autovacuum.

Autovacuum Worker Slots

 testdb=# SHOW autovacuum_worker_slots; autovacuum_worker_slots ------------------------- 16 (1 row) Time: 7.151 ms

testdb=# SHOW autovacuum_worker_slots;

autovacuum_worker_slots

-------------------------

(1 row)

Time: 7.151 ms

Backup and Recovery Improvements:

- pg_combinebackup Enhancements: New –link option enables hard linking for faster backup combinations.
- Tar Format Verification: pg_verifybackup now supports verification of tar-format backups.
- Enhanced pg_upgrade: Statistics preservation during major version upgrades, parallel database checks, and new –swap mode for potentially faster upgrades.

Client and Tool Enhancements

psql Improvements

- Pipeline Query Support: New commands for issuing pipeline queries (\startpipeline, \syncpipeline, etc.).
- Enhanced Information Display: Improved \conninfo output with tabular format and additional connection details.
- Expanded Mode for Lists: All list commands can now use expanded mode with the x suffix. (\x) command for expanded enable or disable.

Connection and Protocol Enhancements

- Protocol Version Control: New libpq parameters to specify minimum and maximum acceptable protocol versions.
- Search Path Reporting: Changes to search_path are now reported to clients.
- SSL Key Logging: New sslkeylogfile parameter for debugging SSL connections.

Infrastructure and Platform Updates

Build System and Dependencies:

Meson on Windows: Meson builds are no longer experimental on Windows platforms.

Updated Requirements:

- Minimum Python version: 3.6.8
- OpenSSL: Version 1.1.1 or later required
- LLVM: Version 14 or later if enabled

NUMA (Non-Uniform Memory Access) Awareness:

New-with-libnuma configure option enables NUMA awareness, with supporting functions: It’s introduced on PostgreSQL version 18, and it is valid for later versions.

- pg_numa_available(): Reports NUMA availability
- pg_shmem_allocations_numa and pg_buffercache_numa: NUMA-aware system views

Migration and Compatibility Considerations

Breaking Changes

- Data Checksums Default: initdb now enables data checksums by default. Use –no-data-checksums to disable.
- Time Zone Handling: Session time zone abbreviations are now favored over the server timezone_abbreviations setting.
- VACUUM Behavior: VACUUM and ANALYZE now process inheritance children by default. Use the new ONLY option for previous behavior.

Upgrade Path

- Statistics Preservation: pg_upgrade can now preserve optimizer statistics, reducing post-upgrade analysis time.
- Parallel Checks: Database checks during upgrade can now run in parallel, significantly reducing upgrade time.
- Checksum Compatibility: pg_upgrade requires matching checksum settings between clusters.

Performance Impact and Real-World Benefits

Expected Performance Improvements

- I/O-Bound Workloads: Applications with heavy read patterns will see the most dramatic improvements from asynchronous I/O.
- Index-Heavy Operations: Parallel GIN index builds and enhanced B-tree skip scans will significantly benefit search-intensive applications.
- Large Table Maintenance: Improved vacuum strategies and parallel operations reduce maintenance windows.

Industry-Specific Benefits

- Analytics and Reporting: Enhanced query optimization and parallel processing benefit OLAP workloads.
- Full-Text Search: Parallel GIN index building and improved JSON handling enhance search applications.
- Time-Series Data: UUIDv7 support and virtual generated columns optimize time-series workloads.

Looking Forward

PostgreSQL 18 marks a key shift toward more efficient, scalable operations, with async I/O boosting its competitiveness while keeping reliability and standards intact.

Key areas where PostgreSQL 18 excels:

- Performance: Asynchronous I/O and query optimization improvements
- Scalability: Enhanced parallel processing and better resource utilization
- Developer Experience: Improved SQL features and better tooling
- Operations: Enhanced monitoring and flexible maintenance options
- Security: Modern authentication methods and improved SSL/TLS support

Conclusion

PostgreSQL 18 delivers on the promise of modern database performance while maintaining PostgreSQL’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.

PostgreSQL 18: Revolutionary Changes in Database Performance and Functionality-

These are the Game-Changing Performance Enhancements

These are the Advanced Data Management Features

Enhanced Developer Experience

Security and Authentication Improvements

Monitoring and Observability

Replication and High Availability

Administrative and Operational Improvements

Client and Tool Enhancements

Infrastructure and Platform Updates

Migration and Compatibility Considerations

Performance Impact and Real-World Benefits

Looking Forward

Conclusion

Author: BYNATREE DATA SOLUTION

Leave a Reply Cancel reply

PostgreSQL 18: Revolutionary Changes in Database Performance and Functionality-

These are the Game-Changing Performance Enhancements

These are the Advanced Data Management Features

Enhanced Developer Experience

Security and Authentication Improvements

Monitoring and Observability

Replication and High Availability

Administrative and Operational Improvements

Client and Tool Enhancements

Infrastructure and Platform Updates

Migration and Compatibility Considerations

Performance Impact and Real-World Benefits

Looking Forward

Conclusion

Author: BYNATREE DATA SOLUTION

Related posts

Leave a Reply Cancel reply