Moving a PostgreSQL Application to YugabyteDB: Compatibility Testing and Migration Planning

YugabyteDB offers a PostgreSQL-compatible SQL interface, YSQL, on top of a distributed storage layer. That makes it a credible next step for PostgreSQL applications that need to scale out or survive the loss of a zone or region. It does not make it a drop-in replacement. Some PostgreSQL features are missing, some behave differently once data is spread across nodes, and the schema usually needs redesign.

This article sets out how we test a PostgreSQL application against YSQL before anyone commits to a migration: what the compatibility boundary looks like on the research date, which release to test against, how to use the project’s migration tooling, and which migration route fits which risk appetite.

What “PostgreSQL-compatible” covers, and where it stops

The YSQL PostgreSQL 15 features page explains that YSQL reuses a fork of the PostgreSQL query layer. Data types, queries, functions, stored procedures, triggers and many extensions are expected to work as they do in PostgreSQL. Starting with the v2025.1 stable series, YugabyteDB is based on PostgreSQL 15. Earlier stable series were based on PostgreSQL 11.

The same page lists PostgreSQL 15 features that are not yet implemented, including the MERGE command and nondeterministic collations. The project’s Enhanced PostgreSQL Compatibility Mode page also publishes a list of unsupported PostgreSQL features. On the research date it included:

• Exclusion constraints, deferrable constraints and constraint triggers.
• GiST indexes, multi-column GIN indexes, and indexes on some complex types such as INET, CITEXT, JSONB and arrays.
• XML functions, XA syntax, UNLOGGED tables and some ALTER TABLE and ALTER TYPE variants.
• Transaction IDs (xid), because YugabyteDB uses hybrid logical clocks instead.

The Voyager known issues for PostgreSQL add items that matter for real applications. DDL inside an explicit transaction does not behave as in PostgreSQL. Two-phase commit (PREPARE TRANSACTION) is not implemented. Unsupported extensions fail at schema import. Columns of types such as PostGIS GEOMETRY and GEOGRAPHY, PG_LSN, XID and XID8 cause the migration to skip the affected data.

None of this is unusual for a distributed database. The point is that compatibility is a property of your application, not of the product. An ORM-heavy service that uses plain tables, indexes and transactions may move with little change. An application built on PostGIS, advisory locks, schema changes inside transactions or deferrable constraints needs a design decision first.

Test against the release you will run

Compatibility changes between release series, so the target version belongs in the test plan from day one. Two examples from the official pages show why:

• The compatibility mode page states that in v2025.2 and later, new universes deployed with yugabyted have read committed isolation, the cost-based optimizer, auto analyze, bitmap scans and parallel append enabled by default. A test on an older release, or on a universe with different flags, will show different plans and different concurrency behaviour.
• The Voyager release notes for v2026.10.1 state that YugabyteDB table inheritance is tech preview in v2025.1 and v2025.2 and generally available in v2026.1. The same notes say Voyager itself cannot migrate data for tables that use INHERITS.

Feature maturity labels matter too. The project’s versioning page defines tech preview, early access and generally available features. Tech preview features can change at any time and are not recommended for production. For example, the compatibility page marks LISTEN/NOTIFY as early access from v2025.2.3.0. If the application depends on a feature, record its maturity on the target release and decide whether that is acceptable.

In practice we pick the target series first, usually the current LTS series unless there is a reason to take the STS series, and build the test universe with the exact flags production will use. Our YugabyteDB engineering page lists the series we work on.

Use the assessment tooling as an inventory, not a verdict

YugabyteDB Voyager is the project’s migration tool. The Voyager introduction describes it as open source and lists PostgreSQL 11.x to 18.x, plus several managed PostgreSQL services, as supported sources. Voyager releases use a YYYY.M.N numbering scheme, and the latest release listed on the research date was v2026.10.1, published on 13 October 2026.

The migration assessment page describes an assess-migration command that reads source metadata and produces a report. It covers recommended schema changes, unsupported features and data types, sharding suggestions, cluster sizing, unsupported query constructs and unsupported constructs inside PL/pgSQL objects. Some details are worth knowing before you run it:

• The page marks migration assessment as tech preview.
• Detecting unsupported query constructs needs pg_stat_statements enabled on the source, and some recommendations need ANALYZE run on the source.
• The page states that no application data, passwords or credentials are collected. Schema metadata and database names are collected, and performance metrics are optional.
• Sizing recommendations are based on the vendor’s own test configuration, so treat them as a starting point for capacity testing.

We use the report to build a backlog: every flagged object becomes a ticket with an owner and a decision. The report cannot see application behaviour that never reached pg_stat_statements, such as batch jobs that run monthly, so we add those paths by hand.

Redesign the schema for distribution

The Migrate from PostgreSQL guide is clear that data modelling changes when rows are spread across nodes. The decisions that usually matter most are:

Decision	What the guide says	What we test
Primary key	Table distribution depends on the primary key. Define it inline at CREATE TABLE; adding it later rewrites the table.	Every table has an explicit key that matches its main access path.
Hash or range sharding	Hash spreads point lookups well but loses ordering. Range keeps order for range scans and is closer to PostgreSQL behaviour.	The heaviest queries read from as few tablets as possible.
Hot spots	The known issues page warns that range-sharded timestamp or date indexes send new writes to one tablet.	Write distribution under a realistic insert pattern.
Small tables	Colocation keeps small reference tables in a single tablet.	Which tables are colocated, and which are opted out because they grow.
Sequences	Cache sequences, consider server-side caching, or use UUIDs, especially across regions.	Key generation under concurrent load from several nodes.
Secondary indexes	Indexes are global and distributed independently of the table.	Index choice from execution plans, not from the PostgreSQL schema by habit.

The guide also notes collation limits: not all collations are supported, and databases can only be created with the “C” collation. Applications that rely on locale-specific sorting at the database level need an explicit check.

Check how the application behaves, not just the schema

A schema that imports cleanly can still fail under load. The migration guide asks applications to retry transactions that fail with error code 40001 (serialization failure). The compatibility page recommends read committed isolation for applications coming from PostgreSQL, where it is the default.

Connection handling also changes. PostgreSQL drivers talk to one endpoint, while every YugabyteDB node can serve queries. The guide describes two ways to spread load: a load balancer, or the YugabyteDB smart drivers. Whichever you choose, test connection pools, failover of a node during a transaction, and retry logic together. These are the areas where we most often find issues that a functional test suite misses.

Finally, compare plans and timings for the statements that matter. Voyager has a compare-performance command, marked tech preview, that compares query statistics from the source with statistics from the target. It is useful for finding regressions. It does not replace a load test with representative data volumes and concurrency.

Choose a migration route that matches your risk tolerance

The Voyager introduction lists four migration types. Live migration, and its fall-forward and fall-back variants, are for PostgreSQL sources only.

Route	How it works	When it fits
Offline	Stop the application, export, import, cut over.	Smaller databases, or when an agreed downtime window is acceptable.
Live	Export a snapshot, stream ongoing changes, then cut over.	Downtime must be short and a return path is handled by other means.
Live with fall-forward	After cutover, changes also flow to a source-replica database.	You want a standby copy outside YugabyteDB after cutover.
Live with fall-back	After cutover, changes stream from YugabyteDB back to the source, so you can cut back.	The business needs a rehearsed way back to PostgreSQL.

The live migration with fall-back page states that this workflow is generally available with the YugabyteDB Connector and tech preview with the gRPC connector. It also warns that fall-back can alter the source database, for example by disabling triggers and foreign keys while changes flow back. That needs sign-off from whoever owns the source.

Recent Voyager releases add schema drift checks before cutover, which catch source schema changes made during a long migration. On the research date the release notes also recorded that Oracle and MySQL offline migration support ended on 13 October 2026, while PostgreSQL migrations were unaffected. Teams planning non-PostgreSQL migrations should confirm their tooling before they start. Our migration and modernization practice covers both cases.

A go/no-go checklist before committing

• Target release series and flags chosen, and the test universe built to match.
• Assessment report reviewed, with every flagged object assigned a decision: keep, rewrite, redesign or drop.
• Extensions and data types checked against the target release, including anything the application loads at runtime.
• Primary keys, sharding and colocation decided per table, with hot-spot tests on the main write paths.
• Retry logic for serialization failures in place and tested with node failures during transactions.
• Plans and timings compared for the critical statements under representative load.
• Migration route chosen, with the cutover and, where needed, fall-back rehearsed end to end.
• Reconciliation checks agreed: row counts, checksums or business totals, and who signs them off.

If several items stay red, staying on PostgreSQL may be the better answer. A well-run PostgreSQL deployment with replicas is simpler to operate; see our PostgreSQL engineering page.

How Bassirah can help

Bassirah runs compatibility assessments, schema redesign and rehearsed migrations from PostgreSQL to self-managed YugabyteDB as independent engineering. Bassirah is not a YugabyteDB, Inc. partner and does not provide vendor support; upstream fixes follow the project’s timeline, as described in our open-source support model. Vendor support and YugabyteDB, Inc.’s commercial products, YugabyteDB Anywhere and YugabyteDB Aeon, are contracted with YugabyteDB, Inc.