Known Limitations in CockroachDB v21.2

This page describes newly identified limitations in the CockroachDB v21.2.0-alpha.1 release as well as unresolved limitations identified in earlier releases.

New limitations

CockroachDB does not properly optimize some left and anti joins with inverted indexes

Left joins and anti joins involving JSONB, ARRAY, or spatial-typed columns with a multi-column or partitioned inverted index will not take advantage of the index if the prefix columns of the index are unconstrained, or if they are constrained to multiple, constant values.

To work around this limitation, make sure that the prefix columns of the index are either constrained to single constant values, or are part of an equality condition with an input column (e.g., col1 = col2, where col1 is a prefix column and col2 is an input column).

For example, suppose you have the following multi-region database and tables:

CREATE DATABASE multi_region_test_db PRIMARY REGION "europe-west1" REGIONS "us-west1", "us-east1" SURVIVE REGION FAILURE;
USE multi_region_test_db;

CREATE TABLE t1 (
  k INT PRIMARY KEY,
  geom GEOMETRY
);

CREATE TABLE t2 (
  k INT PRIMARY KEY,
  geom GEOMETRY,
  INVERTED INDEX geom_idx (geom)
) LOCALITY REGIONAL BY ROW;

And you insert some data into the tables:

INSERT INTO t1 SELECT generate_series(1, 1000), 'POINT(1.0 1.0)';
INSERT INTO t2 (crdb_region, k, geom) SELECT 'us-east1', generate_series(1, 1000), 'POINT(1.0 1.0)';
INSERT INTO t2 (crdb_region, k, geom) SELECT 'us-west1', generate_series(1001, 2000), 'POINT(2.0 2.0)';
INSERT INTO t2 (crdb_region, k, geom) SELECT 'europe-west1', generate_series(2001, 3000), 'POINT(3.0 3.0)';

If you attempt a left join between t1 and t2 on only the geometry columns, CockroachDB will not be able to plan an inverted join:

> EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON st_contains(t1.geom, t2.geom);
                info
------------------------------------
  distribution: full
  vectorized: true

  • cross join (right outer)
  │ pred: st_contains(geom, geom)
  │
  ├── • scan
  │     estimated row count: 3,000
  │     table: t2@primary
  │     spans: FULL SCAN
  │
  └── • scan
        estimated row count: 1,000
        table: t1@primary
        spans: FULL SCAN
(15 rows)

However, if you constrain the crdb_region column to a single value, CockroachDB can plan an inverted join:

> EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON st_contains(t1.geom, t2.geom) AND t2.crdb_region = 'us-east1';
                       info
--------------------------------------------------
  distribution: full
  vectorized: true

  • lookup join (left outer)
  │ table: t2@primary
  │ equality: (crdb_region, k) = (crdb_region,k)
  │ equality cols are key
  │ pred: st_contains(geom, geom)
  │
  └── • inverted join (left outer)
      │ table: t2@geom_idx
      │
      └── • render
          │
          └── • scan
                estimated row count: 1,000
                table: t1@primary
                spans: FULL SCAN
(18 rows)

If you do not know which region to use, you can combine queries with UNION ALL:

> EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON st_contains(t1.geom, t2.geom) AND t2.crdb_region = 'us-east1'
UNION ALL SELECT * FROM t1 LEFT JOIN t2 ON st_contains(t1.geom, t2.geom) AND t2.crdb_region = 'us-west1'
UNION ALL SELECT * FROM t1 LEFT JOIN t2 ON st_contains(t1.geom, t2.geom) AND t2.crdb_region = 'europe-west1';
                           info
----------------------------------------------------------
  distribution: full
  vectorized: true

  • union all
  │
  ├── • union all
  │   │
  │   ├── • lookup join (left outer)
  │   │   │ table: t2@primary
  │   │   │ equality: (crdb_region, k) = (crdb_region,k)
  │   │   │ equality cols are key
  │   │   │ pred: st_contains(geom, geom)
  │   │   │
  │   │   └── • inverted join (left outer)
  │   │       │ table: t2@geom_idx
  │   │       │
  │   │       └── • render
  │   │           │
  │   │           └── • scan
  │   │                 estimated row count: 1,000
  │   │                 table: t1@primary
  │   │                 spans: FULL SCAN
  │   │
  │   └── • lookup join (left outer)
  │       │ table: t2@primary
  │       │ equality: (crdb_region, k) = (crdb_region,k)
  │       │ equality cols are key
  │       │ pred: st_contains(geom, geom)
  │       │
  │       └── • inverted join (left outer)
  │           │ table: t2@geom_idx
  │           │
  │           └── • render
  │               │
  │               └── • scan
  │                     estimated row count: 1,000
  │                     table: t1@primary
  │                     spans: FULL SCAN
  │
  └── • lookup join (left outer)
      │ table: t2@primary
      │ equality: (crdb_region, k) = (crdb_region,k)
      │ equality cols are key
      │ pred: st_contains(geom, geom)
      │
      └── • inverted join (left outer)
          │ table: t2@geom_idx
          │
          └── • render
              │
              └── • scan
                    estimated row count: 1,000
                    table: t1@primary
                    spans: FULL SCAN
(54 rows)

CockroachDB

CockroachCloud

CockroachDB

CockroachCloud

Known Limitations in CockroachDB v21.2

New limitations

CockroachDB does not properly optimize some left and anti joins with inverted indexes

Unresolved limitations

HTTP(S) connections

IMPORT into a REGIONAL BY ROW table

BACKUP of multi-region tables

Differences in syntax and behavior between CockroachDB and PostgreSQL

Multiple arbiter indexes for INSERT ON CONFLICT DO UPDATE

IMPORT into a table with partial indexes

Historical reads on restored objects

Spatial support limitations

Subqueries in SET statements

Enterprise BACKUP does not capture database/table/column comments

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

Change data capture

DB Console may become inaccessible for secure clusters

AS OF SYSTEM TIME in SELECT statements

Large index keys can impair performance

Using LIKE...ESCAPE in WHERE and HAVING constraints

TRUNCATE does not behave like DELETE

Ordering tables by JSONB/JSON-typed columns

Current sequence value not checked when updating min/max value

Using default_int_size session variable in batch of statements

COPY FROM statements are not supported in the CockroachDB SQL shell

COPY syntax not supported by CockroachDB

Import with a high amount of disk contention

Placeholders in PARTITION BY

Adding a column with sequence-based DEFAULT values

Available capacity metric in the DB Console

Schema changes within transactions

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

Schema changes between executions of prepared statements

INSERT ON CONFLICT vs. UPSERT

Size limits on statement input from SQL clients

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

New values generated by DEFAULT expressions during ALTER TABLE ADD COLUMN

Load-based lease rebalancing in uneven latency deployments

Overload resolution for collated strings

Max size of a single column family

Simultaneous client connections and running queries on a single node

Privileges for DELETE and UPDATE

ROLLBACK TO SAVEPOINT in high-priority transactions containing DDL

Concurrent SQL shells overwrite each other's history

Passwords with special characters cannot be passed in connection parameter

CockroachDB does not test for all connection failure scenarios

Some column-dropping schema changes do not roll back properly

Disk-spilling on joins with JSON columns

Disk-spilling not supported for some unordered distinct operations

Using interleaved tables in backups

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

The optimizer won't plan locality optimized searches using unique indexes on virtual computed columns

Unique indexes on virtual computed columns can't be used with multi-region clusters

`IMPORT` into a `REGIONAL BY ROW` table

`BACKUP` of multi-region tables

Multiple arbiter indexes for `INSERT ON CONFLICT DO UPDATE`

`IMPORT` into a table with partial indexes

Subqueries in `SET` statements

Enterprise `BACKUP` does not capture database/table/column comments

`AS OF SYSTEM TIME` in `SELECT` statements

Using `LIKE...ESCAPE` in `WHERE` and `HAVING` constraints

`TRUNCATE` does not behave like `DELETE`

Ordering tables by `JSONB`/`JSON`-typed columns

Using `default_int_size` session variable in batch of statements

`COPY FROM` statements are not supported in the CockroachDB SQL shell

`COPY` syntax not supported by CockroachDB

Placeholders in `PARTITION BY`

Adding a column with sequence-based `DEFAULT` values

`INSERT ON CONFLICT` vs. `UPSERT`

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

New values generated by `DEFAULT` expressions during `ALTER TABLE ADD COLUMN`

Privileges for `DELETE` and `UPDATE`

`ROLLBACK TO SAVEPOINT` in high-priority transactions containing DDL

Disk-spilling on joins with `JSON` columns