PostgreSQL compatibility

YugabyteDB is a PostgreSQL-compatible distributed database that supports the majority of PostgreSQL syntax. This means that existing applications built on PostgreSQL can often be migrated to YugabyteDB without changing application code.

Because YugabyteDB is PostgreSQL compatible, it works with the majority of PostgreSQL database tools such as various language drivers, ORM tools, schema migration tools, and many more third-party database tools.

PostgreSQL compatibility has two aspects:

• Feature compatibility

  Compatibility refers to whether YugabyteDB supports all the features of PostgreSQL and behaves as PostgreSQL does. With full PostgreSQL compatibility, you should be able to take an application running on PostgreSQL and run it on YugabyteDB without any code changes. The application will run without any errors, but it may not perform well because of the distributed nature of YugabyteDB.

• Performance parity

  Performance parity refers to the capabilities of YugabyteDB that allow applications running on PostgreSQL to run with predictable performance on YugabyteDB. In other words, the performance degradation experienced by small and medium scale applications going from a single server database to a distributed database should be predictable and bounded.

Enhanced PostgreSQL Compatibility Mode

To test and take advantage of features developed for PostgreSQL compatibility in YugabyteDB that are currently in EA , you can enable Enhanced PostgreSQL Compatibility Mode (EPCM). When this mode is turned on, YugabyteDB is configured to use all the latest features developed for feature and performance parity. EPCM is available in v2024.1 and later.

After turning this mode on, as you upgrade universes, YugabyteDB will automatically enable new designated PostgreSQL compatibility features.

As features included in the PostgreSQL compatibility mode transition from EA to GA in subsequent versions of YugabyteDB, they become enabled by default on new universes, and are no longer managed under EPCM on your existing universes after the upgrade.

Released

The following features are currently available in EPCM.

Read committed

Flag: yb_enable_read_committed_isolation=true

Read Committed isolation level handles serialization errors and avoids the need to retry errors in the application logic. Read Committed provides feature compatibility, and is the default isolation level in PostgreSQL. When migrating applications from PostgreSQL to YugabyteDB, read committed is the preferred isolation level.

Cost-based optimizer

Configuration parameter: yb_enable_base_scans_cost_model=true

Cost-based optimizer (CBO) creates optimal execution plans for queries, providing significant performance improvements both in single-primary and distributed PostgreSQL workloads. This feature reduces or eliminates the need to use hints or modify queries to optimize query execution. CBO provides improved performance parity.

Wait-on-conflict concurrency

Flag: enable_wait_queues=true

Enables use of wait queues so that conflicting transactions can wait for the completion of other dependent transactions, helping to improve P99 latencies. Wait-on-conflict concurrency control provides feature compatibility, and uses the same semantics as PostgreSQL.

Batched nested loop join

Configuration parameter: yb_enable_batchednl=true

Batched nested loop join (BNLJ) is a join execution strategy that improves on nested loop joins by batching the tuples from the outer table into a single request to the inner table. By using batched execution, BNLJ helps reduce the latency for query plans that previously used nested loop joins. BNLJ provides improved performance parity.

Default ascending indexing

Configuration parameter: yb_use_hash_splitting_by_default=false

Enable efficient execution for range queries on data that can be sorted into some ordering. In particular, the query planner will consider using an index whenever an indexed column is involved in a comparison using one of the following operators: < <= = >= >.

Also enables retrieving data in sorted order, which can eliminate the need to sort the data.

Default ascending indexing provides feature compatibility and is the default in PostgreSQL.

YugabyteDB bitmap scan

Configuration parameter: yb_enable_bitmapscan=true

Bitmap scans use multiple indexes to answer a query, with only one scan of the main table. Each index produces a "bitmap" indicating which rows of the main table are interesting. Bitmap scans can improve the performance of queries containing AND and OR conditions across several index scans. YugabyteDB bitmap scan provides feature compatibility and improved performance parity. For YugabyteDB relations to use a bitmap scan, the PostgreSQL parameter enable_bitmapscan must also be true (the default).

Planned

The following features are planned for EPCM in future releases.

Efficient communication between PostgreSQL and DocDB

Configuration parameter: pg_client_use_shared_memory=true

Enable more efficient communication between YB-TServer and PostgreSQL using shared memory. This feature provides improved performance parity.

Parallel query

Enables the use of PostgreSQL parallel queries. Using parallel queries, the query planner can devise plans that leverage multiple CPUs to answer queries faster. Parallel query provides feature compatibility and improved performance parity.

Enable EPCM

YugabyteDB

To enable EPCM in YugabyteDB:

• Pass the enable_pg_parity_early_access flag to yugabyted when starting your cluster.

For example, from your YugabyteDB home directory, run the following command:

./bin/yugabyted start --enable_pg_parity_early_access

Note: When enabling the cost models, ensure that packed row for colocated tables is enabled by setting the --ysql_enable_packed_row_for_colocated_table flag to true.

YugabyteDB Anywhere

To enable EPCM in YugabyteDB Anywhere v2024.1, see the Release notes.

To enable EPCM in YugabyteDB Anywhere v2024.2 or later:

• When creating a universe, turn on the Enable Enhanced Postgres Compatibility option.

  You can also change the setting on deployed universes using the More > Edit Postgres Compatibility option.

YugabyteDB Aeon

To enable EPCM in YugabyteDB Aeon:

1. When creating a cluster, choose a track with database v2024.1.0 or later.
2. Select the Enhanced Postgres Compatibility option (on by default).

You can also change the setting on the Settings tab for deployed clusters.

Unsupported PostgreSQL features

Because YugabyteDB is a distributed database, supporting all PostgreSQL features in a distributed system is not always feasible. This section documents the known list of differences between PostgreSQL and YugabyteDB. You need to consider these differences while porting an existing application to YugabyteDB.

The following PostgreSQL features are not supported in YugabyteDB: