Known Limitations in CockroachDB v20.2

On this page Carat arrow pointing down
CockroachDB v20.2 is no longer supported as of May 10, 2022. For more details, refer to the Release Support Policy.

This page describes newly identified limitations in the CockroachDB v20.2.19 release as well as unresolved limitations identified in earlier releases.

New limitations

Partitioning on ENUM values

Partitions cannot be created on columns of type ENUM.

Tracking GitHub Issue

Multiple arbiter indexes for INSERT ON CONFLICT DO UPDATE

CockroachDB does not currently support multiple arbiter indexes for INSERT ON CONFLICT DO UPDATE, and will return an error if there are multiple unique or exclusion constraints matching the ON CONFLICT DO UPDATE specification.

Tracking GitHub Issue

IMPORT into a table with partial indexes

CockroachDB does not currently support IMPORTs into tables with partial indexes.

To work around this limitation:

  1. Drop any partial indexes defined on the table.
  2. Perform the IMPORT.
  3. Recreate the partial indexes.

If you are performing an IMPORT of a PGDUMP with partial indexes:

  1. Drop the partial indexes on the PostgreSQL server.
  2. Recreate the PGDUMP.
  4. Add partial indexes on the CockroachDB server.

Tracking GitHub Issue

Historical reads on restored objects

An object's historical data is not preserved upon RESTORE. This means that if an AS OF SYSTEM TIME query is issued on a restored object, the query will fail or the response will be incorrect because there is no historical data to query.

Tracking GitHub Issue

Spatial support limitations

CockroachDB supports efficiently storing and querying spatial data, with the following limitations:

SET does not ROLLBACK in a transaction

SET does not properly apply ROLLBACK within a transaction. For example, in the following transaction, showing the TIME ZONE variable does not return 2 as expected after the rollback:


Tracking GitHub Issue

Unresolved limitations

Optimizer stale statistics deletion when columns are dropped

  • When a column is dropped from a multi-column index, the optimizer will not collect new statistics for the deleted column. However, the optimizer never deletes the old multi-column statistics. This can cause a buildup of statistics in system.table_statistics leading the optimizer to use stale statistics, which could result in sub-optimal plans. To workaround this issue and avoid these scenarios, explicitly delete those statistics from the system.table_statistics table.

    Tracking GitHub Issue

  • Single-column statistics are not deleted when columns are dropped, which could cause minor performance issues.

    Tracking GitHub Issue

Automatic statistics refresher may not refresh after upgrade

The automatic statistics refresher automatically checks whether it needs to refresh statistics for every table in the database upon startup of each node in the cluster. If statistics for a table have not been refreshed in a while, this will trigger collection of statistics for that table. If statistics have been refreshed recently, it will not force a refresh. As a result, the automatic statistics refresher does not necessarily perform a refresh of statistics after an upgrade. This could cause a problem, for example, if the upgrade moves from a version without histograms to a version with histograms. To refresh statistics manually, use CREATE STATISTICS.

Tracking GitHub Issue

Differences in syntax and behavior between CockroachDB and PostgreSQL

CockroachDB supports the PostgreSQL wire protocol and the majority of its syntax. However, CockroachDB does not support some of the PostgreSQL features or behaves differently from PostgreSQL because not all features can be easily implemented in a distributed system.

For a list of known differences in syntax and behavior between CockroachDB and PostgreSQL, see Features that differ from PostgreSQL.

Collation names that include upper-case or hyphens may cause errors

Using a collation name with upper-case letters or hyphens may result in errors.

For example, the following SQL will result in an error:

> CREATE TABLE nocase_strings (s STRING COLLATE "en-US-u-ks-level2");
> INSERT INTO nocase_strings VALUES ('Aaa' COLLATE "en-US-u-ks-level2"), ('Bbb' COLLATE "en-US-u-ks-level2");
> SELECT s FROM nocase_strings WHERE s = ('bbb' COLLATE "en-US-u-ks-level2");
ERROR: internal error: "$0" = 'bbb' COLLATE en_us_u_ks_level2: unsupported comparison operator: <collatedstring{en-US-u-ks-level2}> = <collatedstring{en_us_u_ks_level2}>

As a workaround, only use collation names that have lower-case letters and underscores.

Tracking GitHub issue

Subqueries in SET statements

It is not currently possible to use a subquery in a SET or SET CLUSTER SETTING statement. For example:

> SET application_name = (SELECT 'a' || 'b');
ERROR: invalid value for parameter "application_name": "(SELECT 'a' || 'b')"
DETAIL: subqueries are not allowed in SET

Tracking GitHub Issue

Enterprise BACKUP does not capture database/table/column comments

The COMMENT ON statement associates comments to databases, tables, or columns. However, the internal table (system.comments) in which these comments are stored is not captured by a BACKUP of a table or database.

As a workaround, take a cluster backup instead, as the system.comments table is included in cluster backups.

Tracking GitHub Issue

Cold starts of large clusters may require manual intervention


Resolved as of v20.2.9. See #64567.

If a cluster contains a large amount of data (>500GiB / node), and all nodes are stopped and then started at the same time, clusters can enter a state where they're unable to startup without manual intervention. In this state, logs fill up rapidly with messages like refusing gossip from node x; forwarding to node y, and data and metrics may become inaccessible.

To exit this state, you should:

  1. Stop all nodes.
  2. Set the following environment variables: COCKROACH_SCAN_INTERVAL=60m, and COCKROACH_SCAN_MIN_IDLE_TIME=1s.
  3. Restart the cluster.

Once restarted, monitor the Replica Quiescence graph on the Replication Dashboard. When >90% of the replicas have become quiescent, conduct a rolling restart and remove the environment variables. Make sure that under-replicated ranges do not increase between restarts.

Tracking GitHub Issue

Requests to restarted node in need of snapshots may hang


Resolved as of v20.2.4. See #57789.

When a node is offline, the Raft logs for the ranges on the node get truncated. When the node comes back online, it therefore often needs Raft snapshots to get many of its ranges back up-to-date. While in this state, requests to a range will hang until its snapshot has been applied, which can take a long time.

To work around this limitation, you can adjust the kv.snapshot_recovery.max_rate cluster setting to temporarily relax the throughput rate limiting applied to snapshots. For example, changing the rate limiting from the default 8 MB/s, at which 1 GB of snapshots takes at least 2 minutes, to 64 MB/s can result in an 8x speedup in snapshot transfers and, therefore, a much shorter interruption of requests to an impacted node:

> SET CLUSTER SETTING kv.snapshot_recovery.max_rate = '64mb';

Before increasing this value, however, verify that you will not end up saturating your network interfaces, and once the problem has resolved, be sure to reset to the original value.

Tracking GitHub Issue

Slow (or hung) backups and queries due to write intent buildup

Due to known bugs, transactions do not always clean up their write intents (newly written values) on commit or rollback. Garbage collection is also rather slow to react to them. This can cause the amount of unresolved write intents to build up over time. While this isn't necessarily a problem in itself, some operations do not handle large amounts of intents well. In particular, backups and queries that touch large numbers of values may become very slow and appear to hang.

To verify that intents may be causing an issue, open the Custom Chart debug page in the DB Console, and create a chart for the intentcount metric. This will show the number of intents present over time. The following query can also be used to get intent counts by range:

> SELECT * FROM (SELECT start_pretty, end_pretty, range_id, crdb_internal.range_stats(start_key)->'intent_count' AS intent_count FROM crdb_internal.ranges_no_leases) WHERE intent_count != '0';

To force cleanup of intents, either of the following methods can be used:

  • Do a high-priority scan of the table, which will resolve intents as it runs. Note that this may abort any conflicting transactions that are currently running. If the table has indexes, these can be cleaned by changing <table> into <table>@<index>. Numeric table and/or index identifiers (e.g., as output by the intent query above) can be used instead of names by placing them in brackets: [<table-id>] or [<table-id>]@[<index-id>].

  • Manually enqueue the range for garbage collection. In the DB Console, open the Advanced Debug page, scroll down to Tracing and Profiling Endpoints, and click Run a range through an internal queue. Then select Queue: gc, enter the range ID as output by the intent query above, check SkipShouldQueue, and click Submit. The operation will succeed on the leaseholder node and error on the others; this is expected.

The progress and effect of the cleanup can be monitored via the intent count statistics described above.

Tracking GitHub Issue

Location-based time zone names

Certain features of CockroachDB require time zone data, for example, to support using location-based names as time zone identifiers. When starting a CockroachDB node on a machine missing time zone data, the node will not start.

To resolve this issue on Linux, install the tzdata library (sometimes called tz or zoneinfo).

To resolve this issue on Windows, download Go's official and set the ZONEINFO environment variable to point to the zip file. For step-by-step guidance on setting environment variables on Windows, see this external article.

Make sure to do this across all nodes in the cluster and to keep this time zone data up-to-date.

Tracking GitHub Issue

Change data capture

Change data capture (CDC) provides efficient, distributed, row-level change feeds into Apache Kafka for downstream processing such as reporting, caching, or full-text indexing.

DB Console may become inaccessible for secure clusters

Accessing the DB Console for a secure cluster now requires login information (i.e., username and password). This login information is stored in a system table that is replicated like other data in the cluster. If a majority of the nodes with the replicas of the system table data go down, users will be locked out of the DB Console.


AS OF SYSTEM TIME can only be used in a top-level SELECT statement. That is, we do not support statements like INSERT INTO t SELECT * FROM t2 AS OF SYSTEM TIME <time> or two subselects in the same statement with differing AS OF SYSTEM TIME arguments.

Tracking GitHub Issue

Large index keys can impair performance

The use of tables with very large primary or secondary index keys (>32KB) can result in excessive memory usage. Specifically, if the primary or secondary index key is larger than 32KB the default indexing scheme for storage engine SSTables breaks down and causes the index to be excessively large. The index is pinned in memory by default for performance.

To work around this issue, we recommend limiting the size of primary and secondary keys to 4KB, which you must account for manually. Note that most columns are 8B (exceptions being STRING and JSON), which still allows for very complex key structures.

Tracking GitHub Issue

DB Console: Statements page latency reports

The Statements page does not correctly report "mean latency" or "latency by phase" for statements that result in schema changes or other background jobs.

Tracking GitHub Issue

Using LIKE...ESCAPE in WHERE and HAVING constraints

CockroachDB tries to optimize most comparisons operators in WHERE and HAVING clauses into constraints on SQL indexes by only accessing selected rows. This is done for LIKE clauses when a common prefix for all selected rows can be determined in the search pattern (e.g., ... LIKE 'Joe%'). However, this optimization is not yet available if the ESCAPE keyword is also used.

Tracking GitHub Issue

Using SQLAlchemy with CockroachDB

Users of the SQLAlchemy adapter provided by Cockroach Labs must upgrade the adapter to the latest release before upgrading to CockroachDB v20.2.

DB Console: CPU percentage calculation

For multi-core systems, the user CPU percent can be greater than 100%. Full utilization of one core is considered as 100% CPU usage. If you have n cores, then the user CPU percent can range from 0% (indicating an idle system) to (n*100)% (indicating full utilization).

Tracking GitHub Issue

DB Console: CPU count in containerized environments

When CockroachDB is run in a containerized environment (e.g., Kubernetes), the DB Console does not detect CPU limits applied to a container. Instead, the UI displays the actual number of CPUs provisioned on a VM.

Tracking GitHub Issue

TRUNCATE does not behave like DELETE

TRUNCATE is not a DML statement, but instead works as a DDL statement. Its limitations are the same as other DDL statements, which are outlined in Online Schema Changes: Limitations

Tracking GitHub Issue

Ordering tables by JSONB/JSON-typed columns

CockroachDB does not currently key-encode JSON values. As a result, tables cannot be ordered by JSONB/JSON-typed columns.

Tracking GitHub Issue

Current sequence value not checked when updating min/max value

Altering the minimum or maximum value of a series does not check the current value of a series. This means that it is possible to silently set the maximum to a value less than, or a minimum value greater than, the current value.

Tracking GitHub Issue

Using default_int_size session variable in batch of statements

When setting the default_int_size session variable in a batch of statements such as SET default_int_size='int4'; SELECT 1::IN, the default_int_size variable will not take affect until the next statement. This happens because statement parsing takes place asynchronously from statement execution.

As a workaround, set default_int_size via your database driver, or ensure that SET default_int_size is in its own statement.

Tracking GitHub Issue

COPY FROM statements are not supported in the CockroachDB SQL shell

The built-in SQL shell provided with CockroachDB (cockroach sql / cockroach demo) does not currently support importing data with the COPY statement.

To load data into CockroachDB, we recommend that you use an IMPORT. If you must use a COPY statement, you can issue the statement from the psql client command provided with PostgreSQL, or from another third-party client.

Tracking GitHub Issue

COPY syntax not supported by CockroachDB

CockroachDB does not yet support the following COPY syntax:

Import with a high amount of disk contention

IMPORT can sometimes fail with a "context canceled" error, or can restart itself many times without ever finishing. If this is happening, it is likely due to a high amount of disk contention. This can be mitigated by setting the kv.bulk_io_write.max_rate cluster setting to a value below your max disk write speed. For example, to set it to 10MB/s, execute:

> SET CLUSTER SETTING kv.bulk_io_write.max_rate = '10MB';

Placeholders in PARTITION BY

When defining a table partition, either during table creation or table alteration, it is not possible to use placeholders in the PARTITION BY clause.

Tracking GitHub Issue

Adding a column with sequence-based DEFAULT values

It is currently not possible to add a column to a table when the column uses a sequence as the DEFAULT value, for example:

> INSERT INTO t(x) VALUES (1), (2), (3);
ERROR: nextval(): unimplemented: cannot evaluate scalar expressions containing sequence operations in this context

Tracking GitHub Issue

Available capacity metric in the DB Console

If you are testing your deployment locally with multiple CockroachDB nodes running on a single machine (this is not recommended in production), you must explicitly set the store size per node in order to display the correct capacity. Otherwise, the machine's actual disk capacity will be counted as a separate store for each node, thus inflating the computed capacity.

Schema changes within transactions

Within a single transaction:

  • DDL statements cannot be mixed with DML statements. As a workaround, you can split the statements into separate transactions. For more details, see examples of unsupported statements.
  • As of version v2.1, you can run schema changes inside the same transaction as a CREATE TABLE statement. For more information, see this example.
  • A CREATE TABLE statement containing FOREIGN KEY or INTERLEAVE clauses cannot be followed by statements that reference the new table.
  • Database, schema, table, and user-defined type names cannot be reused. For example, you cannot drop a table named a and then create (or rename) a different table with the name a. Similarly, you cannot rename a database named a to b and then create (or rename) a different database with the name a. As a workaround, split RENAME TO, DROP, and CREATE statements that reuse object names into separate transactions.
  • Schema change DDL statements inside a multi-statement transaction can fail while other statements succeed.
  • As of v19.1, some schema changes can be used in combination in a single ALTER TABLE statement. For a list of commands that can be combined, see ALTER TABLE. For a demonstration, see Add and rename columns atomically.
  • DROP COLUMN can result in data loss if one of the other schema changes in the transaction fails or is canceled. To work around this, move the DROP COLUMN statement to its own explicit transaction or run it in a single statement outside the existing transaction.

If a schema change within a transaction fails, manual intervention may be needed to determine which has failed. After determining which schema change(s) failed, you can then retry the schema changes.

Schema change DDL statements inside a multi-statement transaction can fail while other statements succeed

Schema change DDL statements that run inside a multi-statement transaction with non-DDL statements can fail at COMMIT time, even if other statements in the transaction succeed. This leaves such transactions in a "partially committed, partially aborted" state that may require manual intervention to determine whether the DDL statements succeeded.

If such a failure occurs, CockroachDB will emit a new CockroachDB-specific error code, XXA00, and the following error message:

transaction committed but schema change aborted with error: <description of error>
HINT: Some of the non-DDL statements may have committed successfully, but some of the DDL statement(s) failed.
Manual inspection may be required to determine the actual state of the database.

This limitation exists in versions of CockroachDB prior to 19.2. In these older versions, CockroachDB returned the Postgres error code 40003, "statement completion unknown".


If you must execute schema change DDL statements inside a multi-statement transaction, we strongly recommend checking for this error code and handling it appropriately every time you execute such transactions.

This error will occur in various scenarios, including but not limited to:

  • Creating a unique index fails because values aren't unique.
  • The evaluation of a computed value fails.
  • Adding a constraint (or a column with a constraint) fails because the constraint is violated for the default/computed values in the column.

To see an example of this error, start by creating the following table.

INSERT INTO T(x) VALUES (1), (2), (3);

Then, enter the following multi-statement transaction, which will trigger the error.

pq: transaction committed but schema change aborted with error: (23505): duplicate key value (x)=(3) violates unique constraint "unique_x"
HINT: Some of the non-DDL statements may have committed successfully, but some of the DDL statement(s) failed.
Manual inspection may be required to determine the actual state of the database.

In this example, the INSERT statement committed, but the ALTER TABLE statement adding a UNIQUE constraint failed. We can verify this by looking at the data in table t and seeing that the additional non-unique value 3 was successfully inserted.

(4 rows)

Schema changes between executions of prepared statements

When the schema of a table targeted by a prepared statement changes before the prepared statement is executed, CockroachDB allows the prepared statement to return results based on the changed table schema, for example:

> PREPARE prep1 AS SELECT * FROM users;
> INSERT INTO users VALUES (1, 'Max Roach');
> EXECUTE prep1;
  id |   name
   1 | Max Roach
(1 row)

It's therefore recommended to not use SELECT * in queries that will be repeated, via prepared statements or otherwise.

Also, a prepared INSERT, UPSERT, or DELETE statement acts inconsistently when the schema of the table being written to is changed before the prepared statement is executed:

  • If the number of columns has increased, the prepared statement returns an error but nonetheless writes the data.
  • If the number of columns remains the same but the types have changed, the prepared statement writes the data and does not return an error.

Size limits on statement input from SQL clients

CockroachDB imposes a hard limit of 16MiB on the data input for a single statement passed to CockroachDB from a client (including the SQL shell). We do not recommend attempting to execute statements from clients with large input.

Using \| to perform a large input in the SQL shell

In the built-in SQL shell, using the \| operator to perform a large number of inputs from a file can cause the server to close the connection. This is because \| sends the entire file as a single query to the server, which can exceed the upper bound on the size of a packet the server can accept from any client (16MB).

As a workaround, execute the file from the command line with cat data.sql | cockroach sql instead of from within the interactive shell.

New values generated by DEFAULT expressions during ALTER TABLE ADD COLUMN

When executing an ALTER TABLE ADD COLUMN statement with a DEFAULT expression, new values generated:

  • use the default search path regardless of the search path configured in the current session via SET SEARCH_PATH.
  • use the UTC time zone regardless of the time zone configured in the current session via SET TIME ZONE.
  • have no default database regardless of the default database configured in the current session via SET DATABASE, so you must specify the database of any tables they reference.
  • use the transaction timestamp for the statement_timestamp() function regardless of the time at which the ALTER statement was issued.

Load-based lease rebalancing in uneven latency deployments

When nodes are started with the --locality flag, CockroachDB attempts to place the replica lease holder (the replica that client requests are forwarded to) on the node closest to the source of the request. This means as client requests move geographically, so too does the replica lease holder.

However, you might see increased latency caused by a consistently high rate of lease transfers between datacenters in the following case:

  • Your cluster runs in datacenters which are very different distances away from each other.
  • Each node was started with a single tier of --locality, e.g., --locality=datacenter=a.
  • Most client requests get sent to a single datacenter because that's where all your application traffic is.

To detect if this is happening, open the DB Console, select the Queues dashboard, hover over the Replication Queue graph, and check the Leases Transferred / second data point. If the value is consistently larger than 0, you should consider stopping and restarting each node with additional tiers of locality to improve request latency.

For example, let's say that latency is 10ms from nodes in datacenter A to nodes in datacenter B but is 100ms from nodes in datacenter A to nodes in datacenter C. To ensure A's and B's relative proximity is factored into lease holder rebalancing, you could restart the nodes in datacenter A and B with a common region, --locality=region=foo,datacenter=a and --locality=region=foo,datacenter=b, while restarting nodes in datacenter C with a different region, --locality=region=bar,datacenter=c.

Overload resolution for collated strings

Many string operations are not properly overloaded for collated strings, for example:

> SELECT 'string1' || 'string2';
(1 row)
> SELECT ('string1' collate en) || ('string2' collate en);
pq: unsupported binary operator: <collatedstring{en}> || <collatedstring{en}>

Tracking GitHub Issue

Max size of a single column family

When creating or updating a row, if the combined size of all values in a single column family exceeds the max range size (512 MiB by default) for the table, the operation may fail, or cluster performance may suffer.

As a workaround, you can either manually split a table's columns into multiple column families, or you can create a table-specific zone configuration with an increased max range size.

Simultaneous client connections and running queries on a single node

When a node has both a high number of client connections and running queries, the node may crash due to memory exhaustion. This is due to CockroachDB not accurately limiting the number of clients and queries based on the amount of available RAM on the node.

To prevent memory exhaustion, monitor each node's memory usage and ensure there is some margin between maximum CockroachDB memory usage and available system RAM. For more details about memory usage in CockroachDB, see this blog post.

Privileges for DELETE and UPDATE

Every DELETE or UPDATE statement constructs a SELECT statement, even when no WHERE clause is involved. As a result, the user executing DELETE or UPDATE requires both the DELETE and SELECT or UPDATE and SELECT privileges on the table.

Correlated common table expressions

CockroachDB does not support correlated common table expressions. This means that a CTE cannot refer to a variable defined outside the scope of that CTE.

For example, the following query returns an error:

> SELECT * FROM users
  WHERE id =
    (WITH rides_home AS
      (SELECT revenue FROM rides
       WHERE end_address = address)
     SELECT rider_id FROM rides_home);
ERROR: CTEs may not be correlated

This query returns an error because the WITH rides_home clause references a column (address) returned by the SELECT statement at the top level of the query, outside the rides_home CTE definition.

Tracking GitHub Issue

ROLLBACK TO SAVEPOINT in high-priority transactions containing DDL

Transactions with priority HIGH that contain DDL and ROLLBACK TO SAVEPOINT are not supported, as they could result in a deadlock. For example:

ERROR: unimplemented: cannot use ROLLBACK TO SAVEPOINT in a HIGH PRIORITY transaction containing DDL
HINT: You have attempted to use a feature that is not yet implemented.

Tracking GitHub Issue

Concurrent SQL shells overwrite each other's history

The built-in SQL shell stores its command history in a single file by default (.cockroachsql_history). When running multiple instances of the SQL shell on the same machine. Therefore, each shell's command history can get overwritten in unexpected ways.

As a workaround, set the COCKROACH_SQL_CLI_HISTORY environment variable to different values for the two different shells, for example:

$ export COCKROACH_SQL_CLI_HISTORY=.cockroachsql_history_shell_1
$ export COCKROACH_SQL_CLI_HISTORY=.cockroachsql_history_shell_2

Tracking GitHub Issue

Passwords with special characters cannot be passed in connection parameter

CockroachDB does not allow passwords with special characters to be passed as a connection parameter to cockroach commands.

Tracking GitHub Issue

CockroachDB does not test for all connection failure scenarios

CockroachDB servers rely on the network to report when a TCP connection fails. In most scenarios when a connection fails, the network immediately reports a connection failure, resulting in a Connection refused error.

However, if there is no host at the target IP address, or if a firewall rule blocks traffic to the target address and port, a TCP handshake can linger while the client network stack waits for a TCP packet in response to network requests. To work around this kind of scenario, we recommend the following:

  • When migrating a node to a new machine, keep the server listening at the previous IP address until the cluster has completed the migration.
  • Configure any active network firewalls to allow node-to-node traffic.
  • Verify that orchestration tools (e.g., Kubernetes) are configured to use the correct network connection information.

Tracking GitHub Issue

Some column-dropping schema changes do not roll back properly

Some schema changes that drop columns cannot be rolled back properly.

In some cases, the rollback will succeed, but the column data might be partially or totally missing, or stale due to the asynchronous nature of the schema change.

Tracking GitHub Issue

In other cases, the rollback will fail in such a way that will never be cleaned up properly, leaving the table descriptor in a state where no other schema changes can be run successfully.

Tracking GitHub Issue

To reduce the chance that a column drop will roll back incorrectly:

  • Perform column drops in transactions separate from other schema changes. This ensures that other schema change failures will not cause the column drop to be rolled back.

  • Drop all constraints (including unique indexes) on the column in a separate transaction, before dropping the column.

  • Drop any default values or computed expressions on a column before attempting to drop the column. This prevents conflicts between constraints and default/computed values during a column drop rollback.

If you think a rollback of a column-dropping schema change has occurred, check the jobs table. Schema changes with an error prefaced by cannot be reverted, manual cleanup may be required might require manual intervention.

Disk-spilling on joins with JSON columns

If the execution of a join query exceeds the limit set for memory-buffering operations (i.e., the value set for the sql.distsql.temp_storage.workmem cluster setting), CockroachDB will spill the intermediate results of computation to disk. If the join operation spills to disk, and at least one of the equality columns is of type JSON, CockroachDB returns the error unable to encode table key: *tree.DJSON. If the memory limit is not reached, then the query will be processed without error.

Tracking GitHub Issue

Disk-spilling not supported for unordered aggregation operations

Unordered aggregation operations do not support disk spilling, and are limited by the --max-sql-memory setting. If unordered aggregation operations exceed the amount of memory available to the SQL layer, CockroachDB will throw an error, and in some circumstances could crash.


Setting --max-sql-memory too high could result in performance problems due to increased memory consumption.

See the GitHub tracking issue for details.

GIN indexes cannot be partitioned

CockroachDB does not support partitioning GIN indexes, including spatial indexes.

Tracking GitHub Issue

GIN index scans can't be generated for some statement filters

CockroachDB cannot generate GIN index scans for statements with filters that have both JSON fetch values and containment operators. For example the following statement won't be index-accelerated:

SELECT * FROM mytable WHERE j->'a' @> '{"b": "c"}';

CockroachDB v20.1 and earlier would generate index scans for these filters, though it is not recommended as the normalization rules used to convert the filters into JSON containment expressions would sometimes produce inequivalent expressions.

The workaround is to rewrite the statement filters to avoid using both JSON fetch values and containment operators. The following statement is index-accelerated and equivalent to the non-accelerated statement above:

SELECT * FROM mytable WHERE j @> '{"a": {"b": "c"}}'

Tracking GitHub Issue

Yes No
On this page

Yes No