Comparing PostgreSQL 18 and MySQL 8.4 LTS Updates: Key Changes Developers Need to Know

PostgreSQL and MySQL, the two pillars of relational databases, have both reached significant turning points. PostgreSQL 18 introduces fundamental improvements to the database's internal I/O architecture and identifier generation, while MySQL has restructured into an 8.4 LTS (Long Term Support) version and a 9.0 innovation release cycle that experiments with AI vector features.

For developers designing web backends or managing infrastructure, these changes include elements with major practical implications. We will explore what performance issues both sides aimed to resolve and how they impact application code and schema design.

PostgreSQL 18: Featuring Asynchronous I/O and Built-in UUIDv7

The most significant internal change in PostgreSQL 18 is the introduction of a native asynchronous I/O subsystem. Previously, the system relied on OS-level buffering or synchronous I/O calls when reading large amounts of data from disk, which was prone to bottlenecks. With the asynchronous I/O subsystem now at the core, throughput for disk-intensive sequential scans and VACUUM operations has improved by 2x up to 3x.

A change that directly benefits application developers is the built-in uuidv7() function. The existing UUID v4 is generated as a random value, which caused severe disk page fragmentation and splits when inserted into B-Tree indexes. With UUIDv7, which sorts by time, now included as a standard engine function, you get the benefits of both sorting performance and unique identifiers without needing external extensions.

Example of table definition in PostgreSQL 18 setting UUIDv7 as the default primary key value.

You can now create primary keys that maintain time-series index performance without installing external libraries or the pg\_uuidv7 extension.

In terms of data integrity, the data checksum option is now enabled by default when creating a cluster with initdb. This enhances operational stability by allowing early detection of data corruption caused by disk damage. Standard SQL/JSON support has also been expanded; the JSON\_TABLE() function, which allows semi-structured JSON data to be projected directly into relational table columns, can now be used within queries without complex processing scripts.

MySQL 8.4 LTS and 9.0: Separating Enhanced Authentication Security and Vector Support

Released under Oracle's new release model, MySQL 8.4 LTS prioritizes stability and strict security. The most notable change is that the legacy authentication plugin mysql\_native\_password is now disabled by default. In new installations and upgrade environments, caching\_sha2\_password is enforced. If your service uses older database clients, driver compatibility checks should be performed beforehand as connection errors may occur.

Schema constraints have also become stricter. The outdated and dangerous practice of assigning the AUTO\_INCREMENT attribute to floating-point types like FLOAT and DOUBLE has been completely removed, and index prefixing for partitioning keys is now restricted. This is intended to prevent lax data modeling practices and reduce runtime unpredictability.

In the MySQL 9.0 innovation release, which handles functional experiments, a native VECTOR data type has been added for embedding data. You can define columns for floating-point arrays of up to 16,383 dimensions and convert between strings and vectors using dedicated helper functions.

This opens a path for easily validating simple AI embedding search pipelines when you are not yet ready to build a complex, standalone vector engine.

Which Engine and Features Should You Consider for Production?

If your environment frequently handles large-scale analytical queries, requires prevention of index fragmentation, or relies on standard SQL/JSON transformations, the changes in PostgreSQL 18 provide a powerful advantage. Especially for distributed service architectures that need to use universal identifiers while protecting B-Tree index write performance, the built-in uuidv7() function alone makes the version migration worthwhile.

If you choose MySQL for an enterprise environment where stable, long-term operation is the goal, it is recommended to update to 8.4 LTS, modernize legacy connection libraries, and clean up deprecated schema structures. If you need a lightweight, vector-search-based PoC, it is useful to test the native vector types in MySQL 9.0 in a local environment to gauge the potential for future feature expansion.