Create and Configure Changefeeds

On this page Carat arrow pointing down
Warning:
CockroachDB v22.2 is no longer supported as of June 5, 2024. For more details, refer to the Release Support Policy.

Core and Enterprise changefeeds offer different levels of configurability. Enterprise changefeeds allow for active changefeed jobs to be paused, resumed, and canceled.

Both Core and Enterprise changefeeds require that you enable rangefeeds before creating a changefeed. See the Enable rangefeeds section for further detail.

Considerations

  • It is necessary to enable rangefeeds for changefeeds to work.
  • If you require resolved message frequency under 30s, then you must set the min_checkpoint_frequency option to at least the desired resolved frequency.
  • Many DDL queries (including TRUNCATE, DROP TABLE, and queries that add a column family) will cause errors on a changefeed watching the affected tables. You will need to start a new changefeed. If a table is truncated that a changefeed with on_error='pause' is watching, you will also need to start a new changefeed. See change data capture Known Limitations for more detail.
  • Partial or intermittent sink unavailability may impact changefeed stability. If a sink is unavailable, messages can't send, which means that a changefeed's high-water mark timestamp is at risk of falling behind the cluster's garbage collection window. Throughput and latency can be affected once the sink is available again. However, ordering guarantees will still hold for as long as a changefeed remains active.
  • When an IMPORT INTO statement is run, any current changefeed jobs targeting that table will fail.
  • 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.

When creating a changefeed, it's important to consider the number of changefeeds versus the number of tables to include in a single changefeed:

  • Changefeeds each have their own memory overhead, so every running changefeed will increase total memory usage.
  • Creating a single changefeed that will watch hundreds of tables can affect the performance of a changefeed by introducing coupling, where the performance of a watched table affects the performance of the changefeed watching it. For example, any schema change on any of the tables will affect the entire changefeed's performance.

To watch multiple tables, we recommend creating a changefeed with a comma-separated list of tables. However, we do not recommend creating a single changefeed for watching hundreds of tables.

We suggest monitoring the performance of your changefeeds. See Monitor and Debug Changefeeds for more detail.

Enable rangefeeds

Changefeeds connect to a long-lived request (i.e., a rangefeed), which pushes changes as they happen. This reduces the latency of row changes, as well as reduces transaction restarts on tables being watched by a changefeed for some workloads.

Rangefeeds must be enabled for a changefeed to work. To enable the cluster setting:

icon/buttons/copy
> SET CLUSTER SETTING kv.rangefeed.enabled = true;
Note:

If you are working on a CockroachDB Serverless cluster, the kv.rangefeed.enabled cluster setting is enabled by default.

Any created changefeeds will error until this setting is enabled. Note that enabling rangefeeds currently has a small performance cost (about a 5-10% increase in latencies), whether or not the rangefeed is being used in a changefeed.

The kv.closed_timestamp.target_duration cluster setting can be used with changefeeds. Resolved timestamps will always be behind by at least the duration configured by this setting. However, decreasing the duration leads to more transaction restarts in your cluster, which can affect performance.

The following Enterprise and Core sections outline how to create and configure each type of changefeed:

Configure a changefeed

An Enterprise changefeed streams row-level changes in a configurable format to a configurable sink (i.e., Kafka or a cloud storage sink). You can create, pause, resume, and cancel an Enterprise changefeed. For a step-by-step example connecting to a specific sink, see the Changefeed Examples page.

Create

To create an Enterprise changefeed:

icon/buttons/copy
CREATE CHANGEFEED FOR TABLE table_name, table_name2 INTO '{scheme}://{host}:{port}?{query_parameters}';
Note:

Parameters should always be URI-encoded before they are included the changefeed's URI, as they often contain special characters. Use Javascript's encodeURIComponent function or Go language's url.QueryEscape function to URI-encode the parameters. Other languages provide similar functions to URI-encode special characters.

When you create a changefeed without specifying a sink, CockroachDB sends the changefeed events to the SQL client. Consider the following regarding the display format in your SQL client:

  • If you do not define a display format, the CockroachDB SQL client will automatically use ndjson format.
  • If you specify a display format, the client will use that format (e.g., --format=csv).
  • If you set the client display format to ndjson and set the changefeed format to csv, you'll receive JSON format with CSV nested inside.
  • If you set the client display format to csv and set the changefeed format to json, you'll receive a comma-separated list of JSON values.

For more information, see CREATE CHANGEFEED.

Pause

To pause an Enterprise changefeed:

icon/buttons/copy
PAUSE JOB job_id;

For more information, see PAUSE JOB.

Resume

To resume a paused Enterprise changefeed:

icon/buttons/copy
RESUME JOB job_id;

For more information, see RESUME JOB.

Cancel

To cancel an Enterprise changefeed:

icon/buttons/copy
CANCEL JOB job_id;

For more information, see CANCEL JOB.

Modify a changefeed

To modify an Enterprise changefeed, pause the job and then use:

ALTER CHANGEFEED job_id {ADD table DROP table SET option UNSET option};

You can add new table targets, remove them, set new changefeed options, and unset them.

For more information, see ALTER CHANGEFEED.

Configuring all changefeeds

It is useful to be able to pause all running changefeeds during troubleshooting, testing, or when a decrease in CPU load is needed.

To pause all running changefeeds:

icon/buttons/copy
PAUSE JOBS (WITH x AS (SHOW CHANGEFEED JOBS) SELECT job_id FROM x WHERE status = ('running'));

This will change the status for each of the running changefeeds to paused, which can be verified with SHOW CHANGEFEED JOBS.

To resume all running changefeeds:

icon/buttons/copy
RESUME JOBS (WITH x AS (SHOW CHANGEFEED JOBS) SELECT job_id FROM x WHERE status = ('paused'));

This will resume the changefeeds and update the status for each of the changefeeds to running.

Create a changefeed

A core changefeed streams row-level changes to the client indefinitely until the underlying connection is closed or the changefeed is canceled.

To create a core changefeed:

icon/buttons/copy
EXPERIMENTAL CHANGEFEED FOR table_name;

For more information, see EXPERIMENTAL CHANGEFEED FOR.

See also


Yes No
On this page

Yes No