What's New in v20.1.0

May 12, 2020

With the release of CockroachDB v20.1, we've made a variety of productivity, performance, and security improvements. Check out a comprehensive summary of the most significant user-facing changes and then upgrade to CockroachDB v20.1. You can also read more about these changes in the v20.1 blog post or watch our 20.1 release demo and overview.

Warning:

A denial-of-service (DoS) vulnerability is present in CockroachDB v20.1.0 - v20.1.10 due to a bug in protobuf. This is resolved in CockroachDB v20.1.11 and later releases. When upgrading is not an option, users should audit their network configuration to verify that the CockroachDB HTTP port is not available to untrusted clients. We recommend blocking the HTTP port behind a firewall.

For more information, including other affected versions, see Technical Advisory 58932.

Warning:

Cockroach Labs has discovered a bug relating to incremental backups, for CockroachDB v20.1.0 - v20.1.13. If a backup coincides with an in-progress index creation (backfill), RESTORE, or IMPORT, it is possible that a subsequent incremental backup will not include all of the indexed, restored or imported data.

Users are advised to upgrade to v20.1.15 or later, which includes resolutions.

For more information, including other affected versions, see Technical Advisory 63162.

Get future release notes emailed to you:

Downloads

Docker image

icon/buttons/copy 
$ docker pull cockroachdb/cockroach:v20.1.0

Summary

This section summarizes the most significant user-facing changes in v20.1.0. For a complete list of features and changes, including bug fixes and performance improvements, see the release notes for previous testing releases.

CockroachCloud

  • You can now use the code CRDB30 for a free 30-day trial of CockroachCloud.

  • CockroachCloud pricing is now available on our website.

  • VPC peering is now supported for CockroachCloud clusters running on GCP. Contact us to set up a VPC peering-enabled CockroachCloud cluster.

Core features

These features are freely available in the core version and do not require an enterprise license.

Area Feature Description
SQL Online Primary Key Changes The new ALTER TABLE ... ALTER PRIMARY KEY statement lets you change a table’s primary key with no interruption to data access. The old primary key is converted to a UNIQUE secondary index to help optimize the performance of queries that still filter on the old key. However, if this conversion is not desired, you can drop and add a primary key constraint instead.
SQL Schema Change Controls Online schema changes can now be paused, resumed, and cancelled via PAUSE JOB, RESUME JOB, and CANCEL JOB.
SQL Foreign Key Improvements CockroachDB now supports multiple foreign key constraints on a single column. Also, it's now possible to drop the index on foreign key columns, or on the referenced columns, if another index exists on the same columns and fulfills indexing requirements.
SQL SELECT FOR UPDATE The new SELECT FOR UPDATE statement lets you order transactions by controlling concurrent access to one or more rows of a table. It works by locking the rows returned by a selection query, such that other transactions attempting to SELECT the same data and then UPDATE the results of that selection are forced to wait for the transaction that locked the rows to finish. This prevents transaction retries that would otherwise occur and, thus, leads to increased throughput and decreased tail latency for contended operations.
SQL Nested Transactions and Savepoints CockroachDB now supports the nesting of transactions using savepoints. These nested transactions, also known as sub-transactions, can be rolled back without discarding the state of the entire surrounding transaction. This can be useful in applications that abstract database access using an application development framework or ORM. Different components of the application can operate on different sub-transactions without having to know about each others' internal operations, while trusting that the database will maintain isolation between sub-transactions and preserve data integrity.
SQL Hash-Sharded Indexes For tables indexed on sequential keys, CockroachDB nows offers hash-sharded indexes to distribute sequential traffic uniformly across ranges, eliminating single-range hotspots and improving write performance on sequentially-keyed indexes at a small cost to read performance. This feature is currently experimental.
SQL Slow Query Log You can now enable a slow query log to record SQL queries whose service latency exceeds a specified threshold.
SQL EXPLAIN Improvements The new EXPLAIN ANALYZE (DEBUG) option executes a query and generates a link to a ZIP file that contains the physical query plan, execution statistics, statement tracing, and other information about the query. Also, the (DISTSQL, TYPES) option on EXPLAIN and EXPLAIN ANALYZE include the data types of the input columns in the physical plan.
SQL Recursive Common Table Expressions CockroachDB now supports common table expressions that contain subqueries that refer to their own output.
SQL TIMETZ Data Type CockroachDB now supports the TIMETZ variant of the TIME data type for SQL standard compliance and increased compatibility with ORMS.
SQL Precision in Time Values CockroachDB now supports precision levels from 0 (seconds) to 6 (microseconds) for TIME/TIMETZ and INTERVAL values. Precision in time values specifies the number of fractional digits retained in the seconds field.
SQL Vectorized Execution Improvements Vectorized execution now supports hash joins, merge joins, most window functions, as well as the TIMESTAMPTZ data type in addition to several other previously supported data types.
SQL Column Families in Secondary Indexes Secondary indexes now respect the column family definitions applied to tables. When you define a secondary index, CockroachDB breaks the secondary index key-value pairs into column families, according to the family and stored column configurations.
SQL Temporary Tables CockroachDB now supports session-scoped temporary tables, views, and sequences. Unlike persistent objects, temp objects can only be accessed from the session in which they were created, and they are dropped at the end of the session. This feature is currently experimental.
Dev Tools Expanded ORM Support CockroachDB now supports additional Postgres-compatible ORMs, including Django, PonyORM, and pewee for Python developers, and jOOQ for Java developers.
I/O Bulk Import Improvements The IMPORT and IMPORT INTO statements now support bulk importing from Avro files. This makes it easier to migrate from systems like Spanner that export data in the Avro format.

Also, the new cockroach nodelocal upload command makes it easier to upload a file to a node's external IO directory for import from the node rather than from cloud storage.

Finally, paused imports, when resumed, now continue from their internally recorded progress instead of starting over.
Security RBAC Changes All role-based access control (RBAC) features (CREATE ROLE, ALTER ROLE, DROP ROLE, GRANT ROLE, REVOKE ROLE) are now covered by the BSL license and available to non-enterprise users.
Security Various Improvements Several security features have been added to CockroachDB v20.1, including the ability to customize the mapping between TLS certificates and principals, to name user/roles with periods so as to reflect the structure of internet domain names, to allow or disallow users from authenticating, and to allow or disallow users from creating, altering, and dropping other users.

Also, CockroachDB's support for the PostgreSQL Host-Based Authentication (HBA) configuration language, which enables sites to customize the principal/client address/authentication method matrix, has been extended and unified.
CLI Demo Cluster Improvements Several features have been added to the cockroach demo command, including the ability to start a demo cluster in secure mode using TLS certificates to encrypt network communication (via the --insecure=false flag), to return the client connection URLs for all nodes in a demo cluster (via the \demo ls shell command), to shut down/restart/decommission/recommission individual nodes in a multi-node demo cluster (via the `\demo shutdown
UI Various Improvements The Network Latency page of the Admin UI is now easier to access and has been redesigned to help you understand the round-trip latencies between all nodes in your cluster.

Also, the Statement Details page now allows you to write information about a SQL statement to a diagnostics bundle that you can download. This bundle consists of a JSON file that contains a distributed trace of the SQL statement, a physical query plan, execution statistics, and other information about the query.
Internals Various Improvements CockroachDB's storage layer now uses protected timestamps to ensure the safety of historical data while also enabling shorter GC TTLs. A shorter GC TTL means that fewer previous MVCC values are kept around. This can help lower query execution costs for workloads which update rows frequently throughout the day, since the SQL layer has to scan over previous MVCC values to find the current value of a row.

Also, Cockroach's transaction layer now uses a concurrency manager to sequence incoming requests and provide isolation between the transactions that issued those requests that intend to perform conflicting operations.

Enterprise features

These features require an enterprise license. Register for a 30-day trial license here, or consider testing enterprise features locally using the cockroach demo CLI command, which starts an in-memory CockroachDB cluster with a temporary enterprise license pre-loaded. CockroachCloud clusters also include all enterprise features.

Area Feature Description
Recovery Full-cluster backup and restore CockroachDB's BACKUP feature now supports backing up an entire cluster's data, including all configuration and system information such as user privileges, zone configurations, and cluster settings. In rare disaster recovery situations, CockroachDB's RESTORE feature can now restore a cluster backup to a new cluster. Restoring a cluster backup to an existing cluster is not supported.
Recovery Encrypted backups CockroachDB now supports using an encryption passphrase to encrypt data in Enterprise BACKUP files and to decrypt the data upon RESTORE.
SQL Improved follower reads Follower reads are now available for AS OF SYSTEM TIME queries at least 4.8 seconds in the past, a much shorter window than the previous 48 seconds.

Backward-incompatible changes

Before upgrading to CockroachDB v20.1, be sure to review the following backward-incompatible changes and adjust your application as necessary.

  • The extract() built-in function with sub-second arguments (millisecond, microsecond) is now Postgres-compatible and returns the total number of seconds in addition to sub-seconds instead of returning only sub-seconds.

  • Casting intervals to integers and floats is now Postgres-compatible and values a year at 365.25 days in seconds instead of 365 days.

  • The combination of the CHANGEFEED options format=experimental_avro, envelope=key_only, and updated is now rejected. This is because the use of key_only prevents any rows with updated fields from being emitted, which renders the updated option meaningless.

  • The cockroach init CLI command now waits for server readiness and thus no longer fails when a mistaken server address is provided.

  • The cockroach user CLI command has been removed. It was previously deprecated in CockroachDB v19.2. Note that a v19.2 client (supporting cockroach user) can still operate user accounts in a v20.1 server.

  • CockroachDB now creates files without read permissions for the "others" group. Sites that automate file management (e.g., log collection) using multiple user accounts now must ensure that the CockroachDB server and the management tools running on the same system are part of a shared unix group.

  • The GRANT and REVOKE statements now require that the requesting user already have the target privileges themselves. For example, GRANT SELECT ON t TO foo requires that the requesting user already have the SELECT privilege on t.

  • During an upgrade to v20.1, ongoing schema changes will stop making progress, and it will not be possible to manipulate them via PAUSE JOB/RESUME JOB/CANCEL JOB statements. Once the upgrade has been finalized, these schema changes will run to completion.

  • During an upgrade to v20.1, new schema changes will be blocked and return an error, with the exception of CREATE TABLE statements without foreign key references and no-op schema change statements that use IF NOT EXISTS. Also, ongoing schema changes started will stop making progress, and it will not be possible to manipulate them via PAUSE JOB/RESUME JOB/CANCEL JOB statements. Once the upgrade has been finalized, ongoing schema changes will run to completion and new schema changes will be allowed.

Known limitations

For information about new and unresolved limitations in CockroachDB v20.1, with suggested workarounds where applicable, see Known Limitations.

Education

Area Topic Description
Training Videos Lessons on YouTube Added two Cockroach University playlists to YouTube, one with the entire set of video lessons from "Getting Started with CockroachDB", and the other featuring the first batch of video lessons from the upcoming course, "CockroachDB for Python Developers".
Docs Developer Guide Added guidance on common tasks when building apps on CockroachDB, such as installing Postgres clients; connecting to the database; effectively inserting, querying, updating, and deleting; handling errors; and making queries fast. For convenience, much of the guidance is offered across various popular languages (Java, Python, Go) in addition to straight SQL.
Docs "Hello World" Repos Added several language-specific GitHub repos with the simple starter applications featured in our "Hello World" tutorials.
Docs Multi-Region Sample App and Tutorial Added a full-stack, multi-region sample application (GitHub repo) with an accompanying tutorial on building a multi-region application on a multi-region CockroachCloud cluster. Also added a video demonstration as a YouTube playlist.
Docs Streaming Changefeeds to Snowflake Tutorial Added an end-to-end tutorial on how to use an Enterprise changefeed to stream row-level changes from CockroachCloud to Snowflake, an online analytical processing (OLAP) database.
Docs Improved Backup/Restore Docs Updated the backup/restore docs to better separate broadly applicable guidance and best practices from more advanced topics.
Docs Release Support Policy Added a page explaining Cockroach Labs' policy for supporting major releases of CockroachDB, including the phases of support that each major release moves through, the currently supported releases, and an explanation of the naming scheme used for CockroachDB.

Yes No

Yes No

Product

Resources

Learn

Support Channels

Company

Get developer news