PostgreSQL 19 is the most consequential release since version 10 introduced declarative partitioning. The headline feature is SQL/PGQ — native property graph queries that run directly on your existing relational tables, with no new storage engine, no extensions, and no data migration. Combined with atomic get-or-create via ON CONFLICT DO SELECT, zero-downtime table repacking with REPACK (CONCURRENTLY), query plan pinning via pg_plan_advice, JIT disabled by default, parallel autovacuum for indexes, and native JSON export from COPY TO, PostgreSQL 19 gives builders and small teams the tools that previously required a dedicated graph database or third-party extensions like pg_repack.
As of August 2026, PostgreSQL 19 Beta 2 is available (released July 16, 2026) with the feature set frozen. The final general availability release is expected around September or October 2026 (PostgreSQL 19 Beta 2 announcement).
Last verified: 2026-08-06
- Headline feature: SQL/PGQ property graph queries (SQL:2023 standard)
- Biggest quality-of-life win:
ON CONFLICT DO SELECTfor atomic get-or-create- Most impactful ops feature:
REPACK (CONCURRENTLY)replacespg_repackextension- Breaking change: JIT compilation is now off by default
- Current status: Beta 2 (July 16, 2026); GA expected Sep/Oct 2026
- Pricing: Free, open-source (PostgreSQL License)
What Are the Headline Features in PostgreSQL 19?
PostgreSQL 19 ships with over a dozen new features across six areas: graph queries, DML improvements, query planning, maintenance, operations, and upgrade changes. The seven that matter most for builders and small teams are: SQL/PGQ graph queries, ON CONFLICT DO SELECT, REPACK (CONCURRENTLY), pg_plan_advice, JIT off by default, parallel autovacuum for indexes, and COPY TO with FORMAT JSON (PostgreSQL 19 Beta 1 announcement).
Here is a summary of every major feature and what it replaces:
| Feature | What It Does | What It Replaces | Category |
|---|---|---|---|
| SQL/PGQ | Graph queries on existing relational tables | Dedicated graph databases (for light use) | Developer experience |
| ON CONFLICT DO SELECT | Atomic get-or-create in one statement | Two-query INSERT + SELECT pattern | DML |
| REPACK (CONCURRENTLY) | Online table rewrite without exclusive lock | VACUUM FULL, pg_repack extension | Maintenance |
| pg_plan_advice | Capture and pin good query plans | Manual planner tuning, vendor hints | Query planning |
| JIT off by default | Avoids latency on short OLTP queries | JIT enabled by default (PG 12–18) | Configuration |
| Parallel autovacuum (indexes) | Multiple workers clean indexes | Single-worker vacuum | Maintenance |
| COPY TO FORMAT JSON | Streaming JSON export (NDJSON or array) | row_to_json() workarounds, external tools | Data export |
Sources: PostgreSQL 19 release notes, Neon PostgreSQL 19 feature guide.
How Do Graph Queries Work in PostgreSQL 19?
Graph queries in PostgreSQL 19 use the SQL/PGQ standard (ISO/IEC 9075-16:2023) to let you query relationships between your existing relational tables using graph pattern-matching syntax, without installing a separate graph database like Neo4j or the Apache AGE extension (PostgreSQL 19 Beta 1 announcement, Neon SQL/PGQ guide).
Defining a property graph
You create a property graph that maps your existing tables to vertices (nodes) and edges (relationships). The syntax is straightforward:
CREATE PROPERTY GRAPH my_shop
VERTEX TABLES (
customers, orders, products
)
EDGE TABLES (
customer_orders
SOURCE KEY (customer_id) REFERENCES customers (id)
DESTINATION KEY (order_id) REFERENCES orders (id),
order_items
SOURCE KEY (order_id) REFERENCES orders (id)
DESTINATION KEY (product_id) REFERENCES products (id)
);
This does not copy any data. It works like a view — you are pointing PostgreSQL at tables you already have. The three data tables become vertices and the two join tables become edges. Your schema stays completely unchanged (Neon SQL/PGQ guide).
Querying with GRAPH_TABLE and MATCH
Once the graph is defined, you use the GRAPH_TABLE function with MATCH patterns to traverse relationships. To find all products a customer named Alice has ordered:
SELECT * FROM GRAPH_TABLE (my_shop
MATCH (c IS customers WHERE c.name = 'Alice')
-[IS customer_orders]->(o IS orders)
-[IS order_items]->(p IS products)
COLUMNS (p.name AS product_name, p.price
);
The arrow syntax -[IS customer_orders]-> means a directed edge from customer to order. You can follow the chain: customer to order to product. This replaces a four-join SQL query that would chain across five tables. The result is the same, but the graph syntax is significantly easier to read and maintain.
Key limitation: no variable-length paths (yet)
The initial SQL/PGQ implementation in PostgreSQL 19 has a deliberate limitation: it does not support variable-length path patterns like -[IS follows]->+ (one or more hops) or -[IS follows]->{2,5} (2 to 5 hops). Each hop must be explicitly written. This means recursive traversals — shortest path, transitive closure, "find all connections regardless of depth" — still require recursive CTEs. The *, +, and {m,n} quantifiers are planned for a future PostgreSQL release (Neon SQL/PGQ guide).
SQL/PGQ vs Neo4j vs Apache AGE
| SQL/PGQ (PG 19) | Apache AGE | Neo4j | |
|---|---|---|---|
| Language | SQL:2023 standard | Cypher via extension | Cypher |
| Storage | Your existing tables | Extension storage | Native graph DB |
| Variable-length paths | Not yet | Yes | Yes |
| Shortest path | Not yet | Limited | Yes |
| Installation | Built-in | Extension required | Separate database |
| Full SQL interop | Yes (joins, CTEs, window functions) | Limited | No |
| Standards-based | Yes (ISO/IEC 9075-16:2023) | No | No |
Source: Neon SQL/PGQ comparison.
The verdict: If you need a graph database because you require graph storage and deep traversal performance (shortest path, variable-length traversals across millions of nodes), use Neo4j. If you want graph queries because writing five-table joins in SQL is painful and ugly, SQL/PGQ is a significant readability win that keeps your data in one place.
What Is ON CONFLICT DO SELECT and Why Does It Matter?
ON CONFLICT DO SELECT is a new clause that gives you an atomic get-or-create operation in a single SQL statement — you can insert a row only if it does not already exist, and return the row as a result either way, without needing a separate SELECT query (PostgreSQL 19 INSERT documentation, Neon ON CONFLICT DO SELECT guide).
The problem it solves
Before PostgreSQL 19, the common "insert if not exists, return the row either way" pattern required two queries:
-- Step 1: Try to insert
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO NOTHING RETURNING *;
-- Step 2: If nothing came back, the row already exists — select it
SELECT * FROM users WHERE email = 'alice@example.com';
This is not atomic. Between the INSERT and the SELECT, another transaction could modify the row. It also generates dead tuples when the conflict path fires DO NOTHING.
The PostgreSQL 19 solution
With ON CONFLICT DO SELECT, the entire operation is one atomic statement:
-- Get-or-create: insert if new, select if exists, return the row either way
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO SELECT RETURNING *;
You can also lock the existing row for update in the same statement:
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO SELECT FOR UPDATE RETURNING id, name;
Because it is a single statement, PostgreSQL guarantees atomicity — you either insert or select, never both, never neither. This is a daily-use pattern for idempotent imports, OAuth user upserts, and any system that needs to avoid duplicate rows (Cybertec PostgreSQL ON CONFLICT DO SELECT).
How Does REPACK Replace VACUUM FULL and pg_repack?
REPACK is a new built-in command that combines the functionality of VACUUM FULL and CLUSTER into one operation, with an optional CONCURRENTLY mode that rebuilds tables without taking an access-exclusive lock — meaning the table stays readable and writable throughout the repack (PostgreSQL 19 REPACK documentation, Neon REPACK guide).
Why table bloat happens
PostgreSQL uses multi-version concurrency control (MVCC). When you UPDATE a row, PostgreSQL writes a new version and leaves the old one behind as a "dead tuple." VACUUM marks dead tuples as reusable space, but it never shrinks the table file — the disk space stays allocated. Over time, a table that previously held millions of rows but now holds zero can still consume hundreds of megabytes on disk (River blog: Concurrent repack).
VACUUM FULL solves this by rewriting the table, but it takes an ACCESS EXCLUSIVE lock that blocks all reads and writes for the entire duration. For a 100GB table in production, that means downtime.
How REPACK (CONCURRENTLY) works
-- Reclaim space — table stays online
REPACK (CONCURRENTLY) orders;
-- Reclaim space AND reorder by an index — table stays online
REPACK (CONCURRENTLY) orders USING INDEX orders_created_at_idx;
-- Add ANALYZE to update statistics after repacking
REPACK (CONCURRENTLY, ANALYZE) orders;
The CONCURRENTLY mode works by: (1) creating a new copy of the table, (2) using logical decoding to capture changes made to the original table during the copy, (3) applying those changes to the new copy, and (4) swapping the files at the end with only a brief lock. The table remains readable and writable for most of the operation (Neon REPACK guide).
REPACK vs VACUUM FULL vs pg_repack
| Feature | VACUUM FULL | CLUSTER | REPACK | REPACK (CONCURRENTLY) | pg_repack |
|---|---|---|---|---|---|
| Shrinks table | Yes | Yes | Yes | Yes | Yes |
| Reorders by index | No | Yes | Yes | Yes | Yes |
| Table stays online during operation | No | No | No | Yes | Yes |
| Built-in (no extension) | Yes | Yes | Yes | Yes | No |
| Extra disk space needed | ~1x table size | ~1x table size | ~1x table size | ~2x table + indexes | ~2x table + indexes |
Sources: PostgreSQL 19 REPACK docs, Neon REPACK guide.
One thing to watch: REPACK (CONCURRENTLY) needs enough free disk space for a second copy of the table and all of its indexes during the rebuild. If your table is 50GB, you need at least 50GB free (plus index space) to repack it concurrently.
What Is pg_plan_advice and How Does It Fix Query Plan Regressions?
pg_plan_advice is a new contrib module in PostgreSQL 19 that lets you capture a good query plan and pin it so the planner always uses that plan — preventing the scenario where a query that has been fast for a year suddenly gets slow because the planner changed its mind (PostgreSQL 19 pg_plan_advice documentation, Neon pg_plan_advice guide).
How it works
The workflow has two steps:
Step 1 — Generate advice from a plan you want to keep:
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.total > 1000;
This produces the normal EXPLAIN output plus an advice string like:
JOIN_ORDER(orders customers) NESTED_LOOP_PLAIN(customers)
INDEX_SCAN(orders orders_total_idx)
Step 2 — Lock the plan by setting the advice:
SET pg_plan_advice.advice =
'JOIN_ORDER(orders customers) NESTED_LOOP_PLAIN(customers)
INDEX_SCAN(orders orders_total_idx)';
When you run the query with this advice set, PostgreSQL uses the pinned plan. EXPLAIN tells you whether each hint was matched, partially matched, or not matched — you get immediate feedback on whether your advice is being applied (PostgreSQL 19 pg_plan_advice docs).
You can control specific planner decisions:
-- Force hash join
SET pg_plan_advice.advice = 'HASH_JOIN(customers)';
-- Force index scan
SET pg_plan_advice.advice = 'INDEX_SCAN(events events_type_idx)';
-- Force specific join order
SET pg_plan_advice.advice = 'JOIN_ORDER(orders customers products)';
-- Force or prevent parallel execution
SET pg_plan_advice.advice = 'GATHER(orders)';
SET pg_plan_advice.advice = 'NO_GATHER(orders)';
pg_plan_advice ships as a contrib module. You load it as a shared library — no CREATE EXTENSION needed:
-- For the current session
LOAD 'pg_plan_advice';
-- Or permanently in postgresql.conf
-- shared_preload_libraries = 'pg_plan_advice'
Source: PostgreSQL 19 pg_plan_advice documentation.
Why Is JIT Disabled by Default in PostgreSQL 19?
JIT (Just-In-Time) compilation is now off by default in PostgreSQL 19. The jit configuration parameter defaults to off instead of on. The PostgreSQL development team determined that the planner's cost-based decision about when to invoke JIT was unreliable — it was firing on queries that did not actually benefit from JIT compilation, adding latency without improving performance (PostgreSQL 19 release notes, commit by Jelte Fennema-Nio postgr.es/c/7f8c88c2b).
Who is affected
- OLTP workloads (short queries, high transactions per second): You will likely see no change or a slight improvement from avoiding JIT overhead. Leave it off.
- OLAP/analytical workloads (complex queries, large sequential scans, heavy aggregations): You may see performance regressions. Test with
jit = onand if it helps, explicitly enable it in your configuration.
-- Re-enable JIT if your workload benefits
ALTER SYSTEM SET jit = on;
SELECT pg_reload_conf();
Source: Neon PostgreSQL 19 breaking changes, PostgreSQL 19 release notes.
How Does Parallel Autovacuum Work for Indexes?
PostgreSQL 19 adds the ability to clean up indexes with multiple workers in parallel during autovacuum. Previously, vacuum processed indexes serially for each table, which made vacuuming large tables slow. With parallel index vacuuming, multiple workers can process different indexes simultaneously, significantly reducing the time spent on maintenance for big tables (PostgreSQL 19 release notes, Neon parallel autovacuum guide).
You need to explicitly enable this feature — it is not turned on by default. The relevant parameter is vacuum_max_index_workers, which controls how many index workers can run in parallel during a single vacuum operation.
How Does COPY TO FORMAT JSON Work?
PostgreSQL 19 adds FORMAT JSON as a native option for COPY TO, allowing you to export table data directly as streaming JSON — either as NDJSON (one JSON object per line) or as a JSON array — with constant memory usage regardless of table size (Neon COPY TO JSON guide, commit 7dadd38c).
NDJSON output (default)
COPY users TO STDOUT WITH (FORMAT JSON);
Output:
{"id":1,"email":"alice@example.com","name":"Alice Johnson","active":true}
{"id":2,"email":"bob@example.com","name":"Bob Smith","active":true}
JSON array output
COPY users TO STDOUT WITH (FORMAT JSON, FORCE_ARRAY);
Output:
[
{"id":1,"email":"alice@example.com","name":"Alice Johnson","active":true}
,{"id":2,"email":"bob@example.com","name":"Bob Smith","active":true}
]
Export specific columns or query results
COPY users (email, name) TO STDOUT WITH (FORMAT JSON);
COPY (
SELECT u.email, u.name, count(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id
) TO STDOUT WITH (FORMAT JSON);
Limitations: FORMAT JSON is available for COPY TO only (no COPY FROM with JSON). It cannot be combined with HEADER, DELIMITER, QUOTE, ESCAPE, NULL, or FORCE QUOTE options. Output is compact (no pretty-printing). Source: Neon COPY TO JSON guide.
What Other Notable Changes Ship in PostgreSQL 19?
PostgreSQL 19 includes several additional features and breaking changes worth knowing about before you upgrade:
- GROUP BY ALL: When you add a column to a SELECT, you no longer need to manually add it to the GROUP BY clause —
GROUP BY ALLhandles it automatically (Neon PostgreSQL 19 overview). - TOAST compression defaults to LZ4: The
default_toast_compressionparameter now defaults tolz4instead ofpglz, improving compression throughput for large values (commit 34dfca29). - max_locks_per_transaction increased from 64 to 128: Reduces "out of shared memory" errors during DDL on heavily partitioned tables. Existing custom settings must be doubled to maintain the same capacity (Neon breaking changes).
- MD5 password deprecation warnings: PostgreSQL 19 emits a warning whenever a role authenticates using MD5 password hashing. MD5 is still accepted but signals removal in a future release.
- standard_conforming_strings forced on: Previously a configuration option, this is now always on, with no way to disable it.
- RADIUS authentication removed: The RADIUS authentication method is no longer supported in PostgreSQL 19.
- Up to 2x insert performance with foreign keys: The PostgreSQL Global Development Group reports up to 2x better performance on inserts when foreign key checks are present (InfoQ PostgreSQL 19 coverage).
When Should You Upgrade to PostgreSQL 19?
If you are running PostgreSQL 18 or older, you should plan to upgrade to PostgreSQL 19 shortly after the GA release in September or October 2026. The upgrade requires a dump/restore using pg_dumpall, or use of pg_upgrade, or logical replication (PostgreSQL 19 release notes).
Start testing now: Pull the Beta 2 image and run your real workloads against it. Feature freeze means what you test now is very close to what ships. Pay special attention to:
- JIT change: Run your most expensive queries with
jit = onandjit = offand compare. If your workload is analytical, you may need to explicitly re-enable JIT. - MD5 auth: Check
pg_hba.confformd5entries. They still work in 19 but will warn. - btree_gist indexes on inet/cidr:
pg_upgradewill refuse to migrate clusters that still have these. - max_locks_per_transaction: If you had this set above 64, double your value to maintain the same lock capacity.
- RADIUS auth: If you use RADIUS for authentication, you need to switch to a different method before upgrading.
For builders managing their own data infrastructure — especially teams using AI for database-heavy applications — the SQL/PGQ graph queries and REPACK (CONCURRENTLY) alone justify the upgrade planning. If you are currently using pg_repack as an extension, the built-in REPACK command removes that dependency. And if you maintain vector search or data pipelines on top of Postgres, the native COPY TO FORMAT JSON eliminates a common post-processing step. For a related look at cutting data infrastructure costs, see our guide on building vector search on S3 instead of RAM.
What This Means for You
For builders and small teams using Postgres as their primary database: PostgreSQL 19 is a green light to consolidate. If you have been running a separate Neo4j instance for relationship queries, SQL/PGQ may let you drop that second database for many use cases — saving infrastructure cost and operational complexity. The ON CONFLICT DO SELECT feature eliminates a race condition that has caused subtle bugs in idempotent import systems for years. And REPACK (CONCURRENTLY) means you can reclaim disk space from bloated tables without scheduling a maintenance window.
For teams running AI applications on top of Postgres: The native JSON export via COPY TO FORMAT JSON pairs naturally with ETL pipelines that feed data to AI models — no more double-escaping or memory-hungry json_agg(). If you are building AI-powered backends, these infrastructure improvements compound with our guides on running an AI agent operating system and building vector search on object storage.
For anyone choosing between open-source and managed services: PostgreSQL 19 is a strong argument for staying on open-source. The features that previously required paid extensions or managed-service add-ons — online table repacking, plan stability, native graph queries — are now built in. This mirrors the broader trend we documented in our comparison of open-source vs SaaS infrastructure stacks.
FAQ
Q: When will PostgreSQL 19 be released?
A: PostgreSQL 19 Beta 1 was released on June 4, 2026, and Beta 2 on July 16, 2026. The final general availability release is expected around September or October 2026, following one or more release candidates. Source: PostgreSQL 19 Beta 2 announcement.
Q: Can I use SQL/PGQ graph queries to replace Neo4j?
A: Not fully. PostgreSQL 19's SQL/PGQ implementation supports fixed-depth pattern matching — you can query 2-hop, 3-hop, and deeper relationships by explicitly writing each hop. It does not yet support variable-length path patterns (quantifiers like +, *, {2,5}), shortest path, or transitive closure. For those, you still need a dedicated graph database. But for readability of complex multi-table joins, SQL/PGQ is a major improvement. Source: Neon SQL/PGQ guide.
Q: Is REPACK (CONCURRENTLY) a replacement for the pg_repack extension?
A: For most tables, yes. The built-in REPACK (CONCURRENTLY) uses the same approach (logical decoding for online rebuilds) and works out of the box without installing an extension. However, it is not an exact feature-for-feature replacement — some advanced pg_repack options may not be available. If you have specific pg_repack workflows, test them against the built-in REPACK before removing the extension.
Q: Do I need to change my application code for the JIT default change?
A: No application code change is needed. The change is at the server configuration level. If your workload is OLTP (short queries), leaving JIT off is better. If you run heavy analytical queries, you may want to explicitly set jit = on in your postgresql.conf after testing. Source: Neon breaking changes.
Q: Does COPY TO FORMAT JSON support importing JSON?
A: No. FORMAT JSON is available for COPY TO (export) only. There is no COPY FROM with JSON format in PostgreSQL 19. You still need json_populate_recordset() or another approach to import JSON data. Source: Neon COPY TO JSON guide.
Q: How do I try PostgreSQL 19 Beta 2 right now?
A: Download it from the PostgreSQL download page. The feature set is frozen as of Beta 2, so what you test now is very close to what ships at GA. You can also use Docker with PGDG snapshot packages for isolated testing.
Every claim here is traced to a primary source, dated, and listed under Sources. Research and drafting are AI-assisted; editing, verification and publication are human decisions, and a person is accountable for what appears on this page. How we work →
Discussion
0 comments