
Published by TERMTRIX | Backend Engineering • Database Internals • Open Source
PostgreSQL has always been one of the most powerful open-source relational database systems. With PostgreSQL 19, the project introduces several improvements that focus on production workloads, database administration, distributed systems, query performance and developer experience.
Released on November 19, 2026, PostgreSQL 19 brings a particularly interesting set of capabilities:
- Concurrent table repacking.
- Better query-plan control.
- Improved logical replication.
- Read-your-writes consistency on standby servers.
- Parallel autovacuum.
- More efficient I/O management.
- New SQL capabilities.
- Better observability and administration.
But what do these features actually mean for developers?
In this article, we'll explore the important PostgreSQL 19 changes, understand the problems they solve and see how they apply to real-world applications.
1. REPACK (CONCURRENTLY): Reorganize tables without blocking normal operations
One of the biggest additions in PostgreSQL 19 is the new REPACK command.
The problem
Consider an application that continuously inserts, updates and deletes millions of records.
Over time, PostgreSQL tables can accumulate unused space. This is commonly called table bloat.
Previously, developers used commands such as:
VACUUM FULLCLUSTER
However, these operations have important locking trade-offs. In particular, VACUUM FULL and CLUSTER can block normal access while they operate.
For a production application, that can be problematic.
What's new?
PostgreSQL 19 introduces:
REPACK (CONCURRENTLY) orders;
The new command reorganizes table data and reclaims unused space. With CONCURRENTLY, it can perform this operation without blocking normal reads and writes.
The existing VACUUM FULL and CLUSTER commands remain available for compatibility.
Real-world example
Imagine you're building an e-commerce platform.
Your orders table receives thousands of updates every hour:
UPDATE orders
SET status = 'completed'
WHERE id = 1001;
As your application grows, maintaining table efficiency becomes increasingly important.
With concurrent repacking, database maintenance can happen with less disruption to application traffic.
Where is this useful?
- High-traffic applications.
- Messaging platforms.
- E-commerce systems.
- SaaS applications.
- Large transactional databases.
2. pg_plan_advice and pg_stash_advice: More control over query execution plans
PostgreSQL has a query planner that determines how SQL queries should be executed.
For example:
SELECT *
FROM messages
WHERE room_id = 100
ORDER BY created_at DESC
LIMIT 50;
The planner evaluates different execution strategies, including:
- Sequential scans.
- Index scans.
- Bitmap scans.
- Nested-loop joins.
- Hash joins.
Usually, PostgreSQL selects a suitable execution plan automatically.
However, as your database grows and data distribution changes, a query that was previously fast can become slow because the planner selects a different execution plan.
What's new?
PostgreSQL 19 introduces two extensions:
pg_plan_advice
Provides a way to influence and stabilize query planner decisions using a plan-advice language.
pg_stash_advice
Stores query-specific advice and automatically applies it based on the query identifier.
This means database administrators can experiment with execution plans and stabilize problematic queries without necessarily modifying application SQL.
Real-world example
Imagine your messaging platform has 50 million messages.
A frequently executed query suddenly becomes slow after a major data growth event.
Instead of immediately rewriting the application, you can investigate the execution plan and use plan advice to test alternative strategies.
Where is this useful?
- Large databases.
- Query-heavy applications.
- Analytics platforms.
- Database performance engineering.
- Applications with unpredictable query workloads.
3. Improved logical replication: Enable logical replication without a server restart
Logical replication is an important PostgreSQL capability for distributing selected database changes between servers.
Traditionally, enabling logical replication required configuring:
wal_level = logical
Changing this configuration required restarting PostgreSQL.
That can be inconvenient in production environments.
What's new?
PostgreSQL 19 can automatically raise its effective WAL level to logical when logical replication is needed, provided the configured wal_level is replica.
The new configuration parameter:
effective_wal_level
helps identify the WAL level actually being used.
Why does this matter?
You can initialize logical replication more conveniently without requiring a restart just to change the configured WAL level from replica to logical.
Real-world architecture
Imagine this architecture:
PostgreSQL Primary
|
| WAL / Logical Decoding
|
v
Go CDC Consumer
|
v
Kafka / Event Processing
|
v
Other Microservices
Your Go application can consume database changes through logical replication and distribute those changes to other services.
This is particularly useful when building:
- Change Data Capture (CDC) systems.
- Event-driven architectures.
- Data synchronization services.
- Real-time analytics pipelines.
- Distributed applications.
Important: Logical replication is not the same as physical streaming replication. Logical replication exposes selected database changes at a logical level, while physical replication transfers WAL-level database changes.
4. Logical replication of sequence values
Another important improvement is logical replication of sequence values.
PostgreSQL sequences are commonly used to generate identifiers for records.
For example:
CREATE TABLE users (
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
username TEXT NOT NULL
);
When records are inserted, PostgreSQL uses a sequence to generate identifiers.
Previously, logical replication did not automatically synchronize sequence values in the same way it replicated table data.
What's new?
PostgreSQL 19 adds support for publishing and synchronizing sequence values.
You can publish sequences using:
CREATE PUBLICATION app_publication
FOR ALL SEQUENCES;
PostgreSQL also introduces:
REFRESH SEQUENCES
for synchronizing sequence definitions and values through subscription operations.
Real-world example
Suppose you maintain a primary database and a reporting database.
Both databases need consistent sequence state when using logical replication.
Sequence synchronization helps reduce the risk of sequence-related conflicts when the subscriber later needs to generate identifiers.
This is useful for:
- Database migration.
- Logical replication.
- Data synchronization.
- Multi-environment deployments.
5. WAIT FOR LSN: Read-your-writes consistency on standby servers
This is one of the most interesting additions for distributed systems engineers.
First, let's understand the problem.
Consider a primary and standby PostgreSQL architecture:
Application
|
Write transaction
|
v
Primary PostgreSQL
|
WAL
|
v
Standby PostgreSQL
When your application writes data to the primary, the transaction may commit before the standby has replayed the corresponding WAL records.
Now imagine the application immediately reads the same record from the standby.
The standby might return stale data.
This is a replication-lag problem.
What's new?
PostgreSQL 19 introduces:
WAIT FOR LSN
A session connected to a standby can wait for a specified Log Sequence Number (LSN) to reach a requested state.
The supported states include:
- Written.
- Flushed.
- Replayed.
For read-your-writes consistency, the application needs the relevant WAL position to be replayed before reading.
Real-world example
Imagine TERMTRIX Socket Engine.
- User sends a message.
- Primary PostgreSQL commits the message.
- Application obtains the transaction's commit LSN.
- Application sends a read request to a standby.
- The standby waits until the requested LSN has been replayed.
- Application retrieves the newly committed message.
This helps ensure that users can read their own writes even when reads are routed to standby servers.
Where is this useful?
- Distributed applications.
- Read-replica architectures.
- Messaging platforms.
- Financial applications.
- Applications requiring read-your-writes consistency.
6. Parallel autovacuum: Better database maintenance
Autovacuum is one of PostgreSQL's most important background processes.
It helps reclaim storage associated with obsolete row versions, maintain visibility information and update planner statistics.
However, large tables and indexes can make vacuum operations expensive.
What's new?
PostgreSQL 19 introduces parallel workers for autovacuum index processing.
Two important configuration parameters are:
autovacuum_max_parallel_workers
autovacuum_parallel_workers
PostgreSQL can use parallel workers to vacuum table indexes.
Autovacuum prioritization
Another improvement is a new scoring system that determines which tables should be processed first.
The new configuration parameters include:
autovacuum_freeze_score_weight
autovacuum_multixact_freeze_score_weight
autovacuum_vacuum_score_weight
autovacuum_vacuum_insert_score_weight
autovacuum_analyze_score_weight
This gives database administrators more control over maintenance priorities.
Real-world example
Imagine a SaaS application with:
- A users table containing 2 million records.
- An events table containing 100 million records.
- A messages table receiving continuous updates.
- An audit table storing historical activity.
Not every table requires maintenance at the same frequency.
The new scoring system helps PostgreSQL prioritize tables based on maintenance requirements.
Why it matters: Better maintenance scheduling for large and continuously changing databases.
7. Asynchronous I/O improvements
PostgreSQL 18 introduced improvements to asynchronous I/O.
PostgreSQL 19 builds on that subsystem.
Previously, I/O worker management had less automatic scaling.
What's new?
When io_method is set to worker, PostgreSQL can automatically adjust the number of I/O workers.
New configuration parameters include:
io_min_workers
io_max_workers
io_worker_idle_timeout
io_worker_launch_interval
These replace the previous io_workers setting.
PostgreSQL also improves asynchronous I/O read-ahead scheduling for large requests.
Real-world example
Consider an analytics application that reads large amounts of historical data.
A query might scan millions of records:
SELECT *
FROM security_events
WHERE created_at >= NOW() - INTERVAL '30 days';
Better I/O scheduling can help PostgreSQL manage these workloads more efficiently.
Actual performance improvements depend on storage hardware, query patterns and system configuration.
8. Foreign key performance improvements
Foreign keys are essential for maintaining referential integrity.
For example:
CREATE TABLE users (
id BIGINT PRIMARY KEY
);
CREATE TABLE interviews (
id BIGINT PRIMARY KEY,
user_id BIGINT REFERENCES users(id)
);
Every interview must reference an existing user.
Foreign key checks introduce additional work during inserts and updates.
What's new?
PostgreSQL 19 improves the performance of foreign key constraint checks.
The official release announcement reports that some bulk-insert tests were nearly twice as fast.
This is not a universal performance guarantee. Results depend on the workload.
Real-world example
Imagine CareerLab imports thousands of interview records.
Each interview references a user.
Improved foreign key checking can reduce the overhead associated with bulk inserts.
This is particularly useful for:
- Data imports.
- Transaction-heavy applications.
- Relational data models.
- Bulk processing pipelines.
9. Query planner optimizations
PostgreSQL 19 includes several internal query planner improvements.
These do not require developers to rewrite their existing SQL queries.
Some important improvements include:
A. Better NOT IN optimization
PostgreSQL can convert certain NOT IN clauses into more efficient anti-joins when NULL values are not present.
B. Improved LEFT JOIN optimization
More LEFT JOIN operations can be converted into anti-joins when the conditions allow it.
C. Improved Memoize support
Memoize can now be used for additional anti-join scenarios.
D. Early aggregation
Some aggregate operations can be performed before joins, reducing the number of rows that need to be processed.
E. Better hash joins
Improvements have been made to hash join handling of tuples containing NULL join keys.
F. Improved incremental sorting
The planner can consider explicit incremental sorts in additional Append and MergeAppend scenarios.
G. Better statistics
PostgreSQL adds optimizer statistics support for boolean-returning functions and virtual generated columns.
These changes help PostgreSQL make better execution decisions.
10. LISTEN / NOTIFY improvements
PostgreSQL supports lightweight event notifications using LISTEN and NOTIFY.
For example:
LISTEN new_message;
NOTIFY new_message, 'message_created';
Applications can listen for notifications and react when an event occurs.
What's new?
Previously, a NOTIFY operation could wake up many backends.
PostgreSQL 19 improves this behavior by waking only backends listening to the specified notification channels.
Real-world example
Imagine TERMTRIX has a background job processing service.
When a new job is inserted, your application can send a notification:
NOTIFY new_job, 'job_created';
A worker listening to that channel can react to the event.
This can reduce unnecessary backend wake-ups in multi-channel workloads.
Important: LISTEN/NOTIFY is not a durable message queue. Use a proper queue or event-streaming system when you need persistent delivery, replay and consumer acknowledgements.
11. JSON and SQL/JSON improvements
PostgreSQL already has powerful JSON and JSONB support.
PostgreSQL 19 expands SQL/JSON path functionality with additional string manipulation methods.
New methods include:
ltrim()
rtrim()
btrim()
lower()
upper()
initcap()
replace()
split_part()
Example use case
Imagine you store security events as JSONB:
{
"source": "NGINX",
"severity": "HIGH",
"category": "AUTHENTICATION"
}
You can use JSON path string methods to transform and process values during JSON queries.
This is particularly useful for:
- Security event processing.
- API response data.
- Event metadata.
- Semi-structured application records.
12. COPY improvements: JSON export and better data ingestion
COPY is PostgreSQL's bulk data import/export mechanism.
It is commonly used for large datasets because it avoids the overhead of inserting or retrieving records one at a time.
PostgreSQL 19 introduces several improvements.
A. JSON output
You can now export query results in JSON format using COPY TO.
The FORCE_ARRAY option allows the output to be represented as one JSON array.
B. Better error handling
COPY FROM adds:
ON_ERROR SET_NULL
This allows invalid input values to be set to NULL instead of failing the entire operation.
C. Multiple header lines
COPY FROM can now skip multiple header lines.
D. Partitioned table support
COPY TO can directly process partitioned tables.
E. Performance
COPY FROM text and CSV input processing receives SIMD-based performance improvements.
Real-world example
Suppose your analytics pipeline needs to export 500,000 interview records.
Instead of retrieving every row individually through Python, you can use PostgreSQL's bulk-export capabilities.
This can simplify data engineering pipelines.
13. Window functions: IGNORE NULLS
PostgreSQL 19 adds IGNORE NULLS and RESPECT NULLS support to selected window functions.
Supported functions include:
lead()lag()first_value()last_value()nth_value()
What's the problem?
Suppose you have a table of user activity:
DayScoreMonday80TuesdayNULLWednesday90ThursdayNULLFriday95
Sometimes you want to retrieve the next or previous non-NULL value.
Previously, these window functions did not provide this standard option.
What's new?
You can now specify:
IGNORE NULLS
to skip NULL values when evaluating supported window functions.
This is useful for:
- Time-series analysis.
- Historical records.
- Analytics.
- Missing-value handling.
- Activity tracking.
14. New random date and timestamp generation
PostgreSQL 19 extends the random(min, max) function to support:
datetimestamptimestamptz
Previously, random values were primarily used with numeric types.
Real-world example
You are building a testing environment and need randomly generated dates.
The new date and timestamp variants make this easier without writing custom date-generation logic.
This is useful for:
- Test-data generation.
- Development environments.
- Database benchmarking.
- Simulated datasets.
Remember that random() is not suitable for generating security tokens or cryptographic secrets.
15. Improved EXPLAIN ANALYZE
EXPLAIN ANALYZE is one of the most important tools for PostgreSQL performance engineering.
It helps developers understand how queries execute.
PostgreSQL 19 introduces additional reporting capabilities.
A. I/O reporting
A new IO option reports asynchronous I/O activity.
For example:
EXPLAIN (ANALYZE, IO)
SELECT *
FROM messages
WHERE room_id = 100;
B. WAL reporting
EXPLAIN (ANALYZE, WAL) now includes reporting of full-page write bytes.
C. Memoize reporting
EXPLAIN output now includes Memoize cache and lookup estimates.
Why does this matter?
You can investigate:
- Query execution time.
- Asynchronous I/O activity.
- WAL generation.
- Cache behavior.
- Query plan decisions.
This is useful when diagnosing slow queries in production-like environments.
Conclusion
PostgreSQL 19 is not just another database release with a few new SQL commands.
It brings meaningful improvements across database administration, query planning, replication, observability, I/O management and application development.
Some of the most interesting additions are:
REPACK (CONCURRENTLY)for less disruptive table maintenance.pg_plan_advicefor query-plan control.INSERT ... ON CONFLICT DO SELECTfor get-or-create operations.WAIT FOR LSNfor read-your-writes consistency.- Improved logical replication and sequence synchronization.
- Parallel autovacuum and scoring-based prioritization.
- Better asynchronous I/O management.
- New monitoring views and enhanced EXPLAIN output.
For backend developers, the biggest takeaway is this:
Don't treat PostgreSQL as just a place to store application data. Understand its internals, replication model, storage engine, transaction processing and query planner.
That's how you move from writing database queries to engineering reliable data systems.
Official resources
- PostgreSQL 19 Release Notes: https://www.postgresql.org/docs/19/release-19.html
- PostgreSQL 19 Documentation: https://www.postgresql.org/docs/19/
- PostgreSQL 19 Official Announcement: https://www.postgresql.org/about/news/postgresql-19-released-3449/
About TERMTRIX
TERMTRIX is a developer community focused on backend engineering, system design, databases, AI engineering, cybersecurity and open-source development.
Our goal is to help developers around the world learn deeply, build practical projects and contribute to open-source technology.
Learn. Build. Contribute.
Follow TERMTRIX and become part of the global developer community.