YugabyteDB for PostgreSQL Teams: Compatibility Gaps and Migration

YugabyteDB's YSQL API reuses the PostgreSQL query layer, so psql, your driver, and most of your schema work on day one. The storage underneath is a distributed, Raft-replicated key-value store, and that is where PostgreSQL habits break. A transaction that never failed on PostgreSQL returns ERROR: could not serialize access due to concurrent update (SQLSTATE 40001). A bigserial key hands out values in blocks of 100 per connection. A schema with a GiST index, an exclusion constraint, or an UNLOGGED table will not import. As of November 2026, the current releases are YugabyteDB v2026.1 (STS) and v2025.2 (LTS), and both base YSQL on PostgreSQL 15. This guide covers the gaps to test, how clients should retry, how to design keys, and how YugabyteDB Voyager moves the data.

Scope and review date: November 2026. This post covers YSQL on YugabyteDB v2026.1 (latest patch v2026.1.3.0) and v2025.2 LTS, with PostgreSQL sources moved by YugabyteDB Voyager v2026.10.2. Versions, flags, defaults, and commands were checked against the YugabyteDB documentation and release notes on 9 November 2026. They were not run in a lab, so rehearse every step on a staging copy.

Which YugabyteDB release should a PostgreSQL team target?

The YugabyteDB releases page lists two stable tracks: standard-term support (STS) and long-term support (LTS). The PostgreSQL 15 features page says that from v2025.1 (stable) and v2.25 (preview), YugabyteDB is based on PostgreSQL 15. YSQL was originally based on PostgreSQL 11.2.

A new project should start on v2025.2 LTS or v2026.1 STS. As of November 2026, the newest patches are v2026.1.3.0 (5 November 2026) and v2025.2.8.1 (4 November 2026). The v2026.1 release notes add one upgrade trap: a cluster on v2025.2.6.0 cannot upgrade to v2026.1.0.x or v2026.1.1.x, so go straight to v2026.1.2.0 or later.

-- On the YugabyteDB cluster
SELECT version();

Interpretation: the string starts with the PostgreSQL base and carries a -YB- build suffix, for example PostgreSQL 15.2-YB-2.25.0.0-b0. If it starts with PostgreSQL 11.2, the cluster is on v2024.2 or earlier, and moving it to a PostgreSQL 15 based release goes through the YSQL major upgrade procedure, not a normal rolling upgrade.

Which PostgreSQL features does YSQL not support?

The PostgreSQL compatibility page for v2026.1 lists these as not supported, each with a GitHub issue:

• exclusion constraints, constraint triggers, and the REFERENCING clause for triggers;
• deferrable constraints (#1709); the SQL feature table separately lists deferrable foreign keys, so check which kind you use;
• GiST indexes, multi-column GIN indexes, and CREATE ACCESS METHOD;
• indexes on complex types such as INET, CITEXT, JSONB, and arrays;
• UNLOGGED tables, XA syntax, XML functions, CREATE CONVERSION, and some ALTER TABLE and ALTER TYPE variants.

Two PostgreSQL 15 features are not yet implemented: MERGE and nondeterministic collations. LISTEN/NOTIFY is Early Access from v2025.2.3.0 and needs ysql_yb_enable_listen_notify=true, which defaults to false.

-- Run on the PostgreSQL source before you plan anything
SELECT conrelid::regclass AS table_name, conname, contype, condeferrable
FROM pg_constraint
WHERE contype = 'x' OR condeferrable;

SELECT c.oid::regclass AS index_name, am.amname
FROM pg_class c JOIN pg_am am ON am.oid = c.relam
WHERE c.relkind = 'i' AND am.amname IN ('gist', 'spgist');

SELECT oid::regclass AS unlogged_table
FROM pg_class WHERE relkind = 'r' AND relpersistence = 'u';

SELECT tgrelid::regclass, tgname
FROM pg_trigger WHERE tgconstraint <> 0 AND NOT tgisinternal;

Interpretation: every row is a schema change to plan. The last query finds user-defined constraint triggers; the filter skips the internal ones that foreign keys create. Voyager's assessment finds the same items, but these queries give you a count in a minute.

Also search pg_stat_statements for MERGE, LISTEN, and pg_notify. Rewrite MERGE as INSERT ... ON CONFLICT, and move notifications to a queue unless you accept an Early Access feature.

Which extensions are available?

The extensions page lists these as pre-bundled, enabled with CREATE EXTENSION: pg_stat_statements, pgcrypto, uuid-ossp, pg_trgm, hstore, postgres_fdw, auto_explain, pgAudit, pg_partman, pg_cron, pgvector, pg_hint_plan, and Orafce. PostGIS, TimescaleDB, Citus, and pg_repack are not listed, and only supported extensions can be installed.

-- On the source
SELECT extname, extversion FROM pg_extension ORDER BY 1;
-- On the YugabyteDB target
SELECT name, default_version FROM pg_available_extensions ORDER BY 1;

Interpretation: any source extension missing from the target list blocks the migration until you replace it.

How do isolation levels and retries differ from PostgreSQL?

The isolation levels page maps PostgreSQL's names onto Serializable, Snapshot (REPEATABLE READ), and Read Committed. Read Committed works as in PostgreSQL only when the YB-TServer flag yb_enable_read_committed_isolation is true, and the flag reference lists its default as false. New v2025.2 or later universes deployed with yugabyted, YugabyteDB Anywhere, or Aeon have it enabled. Manually deployed universes and older versions do not, so their default level is effectively Snapshot.

# The YB-TServer web UI lists the running flags
curl -s 'http://tserver-1:9000/varz?raw' | grep yb_enable_read_committed_isolation

Interpretation: check every TServer. =false means your application runs at Snapshot isolation and must handle serialization errors it never saw on PostgreSQL.

With Read Committed on, YugabyteDB retries the whole conflicting statement on a newer snapshot, up to yb_max_query_layer_retries, instead of re-checking only the conflicting rows as PostgreSQL does. The Read Committed page lists where that internal retry stops working: a response larger than ysql_output_buffer_size, several semicolon-separated statements sent through the simple query protocol, and statements after the first in a driver batch.

Under Snapshot and Serializable, the transaction retry guide names two errors the client should retry: 40001 (could not serialize access due to concurrent update) and 40P01 (deadlock detected).

import time
from psycopg2 import errors

RETRYABLE = (errors.SerializationFailure, errors.DeadlockDetected)

def transfer(conn, src, dst, amount, max_attempts=10):
    delay = 0.002
    for attempt in range(1, max_attempts + 1):
        try:
            with conn.cursor() as cur:
                cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (amount, src))
                cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, dst))
            conn.commit()
            return
        except RETRYABLE:
            conn.rollback()
            if attempt == max_attempts:
                raise
            time.sleep(delay)
            delay *= 2

Interpretation: this is the documented pattern: roll back, back off exponentially, cap the attempts, and retry only the two retriable SQLSTATEs. A dropped connection during COMMIT is not retried, because you cannot tell whether the commit landed.

Why do sequences behave differently, and how big should the cache be?

On PostgreSQL, a sequence defaults to CACHE 1, and each nextval() is a cheap local call. On YugabyteDB, sequence state lives in a replicated system table, so every refill costs a distributed write. The CREATE SEQUENCE reference sets the default CACHE to 100 and keeps cached values in the local node's memory. The flag ysql_sequence_cache_minval (default 100) is a floor: the effective cache is the larger of the flag and the CACHE clause.

By default (ysql_sequence_cache_method=connection), each connection grabs its own block. Two sessions inserting at the same time get values such as 1 and 101, and values left in a closed connection's block are never used. With ysql_sequence_cache_method=server, a block is shared by all connections on one TServer. The nextval page notes that setval and restarting a sequence do not work with server caching.

-- Explicit sequence with a large cache, instead of SERIAL
CREATE SEQUENCE orders_id_seq CACHE 1000;
CREATE TABLE orders (
  id         bigint NOT NULL DEFAULT nextval('orders_id_seq'),
  customer   bigint NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (id HASH)
);
ALTER SEQUENCE orders_id_seq OWNED BY orders.id;

Interpretation: the CREATE SEQUENCE page says SERIAL creates a sequence with a cache of 1 and recommends this pattern instead. A larger cache means fewer distributed writes and larger gaps. Code that assumes IDs are gap-free or ordered by insert time breaks either way; if you need neither, gen_random_uuid() is built in.

How should you design primary keys to avoid hotspots?

The CREATE TABLE reference says that by default only the first primary key column is the hash column, so PRIMARY KEY (k1, k2) means k1 HASH, k2 ASC. Hashing spreads sequential IDs evenly, so an insert-heavy table on id HASH has no single hot tablet. The cost is that range scans on that column touch every tablet.

Range sharding (ASC or DESC) keeps rows in key order, which suits range queries. The data sharding page warns that when inserted data is already sorted, all new inserts go to only one tablet. A timestamp or sequence as the leading range column is the classic hotspot.

-- Hotspot: every new row lands in the last tablet
CREATE TABLE events_bad (
  created_at timestamptz,
  id         bigint,
  payload    jsonb,
  PRIMARY KEY (created_at ASC, id ASC)
);

-- Spread writes by tenant, keep time order inside each tenant
CREATE TABLE events (
  tenant_id  bigint,
  created_at timestamptz,
  id         bigint,
  payload    jsonb,
  PRIMARY KEY ((tenant_id) HASH, created_at DESC, id)
) SPLIT INTO 12 TABLETS;

Interpretation: in the second table, writes for different tenants hash to different tablets, and a query such as "latest 50 events for tenant 42" reads one contiguous range. SPLIT INTO applies to hash-sharded tables. Range-sharded tables are pre-split with SPLIT AT VALUES. Automatic tablet splitting (default on) handles later growth.

Tables without a primary key get a hidden ybrowid hash key, and Voyager's live migration does not support them, so add keys on the source first.

When should tables be colocated?

Hundreds of small tables cost memory, files, and network hops per tablet. Colocation puts many tables in one replicated tablet. The docs suggest it for small datasets that need high availability or geo-distribution (they cite databases typically under 50 GB), for large databases with many small tables, and for many small per-tenant databases.

CREATE DATABASE app WITH COLOCATION = true;
\c app
-- Small tables are colocated by default in this database
CREATE TABLE countries (code text PRIMARY KEY, name text NOT NULL);
-- Opt the large, write-heavy table out so it shards normally
CREATE TABLE orders (id bigint, customer bigint, PRIMARY KEY (id HASH))
  WITH (COLOCATION = false);

Interpretation: colocated tables share one tablet, so joins between them stay local. Tablet splitting is disabled for colocated tables, and size metrics are reported for the shared tablet, not per table. Keep high-load tables out, or the shared tablet becomes the hotspot.

What does a multi-region cluster cost in write latency?

Each write commits through Raft consensus, so it waits for a majority of replicas. In one region, that is a few milliseconds of network. Across regions, the inter-region round trip is part of every commit. The global database example shows reads of about 2 ms from local leaders and writes of about 30 ms, because the nearest other replica is about 30 ms away. It also shows that moving the regions closer cuts the example to about 10 ms.

# Pin tablet leaders to the region where the application runs
./bin/yb-admin --master_addresses 10.0.1.10:7100,10.0.2.10:7100,10.0.3.10:7100 \
  set_preferred_zones aws.us-east-2.*:1 aws.us-central-1.*:2 aws.us-west-1.*:3

Interpretation: leaders move to the rank 1 region, so local reads stay fast, but writes still wait for a replica in another region. The same example recommends replication factor 5 with two replicas in each of the two preferred regions, so a failed leader is replaced by a local follower.

For reports that accept stale data, follower reads (yb_read_from_followers in a read-only transaction) serve reads from the nearest replica, up to 30 seconds old by default. Writes get no such shortcut, so a chatty ORM pays the inter-region round trip on every statement it waits on.

How does a YugabyteDB Voyager migration work?

YugabyteDB Voyager is the migration tool. As of November 2026, the latest release is v2026.10.2 (27 October 2026). It supports PostgreSQL 11.x to 18.x sources, including Amazon RDS, Aurora, Cloud SQL, and Azure Database for PostgreSQL. It runs offline or live migration, and live migration can add fall-forward (to a source replica) or fall-back (to the source). Live migration is PostgreSQL only.

A live migration assesses, moves the schema, streams a snapshot and then changes, cuts over, and finalizes. The live migration guide lists the limits that matter most:

• schema changes on the source are not applied to the target during the migration (schema detect-drift, Tech Preview, reports them);
• tables without a primary key are not supported, and TRUNCATE on the source is not replicated;
• types such as POINT, XML, TIMETZ, and VECTOR are not supported, and v2026.10.1 stops export data on tables that use INHERITS;
• sequences not owned by an integer column must be resumed by hand at cutover.

Assess the source with yb-voyager

Start with assess-migration. Its report covers schema changes, unsupported features and query constructs, recommended sharding and colocation, cluster sizing, and a migration time estimate. Unsupported query detection needs pg_stat_statements on the source. If the Voyager host cannot reach the source, run yb-voyager-pg-gather-assessment-metadata.sh next to the database and pass the result with --assessment-metadata-dir. Without --source-db-password, Voyager prompts for the password or reads SOURCE_DB_PASSWORD, which keeps it out of shell history.

What does the live migration runbook look like?

This is the PostgreSQL to YugabyteDB live path without fall-back, in the order the live migration guide gives. Every command also accepts --config-file with the live-migration.yaml template, which keeps credentials out of shell history.

Before step 6, compare row counts on large tables and run the application's read paths against the target.

When is YugabyteDB a fit, and when is PostgreSQL HA enough?

YugabyteDB earns its trade-offs when the requirement is one of these: writes that outgrow a single primary, surviving the loss of a zone or region with automatic failover over synchronous replication, or keeping rows in a specific region for data residency. The 2023 CockroachDB, Spanner, and YugabyteDB comparison covers how it differs from the other distributed SQL engines.

Plain PostgreSQL with streaming replication and Patroni is usually enough when one primary handles the write load, a few seconds of failover is acceptable, and the application depends on extensions or features YSQL lacks. You keep single-node commit latency, every extension, and your current tooling. Our guides to a Patroni production cluster and Patroni with Consul cover that route, and Citus is the middle option when you need sharding but want to stay on PostgreSQL.

A useful test: if one well-sized primary handles your peak write rate with headroom and the data fits on one server, the problem is availability, not scale.

Frequently asked questions

Key takeaways

Plan a YugabyteDB migration with JusDB

JusDB's YugabyteDB migration team can review your PostgreSQL schema and workload against YSQL's gaps, help you decide whether YugabyteDB or PostgreSQL high availability fits the requirement, and plan a Voyager migration with a rehearsed cutover and rollback path.

Bring your PostgreSQL version, the assessment report if you have one, your extension list, and your latency and failover targets. Your team approves every production change.

Related reading

• CockroachDB vs Google Spanner vs YugabyteDB: NewSQL comparison
• Patroni with Consul DCS: PostgreSQL HA without etcd
• PostgreSQL HA with Patroni: production cluster setup guide
• YugabyteDB documentation: Live migration with YugabyteDB Voyager