PostgreSQL 18, asynchronous input and output

PostgreSQL 18, asynchronous input and output

PostgreSQL is a relational database developed for three decades by a community, with no commercial owner, under a licence permitting any use. The current stable line is 18, while nineteen is in beta with release planned for the second half of 2026.

That distinction deserves noting, since material describing nineteen's features talks about something you cannot yet run in production. What you can run today is eighteen, and its most important change concerns how data gets read from disk.

Asynchronous input and output

This is the most substantial internal change in years, and it pays to understand why now.

For most of its history the database read data synchronously: it asked the system for a block, waited for the answer, asked for the next. On a spinning disk that made sense, since the head had to move anyway. On a solid state disk, and especially on network storage in a cloud, it means wasting time waiting while the device could serve dozens of requests at once.

Eighteen introduced a subsystem issuing many read requests simultaneously. The effect shows most clearly when scanning large tables and during operations reading sequentially, where the latency of a single read had been the limiting factor.

The largest gain lands on cloud deployments, where the disk is a network resource and single read latency runs ten times higher than on local storage. There the difference is often felt without any query tuning.

Nineteen extends the mechanism with automatic scaling of the processes serving reads and with exposing that subsystem's statistics in the execution plan. The second is practically more important, since it lets you see whether a query actually uses the new mechanism rather than assuming.

Default configuration and why it is conservative

Before reaching for new capabilities, check the thing responsible for most performance complaints.

The default settings are chosen so the database starts on a machine with modest resources. That means on a server with thirty two gigabytes of memory the database will by default use a fraction of what it could.

Three parameters make the largest difference. The shared buffer size, usually set to a quarter of machine memory. The estimate of memory available for system caching, usually three quarters. And memory for sorting and joining operations, chosen carefully, since it multiplies by the number of parallel operations rather than the number of connections.

A fourth parameter concerns the cost of a random read. The default comes from the era of spinning disks and tells the planner a random read is four times more expensive than a sequential one. On a solid state disk that ratio is far smaller, and leaving the old value makes the planner avoid indexes where it should reach for them.

Changing that one setting is sometimes a fix with a greater effect than a week of rewriting queries, and it is one line in the configuration.

Where the bottlenecks actually sit

Diagnosis in this database has a set order worth knowing, since intuition usually points the wrong way.

First place goes to missing indexes. A query scanning a whole table while looking for one row is the most common cause and the easiest to fix. The execution plan shows it directly.

Second are indexes that exist and go unused, because the query was written in a way that bypasses them. A function applied to a column, a type mismatch, or a text search with a wildcard at the start of the pattern all prevent the planner from using an index.

Third is a problem with the number of queries rather than their content. A loop running a query per list element generates hundreds of network round trips, each taking milliseconds. The total often exceeds one query returning everything at once.

Fourth is table bloat. The database does not physically remove old row versions immediately, and under heavy modification a table occupies several times more space than the row count suggests. The cleanup process runs in the background and under heavy traffic sometimes falls behind.

Keep that order, since tuning configuration with an index missing is like adding oil to an engine with no spark plugs.

Data types worth knowing

This database holds a set going beyond what other systems offer, and some of it saves considerable code.

A type storing data as a document lets you hold a variable structure in a column, with indexing and queries over the contents. That is a sensible route for data whose shape depends on record type, and a poor one for data with a fixed shape that was merely more convenient to dump into one field.

Range types describe an interval of values, a booking period for instance, and let you enforce that two intervals never overlap. Without them the same requires a trigger or a lock and comes out worse.

Arrays let you hold a list of values in one column and make sense for short, fixed lists, labels for instance. For a relation meant to grow, a separate table is right.

Full text search is built in and, on small collections, replaces a separate search engine. Handling inflected forms in a given language requires a dictionary that has to be added, and that step gets skipped by people surprised that search misses inflected words.

The boundary runs at language analysers, synonyms, highlighting of matched fragments, and blending keyword matching with semantic similarity. The built in mechanism offers none of that and Elasticsearch does, at the price of a separate cluster demanding knowledge of Java virtual machine memory tuning, shards, and replicas.

An extension for vector similarity search lets you hold embeddings beside the data and query nearest neighbours. Across collections up to a few hundred thousand fragments that is usually sufficient and operationally simpler than a separate vector database.

Indexes beyond the default kind

The default index kind covers most cases and not all, and choosing the right one is sometimes the difference between a second and a millisecond.

An inverted index serves columns holding many values: documents, arrays, full text search. Without it a query asking whether a key exists in a document column scans the whole table, since the default kind cannot look inside a value.

A block range index occupies a fraction of the space and suits naturally ordered data, above all time. An event table with a timestamp column appended chronologically gets range search almost free from it, at the cost of accuracy on unordered data.

A partial index covers only rows meeting a condition. On a table where ninety nine percent of records carry a completed status while queries concern only active ones, an index over the active rows alone is a hundred times smaller and faster.

An expression index solves the problem described earlier: a function applied to a column rules out an ordinary index, while an index built on exactly that expression works. That is the right answer for case insensitive search.

Remember the cost too. Every index slows writes and occupies space, so a table with eight indexes pays for them on every row change. Indexes the planner never uses show up in system statistics and deserve reviewing occasionally, since they linger after queries that no longer exist.

Transactions and isolation levels

The default isolation level here differs from what some people arriving from other databases assume, and that produces bugs hard to reproduce.

By default every query inside a transaction sees a snapshot of the data as of its own start. That means two reads of the same table in one transaction can return different results if somebody committed a change between them.

For operations resting on read, decide, write, that property leads to the classic problem: two processes read the same state, both conclude they may act, and both write. The symptom is a double booking or a balance that went below zero despite a check.

Three answers exist. A higher isolation level, where the database detects the conflict and rejects one transaction, which requires retry handling in code. A row lock on read, simpler and limiting concurrency. Or a condition inside the writing query itself, so the write fails when the state changed.

The last route is usually the best and the least often chosen, since it requires thinking about the query rather than adding a lock. An update with a condition checking the previous value handles most such cases with no extra infrastructure.

PostgreSQL against the alternatives

The first row pays off under steady heavy load and where data location requirements apply. The price is operational work: backups, major version upgrades, monitoring, and a recovery procedure somebody has to rehearse before it is needed.

Managed variants take that work over and differ mainly in what they add around it. The ability to create a database branch for a deployment preview is an example of something expensive to reproduce yourself, and one that changes how a team works when reviewing merge requests.

The last row recalls a common misunderstanding. An in memory database does not replace a relational one but stands beside it as a layer speeding up reads. Treating it as the source of truth is an architectural mistake easy to reach, since it is fast and convenient.

Replication and maintenance

Two kinds of replication solve different problems, and confusing them leads to poor decisions.

Physical replication copies the whole database byte for byte and exists for availability and spreading reads. A replica is a faithful copy, so its schema cannot be changed and its data cannot be filtered.

Logical replication transmits changes at row level and lets you choose what replicates. It exists for moving data between major versions, integrating with other systems, and upgrading without extended downtime.

Nineteen adds several things here worth knowing when planning: replicating sequence values, enabling logical replication without a server restart, and publishing all tables except named ones. The first two together considerably simplify a major version upgrade, until now the most stressful operation in this database's lifecycle.

Cleanup after modifications is a separate matter. The background process removes outdated row versions and under heavy traffic sometimes falls behind, which shows as growing table sizes and slower queries. Watch it early, since fixing a bloated table requires rewriting it, and that blocks.

Common mistakes

The first is leaving the default configuration on a production machine. The database then uses a fraction of available memory, and the complaint reads "it is slow".

The second is the old random read cost on a solid state disk. The planner then avoids indexes despite their being suitable and chooses a full table scan.

The third is a query in a loop instead of one query returning everything. Hundreds of network round trips add up to longer than a single operation.

The fourth is a function applied to a column in a condition. The index stops being usable, and the execution plan shows a full table scan despite the index existing.

The fifth is holding fixed shape data in a document column. You lose type control and simple indexes and gain only convenience on the first write.

The sixth is having no rehearsed recovery procedure from backups. A backup nobody ever restored is an assumption rather than a safeguard.

FAQ

Which version is stable right now?

The eighteenth line, in patch releases. Nineteen is in beta with a stable release planned for the second half of 2026, so material describing its features concerns something you cannot yet run in production.

What did asynchronous input and output deliver?

The ability to issue many read requests at once rather than waiting for each in turn. The largest gain shows when scanning large tables and in cloud deployments, where the disk is a network resource with high single read latency.

Where do I start with performance tuning?

With execution plans and indexes rather than configuration. A missing index costs more than any setting, and once added it pays to check buffer sizes and the random read cost, since the defaults come from an era of different disks.

Does PostgreSQL suit data with no fixed schema?

Yes, through a document column with indexing and queries over the contents. Use it for genuinely variable data rather than fixed data, though, since with the latter you lose type control and gain nothing.

Do I need a separate vector database?

Across collections up to a few hundred thousand fragments usually not, since the similarity search extension keeps embeddings beside the data and simplifies the whole infrastructure. Across millions of documents a purpose built database starts to lead.

Documentation sits on the project site, and the upcoming release is described in the version 19 notes.