YugabyteDB is a [PostgreSQL-compatible](https://www.yugabyte.com/tech/postgres-compatibility/) 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 {{}}, 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 {{}} 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 {{}} to {{}} 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](../../../deploy/manual-deployment/start-yugabyted/), [YugabyteDB Anywhere](../../../yugabyte-platform/create-deployments/create-universe-multi-zone/), or [YugabyteDB Aeon](../../../yugabyte-cloud/cloud-basics/create-clusters/): - [Read committed](#read-committed) (yb_enable_read_committed_isolation=true) - [Cost-based optimizer](#cost-based-optimizer) (ysql_pg_conf_csv=yb_enable_cbo=on) - [Auto Analyze](../../../additional-features/auto-analyze/) (ysql_enable_auto_analyze=true) - [YugabyteDB bitmap scan](#yugabytedb-bitmap-scan) (yb_enable_bitmapscan=true) - [Parallel append](#parallel-query) (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) 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) | [yb_enable_read_committed_isolation](../yb-tserver/#ysql-default-transaction-isolation) | {{}} | {{}} | | [Wait-on-conflict](#wait-on-conflict-concurrency)1 | [enable_wait_queues](../yb-tserver/#enable-wait-queues) | {{}} | {{}} | | [Cost-based optimizer](#cost-based-optimizer) | [yb_enable_cbo](../yb-tserver/#yb-enable-cbo) | {{}} | {{}} | | [Batch nested loop join](#batched-nested-loop-join)1 | [yb_enable_batchednl](../yb-tserver/#yb-enable-batchednl) | {{}} | {{}} | | [Ascending indexing by default](#default-ascending-indexing) | [yb_use_hash_splitting_by_default](../yb-tserver/#yb-use-hash-splitting-by-default) | {{}} | | | [YugabyteDB bitmap scan](#yugabytedb-bitmap-scan) | [yb_enable_bitmapscan](../yb-tserver/#yb-enable-bitmapscan) | {{}} | {{}} | | [Efficient communication
between PostgreSQL and DocDB](#efficient-communication-between-postgresql-and-docdb) | [pg_client_use_shared_memory](../yb-tserver/#pg-client-use-shared-memory) | {{}} | {{}} | | [Parallel query](#parallel-query)2
- Parallel append
- Parallel query |
[yb_enable_parallel_append](../../../additional-features/parallel-query/)
[yb_parallel_range_rows](../../../additional-features/parallel-query/) | {{}} | {{}} | (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](../../../architecture/transactions/read-committed/). {{}} ### Cost-based optimizer Configuration parameter: `yb_enable_cbo=on` [Cost-based optimizer (CBO)](../../../architecture/query-layer/planner-optimizer/) 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](../../../best-practices-operations/ysql-yb-enable-cbo/). 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](../../../architecture/query-layer/planner-optimizer/) {{}} ### 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](../../../architecture/transactions/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](../../../architecture/query-layer/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 title="Note" >}} Parallel query is not included in EPCM, but is included here because it contributes to PostgreSQL parity. {{< /note >}} 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). 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](../../../additional-features/parallel-query/). {{}} ## 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](../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), enable EPCM. You can enable EPCM as follows. ### YugabyteDB {{}} 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). 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. {{}} Setting Enhanced Postgres Compatibility overrides any [flags you set](../../../yugabyte-platform/manage-deployments/edit-config-flags/) 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. 1. 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 | | ----------- | ----------- | | LOCK TABLE to obtain a table-level lock | {{}}| | Table inheritance | {{}}| | Exclusion constraints | {{}}| | Deferrable constraints | {{}}| | Constraint Triggers|{{}}| | GiST indexes | {{}}| | Events (Listen/Notify) | {{}}| | XML Functions | {{}}| | XA syntax | {{}}| | ALTER TYPE | {{}}| | CREATE CONVERSION | {{}}| | Primary/Foreign key constraints on foreign tables | {{}}, {{}} | | GENERATED ALWAYS AS STORED columns | {{}}| | Multi-column GIN indexes| {{}}| | CREATE ACCESS METHOD | {{}}| | DESC/HASH on GIN indexes (ASC supported) | {{}}| | CREATE SCHEMA with elements | {{}}| | Index on citext column | {{}}| | ABSTIME type | {{}}| | transaction ids (xid)
YugabyteDB uses [Hybrid logical clocks](../../../architecture/transactions/transactions-overview/#hybrid-logical-clocks) instead of transaction ids. | {{}}| | Some ALTER TABLE variants| {{}}| | UNLOGGED table | {{}} | | Indexes on complex datatypes such as INET, CITEXT, JSONB, ARRAYs, and so on.| {{}}, {{}}, {{}} | | %TYPE syntax in Functions/Procedures/Triggers|{{}}| | Storage parameters on indexes or constraints|{{}}| | REFERENCING clause for triggers | {{}}| | BEFORE ROW triggers on partitioned tables | {{}}|