> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL Performance Best Practices

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

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

## DML best practices

### Use multi-row statements instead of multiple single-row statements

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

For more information, see:

* <InternalLink path="insert-data">Insert Data</InternalLink>
* <InternalLink path="update-data">Update Data</InternalLink>
* <InternalLink path="delete-data">Delete Data</InternalLink>
* [How to improve IoT application performance with multi-row DML](https://www.cockroachlabs.com/blog/multi-row-dml)

### Use `UPSERT` instead of `INSERT ON CONFLICT` on tables with no secondary indexes

When inserting or updating columns on a table that does not have <InternalLink path="indexes">secondary indexes</InternalLink>, Cockroach Labs recommends using an `UPSERT` statement instead of `INSERT ON CONFLICT DO UPDATE`. Whereas `INSERT ON CONFLICT` always performs a read, the `UPSERT` statement writes without reading, making it faster. This may be useful if you are using a simple SQL table of two columns to <InternalLink path="sql-faqs">simulate direct KV access</InternalLink>.

If the table has a secondary index, there is no performance difference between `UPSERT` and `INSERT ON CONFLICT`. However, `INSERT` without an `ON CONFLICT` clause may not scan the table for existing values. This can provide a performance improvement over `UPSERT`.

## 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. Experimentally determine the optimal batch size for your application by monitoring the performance for different batch sizes (10 rows, 100 rows, 1000 rows). Do not include bulk `INSERT` statements within an explicit transaction.

<Tip>
  You can also use the <InternalLink path="import-into">`IMPORT INTO`</InternalLink> statement to bulk-insert CSV data into an existing table.
</Tip>

For more information, see <InternalLink path="insert#insert-multiple-rows-into-an-existing-table">Insert Multiple Rows</InternalLink>.

<Note>
  Large multi-row `INSERT` queries can lead to long-running transactions that result in <InternalLink path="transaction-retry-error-reference">transaction retry errors</InternalLink>. If a multi-row `INSERT` query results in an error code <InternalLink path="transaction-retry-error-reference#retry_commit_deadline_exceeded">`40001` with the message `transaction deadline exceeded`</InternalLink>, we recommend breaking up the query up into smaller batches of rows.
</Note>

### Use `IMPORT` instead of `INSERT` for bulk-inserts into new tables

To bulk-insert data into a brand new table, the <InternalLink path="import">`IMPORT`</InternalLink> statement performs better than `INSERT`.

## Bulk-delete best practices

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

The <InternalLink path="truncate">`TRUNCATE`</InternalLink> 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.

### Use batch deletes to delete a large number of rows

To delete a large number of rows, we recommend iteratively deleting batches of rows until all of the unwanted rows are deleted. For an example, see <InternalLink path="bulk-delete-data">Bulk-delete Data</InternalLink>.

### Batch delete "expired" data

CockroachDB has support for Time to Live ("TTL") expiration on table rows, also known as *Row-Level TTL*. Row-Level TTL is a mechanism whereby rows from a table are considered "expired" and can be automatically deleted once those rows have been stored longer than a specified expiration time.

For more information, see <InternalLink path="row-level-ttl">Batch delete expired data with Row-Level TTL</InternalLink>.

## 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 <InternalLink path="column-families">assign them to a distinct column family</InternalLink>.

## Unique ID best practices

The best practices for generating unique IDs in a distributed database like CockroachDB are very different than for a legacy single-node database. Traditional approaches for generating unique IDs for legacy single-node databases include:

1. Using the <InternalLink path="serial">`SERIAL`</InternalLink> pseudo-type for a column to generate random unique IDs. This can result in a performance bottleneck because IDs generated temporally near each other have similar values and are located physically near each other in a table's storage.
2. Generating monotonically increasing <InternalLink path="int">`INT`</InternalLink> IDs by using transactions with roundtrip <InternalLink path="select-clause">`SELECT`</InternalLink> s, e.g., `INSERT INTO tbl (id, …) VALUES ((SELECT max(id)+1 FROM tbl), …)`. This has a **very high performance cost** since it makes all <InternalLink path="insert">`INSERT`</InternalLink> transactions wait for their turn to insert the next ID. You should only do this if your application really does require strict ID ordering. In some cases, using <InternalLink path="change-data-capture-overview">change data capture (CDC)</InternalLink> can help avoid the requirement for strict ID ordering. If you can avoid the requirement for strict ID ordering, you can use one of the higher-performance ID strategies outlined in the following sections.

The preceding approaches are likely to create [hot spots](#hot-spots) for both reads and writes in CockroachDB. We <InternalLink path="schema-design-indexes#best-practices">discourage indexing on sequential keys</InternalLink>. If a table **must** be indexed on sequential keys, use <InternalLink path="hash-sharded-indexes">hash-sharded indexes</InternalLink>. Hash-sharded indexes distribute sequential traffic uniformly across ranges, eliminating single-range <InternalLink path="performance-best-practices-overview#hot-spots">hot spots</InternalLink> and improving write performance on sequentially-keyed indexes at a small cost to read performance.

To create unique and non-sequential IDs, we recommend the following approaches (listed in order from best to worst performance):

| Approach                                                                                                    | Pros                                             | Cons                                                                                                          |
| ----------------------------------------------------------------------------------------------------------- | ------------------------------------------------ | ------------------------------------------------------------------------------------------------------------- |
| 1. [Use multi-column primary keys](#use-multi-column-primary-keys)                                          | Potentially fastest, if done right               | Complex, requires up-front design and testing to ensure performance                                           |
| 2. [Use functions to generate unique IDs](#use-functions-to-generate-unique-ids)                            | Good performance; spreads load well; easy choice | May leave some performance on the table; requires other columns to be useful in queries                       |
| 3. [Use `INSERT` with the `RETURNING` clause](#use-insert-with-the-returning-clause-to-generate-unique-ids) | Easy to query against; familiar design           | Slower performance than the other options; higher chance of [transaction contention](#transaction-contention) |

### Use multi-column primary keys

A well-designed multi-column primary key can yield even better performance than a [UUID primary key](#use-functions-to-generate-unique-ids), but it requires more up-front schema design work. To get the best performance, ensure that any monotonically increasing field is located **after** the first column of the primary key. When done right, such a composite primary key should result in:

* Enough randomness in your primary key to spread the table data / query load relatively evenly across the cluster, which will avoid hot spots. By "enough randomness" we mean that the prefix of the primary key should be relatively uniformly distributed over its domain. Its domain should have at least as many elements as you have nodes.
* A monotonically increasing column that is part of the primary key (and thus indexed) which is also useful in your queries.

For example, consider a social media website. Social media posts are written by users, and on login the user's last 10 posts are displayed. A good choice for a primary key might be `(username, post_timestamp)`. For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE posts (
    username STRING,
    post_timestamp TIMESTAMP,
    post_id INT,
    post_content STRING,
    CONSTRAINT posts_pk PRIMARY KEY(username, post_timestamp)
);
```

This would make the following query efficient.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM posts
          WHERE username = 'alyssa'
       ORDER BY post_timestamp DESC
          LIMIT 10;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  username |      post_timestamp       | post_id | post_content
+----------+---------------------------+---------+--------------+
  alyssa   | 2019-07-31 18:01:00+00:00 |    ...  | ...
  alyssa   | 2019-07-30 10:22:00+00:00 |    ...  | ...
  alyssa   | 2019-07-30 09:12:00+00:00 |    ...  | ...
  alyssa   | 2019-07-29 13:48:00+00:00 |    ...  | ...
  alyssa   | 2019-07-29 13:47:00+00:00 |    ...  | ...
  alyssa   | 2019-07-29 13:46:00+00:00 |    ...  | ...
  alyssa   | 2019-07-29 13:43:00+00:00 |    ...  | ...
  ...

Time: 924µs
```

To see why, let's look at the <InternalLink path="explain">`EXPLAIN`</InternalLink> output. It shows that the query is fast because it does a point lookup on the indexed column `username` (as shown by the line `spans | /"alyssa"-...`). Furthermore, the column `post_timestamp` is already in an index, and sorted (since it's a monotonically increasing part of the primary key).

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> EXPLAIN (VERBOSE)
    SELECT * FROM posts
            WHERE username = 'alyssa'
         ORDER BY post_timestamp DESC
            LIMIT 10;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
                              info
----------------------------------------------------------------
  distribution: local
  vectorized: true

  • revscan
    columns: (username, post_timestamp, post_id, post_content)
    ordering: -post_timestamp
    estimated row count: 10 (missing stats)
    table: posts@posts_pk
    spans: /"alyssa"-/"alyssa"/PrefixEnd
    limit: 10
(10 rows)

Time: 1ms total (execution 1ms / network 0ms)
```

Note that the above query also follows the <InternalLink path="indexes#best-practices">indexing best practice</InternalLink> of indexing all columns in the `WHERE` clause.

### Use functions to generate unique IDs

To auto-generate unique row identifiers, you can use the `gen_random_uuid()`, `uuid_v4()`, or `unique_rowid()` <InternalLink path="functions-and-operators#id-generation-functions">functions</InternalLink>.

To use the <InternalLink path="uuid">`UUID`</InternalLink> column with the `gen_random_uuid()` <InternalLink path="functions-and-operators#id-generation-functions">function</InternalLink> as the <InternalLink path="default-value">default value</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TABLE users (
    id UUID NOT NULL DEFAULT gen_random_uuid(),
    city STRING NOT NULL,
    name STRING NULL,
    address STRING NULL,
    credit_card STRING NULL,
    CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
    FAMILY "primary" (id, city, name, address, credit_card)
);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
INSERT INTO users (name, city) VALUES ('Petee', 'new york'), ('Eric', 'seattle'), ('Dan', 'seattle');
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM users;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
                   id                  |   city   | name  | address | credit_card
+--------------------------------------+----------+-------+---------+-------------+
  cf8ee4e2-cd74-449a-b6e6-a0fb2017baa4 | new york | Petee | NULL    | NULL
  2382564e-702f-42d9-a139-b6df535ae00a | seattle  | Eric  | NULL    | NULL
  7d27e40b-263a-4891-b29b-d59135e55650 | seattle  | Dan   | NULL    | NULL
(3 rows)
```

Alternatively, you can use the <InternalLink path="bytes">`BYTES`</InternalLink> column with the `uuid_v4()` function as the default value:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TABLE users2 (
    id BYTES DEFAULT uuid_v4(),
    city STRING NOT NULL,
    name STRING NULL,
    address STRING NULL,
    credit_card STRING NULL,
    CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
    FAMILY "primary" (id, city, name, address, credit_card)
);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
INSERT INTO users2 (name, city) VALUES ('Anna', 'new york'), ('Jonah', 'seattle'), ('Terry', 'chicago');
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM users;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
                        id                       |   city   | name  | address | credit_card
+------------------------------------------------+----------+-------+---------+-------------+
  4\244\277\323/\261M\007\213\275*\0060\346\025z | chicago  | Terry | NULL    | NULL
  \273*t=u.F\010\274f/}\313\332\373a             | new york | Anna  | NULL    | NULL
  \004\\\364nP\024L)\252\364\222r$\274O0         | seattle  | Jonah | NULL    | NULL
(3 rows)
```

In either case, generated IDs will be 128-bit, sufficiently large to generate unique values. Once the table grows beyond a single key-value range's <InternalLink path="configure-replication-zones">default size</InternalLink>, 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.

This approach has the disadvantage of creating a primary key that may not be useful in a query directly, which can require a join with another table or a secondary index.

If it is important for generated IDs to be stored in the same key-value range, you can use an <InternalLink path="int">integer type</InternalLink> with the `unique_rowid()` <InternalLink path="functions-and-operators#id-generation-functions">function</InternalLink> as the default value, either explicitly or via the <InternalLink path="serial">`SERIAL` pseudo-type</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TABLE users3 (
    id INT DEFAULT unique_rowid(),
    city STRING NOT NULL,
    name STRING NULL,
    address STRING NULL,
    credit_card STRING NULL,
    CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
    FAMILY "primary" (id, city, name, address, credit_card)
);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
INSERT INTO users3 (name, city) VALUES ('Blake', 'chicago'), ('Hannah', 'seattle'), ('Bobby', 'seattle');
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM users3;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
          id         |  city   |  name  | address | credit_card
+--------------------+---------+--------+---------+-------------+
  469048192112197633 | chicago | Blake  | NULL    | NULL
  469048192112263169 | seattle | Hannah | NULL    | NULL
  469048192112295937 | seattle | Bobby  | NULL    | NULL
(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.

To understand the differences between the `UUID` and `unique_rowid()` options, see the <InternalLink path="sql-faqs">SQL FAQs</InternalLink>. For further background on UUIDs, see [What is a UUID, and Why Should You Care?](https://www.cockroachlabs.com/blog/what-is-a-uuid).

### Use `INSERT` with the `RETURNING` clause to generate unique IDs

If something prevents you from using [multi-column primary keys](#use-multi-column-primary-keys) or [`UUID`s](#use-functions-to-generate-unique-ids) to generate unique IDs, you might resort to using `INSERT`s with `SELECT`s to return IDs. Instead, <InternalLink path="insert#insert-and-return-values">use the `RETURNING` clause with the `INSERT` statement</InternalLink> as shown below for improved performance.

#### Generate monotonically-increasing unique IDs

Suppose the table schema is as follows:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE X (
    ID1 INT,
    ID2 INT,
    ID3 INT DEFAULT 1,
    PRIMARY KEY (ID1,ID2)
  );
```

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> BEGIN;

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

> SELECT * FROM X WHERE ID1=1 AND ID2=1;

> COMMIT;
```

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO X VALUES (1,1,1),(2,2,2),(3,3,3)
    ON CONFLICT (ID1,ID2)
    DO UPDATE SET ID3=X.ID3 + 1
    RETURNING ID1,ID2,ID3;
```

#### Generate random unique IDs

Suppose the table schema is as follows:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE X (
    ID1 INT,
    ID2 INT,
    ID3 INT DEFAULT unique_rowid(),
    PRIMARY KEY (ID1,ID2)
  );
```

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> BEGIN;

> INSERT INTO X VALUES (1,1);

> SELECT * FROM X WHERE ID1=1 AND ID2=1;

> COMMIT;
```

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO X VALUES (1,1),(2,2),(3,3)
    RETURNING ID1,ID2,ID3;
```

## Secondary index best practices

See <InternalLink path="schema-design-indexes#best-practices">Secondary Index Best Practices</InternalLink>.

## Join best practices

See <InternalLink path="joins#performance-best-practices">Join Performance Best Practices</InternalLink>.

## Subquery best practices

See <InternalLink path="subqueries#performance-best-practices">Subquery Performance Best Practices</InternalLink>.

## Authorization best practices

See <InternalLink path="security-reference/authorization#authorization-best-practices">Authorization Best Practices</InternalLink>.

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

For example, suppose the table schema is as follows:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE accounts (
    id INT,
    customer STRING,
    address STRING,
    balance INT
    nominee STRING
    );
```

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM ACCOUNTS;
```

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
 > 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 <InternalLink path="select-clause#filter-rows">`SELECT` with the `WHERE` clause</InternalLink> instead.

### Use secondary indexes to optimize queries

See <InternalLink path="sql-tuning-with-explain#issue-full-table-scans">Statement Tuning with `EXPLAIN`</InternalLink>.

### 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 <InternalLink path="select-clause#select-historical-data-time-travel">`... AS OF SYSTEM TIME` clause</InternalLink>. Using this, your query returns data as it appeared at a distinct point in the past and will not cause <InternalLink path="architecture/transaction-layer#transaction-conflicts">conflicts</InternalLink> 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.

### Prevent the optimizer from planning full scans

To avoid overloading production clusters, there are several ways to prevent the <InternalLink path="cost-based-optimizer">cost-based-optimizer</InternalLink> from generating query plans with <InternalLink path="ui-sql-dashboard">full table and index scans</InternalLink>.

#### Use index hints to prevent full scans on tables

* To prevent the optimizer from planning a full scan for a specific table, specify the `NO_FULL_SCAN` index hint. For example:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  SELECT * FROM table_name@{NO_FULL_SCAN};
  ```
* To prevent a full scan of a <InternalLink path="partial-indexes">partial index</InternalLink> for a specific table, you must specify `NO_FULL_SCAN` in combination with the index name using <InternalLink path="table-expressions#force-index-selection">`FORCE_INDEX`</InternalLink>. For example:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  SELECT * FROM table_name@{FORCE_INDEX=index_name,NO_FULL_SCAN} WHERE b > 0;
  ```

  This forces a constrained scan of the partial index. If a constrained scan of the partial index is not possible, an error is returned.

#### Disallow query plans that use full scans

When the `disallow_full_table_scans` <InternalLink path="set-vars">session setting</InternalLink> is enabled, the optimizer will not plan full table or index scans on "large" tables (i.e., those with more rows than <InternalLink path="set-vars">`large_full_scan_rows`</InternalLink>).

* At the cluster level, set `disallow_full_table_scans` for some or <InternalLink path="alter-role#set-default-session-variable-values-for-all-users">all users and roles</InternalLink>. For example:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  ALTER ROLE ALL SET disallow_full_table_scans = true;
  ```
* At the application level, add `disallow_full_table_scans` to the connection string using the <InternalLink path="connection-parameters#additional-connection-parameters">`options` parameter</InternalLink>.

If you disable full scans, you can set the <InternalLink path="set-vars">`large_full_scan_rows` session variable</InternalLink> to specify the maximum table size allowed for a full scan. If no alternative plan is possible, the optimizer will return an error.

If you disable full scans, and you provide an <InternalLink path="indexes#selection">index hint</InternalLink>, the optimizer will try to avoid a full scan while also respecting the index hint. If this is not possible, the optimizer will return an error. If you do not provide an index hint and it is not possible to avoid a full scan, the optimizer will return an error, the full scan will be logged, and the `sql.guardrails.full_scan_rejected.count` <InternalLink path="ui-overview-dashboard">metric</InternalLink> will be updated.

#### Disallow query plans that scan more than a number of rows

When the `transaction_rows_read_err` <InternalLink path="set-vars">session setting</InternalLink> is enabled, the <InternalLink path="cost-based-optimizer">optimizer</InternalLink> will not create query plans with scans that exceed the specified row limit. See [Disallow transactions from reading or writing many rows](#disallow-transactions-from-reading-or-writing-many-rows).

### Disallow transactions from reading or writing many rows

* When the `transaction_rows_read_err` <InternalLink path="set-vars">session setting</InternalLink> is enabled, transactions that read more than the specified number of rows will fail. In addition, the <InternalLink path="cost-based-optimizer">optimizer</InternalLink> will not create query plans with scans that exceed the specified row limit. For example, to set a default value for <InternalLink path="alter-role#set-default-session-variable-values-for-all-users">all users</InternalLink> at the cluster level:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  ALTER ROLE ALL SET transaction_rows_read_err = 1000;
  ```
* When the `transaction_rows_written_err` <InternalLink path="set-vars">session setting</InternalLink> is enabled, transactions that write more than the specified number of rows will fail. For example, to set a default value for all users at the cluster level:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  ALTER ROLE ALL SET transaction_rows_written_err = 1000;
  ```

To assess the impact of configuring these session settings, use the corresponding session settings <InternalLink path="set-vars">`transaction_rows_read_log`</InternalLink> and <InternalLink path="set-vars">`transaction_rows_written_log`</InternalLink> to log transactions that read or write the specified number of rows. Transactions are logged to the <InternalLink path="logging#sql_perf">`SQL_PERF`</InternalLink> channel.

<a id="understanding-and-avoiding-transaction-contention" />

## Transaction contention

*Transaction contention* occurs when the following three conditions are met:

* There are multiple concurrent transactions or statements (sent by multiple clients connected simultaneously to a single CockroachDB cluster).
* They operate on table rows with the same index key values (either on <InternalLink path="primary-key">primary keys</InternalLink> or secondary <InternalLink path="indexes">indexes</InternalLink> ).
* At least one of the transactions holds a <InternalLink path="architecture/transaction-layer#write-intents">write intent</InternalLink> or exclusive <InternalLink path="select-for-update#lock-strengths">locking read</InternalLink> on the data.

By default under <InternalLink path="demo-serializable">`SERIALIZABLE`</InternalLink> isolation, transactions that operate on the same index key values (specifically, that operate on the same <InternalLink path="column-families">column family</InternalLink> for a given index key) are strictly serialized to obey transaction isolation semantics. To maintain this isolation, writing transactions <InternalLink path="architecture/transaction-layer#writing">"lock" rows</InternalLink> to prevent interactions with concurrent transactions.

Locking reads issued with <InternalLink path="select-for-update">`SELECT... FOR UPDATE`</InternalLink> perform a similar function by placing an <InternalLink path="select-for-update#lock-strengths">*exclusive lock*</InternalLink> on rows, which can cause contention for both `SERIALIZABLE` and <InternalLink path="read-committed">`READ COMMITTED`</InternalLink> transactions.

<InternalLink path="performance-recipes#indicators-that-your-application-is-experiencing-transaction-contention">When transactions are experiencing contention</InternalLink>, you may observe:

* <InternalLink path="query-behavior-troubleshooting#hanging-or-stuck-queries">Delays in query completion</InternalLink>. This occurs when multiple transactions are trying to write to the same "locked" data at the same time, making a transaction unable to complete. This is also known as *lock contention*.
* <InternalLink path="transactions#automatic-retries">Transaction retries</InternalLink> performed automatically by CockroachDB. This occurs if a transaction cannot be placed into a <InternalLink path="demo-serializable">serializable ordering</InternalLink> among all of the currently-executing transactions. This is also called a *serializability conflict*.
* <InternalLink path="transaction-retry-error-reference">Transaction retry errors</InternalLink>, which are emitted to your client when an automatic retry is not possible or fails. Your application must address transaction retry errors with <InternalLink path="transaction-retry-error-reference#client-side-retry-handling">client-side retry handling</InternalLink>.
* [Cluster hot spots](#hot-spots).

To mitigate these effects, <InternalLink path="performance-best-practices-overview#reduce-transaction-contention">reduce the causes of transaction contention</InternalLink> and [reduce hot spots](#reduce-hot-spots). For further background on transaction contention, see [What is Database Contention, and Why Should You Care?](https://www.cockroachlabs.com/blog/what-is-database-contention).

### Reduce transaction contention

You can reduce the causes of transaction contention:

* Limit the number of affected rows by following <InternalLink path="apply-statement-performance-rules">optimizing queries</InternalLink> (e.g., avoiding full scans, creating secondary indexes, etc.). Not only will transactions run faster, lock fewer rows, and hold locks for a shorter duration, but the chances of <InternalLink path="architecture/transaction-layer#read-refreshing">read invalidation</InternalLink> when the transaction's <InternalLink path="architecture/transaction-layer#timestamp-cache">timestamp is pushed</InternalLink>, due to a conflicting write, are decreased because of a smaller read set (i.e., a smaller number of rows read).
* Break down larger transactions (e.g., <InternalLink path="bulk-delete-data">bulk deletes</InternalLink>) into smaller ones to have transactions hold locks for a shorter duration. For example, use <InternalLink path="common-table-expressions">common table expressions</InternalLink> to group multiple clauses together in a single SQL statement. This will also decrease the likelihood of <InternalLink path="architecture/transaction-layer#timestamp-cache">pushed timestamps</InternalLink>. For instance, as the size of writes (number of rows written) decreases, the chances of the transaction's timestamp getting bumped by concurrent reads decreases.
* Use <InternalLink path="select-for-update">`SELECT FOR UPDATE`</InternalLink> to aggressively lock rows that will later be updated in the transaction. Updates must operate on the most recent version of a row, so a concurrent write to the row will cause a retry error (<InternalLink path="transaction-retry-error-reference#retry_write_too_old">`RETRY_WRITE_TOO_OLD`</InternalLink>). Locking early in the transaction forces concurrent writers to block until the transaction is finished, which prevents the retry error. Note that this locks the rows for the duration of the transaction; whether this is tenable will depend on your workload. For more information, see [When and why to use `SELECT FOR UPDATE` in CockroachDB](https://www.cockroachlabs.com/blog/when-and-why-to-use-select-for-update-in-cockroachdb).
* Use historical reads (<InternalLink path="as-of-system-time">`SELECT... AS OF SYSTEM TIME`</InternalLink>), preferably <InternalLink path="follower-reads#when-to-use-bounded-staleness-reads">bounded staleness reads</InternalLink> or <InternalLink path="follower-reads#run-queries-that-use-exact-staleness-follower-reads">exact staleness with follower reads</InternalLink> when possible to reduce conflicts with other writes. This reduces the likelihood of <InternalLink path="transaction-retry-error-reference#retry_serializable">`RETRY_SERIALIZABLE`</InternalLink> errors as fewer writes will happen at the historical timestamp. More specifically, writes' timestamps are less likely to be pushed by historical reads as they would <InternalLink path="architecture/transaction-layer#transaction-conflicts">when the read has a higher priority level</InternalLink>. Note that if the `AS OF SYSTEM TIME` value is below the closed timestamp, the read cannot be invalidated.
* When replacing values in a row, use <InternalLink path="upsert">`UPSERT`</InternalLink> and specify values for all columns in the inserted rows. This will usually have the best performance under contention, compared to combinations of <InternalLink path="select-clause">`SELECT`</InternalLink>, <InternalLink path="insert">`INSERT`</InternalLink>, and <InternalLink path="update">`UPDATE`</InternalLink>.
* If applicable to your workload, assign <InternalLink path="column-families#default-behavior">column families</InternalLink> and separate columns that are frequently read and written into separate columns. Transactions will operate on disjoint column families and reduce the likelihood of conflicts.
* As a last resort, consider adjusting the <InternalLink path="architecture/transaction-layer#closed-timestamps">closed timestamp interval</InternalLink> using the `kv.closed_timestamp.target_duration` <InternalLink path="cluster-settings">cluster setting</InternalLink> to reduce the likelihood of long-running write transactions having their <InternalLink path="architecture/transaction-layer#timestamp-cache">timestamps pushed</InternalLink>. This setting should be carefully adjusted if **no other mitigations are available** because there can be downstream implications (e.g., historical reads, change data capture feeds, statistics collection, handling zone configurations, etc.). For example, a transaction *A* is forced to refresh (i.e., change its timestamp) due to hitting the maximum <InternalLink path="architecture/transaction-layer#closed-timestamps">*closed timestamp*</InternalLink> interval (closed timestamps enable <InternalLink path="follower-reads#how-stale-follower-reads-work">Follower Reads</InternalLink> and <InternalLink path="change-data-capture-overview">Change Data Capture (CDC)</InternalLink>). This can happen when transaction *A* is a long-running transaction, and there is a write by another transaction to data that *A* has already read.

<Note>
  If you increase the `kv.closed_timestamp.target_duration` setting, it means that you are increasing the amount of time by which the data available in <InternalLink path="follower-reads">Follower Reads</InternalLink> and <InternalLink path="change-data-capture-overview">CDC changefeeds</InternalLink> lags behind the current state of the cluster. In other words, there is a trade-off here: if you absolutely must execute long-running transactions that execute concurrently with other transactions that are writing to the same data, you may have to settle for longer delays on Follower Reads and/or CDC to avoid frequent serialization errors. The anomaly that would be exhibited if these transactions were not retried is called [write skew](https://www.cockroachlabs.com/blog/what-write-skew-looks-like).
</Note>

### Improve transaction performance by sizing and configuring the cluster

To maximize transaction performance, you'll need to maximize the performance of a single <InternalLink path="architecture/glossary">range</InternalLink>. To achieve this, you can apply multiple strategies:

* Minimize the network distance between the <InternalLink path="architecture/overview">replicas of a range</InternalLink>, possibly using <InternalLink path="configure-replication-zones">zone configs</InternalLink> and <InternalLink path="partitioning">partitioning</InternalLink>, or the newer <InternalLink path="multiregion-overview">Multi-region SQL capabilities</InternalLink>.
* Use the fastest <InternalLink path="recommended-production-settings#storage">storage devices</InternalLink> available.
* If the contending transactions operate on different keys within the same range, add <InternalLink path="recommended-production-settings#sizing">more CPU power (more cores) per node</InternalLink>. However, if the transactions all operate on the same key, this may not provide an improvement.

## Hot spots

A *hot spot* is any location on the cluster receiving significantly more requests than another. Hot spots are a symptom of *resource contention* and can create problems as requests increase, including excessive [transaction contention](#transaction-contention).

<InternalLink path="performance-recipes#indicators-that-your-cluster-has-hot-spots">Hot spots occur</InternalLink> when an imbalanced workload access pattern causes significantly more reads and writes on a subset of data. For example:

* Transactions operate on the **same range but different index keys**. These operations are limited by the overall hardware capacity of <InternalLink path="architecture/overview#cockroachdb-architecture-terms">the range leaseholder</InternalLink> node.
* A range is indexed on a column of data that is sequential in nature (e.g., <InternalLink path="sql-faqs">an ordered sequence</InternalLink>, or a series of increasing, non-repeating <InternalLink path="timestamp">`TIMESTAMP`s</InternalLink> ), such that all incoming writes to the range will be the last (or first) item in the index and appended to the end of the range. Because the system is unable to find a split point in the range that evenly divides the traffic, the range cannot benefit from <InternalLink path="load-based-splitting">load-based splitting</InternalLink>. This creates a hot spot at the single range.

Read hot spots can occur if you perform lots of scans of a portion of a table index or a single key.

### Reduce hot spots

* Use index keys with a random distribution of values, so that transactions over different rows are more likely to operate on separate data ranges. See the <InternalLink path="sql-faqs">SQL FAQs</InternalLink> on row IDs for suggestions.
* Place parts of the records that are modified by different transactions in different tables. That is, increase [normalization](https://wikipedia.org/wiki/Database_normalization). However, there are benefits and drawbacks to increasing normalization.
  * Benefits of increasing normalization:
    * Can improve performance for write-heavy workloads. This is because, with increased normalization, a given business fact must be written to one place rather than to multiple places.
    * Allows separate transactions to modify related underlying data without causing [contention](#transaction-contention).
    * Reduces the chance of data inconsistency, since a given business fact must be written only to one place.
    * Reduces or eliminates data redundancy.
    * Uses less disk space.
  * Drawbacks of increasing normalization:
    * Can reduce performance for read-heavy workloads. This is because increasing normalization results in more joins, and can make the SQL more complicated in other ways.
    * More complex data model.
  * In general:
    * Increase normalization for write-intensive and read/write-intensive transactional workloads.
    * Do not increase normalization for read-intensive reporting workloads.
* If the application strictly requires operating on very few different index keys, consider using <InternalLink path="alter-table#split-at">`ALTER... SPLIT AT`</InternalLink> so that each index key can be served by a separate group of nodes in the cluster.
* If you are working with a table that **must** be indexed on sequential keys, consider using <InternalLink path="hash-sharded-indexes">hash-sharded indexes</InternalLink>. For details about the mechanics and performance improvements of hash-sharded indexes in CockroachDB, see the blog post [Hash Sharded Indexes Unlock Linear Scaling for Sequential Workloads](https://www.cockroachlabs.com/blog/hash-sharded-indexes-unlock-linear-scaling-for-sequential-workloads). As part of this, we recommend doing thorough performance testing with and without hash-sharded indexes to see which works best for your application.
* To avoid read hot spots:
  * Increase data distribution, which will allow for more ranges. The hot spot exists because the data being accessed is all co-located in one range.
  * Increase <InternalLink path="recommended-production-settings#load-balancing">load balancing</InternalLink> across more nodes in the same range. Most transactional reads must go to the leaseholder in CockroachDB, which means that opportunities for load balancing over replicas are minimal.

    However, the following features do permit load balancing over replicas:

    * <InternalLink path="global-tables">Global tables</InternalLink>.
    * <InternalLink path="follower-reads">Follower reads</InternalLink> (both the bounded staleness and the exact staleness kinds).

      In these cases, more replicas will help, up to the number of nodes in the cluster.

For a demo on hot spot reduction, watch the following video:

## See also

* If you aren't sure whether SQL query performance needs to be improved on your cluster, see <InternalLink path="query-behavior-troubleshooting#identify-slow-queries">Identify slow queries</InternalLink>.
* For deployment and data location techniques to minimize network latency in multi-region clusters, see <InternalLink path="topology-patterns">Topology Patterns</InternalLink>.
* To read more about SQL best practices, see our [SQL Performance Best Practices](https://www.cockroachlabs.com/blog/sql-performance-best-practices) blog post.