CockroachDB and YugabyteDB

CockroachDB and YugabyteDB accept PostgreSQL-like syntax while missing PostgreSQL capabilities, so Ptah routes each one as a distinct dialect through the PostgreSQL implementation family with its own capability preset instead of treating the server as a drop-in PostgreSQL server. A live connection reads the server banner and selects the matching preset automatically.

Spanner’s PostgreSQL interface is not one of these dialects. Ptah serves it only through the optional Spanner provider, which states its own capabilities. A postgres:// connection whose server banner names Spanner is refused with provider_engine_identified before anything is read, and is never inspected or planned as PostgreSQL.

• CockroachDB: the preset excludes concurrent index creation and drops, XML columns, and advisory locks. Live CockroachDB v26.2.5 accepts role management, row-level security, standalone sequences, and SERIAL columns. A CockroachDB role has no SUPERUSER and no INHERIT option, and CockroachDB refuses both words, so CREATE ROLE leaves them out. A role declared as a superuser, or with inherit set to false, is refused by name before any statement runs: a CockroachDB role is a superuser only as a member of admin, and every role inherits. It is also the one target that ADDS to PostgreSQL’s surface rather than subtracting from it: see CockroachDB row-level TTL. A column declared INT or INTEGER without a width is compared at the width CockroachDB builds for it, which the session’s default_int_size sets: INT8 by default, INT4 when the session sets 4. A declared width such as INT4 or BIGINT is compared as written. A column declared in CockroachDB’s own names, STRING, BYTES or STRING[], is compared as the text, bytea or text[] the catalog reports, and BLOB and JSON as the bytea and jsonb it builds from them. A sized STRING(10) is a text column with a width rather than a VARCHAR(10), and it is read and inspected as STRING(10), so a width change is still a change. An index is compared by its uniqueness, its keys (column or expression, and direction), its STORING columns and its access method, and a change rebuilds it. Its NULLS ordering, operator class and storage parameters are not compared: CockroachDB has no NULLS clause in an index key, reports no operator class, and stores no index storage parameter. A unique gin or gist index is refused before anything is rendered, as CockroachDB refuses it with inverted indexes can't be unique.
• YugabyteDB: the preset includes concurrent index creation, role management, row-level security, standalone sequences, XML columns, and advisory locks on the measured 2026.1 line. DROP INDEX CONCURRENTLY remains excluded because that server line rejects it. A generated concurrent create therefore rolls back with ordinary DROP INDEX; only the forward migration requires no-transaction execution. An index is compared by its uniqueness, its keys (column or expression, direction, NULLS ordering and operator class), its INCLUDE columns and its access method, and a change rebuilds it. YugabyteDB builds btree and hash as lsm, and gin as ybgin, so the comparison reads them as one. A unique hash index is built as a unique lsm index. A unique gin or ybgin index is refused before anything is rendered, as YugabyteDB refuses it. Its storage parameters are not compared, because YugabyteDB refuses every one Ptah writes for an index. An ascending key with a NULLS ordering is written with ASC: without a direction, YugabyteDB builds a leading key as a hash key and ignores the ordering. A column of a serial type is NOT NULL whether or not the file says so, as the server builds it.

CockroachDB and YugabyteDB open a transaction around a migration body, and a schema statement does not wait for it to end. See What a failed migration leaves for what each keeps, and Apply migrations for what each target records.

Coverage in continuous integration

CockroachDB and YugabyteDB run in integration coverage against live open-source containers. Their reader coverage seeds a table, index, view, materialized view, sequence, and row-level security policy, then verifies both ptah db read and ptah-compat schema inspect.

PostgreSQL and YugabyteDB keep the database-scoped publications, subscriptions, logical replication slots, event triggers, and non-extension foreign-data objects a dev database held when the command started, and reject one the run created before dev-database cleanup. PostgreSQL additionally removes database large objects inside the cleanup transaction; YugabyteDB does not support that catalog write path.

What a failed migration leaves

CockroachDB and YugabyteDB open the transaction PostgreSQL does around a migration file, and a ROLLBACK there does not take the schema statements back. They differ in what happens to the rest:

• CockroachDB 25 and later, with autocommit_before_ddl on, its default: a schema statement commits the transaction first and the session then has none. Data ahead of it and data after it both stay.
• YugabyteDB, with yb_ddl_transaction_block_enabled off, its default: a schema statement commits on its own and the transaction stays open. Data ahead of it and data after it both roll back.

CREATE, ALTER, DROP, TRUNCATE, COMMENT and GRANT all behave this way, measured on CockroachDB v26.3.5 and YugabyteDB 2025.2.5 and 2026.1.2. migrations up --json reports what stays. Each cell is the outcome, the statements the row records as applied, and what the database holds afterward:

Body	PostgreSQL	CockroachDB	YugabyteDB	MySQL, MariaDB
CREATE TABLE, then a failure	failed, 0 of 2, no table	partial, 1 of 2, the table	partial, 1 of 2, the table	partial, 1 of 2, the table
CREATE TABLE, INSERT into it, then a failure	failed, 0 of 3, no table	partial, 2 of 3, the table and its row	partial, 1 of 3, the table and no row	partial, 2 of 3, the table and its row
INSERT, CREATE TABLE, then a failure	failed, 0 of 3, nothing	partial, 2 of 3, the row and the table	unknown, 0 of 3, the table and no row	partial, 2 of 3, the row and the table
INSERT, then a CREATE TABLE the server refuses	failed, 0 of 2, no row	partial, 1 of 2, the row	failed, 0 of 2, no row	unknown, 1 of 2, the row
INSERT, then a failure	failed, 0 of 2, no row	failed, 0 of 2, no row	failed, 0 of 2, no row	failed, 0 of 2, no row

A partial row is resumed with ptah migrations up --allow-dirty, which skips the statements the row counts. An unknown row has no statement to resume from. Inspect the database and finish the migration by hand, then record it with ptah migrations repair --version <version>.

A server can turn this off. CockroachDB 24.3 keeps autocommit_before_ddl off, and a YugabyteDB server started with ysql_yb_ddl_transaction_block_enabled rolls schema statements back. Ptah reads the answer from the server in both cases, so a failure there reports failed, 0 of 2, with no table. schema apply reports unknown for a plan that stopped after it was sent, on every engine, because it keeps no count: see The apply document.

Scoped schema clean

ptah-compat schema clean with --include or --exclude drops only the selected objects, with RESTRICT, and drops a refused object again once the selected objects that depend on it are gone.

CockroachDB 25 and later commit each schema statement as it runs: autocommit_before_ddl is on by default, so a DROP inside a transaction commits the transaction first. The scoped plan therefore opens no transaction there. Each drop runs on its own and takes no relation lock. A plan that an unselected dependency stops keeps the drops that ran before it, and the error names them, as in this run measured on v26.3.2:

Error: drop schema object enum "mood": SQL execution failed: ERROR: cannot drop type "mood" because other objects ([ref_fix.public.keep_t]) still depend on it (SQLSTATE 2BP01)
SQL: DROP TYPE IF EXISTS "mood" RESTRICT
the server commits each drop as it runs; dropped before the failure: table "free_t"

YugabyteDB runs the plan in one transaction, each drop under a savepoint. With yb_ddl_transaction_block_enabled at its default, off, the server does not roll back a drop when the transaction rolls back. Measured on 2026.1.2, a refused plan leaves the earlier drops in place, and the error does not name them.

CockroachDB row-level TTL

CockroachDB expires rows on a schedule the server runs, declared as table storage parameters. Ptah manages that policy through the render, plan, apply, introspect, and diff cycle: a declared TTL is applied, read back from pg_class.reloptions, and compared to zero difference on the next run.

Declare it as platform.cockroachdb properties on the table, named exactly for the storage parameters they become. A property whose name begins with ttl but is not one of them, misspelled or in upper case, is refused by name rather than ignored:

//ptah:schema:table name="sessions" platform.cockroachdb.ttl_expiration_expression="expires_at" platform.cockroachdb.ttl_job_cron="@daily"
type Sessions struct {
  //ptah:schema:field name="id" type="BIGINT" primary="true"
  ID int64
  //ptah:schema:field name="expires_at" type="TIMESTAMPTZ"
  ExpiresAt time.Time
}

A YAML schema puts the same names in the table’s cockroachdb platform group:

tables:
  sessions:
    platform:
      cockroachdb:
        ttl_expiration_expression: expires_at
        ttl_job_cron: "@daily"

ptah schema render --dialect cockroachdb emits that as:

CREATE TABLE "sessions" (
  "id" BIGINT PRIMARY KEY NOT NULL,
  "expires_at" TIMESTAMPTZ
) WITH (ttl_expiration_expression = 'expires_at', ttl_job_cron = '@daily');

Changing the policy emits ALTER TABLE ... SET (...), and removing it emits ALTER TABLE ... RESET (ttl), which drops the whole configuration in one statement and leaves the table alone. A table in a Go or YAML schema that names no TTL property declares no TTL, so a policy on the live table is removed. ptah introspect writes a read policy back as the same properties.

What Ptah manages

Eleven parameters. One of the two enablers is required; the rest are refused without one. Nine read back from the catalog exactly as written on both declared lines. ttl_expire_after and ttl_row_stats_poll_interval are compared by the interval and the duration they denote rather than by their text, because the server rewrites the value it stores:

Property	What it sets
ttl_expiration_expression	The SQL expression whose value is when a row expires.
ttl_expire_after	The interval after a row is written at which it expires, such as 3 days.
ttl_row_stats_poll_interval	How often the job refreshes its row-count estimate, such as 10m.
ttl_job_cron	The schedule the deletion job runs on.
ttl_select_batch_size	Rows selected per batch; at least 1.
ttl_delete_batch_size	Rows deleted per batch; at least 1.
ttl_select_rate_limit	Rows selected per second; at least 1.
ttl_delete_rate_limit	Rows deleted per second; at least 1.
ttl_pause	Pauses the deletion job without removing the policy.
ttl_label_metrics	Labels the job’s metrics with the table name.
ttl_disable_changefeed_replication	Omits the job’s deletes from changefeeds.

What Ptah refuses, and why

• An interval Ptah cannot read is refused. ttl_expire_after accepts a sequence of quantity-and-unit pairs (3 days, 2 years 3 months, 1 day 2 hours), an optional trailing HH:MM:SS, and the ISO-8601 form (P1Y2M3D, PT1H30M). A spelling outside that surface is refused rather than sent, because the server would normalize it into a form Ptah could not predict and the plan would re-issue the change forever. Ambiguous abbreviations such as a bare m are refused for the same reason: minutes and months are two different retention policies.
• A poll interval the server would not keep is refused. The server truncates ttl_row_stats_poll_interval to whole seconds and stores nothing at all for a value below one second.
• ttl cannot be declared. It is derived from the other parameters, and the server refuses it when it arrives alone.
• A knob without ttl_expiration_expression or ttl_expire_after is refused, because the server refuses it too: every other ttl_ parameter needs an expiry configured.
• Zero and negative knob values are refused. The server rejects a negative value and accepts zero while storing the parameter nowhere at all, so neither can ever read back as declared. Omit the property to keep the engine default.
• A false boolean normalizes to “not declared”, because on the server those are the same state: ttl_pause = false is stored nowhere, and setting it erases an existing true exactly as a reset does.

Two server behaviors Ptah works around

The interval is rewritten on the way in. Measured on both declared lines, ttl_expire_after = '72 hours' is stored as '72:00:00', '5 minutes' as '00:05:00', '1 week' as '7 days', and 'P1Y2M3D' as '1 year 2 mons 3 days'. Ptah sends what you wrote and compares what the interval denotes, so a declaration converges whichever spelling it uses. The three fields of a PostgreSQL interval stay apart in that comparison: a month is not thirty days and a day is not twenty-four hours, and the server keeps them apart too.

Hidden columns are left out of the description. ttl_expire_after adds a crdb_internal_expiration column that CockroachDB marks hidden, and a table declaring no primary key gets a hidden rowid the same way. Neither is a column anybody declared, and describing them made a read unreplayable — applying it back asked for a column the engine owns. Both are now excluded from a CockroachDB read. PostgreSQL and YugabyteDB have no such notion and their reads are unchanged.

A declared primary key that leaves becomes the hidden rowid. Every CockroachDB table holds a primary key, and the server refuses a key’s drop with no key added in the same statement. So a plan adds the hidden column, then drops the key and adds one over that column in one statement, which leaves the table as one declared without a key. A table that holds a column named rowid stops at the first statement, before anything changes.

On other engines

The platform.cockroachdb properties apply to CockroachDB only, as every platform property applies to its own target, so the same schema renders for PostgreSQL without the policy. A policy attached to a table in Go code without that binding is refused on every other target before anything is applied. PostgreSQL answers unrecognized parameter "ttl_expiration_expression" on its own, but YugabyteDB first answers WARNING: storage parameter ttl_expiration_expression is unsupported, ignoring. An engine that ignores a retention policy is worse than one that refuses it, so Ptah does not leave that decision to the server.

A schema format that cannot declare the policy, such as HCL or SQL, leaves a live policy alone instead of removing it, and HCL export reports the policy it cannot write.

A CockroachDB dev database is required for dev-database workflows on a CockroachDB target; a mismatched --dev-url is refused with --dev-url dialect "postgres" does not match --url dialect "cockroachdb".

Invisible indexes

CockroachDB hides an index from the optimizer while it keeps the index up to date. The clause comes last in CREATE INDEX, after the key parts and after WHERE, and NOT VISIBLE WHERE ... is a syntax error:

CREATE INDEX k_a ON ic (a) WHERE a > 0 NOT VISIBLE;

Ptah reads the clause from pg_get_indexdef, which prints it the same way, and from a schema file, where MySQL’s INVISIBLE is taken as a synonym. It writes NOT VISIBLE and changes a visibility in place with ALTER INDEX ic@k_a VISIBLE. The server refuses to hide a primary key.

A partially visible index, VISIBILITY 0.5, is refused by name, in a schema file and in a database read. The model holds a visible or a hidden index, and either reading would move the index on the next apply. VISIBILITY 0.0 and VISIBILITY 1.0 are read as hidden and visible. Measured on CockroachDB 26.3.2; the capability probe measures the 25.4 and 26.2 lines. PostgreSQL and YugabyteDB have no such index, and Ptah refuses an invisible index there rather than build it visible.

Trigger conditions

A trigger’s WHEN condition is read through pg_get_triggerdef, which CockroachDB 25.4 does not have. On that line Ptah reads each trigger without its condition and does not compare one, so a declared condition is neither reported as missing nor planned again. CockroachDB 26.2 and 26.3 have the function and print the condition without the outer parentheses and with each constant annotated, (new).a > 0:::INT8; the comparison drops the annotation. Every YugabyteDB line prints the condition as PostgreSQL does.

CockroachDB takes a condition on a row value written as (NEW).a, and refuses NEW.a with no data source matches prefix: new. It also refuses UPDATE OF column lists, statement-level triggers and so TRUNCATE triggers. YugabyteDB refuses REFERENCING transition tables. Measured on CockroachDB 25.4.16, 26.2.7 and 26.3.1 and on YugabyteDB 2024.2, 2025.2 and 2026.1.

YugabyteDB’s own extensions

Every YugabyteDB database starts with extensions the server installs into pg_catalog: pg_stat_statements on 2024.2, 2025.2 and 2026.1, and postgres_fdw as well on 2026.1. On 2026.1 the server’s global views depend on both, and YugabyteDB refuses DROP EXTENSION for either one.

A comparison never plans the removal of these extensions, whatever the ignore list holds, so a declaration that does not name them applies. Only the removal is withheld: a declaration that names postgres_fdw creates it on a line that does not install it.

A column’s size

decimal and numeric hold any number where a column declares no precision, on all three engines, so numeric declared numeric(10,2) is a change rated destructive and numeric(10,2) declared numeric is a change rated safe. See how a column change is rated.

A lower capacity is rated destructive here as elsewhere: string(100) declared string(5) on CockroachDB, double precision declared real on all three, and a fraction under an integer. CockroachDB keeps the eight bytes of a row it has already written under FLOAT4, so its value reads back whole, and the rating follows the type.

Next steps

• Which release lines are declared and at what support level: Database support matrix.
• What PostgreSQL itself manages: PostgreSQL.
• Capability keys per dialect: Capabilities.
• Spanner’s PostgreSQL interface, through its provider: Configure the Spanner provider.