PostgreSQL compatibility

This page documents a preview version. v2.23 Preview
Preview includes features under active development and is for development and testing only.
For production, use the latest stable version (v2024.1).

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.

Note

If you have set these features independent of EPCM, you cannot use EPCM.

Conversely, if you are using EPCM on a universe, you cannot set any of the features independently.

Feature Flag/Configuration Parameter EA GA
Read committed yb_enable_read_committed_isolation 2.20 and
2024.1
Wait-on-conflict enable_wait_queues 2.20 2024.1
Cost-based optimizer yb_enable_base_scans_cost_model 2024.1
Batch nested loop join yb_enable_batchednl 2.20 2024.1
Ascending indexing by default yb_use_hash_splitting_by_default 2024.1
YugabyteDB bitmap scan yb_enable_bitmapscan 2024.1.3 2024.2
Planned Feature Flag/Configuration Parameter EA
Efficient communication
between PostgreSQL and DocDB
pg_client_use_shared_memory 2024.2
Parallel query Planned

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.

To learn more about read committed isolation, see Read Committed.

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.

Note: When enabling this parameter, you must run ANALYZE on user tables to maintain up-to-date statistics.

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.

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.

To learn more about concurrency control in YugabyteDB, see Concurrency control.

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.

To learn more about join strategies in YugabyteDB, see Join strategies.

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_tech_preview flag to yugabyted when starting your cluster.

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

./bin/yugabyted start --enable_pg_parity_tech_preview

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.

Flag settings

Setting Enhanced Postgres Compatibility overrides any flags you set individually for the universe. The G-Flags tab will however continue to display the setting that you customized.

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:

Unsupported PostgreSQL feature Track feature request GitHub issue
LOCK TABLE to obtain a table-level lock 5384
Table inheritance 5956
Exclusion constraints 3944
Deferrable constraints 1709
Constraint Triggers 4700
GiST indexes 1337
Events (Listen/Notify) 1872
XML Functions 1043
XA syntax 11084
ALTER TYPE 1893
CREATE CONVERSION 10866
Primary/Foreign key constraints on foreign tables 10698, 10699
GENERATED ALWAYS AS STORED columns 10695
Multi-column GIN indexes 10652
CREATE ACCESS METHOD 10693
DESC/HASH on GIN indexes (ASC supported) 10653
CREATE SCHEMA with elements 10865
Index on citext column 9698
ABSTIME type 15637
transaction ids (xid)
YugabyteDB uses Hybrid logical clocks instead of transaction ids.
15638
DDL operations within transaction 1404
Some ALTER TABLE variants 1124
UNLOGGED table 1129
Indexes on complex datatypes such as INET, CITEXT, JSONB, ARRAYs, and so on. 9698, 23829, 17017
%TYPE syntax in Functions/Procedures/Triggers 23619
Storage parameters on indexes or constraints 23467