What’s New in v22.2With the release of CockroachDB v22.2, we’ve added new capabilities in CockroachDB to help you build, optimize, and operate more effectively and efficiently. Check out a summary of the most significant user-facing changes and then upgrade to CockroachDB v22.2.For demos and videos on the new features, see the v22.2 launch page. Join our webinar on schema design and query tuning in a distributed SQL database.Feature highlightsThis section summarizes the most significant user-facing changes in v22.2.0. For a complete list of features and changes, including bug fixes and performance improvements, see the release notes for previous testing releases. You can also search for what’s new in v22.2 in our docs.SQL
User-defined functions (UDFs) – Migrate existing workloads that use user-defined functions (UDFs) to CockroachDB without the need to change them. CockroachDB supports invoking UDFs in SELECT, FROM, and WHERE clauses of DML statements.
Schema Conversion Tool – Use the Schema Conversion Tool on the CockroachDB Cloud Console to analyze your schema for SQL incompatibilities. The tool will identify and help you resolve errors in your schema, and then create a new CockroachDB database with the converted schema.
Row-level time to live (TTL) – Row-level TTL is now generally available (GA). The GA release of row-level TTL support builds upon the existing ability to configure the lifespan of data by extending its configurability down to the row level. This release also improves TTL performance, and adds additional syntax to make calculating TTL expiration easier.
Trigram indexes – Perform text search directly within CockroachDB with trigram indexes. Trigram indexes enable “fuzzy” search (i.e., match a search term closely instead of exactly) within CockroachDB without the need to change your queries or set up complex full text search systems.
Invisible indexes – Create and test indexes without affecting your overall application.
Insights page – View problems that CockroachDB has detected in your workloads and schemas on the Insights page. The page helps identify SQL statements with high retry counts, slow execution, or suboptimal plans. It also helps identify indexes that should be created, altered, replaced, or dropped to improve performance.

Recovery and I/O

Change data capture (CDC) transformations (Preview, Enterprise-only) – Simplify your event-based applications and data pipelines by extracting only the data you need from CockroachDB. Use standard SQL expressions to specify up-front transformations to the data you want to stream to the rest of your system.
CDC performance improvements (Enterprise-only) – Get up to 9x faster changefeed performance for certain workloads or deployments with large storage volumes (in the order of terabytes).
Backup validation – Use backup validation tools to check that backups you have in storage are restorable.

Database operations

Secondary regions – Use secondary regions to specify which region the leaseholders move to in the event of a failure. Secondary regions let you improve latency during failures, plan better for outages, and routinely test failover scenarios without impacting performance.
PostgREST support – Perform basic CRUD operations in CockroachDB using a REST API with new support for the open source tool PostgREST. Docs coming soon.
Hasura integration – Build APIs, secure access, deploy and scale the app server, and optimize performance with the CockroachDB integration with Hasura. Hasura acts as a middleware for translating GraphQL queries into SQL and provides an easy way to offer GraphQL functionality, so you do not have to write application logic for these capabilities. In addition to GraphQL, Hasura also provides an easy way to build REST APIs on top of CockroachDB. Docs coming soon.

Security

Private CockroachDB dedicated clusters – Secure your CockroachDB dedicated clusters with private IPs to protect your data from potential threats. Updates to give your cluster end-to-end protection include the ability to secure clusters with only node-level private IPs, to access public external resources using a NAT Gateway, and to access cloud storage over your provider’s private connectivity.
Egress perimeter controls on CockroachDB dedicated clusters (Preview) – Use egress perimeter controls to reduce risk by configuring allowed external destinations for data. This new feature lets admins specify where users are allowed to send backups, data exports, changefeeds, etc. The virtual firewall capability significantly reduces the risk of data exfiltration and lets you restrict a cluster’s access to only organizationally allowed resources.
Cloud cluster Single-Sign On (SSO) (Preview) – Use cluster SSO to allow application-level SQL identities to use JSON web tokens (JWT) to authenticate on CockroachDB Cloud clusters. SSO also lets SQL users access their cluster using the same SSO provider that you’ve already set up for the CockroachDB Cloud Console.
Role-Based Access Control (RBAC) for backup, restore, and observability – Combine or inherit task-specific permissions into SQL roles for backup, restore, and observability. Roles can be assigned to relevant SQL users to simplify management of access control and prevent escalation of privilege.
Role-Based Access Control (RBAC) for CDC (Enterprise-only) – Combine or inherit task-specific permissions into SQL roles for CDC. Roles can be assigned to relevant SQL users to simplify management of access control and prevent escalation of privilege.
Identity and Access Management (IAM) roles for bulk operations – Create secure IAM roles in your cloud provider to access your cloud resources, so developers and operators can configure backups, restores, import, export, and CDC without requiring direct access to those resources or to relevant credentials.
Cloud organization audit logs – Export information on your team’s actions in your Cloud organization (e.g., managing users and their access, creating and deleting clusters, and configuring IP allowlisting). Access these logs as needed, or create simple pull-based clients to incrementally send those to your Security Information and Event Management (SIEM) tools.

Backward-incompatible changesBefore upgrading to CockroachDB v22.2, be sure to review the following backward-incompatible changes and adjust your deployment as necessary.
CockroachDB no longer performs environment variable expansion in the parameter –certs-dir. Uses like –certs-dir=”$HOME/path” (expansion by CockroachDB) can be replaced by –certs-dir=”$HOME/path” (expansion by the Unix shell). #81298
In the Cockroach CLI, BOOL values are now formatted as t or f instead of True or False. #81943
Removed the cockroach quit command. It has been deprecated since v20.1. To shut down a node gracefully, send a SIGTERM signal to it. #82988
Added a cluster version to allow the Pebble storage engine to recombine certain SSTables (specifically, user keys that are split across multiple files in a level of the log-structured merge-tree). Recombining the split user keys is required for supporting the range keys feature. The migration to recombine the SSTables is expected to be short (split user keys are rare in practice), but will block subsequent migrations until all tables have been recombined. The storage.marked-for-compaction-files time series metric can show the progress of the migration. #84887
Using a single TCP port listener for both RPC (node-node) and SQL client connections is now deprecated. This capability will be removed in the next version of CockroachDB. Instead, make one of the following configuration changes to your CockroachDB deployment:
Preferred: keep port 26257 for SQL, and allocate a new port, e.g., 26357, for node-node RPC connections. For example, you might configure a node with the flags –listen-addr=:26357 –sql-addr=:26257, where subsequent nodes seeking to join would then use the flag –join=othernode:26357,othernode:26257. This will become the default configuration in the next version of CockroachDB. When using this mode of operation, care should be taken to use a –join flag that includes both the previous and new port numbers for other nodes, so that no network partition occurs during the upgrade.
Optional: keep port 26257 for RPC, and allocate a new port, e.g., 26357, for SQL connections. For example, you might configure a node with the flags –listen-addr=:26257 –sql-addr=:26357. When using this mode of operation, the –join flags do not need to be modified. However, SQL client apps or the SQL load balancer configuration (when in use) must be updated to use the new SQL port number. #85671

If no nullif option is specified while using IMPORT CSV, then a zero-length string in the input is now treated as NULL. The quoted empty string in the input is treated as an empty string. Similarly, if nullif is specified, then an unquoted value is treated as NULL, and a quoted value is treated as that string. These changes were made to make IMPORT CSV behave more similarly to COPY CSV. If the previous behavior (i.e., treating either quoted or unquoted values that match the nullif setting as NULL) is desired, you can use the new allow_quoted_null option in the IMPORT statement. #84487
COPY FROM operations are now atomic by default instead of being segmented into 100 row transactions. Set the copy_from_atomic_enabled session setting to false for the previous behavior. #85986
The GRANT privilege has been removed and replaced by the more granular WITH GRANT OPTION, which provides control over which privileges are allowed to be granted. #81310
Removed the ability to cast int, int2, and int8 to a 0 length BIT or VARBIT. #81266
Removed the deprecated GRANT privilege. #81310
Removed the ttl_automatic_column storage parameter. The crdb_internal_expiration column is created when ttl_expire_after is set and removed when ttl_expire_after is reset. #83134
Removed the byte string parameter in the crdb_internal.schedule_sql_stats_compaction function. #82560

Deprecations

debug zip’s –redact-logs flag has been deprecated in favor of the –redact flag, which applies to a broader scope than just logs, and also includes logs. The new –redact flag triggers the redaction of all sensitive data in debug zip bundles, with the exception of range keys. Range keys must remain unredacted because they are essential to support CockroachDB. The –redact-logs flag is still available but displays a redaction warning and is interpreted as –redact instead. #88266
Previously, BACKUP allowed the user to specify a custom subdirectory name for their backups via BACKUP .. INTO {subdir} IN {collectionURI}. This is no longer supported. Users can only create a full backup via BACKUP … INTO {collectionURI} or an incremental backup on the latest full backup in their collection via BACKUP … INTO LATEST IN {collectionURI}. This deprecation also removes the need to address a bug in SHOW BACKUPS IN, which cannot display user-defined subdirectories. #79447
The debug unsafe-remove-dead-replicas CLI command has been deprecated, and will be removed in v23.1. Users should use the new debug recover set of commands instead. #88765