Database backend: PostgreSQL or Microsoft SQL Server

CDC persists all of its durable state (governed records, process instances, model definitions, dead letters, key-vault secrets, and the L2 distributed cache) in one relational database. That database can be PostgreSQL (the default) or Microsoft SQL Server. The choice is a deployment decision, not a fork: one host binary, one connection-string slot, one configuration key. Both backends produce the same logical schema and the same observable behaviour across every area of the platform.

This page is for the architect or operator deciding which backend to run and what a SQL Server deployment requires. Read it alongside the production-operations runbook, which covers standing the platform up and keeping it running.

Choosing the backend

A single configuration key selects the backend:

• Database:Provider = Postgres (the default when the key is unset or empty)
• Database:Provider = SqlServer

Both dialects read the same connection-string slot, datacontrollerDb, so deployment configuration does not branch per provider. An unrecognised value fails fast at startup rather than silently pointing the host at the wrong backend.

The PostgreSQL path is unchanged by the addition of SQL Server. An existing PostgreSQL deployment behaves exactly as it did before, and leaving the key unset keeps that behaviour.

Required infrastructure for a SQL Server deployment

A SQL Server deployment can be fully PostgreSQL-free: the data provider, the L2 cache, and the identity store all run on SQL Server.

• SQL Server 2025 or later, or Azure SQL Database. 2025 is the floor because the platform requires the native json type introduced there, rather than porting PostgreSQL behaviour by workaround. Azure SQL Database and Azure SQL Managed Instance on the SQL Server 2025 or always-up-to-date update policy are supported. At startup the platform checks that the engine accepts the native json type, rather than reading a version number. An engine without it is refused before any schema migration runs, so the database is left untouched. CDC creates its schema on an empty database and does not convert a database created by an earlier version, so plan a SQL Server deployment as a fresh installation rather than an upgrade.

• A case-sensitive database collation. The CDC database must be created with the collation Latin1_General_100_CS_AS_SC, which is case-sensitive, accent-sensitive, and supplementary-character aware. This mirrors PostgreSQL’s case-sensitive default, so string comparisons mean the same thing on both backends. The platform does not create its own database, so the collation is a provisioning responsibility:

  CREATE DATABASE [cdc] COLLATE Latin1_General_100_CS_AS_SC;
  ALTER DATABASE [cdc] SET READ_COMMITTED_SNAPSHOT ON;

  Migrations refuse to run against a database with any other collation and fail with a message naming the required value, rather than starting up and comparing strings by different rules than intended. Setting the collation afterwards is not a practical repair: altering a database’s collation changes the default only for new objects, so existing text columns keep the collation they were created with. Create the database correctly, or recreate it. Individual comparisons that are deliberately case-insensitive, such as matching a model name, state that collation explicitly and are unaffected. The local dev stack states the collation when it creates its database, so a fresh dev environment comes up correctly without manual provisioning.

• READ_COMMITTED_SNAPSHOT enabled on the CDC database. SQL Server’s default isolation level makes a reader take shared locks, so reads block against concurrent writes and can deadlock with them. The platform’s workload has exactly that shape: the process engine writes steps continuously while the management API reads instance lists, and the user interface refetches those reads on every engine state change. Enabling row versioning removes that whole class of deadlock, so it is a requirement rather than a tuning option, and it applies to the CDC database as well as to Keycloak’s:

  ALTER DATABASE [cdc] SET READ_COMMITTED_SNAPSHOT ON;

  Run this statement when nothing is connected to the database. Enabling the setting needs exclusive access: SQL Server waits until every other connection drains before it takes effect. The statement above is therefore best run immediately after CREATE DATABASE, before any host starts.

  Migrations refuse to run against a database where the setting is off, and fail with a message naming the database and this exact statement. The platform never runs the statement itself, precisely because of the exclusive access it needs: an automatic attempt at host startup would block behind a second host replica’s connections instead of failing visibly. Repairing an existing database is a matter of draining connections and running the one statement. Unlike the collation, the setting is repairable after the fact. The local dev stack enables it when it creates its database, so a fresh dev environment comes up correctly without manual provisioning.

• The L2 distributed cache on SQL Server. When the backend is SQL Server the cache runs on SQL Server too, in its own schema, so no separate cache infrastructure (and no PostgreSQL) is required for a cache tier.

• Keycloak on SQL Server. The identity store runs with KC_DB=mssql. Two operational constraints apply:

  • The Keycloak database must have READ_COMMITTED_SNAPSHOT enabled before Keycloak first connects (its default isolation level deadlocks under load on SQL Server). CDC provisions this automatically in the local dev stack; production provisioning must ensure the database exists with READ_COMMITTED_SNAPSHOT ON before the Keycloak container starts.
  • The connection is configured with an explicit JDBC URL and TLS disabled against a dev-issued certificate (encrypt=false); production should point at a properly trusted certificate.
  • Driver caveat: the SQL Server JDBC driver ships in the community Keycloak image the platform uses. The Red Hat build of Keycloak excludes it, so re-platforming onto that build would require adding the driver manually.

The rest of the stateful surface is identical to a PostgreSQL deployment: an OpenID Connect identity provider (Keycloak), a secret store, and a message broker where the deployment runs workers or governed boundaries.

Accepted divergences from PostgreSQL

Both backends are held to the same behaviour, proven by a provider-parity test suite that runs against both. A small number of differences are inherent to SQL Server and have been reviewed and accepted. They are behavioural trade-offs to understand before choosing SQL Server, not defects:

1. Random-GUID primary-key fragmentation. Every primary key is a client-generated GUID. As a clustered key this fragments over time. Accepted; revisit only if measurement shows it matters.
2. Non-numeric JSON in numeric comparisons. A numeric comparison against a JSON value that is not numeric is silently skipped rather than erroring. Accepted and documented.
3. NULL handling in uniqueness rules. SQL Server treats NULLs as equal in a unique index, so CDC reproduces PostgreSQL’s “NULLs are distinct” behaviour with an explicit mechanism. The observable result matches PostgreSQL.
4. Case handling at one comparison site. One equality comparison on stored state uses linguistic rather than binary equivalence under the configured collation. This is a single accepted site.
5. Cache key length. Cache keys are bounded to 450 characters (PostgreSQL’s are unbounded). CDC’s keys run near 114 characters, so this is a theoretical bound only.
6. Keycloak driver source. As above, the SQL Server JDBC driver is present in the community Keycloak image but not the Red Hat build.
7. Long values inside an array filter. A filter that looks inside every element of an array reads a value of more than 4000 characters in an element as empty, so that element does not match. A filter on a single value of any length matches as it does on PostgreSQL. Accepted.
8. A lone surrogate character is replaced, not refused. SQL Server’s json type accepts a JSON document that contains half of a UTF-16 surrogate pair and stores replacement characters in its place, where PostgreSQL refuses the document. No input path of the platform can deliver such a document, because every ingest path parses the payload first and that parse refuses it. Accepted and recorded.

JSON is stored in the native json type, which keeps the value of a document but not always its exact text. The type removes whitespace, returns an escaped character as the character itself, and returns a negative zero as zero. CDC promises the value of a stored document, not the text a producer chose to express it in, and no part of the platform compares two stored documents as text. PostgreSQL also renders stored JSON in its own way, so the two backends can return different text for the same value.

Three divergences that used to be on this list have been removed rather than accepted. The native json type removed two of them. JSON storage no longer roughly doubles in size against PostgreSQL, because the type stores a compact binary form. A filter on a single value longer than 4000 characters no longer matches nothing, because the platform now reads the full value. The third is the time-window clock source. Age-based filters, such as the sweep that retires abandoned debug runs, once computed their cutoff from the application server’s clock on SQL Server while stamping the completion time from the database’s clock in the same statement. Under clock skew between the two machines the backends retired different sets of instances. Both backends now pass the threshold as a number of seconds and let the database subtract it from its own clock, so a statement never mixes clock sources and the two backends select the same rows.

What to take into an evaluation

• The backend is a deployment choice through one configuration key against one connection-string slot. PostgreSQL is the default and is unchanged.
• SQL Server enables a fully PostgreSQL-free deployment, including the identity store and the cache tier, for operators standardised on SQL Server or barred from running PostgreSQL.
• The two backends converge on the same logical schema and the same observable behaviour, with a short list of reviewed, accepted differences.