SQL Performance Best Practices

This page provides best practices for optimizing SQL performance in CockroachDB.

Tip:

For a demonstration of some of these techniques, see Performance Tuning.

Multi-row DML best practices

Use multi-row DML instead of multiple single-row DMLs

For INSERT, UPSERT, and DELETE statements, a single multi-row DML is faster than multiple single-row DMLs. Whenever possible, use multi-row DML instead of multiple single-row DMLs.

For more information, see:

Use TRUNCATE instead of DELETE to delete all rows in a table

The TRUNCATE statement removes all rows from a table by dropping the table and recreating a new table with the same name. This performs better than using DELETE, which performs multiple transactions to delete all rows.

Bulk insert best practices

Use multi-row INSERT statements for bulk inserts into existing tables

To bulk-insert data into an existing table, batch multiple rows in one multi-row INSERT statement and do not include the INSERT statements within a transaction. Experimentally determine the optimal batch size for your application by monitoring the performance for different batch sizes (10 rows, 100 rows, 1000 rows). For more information, see Insert Multiple Rows.

Use IMPORT instead of INSERT for bulk inserts into new tables

To bulk-insert data into a brand new table, the IMPORT statement performs better than INSERT.

Assign column families

A column family is a group of columns in a table that is stored as a single key-value pair in the underlying key-value store.

When a table is created, all columns are stored as a single column family. This default approach ensures efficient key-value storage and performance in most cases. However, when frequently updated columns are grouped with seldom updated columns, the seldom updated columns are nonetheless rewritten on every update. Especially when the seldom updated columns are large, it's therefore more performant to assign them to a distinct column family.

Interleave tables

Interleaving tables improves query performance by optimizing the key-value structure of closely related tables, attempting to keep data on the same key-value range if it's likely to be read and written together. This is particularly helpful if the tables are frequently joined on the columns that consist of the interleaving relationship.

However, the above is only true for tables where all operations (e.g., SELECT or INSERT) are performed on a single value shared between both tables. The following types of operations may actually become slower after interleaving:

  • Operations that span multiple values.
  • Operations that do not specify the interleaved parent ID.
    This happens because when data is interleaved, queries that work on the parent table(s) will need to "skip over" the data in interleaved children, which increases the read and write latencies to the parent in proportion to the number of interleaved values.

Unique ID best practices

A traditional approach for generating unique IDs is one of the following:

  • Monotonically increase INT IDs by using transactions with roundtrip SELECTs.
  • Use the SERIAL pseudo-type for a column to generate random unique IDs.
    The first approach does not take advantage of the parallelization possible in a distributed database like CockroachDB.

The bottleneck with the second approach is that IDs generated temporally near each other have similar values and are located physically near each other in a table. This can cause a hotspot for reads and writes in a table.

The best practice in CockroachDB is to generate unique IDs using the UUID type, which generates random unique IDs in parallel, thus improving performance.

Use UUID to generate unique IDs

To auto-generate unique row IDs, use the UUID column with the gen_random_uuid() function as the default value:

  1. > CREATE TABLE t1 (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name STRING);
  1. > INSERT INTO t1 (name) VALUES ('a'), ('b'), ('c');
  1. > SELECT * FROM t1;
  1. +--------------------------------------+------+
  2. |                  id                  | name |
  3. +--------------------------------------+------+
  4. | 60853a85-681d-4620-9677-946bbfdc8fbc | c    |
  5. | 77c9bc2e-76a5-4ebc-80c3-7ad3159466a1 | b    |
  6. | bd3a56e1-c75e-476c-b221-0da9d74d66eb | a    |
  7. +--------------------------------------+------+
  8. (3 rows)

Alternatively, you can use the BYTES column with the uuid_v4() function as the default value instead:

  1. > CREATE TABLE t2 (id BYTES PRIMARY KEY DEFAULT uuid_v4(), name STRING);
  1. > INSERT INTO t2 (name) VALUES ('a'), ('b'), ('c');
  1. > SELECT * FROM t2;
  1. +---------------------------------------------------+------+
  2. |                        id                         | name |
  3. +---------------------------------------------------+------+
  4. | "\x9b\x10\xdc\x11\x9a\x9cGB\xbd\x8d\t\x8c\xf6@vP" | a    |
  5. | "\xd9s\xd7\x13\n_L*\xb0\x87c\xb6d\xe1\xd8@"       | c    |
  6. | "\uac74\x1dd@B\x97\xac\x04N&\x9eBg\x86"           | b    |
  7. +---------------------------------------------------+------+
  8. (3 rows)

In either case, generated IDs will be 128-bit, large enough for there to be virtually no chance of generating non-unique values. Also, once the table grows beyond a single key-value range (more than 64MB by default), new IDs will be scattered across all of the table's ranges and, therefore, likely across different nodes. This means that multiple nodes will share in the load.

If it is important for generated IDs to be stored in the same key-value range, you can use an integer type with the unique_rowid() function as the default value, either explicitly or via the SERIAL pseudo-type:

  1. > CREATE TABLE t3 (id INT PRIMARY KEY DEFAULT unique_rowid(), name STRING);
  1. > INSERT INTO t3 (name) VALUES ('a'), ('b'), ('c');
  1. > SELECT * FROM t3;
  1. +--------------------+------+
  2. |         id         | name |
  3. +--------------------+------+
  4. | 293807573840855041 | a    |
  5. | 293807573840887809 | b    |
  6. | 293807573840920577 | c    |
  7. +--------------------+------+
  8. (3 rows)

Upon insert or upsert, the unique_rowid() function generates a default value from the timestamp and ID of the node executing the insert. Such time-ordered values are likely to be globally unique except in cases where a very large number of IDs (100,000+) are generated per node per second. Also, there can be gaps and the order is not completely guaranteed.

Use INSERT with the RETURNING clause to generate unique IDs

If something prevents you from using UUID to generate unique IDs, you might resort to using INSERTs with SELECTs to return IDs. Instead, use the RETURNING clause with the INSERT statement for improved performance.

Generate monotonically-increasing unique IDs

Suppose the table schema is as follows:

  1. > CREATE TABLE X (
  2.     ID1 INT,
  3.     ID2 INT,
  4.     ID3 INT DEFAULT 1,
  5.     PRIMARY KEY (ID1,ID2)
  6.   );

The common approach would be to use a transaction with an INSERT followed by a SELECT:

  1. > BEGIN;
  3. > INSERT INTO X VALUES (1,1,1)
  4.     ON CONFLICT (ID1,ID2)
  5.     DO UPDATE SET ID3=X.ID3+1;
  7. > SELECT * FROM X WHERE ID1=1 AND ID2=1;
  9. > COMMIT;

However, the performance best practice is to use a RETURNING clause with INSERT instead of the transaction:

  1. > INSERT INTO X VALUES (1,1,1),(2,2,2),(3,3,3)
  2.     ON CONFLICT (ID1,ID2)
  3.     DO UPDATE SET ID3=X.ID3 + 1
  4.     RETURNING ID1,ID2,ID3;

Generate random unique IDs

Suppose the table schema is as follows:

  1. > CREATE TABLE X (
  2.     ID1 INT,
  3.     ID2 INT,
  4.     ID3 INT DEFAULT unique_rowid(),
  5.     PRIMARY KEY (ID1,ID2)
  6.   );

The common approach to generate random Unique IDs is a transaction using a SELECT statement:

  1. > BEGIN;
  3. > INSERT INTO X VALUES (1,1);
  5. > SELECT * FROM X WHERE ID1=1 AND ID2=1;
  7. > COMMIT;

However, the performance best practice is to use a RETURNING clause with INSERT instead of the transaction:

  1. > INSERT INTO X VALUES (1,1),(2,2),(3,3)
  2.     RETURNING ID1,ID2,ID3;

Indexes best practices

Use secondary indexes

You can use secondary indexes to improve the performance of queries using columns not in a table's primary key. You can create them:

  • At the same time as the table with the INDEX clause of CREATE TABLE. In addition to explicitly defined indexes, CockroachDB automatically creates secondary indexes for columns with the UNIQUE constraint.
  • For existing tables with CREATE INDEX.
  • By applying the UNIQUE constraint to columns with ALTER TABLE, which automatically creates an index of the constrained columns.
    To create the most useful secondary indexes, check out our best practices.

Use indexes for faster joins

See Join Performance Best Practices.

Drop unused indexes

Though indexes improve read performance, they incur an overhead for every write. In some cases, like the use cases discussed above, the tradeoff is worth it. However, if an index is unused, it slows down DML operations. Therefore, drop unused indexes whenever possible.

Join best practices

See Join Performance Best Practices.

Subquery best practices

See Subquery Performance Best Practices.

Table scans best practices

Avoid SELECT * for large tables

For large tables, avoid table scans (that is, reading the entire table data) whenever possible. Instead, define the required fields in a SELECT statement.

Example

Suppose the table schema is as follows:

  1. > CREATE TABLE accounts (
  2.     id INT,
  3.     customer STRING,
  4.     address STRING,
  5.     balance INT
  6.     nominee STRING
  7.     );

Now if we want to find the account balances of all customers, an inefficient table scan would be:

  1. > SELECT * FROM ACCOUNTS;

This query retrieves all data stored in the table. A more efficient query would be:

  1.  > SELECT CUSTOMER, BALANCE FROM ACCOUNTS;

This query returns the account balances of the customers.

Avoid SELECT DISTINCT for large tables

SELECT DISTINCT allows you to obtain unique entries from a query by removing duplicate entries. However, SELECT DISTINCT is computationally expensive. As a performance best practice, use SELECT with the WHERE clause instead.

Use AS OF SYSTEM TIME to decrease conflicts with long-running queries

If you have long-running queries (such as analytics queries that perform full table scans) that can tolerate slightly out-of-date reads, consider using the … AS OF SYSTEM TIME clause. Using this, your query returns data as it appeared at a distinct point in the past and will not cause conflicts with other concurrent transactions, which can increase your application's performance.

However, because AS OF SYSTEM TIME returns historical data, your reads might be stale.

Understanding and avoiding transaction contention

Transaction contention occurs when the following three conditions are met:

  • There are multiple concurrent transactions or statements (sent bymultiple clients connected simultaneously to a single CockroachDBcluster).
  • They operate on the same data, specifically over table rows with thesame index key values (either on primary keys orsecondary indexes, or viainterleaving) or using index key valuesthat are close to each other, and thus place the indexed data on thesame data ranges.
  • At least some of the transactions write or modify the data.
    A set of transactions that all contend on the same keys will belimited in performance to the maximum processing speed of a singlenode (limited horizontal scalability). Non-contended transactions arenot affected in this way.

There are two levels of contention:

  • Transactions that operate on the same range but different index keyvalues will be limited by the overall hardware capacity of a singlenode (the range lease holder).

  • Transactions that operate on the same index key values(specifically, that operate on the same columnfamily for a given index key) will be morestrictly serialized to obey transaction isolation semantics.

Transaction contention can also increase the rate of transactionrestarts, and thus make the proper implementation of client-sidetransactionretries morecritical.

To avoid contention, multiple strategies can be applied:

  • Use index key values with a more random distribution of values, sothat transactions over different rows are more likely to operate onseparate data ranges. See the SQL FAQs on row IDs forsuggestions.

  • Make transactions smaller, so that each transaction has less work todo. In particular, avoid multiple client-server exchanges pertransaction. For example, use common tableexpressions to group multipleSELECT andINSERT/UPDATE/DELETE/UPSERTclauses together in a single SQL statement.

  • When replacing values in a row, use UPSERT andspecify values for all columns in the inserted rows. This willusually have the best performance under contention, compared tocombinations of SELECT,INSERT, and UPDATE.

  • Increasenormalizationof the data to place parts of the same records that are modified bydifferent transactions in different tables. Note however that thisis a double-edged sword, because denormalization can also increaseperformance by creating multiple copies of often-referenced data inseparate ranges.

  • If the application strictly requires operating on very few differentindex key values, consider using ALTER … SPLIT
    AT     so that each index key value can be served bya separate group of nodes in the cluster.

It is always best to avoid contention as much as possible via thedesign of the schema and application. However, sometimes contention isunavoidable. To maximize performance in the presence of contention,you'll need to maximize the performance of a single range.To achieve this, multiple strategies can be applied:

  • Minimize the network distance between the replicas of a range,possibly using zone configs and partitioning.
  • Use the fastest storage devices available.
  • If the contending transactions operate on different keys within thesame range, add more CPU power (more cores) per node. Note howeverthat this is less likely to provide an improvement if thetransactions all operate on the same key.

Was this page helpful?
YesNo