--- title: "Enhanced PostgreSQL Compatibility Mode" url: "https://docs.yugabyte.com/stable/reference/configuration/postgresql-compatibility/" --- # Enhanced PostgreSQL Compatibility Mode Enhance your application performance for PostgreSQL parity YugabyteDB is a [PostgreSQL-compatible](https://www.yugabyte.com/tech/postgres-compatibility/ "PostgreSQL-compatible") distributed database that supports the majority of PostgreSQL syntax. YugabyteDB is methodically expanding its features to deliver PostgreSQL-compatible performance that can substantially improve your application's efficiency. To test and take advantage of features developed for enhanced PostgreSQL compatibility in YugabyteDB that are in [EA](/stable/releases/versioning/#feature-maturity) , you can enable Enhanced PostgreSQL Compatibility Mode (EPCM). When this mode is turned on, YugabyteDB is configured to use the features developed for feature and performance parity. EPCM is available in [v2024.1](/stable/releases/ybdb-releases/end-of-life/v2024.1) and later. ## Feature availability After turning EPCM 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](/stable/releases/versioning/#feature-maturity) to [GA](/stable/releases/versioning/#feature-maturity) in subsequent versions of YugabyteDB, they are no longer managed under EPCM on your existing universes after the upgrade. Note that in v2025.2 and later, the following features in EPCM are enabled by default when you deploy a universe using [yugabyted](/stable/deploy/manual-deployment/start-yugabyted/ "yugabyted"), [YugabyteDB Anywhere](/stable/yugabyte-platform/create-deployments/create-universe-multi-zone/ "YugabyteDB Anywhere"), or [YugabyteDB Aeon](/stable/yugabyte-cloud/cloud-basics/create-clusters/ "YugabyteDB Aeon"): - [Read committed](#read-committed "Read committed") (yb\_enable\_read\_committed\_isolation=true) - [Cost-based optimizer](#cost-based-optimizer "Cost-based optimizer") (ysql\_pg\_conf\_csv=yb\_enable\_cbo=on) - [Auto Analyze](/stable/additional-features/auto-analyze/ "Auto Analyze") (ysql\_enable\_auto\_analyze=true) - [YugabyteDB bitmap scan](#yugabytedb-bitmap-scan "YugabyteDB bitmap scan") (yb\_enable\_bitmapscan=true) - [Parallel append](#parallel-query "Parallel append") (yb\_enable\_parallel\_append=true) ### Upgrading If you upgrade a universe with EPCM to v2025.2 or later, YugabyteDB will automatically enable all EPCM features. Going forward, use individual flags to set features. When upgrading a universe that does not have EPCM, YugabyteDB does not enable features automatically. If the universe has the [cost-based optimizer](#cost-based-optimizer "cost-based optimizer") enabled, however, YugabyteDB will enable the following features: - Auto Analyze: ysql\_enable\_auto\_analyze is set to true. - Bitmap scans: yb\_enable\_bitmapscan is set to true. - Parallel append: yb\_enable\_parallel\_append is set to true. ## Released features The following features are currently included in EPCM. Feature Flag/Configuration Parameter EA GA [Read committed](#read-committed "Read committed") [yb\_enable\_read\_committed\_isolation](/stable/reference/configuration/yb-tserver/#ysql-default-transaction-isolation "yb_enable_read_committed_isolation") [v2.20](/stable/releases/ybdb-releases/end-of-life/v2.20), [v2024.1](/stable/releases/ybdb-releases/end-of-life/v2024.1) [v2024.2.2](/stable/releases/ybdb-releases/v2024.2#v2024.2.2.0) [Wait-on-conflict](#wait-on-conflict-concurrency "Wait-on-conflict")1 [enable\_wait\_queues](/stable/reference/configuration/yb-tserver/#enable-wait-queues "enable_wait_queues") [v2.20](/stable/releases/ybdb-releases/end-of-life/v2.20) [v2024.1.0.0](/stable/releases/ybdb-releases/end-of-life/v2024.1#v2024.1.0.0) [Cost-based optimizer](#cost-based-optimizer "Cost-based optimizer") [yb\_enable\_cbo](/stable/reference/configuration/yb-tserver/#yb-enable-cbo "yb_enable_cbo") [v2024.1.0.0](/stable/releases/ybdb-releases/end-of-life/v2024.1#v2024.1.0.0) [v2025.1.0.0](/stable/releases/ybdb-releases/v2025.1#v2025.1.0.0) [Batch nested loop join](#batched-nested-loop-join "Batch nested loop join")1 [yb\_enable\_batchednl](/stable/reference/configuration/yb-tserver/#yb-enable-batchednl "yb_enable_batchednl") [v2.20](/stable/releases/ybdb-releases/end-of-life/v2.20) [v2024.1.0.0](/stable/releases/ybdb-releases/end-of-life/v2024.1#v2024.1.0.0) [Ascending indexing by default](#default-ascending-indexing "Ascending indexing by default") [yb\_use\_hash\_splitting\_by\_default](/stable/reference/configuration/yb-tserver/#yb-use-hash-splitting-by-default "yb_use_hash_splitting_by_default") [v2024.1](/stable/releases/ybdb-releases/end-of-life/v2024.1) [YugabyteDB bitmap scan](#yugabytedb-bitmap-scan "YugabyteDB bitmap scan") [yb\_enable\_bitmapscan](/stable/reference/configuration/yb-tserver/#yb-enable-bitmapscan "yb_enable_bitmapscan") [v2024.1.3.0](/stable/releases/ybdb-releases/end-of-life/v2024.1#v2024.1.3.0) [v2025.1.0.0](/stable/releases/ybdb-releases/v2025.1#v2025.1.0.0) [Efficient communication between PostgreSQL and DocDB](#efficient-communication-between-postgresql-and-docdb "Efficient communication between PostgreSQL and DocDB") [pg\_client\_use\_shared\_memory](/stable/reference/configuration/yb-tserver/#pg-client-use-shared-memory "pg_client_use_shared_memory") [v2024.1](/stable/releases/ybdb-releases/end-of-life/v2024.1) [v2024.2.0.0](/stable/releases/ybdb-releases/v2024.2#v2024.2.0.0) [Parallel query](#parallel-query "Parallel query")2 \- Parallel append \- Parallel query [yb\_enable\_parallel\_append](/stable/additional-features/parallel-query/ "yb_enable_parallel_append") [yb\_parallel\_range\_rows](/stable/additional-features/parallel-query/ "yb_parallel_range_rows") [v2024.2.3.0](/stable/releases/ybdb-releases/v2024.2#v2024.2.3.0) [v2025.1.0.0](/stable/releases/ybdb-releases/v2025.1#v2025.1.0.0) (1) Wait-on-conflict concurrency and Batched nested loop join are enabled by default in v2024.1 and later. (2) Parallel query is not included in EPCM, but is included here because it contributes to PostgreSQL parity. ### 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 about read committed isolation, see [Read Committed](/stable/architecture/transactions/read-committed/ "Read Committed"). ### Cost-based optimizer Configuration parameter: `yb_enable_cbo=on` [Cost-based optimizer (CBO)](/stable/architecture/query-layer/planner-optimizer/ "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. For information on configuring CBO, refer to [Enable cost-based optimizer](/stable/best-practices-operations/ysql-yb-enable-cbo/ "Enable cost-based optimizer"). When enabling this parameter, you must run ANALYZE on user tables to maintain up-to-date statistics. To learn how CBO works, see [Query Planner / CBO](/stable/architecture/query-layer/planner-optimizer/ "Query Planner / CBO") ### 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. Wait-on-conflict concurrency is enabled (`true`) by default starting in v2024.1. To learn about concurrency control in YugabyteDB, see [Concurrency control](/stable/architecture/transactions/concurrency-control/ "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. Batched nested loop join is enabled (`true`) by default starting in v2024.1. To learn about join strategies in YugabyteDB, see [Join strategies](/stable/architecture/query-layer/join-strategies/ "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). ### 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 Note Parallel query is not included in EPCM, but is included here because it contributes to PostgreSQL parity. Configuration parameters: - Parallel query - `yb_enable_parallel_scan_colocated=true` - `yb_enable_parallel_scan_hash_sharded=true` - `yb_enable_parallel_scan_range_sharded=true` - `yb_parallel_range_rows=10000` (recommended) - Parallel append - `yb_enable_parallel_append=true` Enables the use of [PostgreSQL parallel queries](https://www.postgresql.org/docs/15/parallel-query.html "PostgreSQL parallel queries"). Using parallel queries, the query planner can devise plans that leverage multiple CPUs to answer queries faster. YugabyteDB supports parallel query for colocated, hash-, and range-sharded tables. Parallel query provides feature compatibility and improved performance parity. To learn about using parallel queries, see [Parallel queries](/stable/additional-features/parallel-query/ "Parallel queries"). ## Enable EPCM In v2025.2 and later, most of the features in EPCM are *enabled by default* in new universes when you deploy using [yugabyted](/stable/reference/configuration/yugabyted/ "yugabyted"), YugabyteDB Anywhere, or YugabyteDB Aeon. If you are deploying v2025.2 or later: - In YugabyteDB and YugabyteDB Anywhere, use individual flags to set features instead of EPCM. - In YugabyteDB Aeon, if you want [Ascending indexing by default](#default-ascending-indexing "Ascending indexing by default"), enable EPCM. You can enable EPCM as follows. ### YugabyteDB Note If you have set any of the compatibility features using their own flags, you cannot enable EPCM. Conversely, if you are using EPCM on a universe, you cannot set any of the features independently. To enable EPCM when deploying using yugabyted (versions earlier than v2025.2 only): - 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: ```sh ./bin/yugabyted start --enable_pg_parity_early_access ``` ### YugabyteDB Anywhere To enable EPCM in YugabyteDB Anywhere v2024.1, see the [Release notes](/stable/releases/yba-releases/v2024.1/#v2024.1.0.0 "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](/stable/yugabyte-platform/manage-deployments/edit-config-flags/ "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 Although YugabyteDB aims to be fully compatible with PostgreSQL, 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 | |-----------------------------------------------------------------------------------------------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | Exclusion constraints | [#3944](https://github.com/yugabyte/yugabyte-db/issues/3944) | | Deferrable constraints | [#1709](https://github.com/yugabyte/yugabyte-db/issues/1709) | | Constraint Triggers | [#4700](https://github.com/yugabyte/yugabyte-db/issues/4700) | | GiST indexes | [#1337](https://github.com/yugabyte/yugabyte-db/issues/1337) | | Events (Listen/Notify) ([EA](/stable/releases/versioning/#feature-maturity) supported in v2025.2.3.0 and later) | [#1872](https://github.com/yugabyte/yugabyte-db/issues/1872) | | XML Functions | [#1043](https://github.com/yugabyte/yugabyte-db/issues/1043) | | XA syntax | [#11084](https://github.com/yugabyte/yugabyte-db/issues/11084) | | Some ALTER TYPE variants | [#1893](https://github.com/yugabyte/yugabyte-db/issues/1893) | | CREATE CONVERSION | [#10866](https://github.com/yugabyte/yugabyte-db/issues/10866) | | Multi-column GIN indexes | [#10652](https://github.com/yugabyte/yugabyte-db/issues/10652) | | CREATE ACCESS METHOD | [#10693](https://github.com/yugabyte/yugabyte-db/issues/10693) | | DESC/HASH on GIN indexes (ASC supported) | [#10653](https://github.com/yugabyte/yugabyte-db/issues/10653) | | CREATE SCHEMA with elements | [#10865](https://github.com/yugabyte/yugabyte-db/issues/10865) | | Some ALTER TABLE variants | [#1124](https://github.com/yugabyte/yugabyte-db/issues/1124) | | UNLOGGED table | [#1129](https://github.com/yugabyte/yugabyte-db/issues/1129) | | Indexes on complex datatypes such as INET, CITEXT, JSONB, ARRAYs, and so on. | [#9698](https://github.com/yugabyte/yugabyte-db/issues/9698), [#23829](https://github.com/yugabyte/yugabyte-db/issues/23829), [#17017](https://github.com/yugabyte/yugabyte-db/issues/17017) | | REFERENCING clause for triggers | [#1668](https://github.com/yugabyte/yugabyte-db/issues/1668) | ### Features that do no apply to YugabyteDB Unsupported PostgreSQL feature Track feature request GitHub issue Storage parameters on indexes or constraints [#23467](https://github.com/yugabyte/yugabyte-db/issues/23467) transaction ids (xid) YugabyteDB uses [Hybrid logical clocks](/stable/architecture/transactions/transactions-overview/#hybrid-logical-clocks "Hybrid logical clocks") instead of transaction ids. [#15638](https://github.com/yugabyte/yugabyte-db/issues/15638)