> ## 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.
# EXPERIMENTAL CHANGEFEED FOR
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
{children}
;
};
export const version = "v25.2";
The `EXPERIMENTAL CHANGEFEED FOR` statement is **deprecated** as of v25.2 and will be removed in a future release. For the same functionality, use the `CREATE CHANGEFEED` statement to create a sinkless changefeed.
The `EXPERIMENTAL CHANGEFEED FOR` statement creates a new sinkless changefeed, which streams row-level changes to the client indefinitely until the underlying connection is closed or the changefeed is canceled. A sinkless changefeed can watch one table or multiple tables in a comma-separated list.
For more information, see Change Data Capture Overview.
**This feature is in preview** and subject to change. To share feedback and/or issues, contact [Support](https://support.cockroachlabs.com).
## Required privileges
In v22.2 and above, CockroachDB introduces a new system-level privilege model that provides more fine-grained control over a user's privileges to work with the cluster, including the ability to create and manage changefeeds.
There is continued support for the [legacy privilege model](#legacy-privilege-model) for changefeeds in v23.1, however it **will be removed** in a future release of CockroachDB. We recommend implementing the new privilege model that follows in this section for all changefeeds.
To create a changefeed with `EXPERIMENTAL CHANGEFEED FOR`, a user must have the `SELECT` privilege on the changefeed's source tables.
You can grant a user the `SELECT` privilege to allow them to create sinkless changefeeds on a specific table:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
GRANT SELECT ON TABLE example_table TO user;
```
### Legacy privilege model
Changefeeds can only be created by superusers, i.e., members of the `admin` role. The admin role exists by default with `root` as the member.
## Considerations
* Because sinkless changefeeds return results differently than other SQL statements, they require a dedicated database connection with specific settings around result buffering. In normal operation, CockroachDB improves performance by buffering results server-side before returning them to a client; however, result buffering is automatically turned off for sinkless changefeeds. Also, sinkless changefeeds have different cancellation behavior than other queries: they can only be canceled by closing the underlying connection or issuing a `CANCEL QUERY` statement on a separate connection. Combined, these attributes of changefeeds mean that applications should explicitly create dedicated connections to consume changefeed data, instead of using a connection pool as most client drivers do by default.
This cancellation behavior (i.e., close the underlying connection to cancel the changefeed) also extends to client driver usage; in particular, when a client driver calls `Rows.Close()` after encountering errors for a stream of rows. The pgwire protocol requires that the rows be consumed before the connection is again usable, but in the case of a sinkless changefeed, the rows are never consumed. It is therefore critical that you close the connection, otherwise the application will be blocked forever on `Rows.Close()`.
* In most cases, each version of a row will be emitted once. However, some infrequent conditions (e.g., node failures, network partitions) will cause them to be repeated. This gives our changefeeds an at-least-once delivery guarantee. For more information, see Ordering Guarantees.
* As of v22.1, changefeeds filter out `VIRTUAL` computed columns from events by default. This is a backward-incompatible change. To maintain the changefeed behavior in previous versions where `NULL` values are emitted for virtual computed columns, see the `virtual_columns` option for more detail.
## Synopsis
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> EXPERIMENTAL CHANGEFEED FOR table_name [ WITH (option [= value] [, ...]) ];
```
## Parameters
| Parameter | Description |
| ------------------ | --------------------------------------------------------------------------------------- |
| `table\_name` | The name of the table (or tables in a comma separated list) to create a changefeed for. |
| `option` / `value` | For a list of available options and their values, see [Options](#options) below. |
### Options
| Option | Value | Description | |
| ------------------------------------------------------------- | -------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `confluent\_schema\_registry` | Schema Registry address | The [Schema Registry](https://docs.confluent.io/current/schema-registry/docs#sr) address is required to use `avro`. | |
| `cursor` | Timestamp | Emits any changes after the given timestamp, but does not output the current state of the table first. If `cursor` is not specified, the changefeed starts by doing a consistent scan of all the watched rows and emits the current value, then moves to emitting any changes that happen after the scan. `cursor` can be used to start a new changefeed where a previous changefeed ended. Example: `CURSOR=1536242855577149065.0000000000` | |
| `end\_time` | Timestamp | Indicate the timestamp up to which the changefeed will emit all events and then complete with a `successful` status. Provide a future timestamp to `end\_time` in number of nanoseconds since the [Unix epoch](https://wikipedia.org/wiki/Unix_time). For example, `end\_time="1655402400000000000"`. | |
| | `envelope` | `wrapped` / `enriched` / `bare` / `key\_only` / `row` | `wrapped` the default envelope structure for changefeed messages containing an array of the primary key, a top-level field for the type of message, and the current state of the row (or `null` for deleted rows). Refer to Changefeed Message Envelopes page for more detail on each envelope. Default: `envelope=wrapped`. |
| | `format` | `json` / `avro` / `csv` / `parquet` | Format of the emitted message. `avro`: For mappings of CockroachDB types to Avro types, refer to the table and detail on [Avro limitations](#avro-limitations). **Note:**[`confluent\_schema\_registry`](#confluent-registry) is required with `format=avro`. `csv`: You cannot combine `format=csv` with the `diff` or [`resolved`](#resolved-option) options. Changefeeds use the same CSV format as the `EXPORT` statement. Refer to Export data with changefeeds for details using these options to create a changefeed as an alternative to `EXPORT`. **Note:**[`initial\_scan = 'only'`](#initial-scan) is required with `format=csv`. `parquet`: Cloud storage is the only supported sink. The `topic\_in\_value` option is not compatible with `parquet` format. Default: `format=json`. |
| `initial\_scan` / `no\_initial\_scan` / `initial\_scan\_only` | N/A | Control whether or not an initial scan will occur at the start time of a changefeed. `initial\_scan\_only` will perform an initial scan and then the changefeed job will complete with a `successful` status. You cannot use [`end\_time`](#end-time) and `initial\_scan\_only` simultaneously. If none of these options are specified, an initial scan will occur if there is no [`cursor`](#cursor-option), and will not occur if there is one. This preserves the behavior from previous releases. You cannot specify `initial\_scan` and `no\_initial\_scan` or `no\_initial\_scan and``initial\_scan\_only` simultaneously. Default: `initial\_scan` If used in conjunction with `cursor`, an initial scan will be performed at the cursor timestamp. If no `cursor` is specified, the initial scan is performed at `now()`. | |
| `min\_checkpoint\_frequency` | [Duration string](https://pkg.go.dev/time#ParseDuration) | Controls how often nodes flush their progress to the coordinating changefeed node. Changefeeds will wait for at least the specified duration before a flushing. This can help you control the flush frequency to achieve better throughput. If this is set to `0s`, a node will flush as long as the high-water mark has increased for the ranges that particular node is processing. If a changefeed is resumed, then `min\_checkpoint\_frequency` is the amount of time that changefeed will need to catch up. That is, it could emit duplicate messages during this time. **Note:**[`resolved`](#resolved-option) messages will not be emitted more frequently than the configured `min\_checkpoint\_frequency` (but may be emitted less frequently). Since `min\_checkpoint\_frequency` defaults to `30s`, you **must** configure `min\_checkpoint\_frequency` to at least the desired `resolved` message frequency if you require `resolved` messages more frequently than `30s`. **Default:**`30s` | |
| `mvcc\_timestamp` | N/A | Include the MVCC timestamp for each emitted row in a changefeed. With the `mvcc\_timestamp` option, each emitted row will always contain its MVCC timestamp, even during the changefeed's initial backfill. | |
| | `resolved` | [Duration string](https://pkg.go.dev/time#ParseDuration) | Emit resolved timestamps for the changefeed. Resolved timestamps do not emit until all ranges in the changefeed have progressed to a specific point in time. Set a minimum amount of time that the changefeed's high-water mark (overall resolved timestamp) must advance by before another resolved timestamp is emitted. Example: `resolved='10s'`. This option will **only** emit a resolved timestamp if the timestamp has advanced (and by at least the optional duration, if set). If a duration is unspecified, all resolved timestamps are emitted as the high-water mark advances. **Note:** If you set `resolved` lower than `30s`, then you **must** also set the [`min\_checkpoint\_frequency`](#min-checkpoint-frequency) option to at minimum the same value as `resolved`, because `resolved` messages may be emitted less frequently than `min\_checkpoint\_frequency`, but cannot be emitted more frequently. Refer to Resolved messages for more detail. |
| `split\_column\_families` | N/A | Target a table with multiple columns families. Emit messages for each column family in the target table. Each message will include the label: `table.family`. | |
| | `updated` | N/A | Include updated timestamps with each row. |
| `virtual\_columns` | `STRING` | Changefeeds omit virtual computed columns from emitted messages by default. To maintain the behavior of previous CockroachDB versions where the changefeed would emit `NULL` values for virtual computed columns, set `virtual\_columns = "null"` when you start a changefeed. You may also define `virtual\_columns = "omitted"`, though this is already the default behavior for v22.1+. If you do not set `"omitted"` on a table with virtual computed columns when you create a changefeed, you will receive a warning that changefeeds will filter out virtual computed values. **Default:**`"omitted"` | |
#### Avro limitations
Creating a changefeed using Avro is available in Core and Enterprise changefeeds with the `confluent_schema_registry` option.
Below are clarifications for particular SQL types and values for Avro changefeeds:
* Decimals must have precision specified.
* `BYTES` (or its aliases `BYTEA` and `BLOB` ) are often used to store machine-readable data. When you stream these types through a changefeed with `format=avro`, CockroachDB does not encode or change the data. However, Avro clients can often include escape sequences to present the data in a printable format, which can interfere with deserialization. A potential solution is to hex-encode `BYTES` values when initially inserting them into CockroachDB. This will ensure that Avro clients can consistently decode the hexadecimal. Note that hex-encoding values at insertion will increase record size.
* `BIT` and `VARBIT` types are encoded as arrays of 64-bit integers.
For efficiency, CockroachDB encodes `BIT` and `VARBIT` bitfield types as arrays of 64-bit integers. That is, [base-2 (binary format)](https://wikipedia.org/wiki/Binary_number#Conversion_to_and_from_other_numeral_systems) `BIT` and `VARBIT` data types are converted to base 10 and stored in arrays. Encoding in CockroachDB is [big-endian](https://wikipedia.org/wiki/Endianness), therefore the last value may have many trailing zeroes. For this reason, the first value of each array is the number of bits that are used in the last value of the array.
For instance, if the bitfield is 129 bits long, there will be 4 integers in the array. The first integer will be `1`; representing the number of bits in the last value, the second integer will be the first 64 bits, the third integer will be bits 65–128, and the last integer will either be `0` or `9223372036854775808` (i.e., the integer with only the first bit set, or `1000000000000000000000000000000000000000000000000000000000000000` when base 2).
This example is base-10 encoded into an array as follows:
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{"array": [1, , , 0 or 9223372036854775808]}
```
For downstream processing, it is necessary to base-2 encode every element in the array (except for the first element). The first number in the array gives you the number of bits to take from the last base-2 number — that is, the most significant bits. So, in the example above this would be `1`. Finally, all the base-2 numbers can be appended together, which will result in the original number of bits, 129.
In a different example of this process where the bitfield is 136 bits long, the array would be similar to the following when base-10 encoded:
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{"array": [8, 18293058736425533439, 18446744073709551615, 13690942867206307840]}
```
To then work with this data, you would convert each of the elements in the array to base-2 numbers, besides the first element. For the above array, this would convert to:
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
[8, 1111110111011011111111111111111111111111111111111111111111111111, 1111111111111111111111111111111111111111111111111111111111111111, 1011111000000000000000000000000000000000000000000000000000000000]
```
Next, you use the first element in the array to take the number of bits from the last base-2 element, `10111110`. Finally, you append each of the base-2 numbers together — in the above array, the second, third, and truncated last element. This results in 136 bits, the original number of bits.
* A changefeed in Avro format will not be able to serialize user-defined composite (tuple) types.
## Examples
### Create a changefeed
To start a changefeed:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
EXPERIMENTAL CHANGEFEED FOR cdc_test;
```
In the terminal where the sinkless changefeed is streaming, the output will appear:
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
table,key,value
cdc_test,[0],"{""after"": {""a"": 0}}"
```
For step-by-step guidance on creating a sinkless changefeed, see the Changefeed Examples page.
### Create a changefeed with Avro
To start a changefeed in Avro format:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
EXPERIMENTAL CHANGEFEED FOR cdc_test WITH format = avro, confluent_schema_registry = 'http://localhost:8081';
```
In the terminal where the sinkless changefeed is streaming, the output will appear:
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
table,key,value
cdc_test,\000\000\000\000\001\002\000,\000\000\000\000\002\002\002\000
```
For step-by-step guidance on creating a sinkless changefeed with Avro, see the Changefeed Examples page.
### Create a changefeed on a table with column families
To create a changefeed on a table with column families, use the `FAMILY` keyword for a specific column family:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
EXPERIMENTAL CHANGEFEED FOR TABLE cdc_test FAMILY f1;
```
To create a changefeed on a table and output changes for each column family, use the `split_column_families` option:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
EXPERIMENTAL CHANGEFEED FOR TABLE cdc_test WITH split_column_families;
```
For step-by-step guidance creating a sinkless changefeed on a table with multiple column families, see the Changefeed Examples page.
## See also
* Change Data Capture Overview
* SQL Statements