# Known Limitations in CockroachDB v2.1

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

## New limitations

### Adding stores to a node

After a node has initially joined a cluster, it is not possible to add additional stores to the node. Stopping the node and restarting it with additional stores causes the node to not reconnect to the cluster.

To work around this limitation, decommission the node, remove its data directory, and then run `cockroach start` to join the cluster again as a new node.

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.

The following are limitations in the v2.1 release and will be addressed in the future:

- The CockroachDB core changefeed is not ready for external testing.
- Changefeeds only work on tables with a single column family (which is the default for new tables).
- Many DDL queries (including `TRUNCATE` and `DROP TABLE`) will cause errors on a changefeed watching the affected tables. You will need to start a new changefeed.
- Changefeeds cannot be backed up or restored.
- Changefeed backoff/retry behavior during partial or intermittent sink unavailability has not been optimized; however, ordering guarantees will still hold for as long as a changefeed remains active.
- Changefeeds use a pull model, but will use a push model in the future, lowering latencies considerably.
- Changefeeds cannot be altered. To alter, cancel the changefeed and create a new one with updated settings from where it left off.
- Additional envelope options will be added, including one that displays the old and new values for the changed row.
- Additional target options will be added, including partitions and ranges of primary key rows.

### Cold starts of large clusters may require manual intervention

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, you should monitor the Replica Quiescence graph on the Replication Dashboard. When >90% of the replicas have become Quiescent, you can conduct a rolling restart and remove the environment variables. Be sure to ensure that underreplicated ranges do not increase between restarts.

Once in a stable state, the risk of this issue recurring can be mitigated by increasing your range_max_bytes to 134217728 (128MiB). We always recommend testing changes to max_range_bytes in a development environment before making changes on production.

Tracking Github Issue

### Admin UI may become inaccessible for secure clusters

Accessing the Admin UI 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 Admin UI.

### `AS OF SYSTEM TIME` in `SELECT` statements

`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 RocksDB 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

### Admin UI: 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 2.1.

### Admin UI: 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

### Admin UI: CPU count in containerized environments

When CockroachDB is run in a containerized environment (e.g., Kubernetes), the Admin UI 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

### `GROUP BY` referring to `SELECT` aliases

Applications developed for PostgreSQL that use `GROUP BY` to refer to column aliases produced in the same `SELECT` clause must be changed to use the full underlying expression instead. For example, `SELECT x+y AS z ... GROUP BY z` must be changed to `SELECT x+y AS z ... GROUP BY x+y`. Otherwise, CockroachDB will produce either a planning error or, in some cases, invalid results.

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

### Using columns in `SELECT` not listed in `GROUP BY`

Applications developed for PostgreSQL can exploit the fact that PostgreSQL allows a `SELECT` clause to name a column that is not also listed in `GROUP BY` in some cases, for example `SELECT a GROUP BY b`. This is not yet supported by CockroachDB.

To work around this limitation, and depending on expected results, the rendered columns should be either added at the end of the `GROUP BY` list (e.g., `SELECT a GROUP BY b, a`), or `DISTINCT` should also be used (e.g., `SELECT DISTINCT a GROUP BY b`).

Tracking GitHub Issue

### Cannot `DELETE` multiple rows with self-referencing FKs

Because CockroachDB checks foreign keys eagerly (i.e., per row), it cannot trivially delete multiple rows from a table with a self-referencing foreign key.

To successfully delete multiple rows with self-referencing foreign keys, you need to ensure they're deleted in an order that doesn't violate the foreign key constraint.

Tracking GitHub Issue

### `DISTINCT` operations cannot operate over JSON values

CockroachDB does not currently key-encode JSON values, which prevents `DISTINCT` filters from working on them.

As a workaround, you can return the JSON field's values to a `string` using the `->>` operator, e.g., `SELECT DISTINCT col->>'field'...`.

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 common table expressions in `VALUES` and `UNION` clauses

When the cost-based optimizer is disabled (which is the default), or when it does not support a query, a common table expression defined outside of a `VALUES` or `UNION`clause will not be available inside it. For example `...WITH a AS (...) SELECT ... FROM (VALUES(SELECT * FROM a))`.

This limitation will be lifted when the cost-based optimizer covers all queries. Until then applications can work around this limitation by including the entire CTE query in the place where it is used.

Tracking GitHub Issue

### Conversion of integers to date/time values

CockroachDB supports an experimental extension to the SQL standard where an integer value can be converted to a `DATE`/`TIME`/`TIMESTAMP` value, taking the number as a number of seconds since the Unix epoch.

This conversion is currently only well defined for a small range of integers, i.e., large absolute values are not properly converted. For example, `(-9223372036854775808):::int64::date` converts to `1970-01-01 00:00:00+00:00`.

Tracking GitHub Issue

### Importing data using the PostgreSQL COPY protocol

Currently, the built-in SQL shell provided with CockroachDB (`cockroach sql` / `cockroach demo`) does not support importing data using the `COPY` statement. Users can use the `psql` client command provided with PostgreSQL to load this data into CockroachDB instead.

Tracking GitHub Issue

### Importing an interleaved table from a `cockroach dump` output

After using `cockroach dump` to dump the schema and data of an interleaved table, the output must be edited before it can be imported via `IMPORT`. See #35462 for the workaround and more details.

## Unresolved limitations

### Location-based time zone names on Windows

Certain features of CockroachDB require time zone data, for example, to support using location-based names as time zone identifiers. On most distributions, it is therefore required to install and keep up-to-date the `tzdata` library. However, on Windows, even with this library installed, location-based time zone names may not resolve.

To work around this limitation, install the Go toolchain on the Windows machines running CockroachDB nodes. In this case, the CockroachDB nodes will use the timezone data from that toolchain.

Tracking GitHub Issue

### Database and table renames are not transactional

Database and table renames using `RENAME DATABASE` and `RENAME TABLE` are not transactional.

Specifically, when run inside a `BEGIN` ... `COMMIT` block, it’s possible for a rename to be half-done - not persisted in storage, but visible to other nodes or other transactions. For more information, see Table renaming considerations. For an issue tracking this limitation, see cockroach#12123.

### Changes to the default replication zone are not applied to existing replication zones

Changes to the `.default` cluster-wide replication zone are not automatically applied to existing replication zones, including pre-configured zones for important system ranges that must remain available for the cluster as a whole to remain available. The zones for these system ranges have an initial replication factor of 5 to make them more resilient to node failure. However, if you increase the `.default` zone's replication factor above 5, consider increasing the replication factor for important system ranges as well.

### Silent validation error with `DECIMAL` values

Under the following conditions, the value received by CockroachDB will be different than that sent by the client and may cause incorrect data to be inserted or read from the database, without a visible error message:

1. A query uses placeholders (e.g., `$1`) to pass values to the server.
2. A value of type `DECIMAL` is passed.
3. The decimal value is encoded using the binary format.

Most client drivers and frameworks use the text format to pass placeholder values and are thus unaffected by this limitation. However, we know that the Ecto framework for Elixir is affected, and others may be as well. If in doubt, use SQL statement logging to control how CockroachDB receives decimal values from your client.

### 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';

### Referring to a CTE by name more than once

It is currently not possible to refer to a common table expression by name more than once.

For example, the following query is invalid because the CTE `a` is referred to twice:

    > WITH a AS (VALUES (1), (2), (3))
      SELECT * FROM a, a;

Tracking GitHub Issue

### Assigning latitude/longitude for the Node Map

You cannot assign latitude/longitude coordinates to localities if the components of your localities have the same name. For example, consider the following partial configuration:

Node | Region | Datacenter
--- | --- | ---
Node1 | us-east | datacenter-1
Node2 | us-west | datacenter-1

In this case, if you try to set the latitude/longitude coordinates to the datacenter level of the localities, you will get the "primary key exists" error and the Node Map will not be displayed. You can, however, set the latitude/longitude coordinates to the region components of the localities, and the Node Map will be displayed.

### 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.

### 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:

    > CREATE TABLE t (x INT);

    > INSERT INTO t(x) VALUES (1), (2), (3);

    > CREATE SEQUENCE s;

    > ALTER TABLE t ADD COLUMN y INT DEFAULT nextval('s');

    ERROR: nextval(): unimplemented: cannot evaluate scalar expressions containing sequence operations in this context
    SQLSTATE: 0A000

Tracking GitHub Issue

### Available capacity metric in the Admin UI

If you are running multiple nodes on a single machine (not recommended in production) and didn't specify the maximum allocated storage capacity for each node using the `--store` flag, the capacity metrics in the Admin UI are incorrect. This is because when multiple nodes are running on a single machine, the machine's hard disk is treated as an available store for each node, while in reality, only one hard disk is available for all nodes. The total available capacity is then calculated as the hard disk size multiplied by the number of nodes on the machine.

### 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.
- A `CREATE TABLE` statement containing `FOREIGN KEY` or `INTERLEAVE` clauses cannot be followed by statements that reference the new table.
- A table cannot be dropped and then recreated with the same name. This is not possible within a single transaction because `DROP TABLE` does not immediately drop the name of the table. As a workaround, split the `DROP TABLE` and `CREATE TABLE` statements into separate transactions.

### Schema changes between executions of prepared statements

When the schema of a table targeted by a prepared statement changes after the prepared statement is created, future executions of the prepared statement could result in an error. For example, adding a column to a table referenced in a prepared statement with a `SELECT *` clause will result in an error:

    CREATE TABLE users (id INT PRIMARY KEY);

    PREPARE prep1 AS SELECT * FROM users;

    ALTER TABLE users ADD COLUMN name STRING;

    INSERT INTO users VALUES (1, 'Max Roach');

    EXECUTE prep1;

    ERROR: cached plan must not change result type
    SQLSTATE: 0A000

It's therefore recommended to explicitly list result columns instead of using `SELECT *` in prepared statements, when possible.

### `INSERT ON CONFLICT` vs. `UPSERT`

When inserting/updating all columns of a table, and the table has no secondary indexes, we recommend using an `UPSERT` statement instead of the equivalent `INSERT ON CONFLICT` statement. Whereas `INSERT ON CONFLICT` always performs a read to determine the necessary writes, the `UPSERT` statement writes without reading, making it faster.

This issue is particularly relevant when using a simple SQL table of two columns to simulate direct KV access. In this case, be sure to use the `UPSERT` statement.

### Write and update limits for a single statement

A single statement can perform at most 64MiB of combined updates. When a statement exceeds these limits, its transaction gets aborted. Currently, `INSERT INTO ... SELECT FROM` and `CREATE TABLE AS SELECT` queries may encounter these limits.

To increase these limits, you can update the cluster-wide setting `kv.raft.command.max_size`, but note that increasing this setting can affect the memory utilization of nodes in the cluster. For `INSERT INTO .. SELECT FROM` queries in particular, another workaround is to manually page through the data you want to insert using separate transactions.

In the v1.1 release, the limit referred to a whole transaction (i.e., the sum of changes done by all statements) and capped both the number and the size of update. In this release, there's only a size limit, and it applies independently to each statement. Note that even though not directly restricted any more, large transactions can have performance implications on the cluster.

### 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 Admin UI, 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';

    +------------------------+
    | 'string1' || 'string2' |
    +------------------------+
    | string1string2         |
    +------------------------+
    (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 (64MiB 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.

### SQL subexpressions and memory usage

Many SQL subexpressions (e.g., `ORDER BY`, `UNION`/`INTERSECT`/`EXCEPT`, `GROUP BY`, subqueries) accumulate intermediate results in RAM on the node processing the query. If the operator attempts to process more rows than can fit into RAM, the node will either crash or report a memory capacity error. For more details about memory usage in CockroachDB, see this blog post.

### Query planning for `OR` expressions

Given a query like `SELECT * FROM foo WHERE a > 1 OR b > 2`, even if there are appropriate indexes to satisfy both `a > 1` and `b > 2`, the query planner performs a full table or index scan because it cannot use both conditions at once.

### 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.

### `cockroach dump` does not support cyclic foreign key references

Note:

Resolved as of v2.1.0-alpha.20180507. See #24716.

The `cockroach dump` command will successfully create a dump file for a table with a foreign key reference to itself, or a set of tables with a cyclic foreign key dependency (e.g., a depends on b depends on a). That dump file, however, can only be executed after manually editing the output to remove the foreign key definitions from the `CREATE TABLE` statements and adding them as `ALTER TABLE ... ADD CONSTRAINT` statements after the `INSERT` statements.