DBAtlas · Database operations atlas

Set compatibility level / feature version

Oracle

-- Raise the COMPATIBLE initialization parameter (irreversible!):
ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE;
-- Then restart the instance. To see the current value:
SHOW PARAMETER compatible;

The COMPATIBLE parameter determines which version features and file formats are enabled. Once raised and the instance restarted, it CANNOT BE REVERTED (downgrade is blocked); therefore raise it gradually until the upgrade's success is confirmed.

PostgreSQL

# Not applicable: PostgreSQL has no database compatibility level.
# If legacy behavior is needed, set the relevant GUCs (e.g. SET standard_conforming_strings=off)
# or manage the major migration with dump/restore.

PostgreSQL has no 'compatibility level' concept like SQL Server/Oracle; version behavior is tied directly to the binary version. Migration between versions is done with pg_dump/pg_restore or pg_upgrade.

MySQL

# Not applicable: MySQL has no configurable database compatibility level.
# Behavioral differences are managed with sql_mode:
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

MySQL has no version-based compatibility level; SQL behavior depends mostly on the binary version and the sql_mode setting. Fine-tune sql_mode to preserve legacy application behavior.

SQL Server

-- Set the database compatibility level (e.g. SQL Server 2022 = 160):
ALTER DATABASE Sales SET COMPATIBILITY_LEVEL = 160;
-- To see the current value:
SELECT name, compatibility_level FROM sys.databases;

COMPATIBILITY_LEVEL determines which version the query optimizer and language features behave like (150=2019, 160=2022). After upgrading the instance, databases can stay at the old level; raising it gradually and monitoring plan regressions is recommended. It can be lowered again.

MongoDB

// Set the Feature Compatibility Version (FCV):
db.adminCommand({ setFeatureCompatibilityVersion: "7.0", confirm: true })
// Read the current value:
db.adminCommand({ getParameter: 1, featureCompatibilityVersion: 1 })

FCV controls when persistent data format changes and new features that come with a version are enabled. Raise it after the binary upgrade while the cluster is stable; lowering it is only possible if the same binary version still supports the lower FCV.

Redis

# Not applicable: Redis has no compatibility level / feature version concept.
# Behavior depends directly on the running binary version.

Redis has no configurable compatibility level; the feature set depends entirely on the running version. Compatibility is preserved only at the RDB/AOF file format level, in a forward direction.

Elasticsearch

# Not applicable: Elasticsearch has no configurable compatibility level.
# An index is tied to the format of the version it was created in; very old indices
# cannot be read in the new version and require a reindex.

Elasticsearch has no compatibility level setting; each index carries the format of the major version it was created in. Indices created in the previous major version can be read, while older ones must be moved to the new version with _reindex.

Open in DBAtlas →