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

# Follower Reads

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>;
};

A *follower read* is performed on the <InternalLink path="architecture/overview">nearest replica</InternalLink> relative to the SQL gateway that is executing the SQL statement regardless of the replica's <InternalLink path="architecture/overview">leaseholder</InternalLink> status. Using the nearest replica can reduce read latencies and increase throughput. Applications in <InternalLink path="topology-follower-reads">multi-region deployments</InternalLink> especially can use follower reads to get improved performance.

## Follower read types

A *strong follower read* is a read taken from a <InternalLink path="global-tables">Global</InternalLink> table. Such tables are optimized for low-latency reads from every region in the database. The tradeoff is that writes will incur higher latencies from any given region, since writes have to be replicated across every region to make the global low-latency reads possible. For more information about global tables, including troubleshooting information, see <InternalLink path="global-tables">Global Tables</InternalLink>.

A [*stale follower read*](#stale-follower-reads) is a historical read taken from the nearest replica. You should use stale follower reads only when your application can tolerate reading stale data, since the results of stale follower reads may not reflect the latest writes against the tables you are querying.

The following table summarizes the read types and how to accomplish them.

|                       | Strong Reads               | Stale Reads                        |
| --------------------- | -------------------------- | ---------------------------------- |
| Only From Leaseholder | `SELECT`                   | N/A                                |
| From Nearest Replica  | `SELECT` on `GLOBAL` table | `SELECT` with `AS OF SYSTEM TIME ` |

## Stale follower reads

CockroachDB provides the following types of stale follower reads:

* *Exact staleness read*: A historical read as of a static, user-provided timestamp. See [Exact staleness reads](#exact-staleness-reads).
* *Bounded staleness read*: A historical read that uses a dynamic, system-determined timestamp to minimize staleness while being more tolerant to replication lag than an exact staleness read. See [Bounded staleness reads](#bounded-staleness-reads).

<Note>
  Stale follower reads are always served from a consistent view; CockroachDB does not allow a historical read to view
  uncommitted data.
</Note>

### Exact staleness reads

An *exact staleness read* is a historical read as of a static, user-provided timestamp.

For requirements and limitations, see [Exact staleness reads and long-running writes](#exact-staleness-reads-and-long-running-writes) and [Exact staleness read timestamps must be far enough in the past](#exact-staleness-read-timestamps-must-be-far-enough-in-the-past).

#### When to use exact staleness reads

Use exact staleness follower reads when you:

* Need multi-statement reads inside <InternalLink path="transactions">transactions</InternalLink>.
* Can tolerate reading older data (at least 4.2 seconds in the past), to reduce the chance that the historical query timestamp is not quite old enough to prevent blocking on a conflicting write and thus being able to be served by a local replica.
* Do not need the increase in availability provided by [bounded staleness reads](#bounded-staleness-reads) in the face of <InternalLink path="cluster-setup-troubleshooting#network-partition">network partitions</InternalLink> or other failures.
* Need a read that is slightly cheaper to perform than a [bounded staleness read](#bounded-staleness-reads), because exact staleness reads don't need to dynamically compute the query timestamp.

#### Run queries that use exact staleness follower reads

Any <InternalLink path="select-clause">`SELECT` statement</InternalLink> with an appropriate <InternalLink path="as-of-system-time">`AS OF SYSTEM TIME`</InternalLink> value is an exact staleness follower read. You can use the convenience <InternalLink path="functions-and-operators#date-and-time-functions">function</InternalLink> `follower_read_timestamp()`, which returns a <InternalLink path="timestamp">`TIMESTAMP`</InternalLink> that provides a high probability of being served locally while not [blocking on conflicting writes](#exact-staleness-reads-and-long-running-writes).

Use this function in an `AS OF SYSTEM TIME` statement as follows:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT ... FROM ... AS OF SYSTEM TIME follower_read_timestamp();
```

To see the current value of the follower read timestamp, execute the following query:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT now() - follower_read_timestamp();
```

#### Exact staleness follower reads in read-only transactions

You can set the <InternalLink path="as-of-system-time">`AS OF SYSTEM TIME`</InternalLink> clause's value for all operations in a read-only <InternalLink path="transactions">transaction</InternalLink>:

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

SET TRANSACTION AS OF SYSTEM TIME follower_read_timestamp();
SELECT ...
SELECT ...

COMMIT;
```

Follower reads are "read-only" operations; you **cannot** use them in read-write transactions.

<Tip>
  Using the <InternalLink path="set-transaction#use-the-as-of-system-time-option">`SET TRANSACTION`</InternalLink> statement as shown in the preceding example will make it easier to use exact staleness follower reads from <InternalLink path="install-client-drivers">drivers and ORMs</InternalLink>.

  To set `AS OF SYSTEM TIME follower_read_timestamp()` on all implicit and explicit read-only transactions by default, use one of the following options:

  * Set the `default_transaction_use_follower_reads` <InternalLink path="set-vars">session variable</InternalLink> to `on`. When `default_transaction_use_follower_reads=on`, all read-only transactions use exact staleness follower reads.
  * Execute the `SET SESSION CHARACTERISTICS AS TRANSACTION AS OF SYSTEM TIME follower_read_timestamp()` <InternalLink path="set-vars#special-syntax-cases">SQL statement</InternalLink>. This has the same effect as setting the session variable as shown above.

  You can set `default_transaction_use_follower_reads` on a per-role basis; for instructions, see <InternalLink path="alter-role#set-default-session-variable-values-for-a-role">Set default session variable values for a role</InternalLink>.
</Tip>

### Bounded staleness reads

A *bounded staleness read* is a historical read that uses a dynamic, system-determined timestamp to minimize staleness while being more tolerant to replication lag than an exact staleness read. Bounded staleness reads also help increase system availability, since they provide the ability to serve reads from local replicas even in the presence of network partitions or other failures that prevent the SQL gateway from communicating with the leaseholder.

#### When to use bounded staleness reads

Use bounded staleness follower reads when you:

* Need minimally stale reads from the nearest replica without blocking on <InternalLink path="performance-best-practices-overview#transaction-contention">conflicting transactions</InternalLink>. This is possible because the historical timestamp is chosen dynamically and the least stale timestamp that can be served locally without blocking is used.
* Can confine the read to a single statement that meets the [bounded staleness limitations](#bounded-staleness-read-limitations).
* Need higher availability than is provided by [exact staleness reads](#exact-staleness-reads). Specifically, what we mean by availability in this context is:
  * The ability to serve a read with low latency from a local replica rather than a leaseholder.
  * The ability to serve reads from local replicas even in the presence of a network partition or other failure event that prevents the SQL gateway from communicating with the leaseholder. Once a replica begins serving follower reads at a timestamp, it will always continue to serve follower reads at that timestamp. Even if the replica becomes completely partitioned away from the rest of its range, it will continue to stay available for (increasingly) stale reads.

#### Run queries that use bounded staleness follower reads

To get a bounded staleness read, use one of the following built-in functions:

| Name                                                 | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| ---------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `with\_min\_timestamp(TIMESTAMPTZ, [nearest\_only])` | Defines a minimum <InternalLink path="timestamp">timestamp</InternalLink> at which to perform the <InternalLink path="follower-reads#bounded-staleness-reads">bounded staleness read</InternalLink>. The actual timestamp of the read may be equal to or later than the provided timestamp, but cannot be before the provided timestamp. This is useful to request a read from nearby followers, if possible, while enforcing causality between an operation at some point in time and any dependent reads. This function accepts an optional `nearest\_only` argument that will error if the reads cannot be serviced from a nearby replica. |
| `with\_max\_staleness(INTERVAL, [nearest\_only])`    | Defines a maximum staleness interval with which to perform the <InternalLink path="follower-reads#bounded-staleness-reads">bounded staleness read</InternalLink>. The timestamp of the read can be at most this stale with respect to the current time. This is useful to request a read from nearby followers, if possible, while placing some limit on how stale results can be. Note that `with\_max\_staleness(INTERVAL)` is equivalent to `with\_min\_timestamp(now() - INTERVAL)`. This function accepts an optional `nearest\_only` argument that will error if the reads cannot be serviced from a nearby replica.                    |

This example performs a bounded staleness follower read against a <InternalLink path="cockroach-demo">demo cluster</InternalLink> with the <InternalLink path="movr">MovR dataset</InternalLink>.

1. Start the demo cluster with 3 nodes:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   cockroach demo --nodes=3
   ```

2. Issue a single-statement point query to <InternalLink path="selection-queries">select</InternalLink> a single row from a table at a historical <InternalLink path="timestamp">timestamp</InternalLink> by passing the output of the `with_max_staleness()` <InternalLink path="functions-and-operators">function</InternalLink> to the <InternalLink path="as-of-system-time">`AS OF SYSTEM TIME`</InternalLink> clause:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   SELECT code FROM promo_codes AS OF SYSTEM TIME with_max_staleness('10s') where code = '0_explain_theory_something';
   ```

   ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
                code
   ------------------------------
     0_explain_theory_something
   (1 row)
   ```

   The query returns successfully.

   If it had failed with the following error message, you would need to [troubleshoot your query to ensure it meets the conditions required for bounded staleness reads](#bounded-staleness-read-limitations).

   ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   ERROR: unimplemented: cannot use bounded staleness for queries that may touch more than one row or require an index join
   SQLSTATE: 0A000
   HINT: You have attempted to use a feature that is not yet implemented.
   See: https://go.crdb.dev/issue-v/67562/v23.2
   ```

   You can verify using <InternalLink path="explain">`EXPLAIN`</InternalLink> that the reason this query was able to perform a bounded staleness read is that it performed a point lookup from a single row:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   EXPLAIN SELECT code FROM promo_codes AS OF SYSTEM TIME with_max_staleness('10s') where code = '0_explain_theory_something';
   ```

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

     • scan
       estimated row count: 1 (0.10% of the table; stats collected 4 minutes ago)
       table: promo_codes@primary
       spans: [/'0_explain_theory_something' - /'0_explain_theory_something']
   (7 rows)
   ```

### Verify that CockroachDB is performing follower reads

To verify that a cluster is performing follower reads, go to the <InternalLink path="ui-custom-chart-debug-page">**Custom Chart** debug page in the DB Console</InternalLink> and add the metric `follower_reads.success_count` to the time-series graph. The number of follower reads performed by your cluster will be shown.

To verify that a specific query uses a follower read, use <InternalLink path="explain-analyze">`EXPLAIN ANALYZE`</InternalLink> to see the statement plan. For example, to test the [preceding example](#run-queries-that-use-bounded-staleness-follower-reads):

1. Use the `\demo ls` <InternalLink path="cockroach-demo#commands">shell command</InternalLink> to list the connection parameters for all nodes:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   \demo ls
   ```

   The output will list the connection parameters for each node:

   ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   node 3:
   ...
      (sql)      postgresql://demo:demo37199@127.0.0.1:26259/movr?options=-ccluster%3Ddemoapp&sslmode=equire&sslrootcert=%2FUsers%2Fuser%2F.cockroach-demo%2Fca.crt
   ```

2. Open a new terminal and open the SQL shell on a non-leaseholder node. For example:

   Identify the leaseholder node:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   SELECT lease_holder FROM [SHOW RANGES FROM TABLE promo_codes WITH DETAILS];
   ```

   ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     lease_holder
   ----------------
                1
   ```

   Connect to a node other than node 1 (such as node 3, using the preceding output):

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   cockroach sql --url='postgresql://demo:demo37199@127.0.0.1:26259/movr?options=-ccluster%3Ddemoapp&sslmode=equire&sslrootcert=%2FUsers%2Fuser%2F.cockroach-demo%2Fca.crt'
   ```

3. Issue the <InternalLink path="explain-analyze">`EXPLAIN ANALYZE`</InternalLink> statement on the non-leaseholder node:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   EXPLAIN ANALYZE SELECT code FROM promo_codes AS OF SYSTEM TIME with_max_staleness('10s') where code = '0_explain_theory_something';
   ```

   ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
                                         info
   --------------------------------------------------------------------------------
     planning time: 5ms
     execution time: 1ms
     distribution: local
     ...
     historical: AS OF SYSTEM TIME 2024-11-19 21:04:00.829742 (bounded staleness)

     • scan
       sql nodes: n3
       kv nodes: n3
       regions: us-east1
       used follower read
       ...
       table: promo_codes@promo_codes_pkey
       spans: [/'0_explain_theory_something' - /'0_explain_theory_something']
   ```

   In the preceding output, `used follower read` indicates that the read was served by the follower replica. `historical: AS OF SYSTEM TIME... (bounded staleness)` shows that it was a historical, bounded staleness read.

### How stale follower reads work

Each CockroachDB range tracks a property called its <InternalLink path="architecture/transaction-layer#closed-timestamps">*closed timestamp*</InternalLink>, which means that no new writes can ever be introduced at or below that timestamp. The closed timestamp is advanced continuously on the leaseholder, and lags the current time by some target interval. As the closed timestamp is advanced, notifications are sent to each follower. If a range receives a write at a timestamp less than or equal to its closed timestamp, the write is forced to change its timestamp, which might result in a <InternalLink path="transaction-retry-error-reference">transaction retry error</InternalLink>.

With follower reads, any replica in a range can serve a read for a key as long as the time at which the operation is performed (i.e., the <InternalLink path="as-of-system-time">`AS OF SYSTEM TIME`</InternalLink> value) is less than or equal to the range's closed timestamp.

When a gateway node in a cluster receives a request to read a key with a sufficiently old <InternalLink path="as-of-system-time">`AS OF SYSTEM TIME`</InternalLink> value, it forwards the request to the closest node that contains a replica of the data—whether it be a follower or the leaseholder.

For further details, see [An Epic Read on Follower Reads](https://www.cockroachlabs.com/blog/follower-reads-stale-data).

### Known limitations

##### Exact staleness reads and long-running writes

Long-running write transactions will create <InternalLink path="architecture/transaction-layer#write-intents">write intents</InternalLink> with a timestamp near when the transaction began. When an exact staleness follower read encounters a write intent, it will often end up in a <InternalLink path="architecture/transaction-layer#txnwaitqueue">"transaction wait queue"</InternalLink>, waiting for the operation to complete; however, this runs counter to the benefit exact staleness reads provide.

To counteract this, you can issue all follower reads in explicit <InternalLink path="transactions#transaction-priorities">transactions set with `HIGH` priority</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BEGIN PRIORITY HIGH AS OF SYSTEM TIME follower_read_timestamp();
SELECT ...
SELECT ...
COMMIT;
```

##### Exact staleness read timestamps must be far enough in the past

If an exact staleness read is not using an <InternalLink path="as-of-system-time">`AS OF SYSTEM TIME`</InternalLink> value far enough in the past, CockroachDB cannot perform a follower read. Instead, the read must access the <InternalLink path="architecture/overview">leaseholder replica</InternalLink>. This adds network latency if the leaseholder is not the closest replica to the gateway node. Most users will <InternalLink path="follower-reads#run-queries-that-use-exact-staleness-follower-reads">use the `follower_read_timestamp()` function</InternalLink> to get a timestamp far enough in the past that there is a high probability of getting a follower read.

##### Bounded staleness read limitations

Bounded staleness reads have the following limitations:

* They must be used in a <InternalLink path="transactions#individual-statements">single-statement (aka implicit) transaction</InternalLink>.
* They must read from a single row.
* They must not require an <InternalLink path="indexes">index</InternalLink> <InternalLink path="joins">join</InternalLink>. In other words, the index used by the read query must be either a <InternalLink path="primary-key">primary</InternalLink> <InternalLink path="indexes">index</InternalLink>, or some other index that covers the entire query by <InternalLink path="create-index#store-columns">`STORING`</InternalLink> all columns.

For example, let's look at a read query that cannot be served as a bounded staleness read. We will use a <InternalLink path="cockroach-demo">demo cluster</InternalLink>, which automatically loads the <InternalLink path="movr">MovR dataset</InternalLink>.

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
cockroach demo
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT code FROM promo_codes AS OF SYSTEM TIME with_max_staleness('10s') LIMIT 1;
ERROR: unimplemented: cannot use bounded staleness for queries that may touch more than one row or require an index join
SQLSTATE: 0A000
HINT: You have attempted to use a feature that is not yet implemented.
See: https://go.crdb.dev/issue-v/67562/v23.2
```

As noted by the error message, this query cannot be served as a bounded staleness read because in this case it would touch more than one row. Even though we used a <InternalLink path="limit-offset">`LIMIT 1` clause</InternalLink>, the query would still have to touch more than one row in order to filter out the additional results.

We can verify that more than one row would be touched by issuing <InternalLink path="explain">`EXPLAIN`</InternalLink> on the same query, but without the <InternalLink path="as-of-system-time">`AS OF SYSTEM TIME`</InternalLink> clause:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
EXPLAIN SELECT code FROM promo_codes LIMIT 5;
```

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

  • scan
    estimated row count: 1 (0.10% of the table; stats collected 1 minute ago)
    table: promo_codes@primary
    spans: LIMITED SCAN
    limit: 1
(8 rows)
```

The output verifies that this query performs a scan of the primary <InternalLink path="indexes">index</InternalLink> on the `promo_codes` table, which is why it cannot be used for a bounded staleness read.

For an example showing how to successfully perform a bounded staleness read, see <InternalLink path="follower-reads#run-queries-that-use-bounded-staleness-follower-reads">Run queries that use bounded staleness follower reads</InternalLink>.

### Video demo

For a demo of how to use follower reads to improve performance, watch the following video:

## See also

* <InternalLink path="topology-follower-reads">Follower Reads Topology</InternalLink>
* <InternalLink path="cluster-settings">Cluster Settings Overview</InternalLink>
* <InternalLink path="load-based-splitting">Load-Based Splitting</InternalLink>
* <InternalLink path="ui-network-latency-page">Network Latency Page</InternalLink>