PostgreSQL 19: Key Features 🐘🐘🐘

@alxshp
alex shapalov@alxshp
3 views Nov 20, 2026 ~8 min read
Advertisement

PostgreSQL 19 is almost here. And this release has some really useful changes.

Media image

I went through the release notes, and a few things caught my attention.

We finally get a better way to clean up large tables, simpler SQL for common problems, improvements to replication, and more control over query plans.

Here are the features I think developers should know about.

https://x.com/i/status/2085450621780730334

1. REPACK: Clean Up Tables Without Blocking Your App

This might be my favorite change in PostgreSQL 19.

If you've worked with Postgres long enough, you probably know about table bloat.

When you update or delete rows, PostgreSQL doesn't always immediately return that space to the operating system.

Over time, tables can grow much larger than they need to be.

We already have VACUUM, but sometimes we need VACUUM FULL to reclaim disk space. The problem? It locks the table.

PostgreSQL 19 introduces a new command:

REPACK orders;

And, more importantly:

REPACK (CONCURRENTLY) orders;

This allows PostgreSQL to rewrite a table and reclaim unused disk space while other transactions continue reading and writing.

There is still a short exclusive lock during the final swap, and the operation needs extra disk space. But it's a big improvement over locking the table for the entire rewrite.

You can also monitor progress:

SELECT * FROM pg_stat_progress_repack;

For anyone maintaining large production databases, this is a feature worth testing.

About AI:

https://x.com/i/status/2085777788989288655

2. ON CONFLICT DO SELECT: Finally!

Here's a common problem.

You want to create a user. But if that user already exists, you just want to return the existing record.

Previously, developers often needed extra queries or awkward workarounds.

PostgreSQL 19 makes this much easier.

INSERT INTO users (email)
VALUES ('alex@example.com')
ON CONFLICT (email) DO SELECT
RETURNING id, email;

That's it.

If the email doesn't exist, PostgreSQL inserts a new user.

If the email already exists, PostgreSQL returns the existing user without updating it.

This requires a unique constraint on email.

I really like this one because the get-or-create pattern is everywhere.

Think about user registration, API requests, background jobs, and any operation that might run more than once.

Less application code. Fewer workarounds.

3. IGNORE NULLS in Window Functions

Another small feature that can save a lot of SQL.

Imagine you store daily sales:

DaySalesMonday100TuesdayNULLWednesday150ThursdayNULL

You want to display the most recent known sales value for each day.

Previously, this often required more complicated queries.

PostgreSQL 19 supports IGNORE NULLS with functions like LAG, LEAD, FIRST_VALUE, and LAST_VALUE.

SELECT
  day,
  LAST_VALUE(sales) IGNORE NULLS OVER (
    ORDER BY day
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND CURRENT ROW
  ) AS latest_sales
FROM daily_sales;

Result:

DayLatest salesMonday100Tuesday100Wednesday150Thursday150

Simple.

This is especially useful for analytics, time-series data, financial reporting, and dashboards.

4. WAIT: Read Your Own Writes on Replicas

If you've built applications with PostgreSQL read replicas, you've probably seen this problem.

  • Your application writes data to the primary database.
  • It immediately reads from a replica.
  • The new data isn't there yet.
  • Why?

    Because replication takes time.

    PostgreSQL 19 introduces a new WAIT command that helps solve this.

    First, after a transaction commits on the primary, get a WAL position:

    SELECT pg_current_wal_insert_lsn();

    Then, on the standby, wait for that position to be replayed:

    WAIT FOR LSN '0/0306EE20';

    The LSN here is just an example. Your application uses the actual value returned by the primary.

    Once the wait succeeds, your application can query the replica knowing it has replayed the requested changes.

    SELECT * FROM orders WHERE id = 42;

    You can also specify a timeout:

    WAIT FOR LSN '0/0306EE20'
    WITH (TIMEOUT '2s');

    This is useful for applications that want to use replicas without showing users stale data immediately after a write.

    5. Export JSON Directly With COPY

    PostgreSQL already has great JSON support.

    But exporting query results as JSON was not always as convenient as it could be.

    PostgreSQL 19 adds JSON output to COPY.

    COPY (
    SELECT id, email FROM users
    )
    TO STDOUT WITH (FORMAT JSON);

    You can also export a single JSON array:

    COPY users
    TO STDOUT WITH (
      FORMAT JSON,
      FORCE_ARRAY
    );
    This makes it easier to export data for APIs, scripts, backups, or other systems.
    There's another improvement for imports.
    With ON_ERROR SET_NULL, invalid field values in text or CSV imports can be replaced with NULL instead of stopping the whole import.
    COPY users FROM '/tmp/users.csv'
    WITH (
      FORMAT CSV,
      HEADER true,
      ON_ERROR SET_NULL
    );

    Of course, you still need to consider constraints such as NOT NULL.

    But for messy real-world data, this is useful.

    6. More Control Over the Query Planner

    This one is interesting for developers who spend time optimizing SQL.

    Sometimes PostgreSQL chooses a query plan that looks reasonable but runs slowly.

    Maybe it chooses a nested loop instead of a hash join. Maybe it scans a table when you expected an index.

    We already have EXPLAIN, but changing the planner's decision for a specific query has historically been harder.

    PostgreSQL 19 includes a new module called pg_plan_advice.

    After loading it, you can ask PostgreSQL to show its planning decisions:

    LOAD 'pg_plan_advice';
    
    EXPLAIN (COSTS OFF, PLAN_ADVICE)
    SELECT *
    FROM orders
    WHERE customer_id = 42;

    The module can generate plan advice, which you can then use to influence decisions such as join order, scan methods, and join algorithms.

    There's also pg_stash_advice, which can automatically apply stored advice to matching queries.

    This doesn't mean everyone should start overriding the planner.

    Usually, PostgreSQL makes good choices. And forcing a plan can become a problem when your data changes.

    But when you're debugging a difficult performance problem, having more control is valuable.

    7. Autovacuum Gets Smarter

    Autovacuum is one of those PostgreSQL features that you don't think about until something goes wrong.

    It cleans up old row versions and helps keep tables healthy.

    PostgreSQL 19 brings two important improvements.

    Parallel autovacuum

    Autovacuum can now use parallel workers for index vacuuming.

    For large tables with many indexes, this could help maintenance finish faster.

    Better prioritization

    PostgreSQL also introduces a scoring system that helps decide which tables autovacuum should process first.

    You can inspect those scores:

    SELECT *
    FROM pg_stat_autovacuum_scores;

    This gives database administrators more visibility into what autovacuum is doing and why.

    These changes won't magically eliminate table bloat, but they should make PostgreSQL easier to operate as databases grow.

    8. Logical Replication Gets Better

    PostgreSQL 19 also improves logical replication.

    One welcome addition is support for replicating sequence values.

    For example, you can now create a publication for sequences:

    CREATE PUBLICATION seq_pub
    FOR ALL SEQUENCES;
    
    You can also exclude specific tables from a publication:
    
    CREATE PUBLICATION app_pub
    FOR ALL TABLES
    EXCEPT (TABLE audit_logs);

    And PostgreSQL can now enable logical replication without a server restart when wal_level is configured as replica.

    For teams using CDC, data synchronization, and database migration tools, these are useful improvements.

    9. Faster Queries and Better Performance

    Not every important feature comes with a new SQL command.

    PostgreSQL 19 includes several performance improvements under the hood.

    A few worth mentioning:

  • Faster foreign-key checks.
  • Better asynchronous I/O scheduling.
  • Automatic scaling of worker-based I/O processes.
  • Improved sorting with radix sort.
  • Faster CSV and text imports using SIMD instructions.
  • Better optimization of some joins and aggregations.
  • LZ4 becomes the default TOAST compression method.
  • There's also improved I/O reporting in EXPLAIN.

    EXPLAIN (ANALYZE, BUFFERS, IO)
    SELECT *
    FROM orders
    WHERE total > 100;

    This can help you understand where PostgreSQL spends time reading data.

    Performance gains will depend on your queries, data, hardware, and configuration. But it's good to see continued investment in the database engine itself.

    10. A Few Changes to Check Before Upgrading

    There are also some changes that could affect existing applications.

    One important example: JIT is disabled by default in PostgreSQL 19.

    The PostgreSQL team found that the previous cost-based decision to enable JIT wasn't always reliable.

    If you run large analytical queries and benefit from JIT, you'll want to check your configuration after upgrading.

    There are also changes related to MD5 password authentication, text search behavior, and some server settings.

    And remember: a major version upgrade still requires a proper migration, such as pg_upgrade or dump and restore.

    Read the migration notes before upgrading production.

    https://x.com/i/status/2084709685542961631

    Final Thoughts

    ##

    What I like about PostgreSQL 19 is that many of its improvements solve problems developers already have.

    Cleaning up large tables without long blocking operations.

    Getting an existing row from an insert without extra workarounds.

    Handling missing values in SQL.

    Waiting for replicas to catch up.

    Exporting JSON more easily.

    And getting more visibility into query plans and database maintenance.

    None of these features alone changes everything.

    But together, they make PostgreSQL easier to use, easier to maintain, and more powerful.

    That's exactly what I want from a new Postgres release.

    PostgreSQL 19 is scheduled for November 19, 2026. These features are based on the RC2 documentation and could still receive final fixes before release.

    🐘 Which PostgreSQL 19 feature are you most excited about?

    Official release notes: postgresql.org/docs/release/19.0

    🐘 2026: THE YEAR POSTGRES WON 🚀

    https://x.com/i/status/2120927011388670079

    Also you can use new terminal:

    https://x.com/i/status/2106068877717496313

    github.com/pgrundev/pgbot

    github.com/pgrundev/

    🚀 PostgreSQL 19 delivers up to 2× faster INSERTs with foreign keys.

    Same schema. Same constraints. Less overhead.

    🔥 PostgreSQL 19 introduces REPACK (CONCURRENTLY).

    Reclaim disk space and reorganize tables without blocking reads and writes.

    🐘 Finally! PostgreSQL 19 adds IGNORE NULLS to window functions.

    Cleaner SQL. Fewer workarounds.

    @grok how to fast and easy update Postgres on production without downtime?!

    Actions
    What You Can Do
    • Export as PDF or Markdown
    • Batch Export to Notion
    • Bookmark & Highlight
    • Screenshot Tweet
    Create Free Account

    Includes 7-day Premium trial

    Advertisement