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

# Delete Data

This page has instructions for deleting rows of data from CockroachDB, using the [`DELETE`](https://docs.cockroachlabs.com/docs/v24.1/delete) [SQL statement](https://docs.cockroachlabs.com/docs/v24.1/sql-statements.md).

## Before you begin

Before reading this page, do the following:

* [Create a CockroachDB Standard cluster](https://docs.cockroachlabs.com/docs/cockroachcloud/quickstart.md) or [start a local cluster](https://docs.cockroachlabs.com/docs/cockroachcloud/quickstart.md?filters=local).
* [Install a Driver or ORM Framework](https://docs.cockroachlabs.com/docs/v24.1/install-client-drivers.md).
* [Connect to the database](https://docs.cockroachlabs.com/docs/v24.1/connect-to-the-database.md).
* [Create a database schema](https://docs.cockroachlabs.com/docs/v24.1/schema-design-overview.md).
* [Insert data](https://docs.cockroachlabs.com/docs/v24.1/insert-data.md) that you now want to delete.

  In the examples on this page, we use sample [`movr`](https://docs.cockroachlabs.com/docs/v24.1/movr) data imported with the [`cockroach workload` command](https://docs.cockroachlabs.com/docs/v24.1/cockroach-workload).

## Use `DELETE`

To delete rows in a table, use a [`DELETE` statement](https://docs.cockroachlabs.com/docs/v24.1/update) with a `WHERE` clause that filters on the columns that identify the rows that you want to delete.

### SQL syntax

In SQL, `DELETE` statements generally take the following form:

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
DELETE FROM {table} WHERE {filter_column} {comparison_operator} {filter_value}
```

Where:

* `{table}` is a table with rows that you want to delete.
* `{filter_column}` is the column to filter on.
* `{comparison_operator}` is a [comparison operator](https://docs.cockroachlabs.com/docs/v24.1/functions-and-operators.md#operators) that resolves to `TRUE` or `FALSE` (e.g., `=` ).
* `{filter_value}` is the matching value for the filter.

> [!TIP]
>
> For detailed reference documentation on the `DELETE` statement, including additional examples, see the [`DELETE` syntax page](https://docs.cockroachlabs.com/docs/v24.1/delete).

### Best practices

Here are some best practices to follow when deleting rows:

* Limit the number of `DELETE` statements that you execute. It's more efficient to delete multiple rows with a single statement than to execute multiple `DELETE` statements that each delete a single row.
* Always specify a `WHERE` clause in `DELETE` queries. If no `WHERE` clause is specified, CockroachDB will delete all of the rows in the specified table.
* To delete all of the rows in a table, use a [`TRUNCATE` statement](https://docs.cockroachlabs.com/docs/v24.1/truncate) instead of a `DELETE` statement.
* To delete a large number of rows (i.e., tens of thousands of rows or more), use a [batch-delete loop](https://docs.cockroachlabs.com/docs/v24.1/bulk-delete-data.md#batch-delete-on-an-indexed-column).
* When executing `DELETE` statements from an application, make sure that you wrap the SQL-executing functions in [a retry loop that handles transaction errors](https://docs.cockroachlabs.com/docs/v24.1/query-behavior-troubleshooting.md#transaction-retry-errors) that can occur under [contention](https://docs.cockroachlabs.com/docs/v24.1/performance-best-practices-overview.md#transaction-contention).
* Review the [performance considerations below](#performance-considerations).

### Examples

#### Delete rows filtered on a non-unique column

Suppose that you want to delete the vehicle location history data recorded during a specific hour of a specific day. To delete all of the rows in the `vehicle_location_histories` table where the `timestamp` is between two [`TIMESTAMP`](https://docs.cockroachlabs.com/docs/v24.1/timestamp) values:

> [tab] SQL
>
> ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DELETE FROM vehicle_location_histories WHERE timestamp BETWEEN '2021-03-17 14:00:00' AND '2021-03-17 15:00:00';
> ```
>
> For more information about how to use the built-in SQL client, see the [`cockroach sql`](https://docs.cockroachlabs.com/docs/v24.1/cockroach-sql) reference docs.

> [tab] Go (lib/pq)
>
> ```go theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> // 'db' is an open database connection
>
> tsOne := "2021-03-17 14:00:00"
> tsTwo := "2021-03-17 15:00:00"
>
> if _, err := db.Exec("DELETE FROM vehicle_location_histories WHERE timestamp BETWEEN $1 AND $2", tsOne, tsTwo); err != nil {
>   return err
> }
> return nil
> ```

> [tab] Java (JDBC)
>
> ```java theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> // ds is an org.postgresql.ds.PGSimpleDataSource
>
> String tsOne = "2021-03-17 14:00:00";
> String tsTwo = "2021-03-17 15:00:00";
>
> try (Connection connection = ds.getConnection()) {
>     PreparedStatement p = connection.prepareStatement("DELETE FROM vehicle_location_histories WHERE timestamp BETWEEN ? AND ?");
>     p.setString(1, tsOne);
>     p.setString(2, tsTwo);
>     p.executeUpdate();
>
> } catch (SQLException e) {
>     System.out.printf("sql state = [%s]\ncause = [%s]\nmessage = [%s]\n", e.getSQLState(), e.getCause(),
>             e.getMessage());
> }
> ```

> [tab] Python (psycopg2)
>
> ```python theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> # conn is a psycopg2 connection
>
> tsOne = '2021-03-17 14:00:00'
> tsTwo = '2021-03-17 15:00:00'
>
> with conn.cursor() as cur:
>     cur.execute(
>         "DELETE FROM vehicle_location_histories WHERE timestamp BETWEEN %s AND %s", (tsOne, tsTwo))
> ```

> [!TIP]
>
> If the `WHERE` clause evaluates to `TRUE` for a large number of rows (i.e., tens of thousands of rows), use a [batch-delete loop](https://docs.cockroachlabs.com/docs/v24.1/bulk-delete-data.md#batch-delete-on-an-indexed-column) instead of executing a simple `DELETE` query.

#### Delete rows filtered on a unique column

Suppose that you want to delete the promo code data for a specific set of codes. To delete the rows in the `promo_codes` table where the `code` matches a string in a set of string values:

> [tab] SQL
>
> ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DELETE from promo_codes WHERE code IN ('0_explain_theory_something', '100_address_garden_certain', '1000_do_write_words');
> ```
>
> For more information about how to use the built-in SQL client, see the [`cockroach sql`](https://docs.cockroachlabs.com/docs/v24.1/cockroach-sql) reference docs.

> [tab] Go (lib/pq)
>
> ```go theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> // 'db' is an open database connection
>
> codeOne := "0_explain_theory_something"
> codeTwo := "100_address_garden_certain"
> codeThree := "1000_do_write_words"
>
> if _, err := db.Exec("DELETE from promo_codes WHERE code IN ($1, $2, $3)", codeOne, codeTwo, codeThree); err != nil {
>   return err
> }
> return nil
> ```

> [tab] Java (JDBC)
>
> ```java theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> // ds is an org.postgresql.ds.PGSimpleDataSource
>
> String codeOne = "0_explain_theory_something";
> String codeTwo = "100_address_garden_certain";
> String codeThree = "1000_do_write_words";
>
> try (Connection connection = ds.getConnection()) {
>     PreparedStatement p = connection.prepareStatement("DELETE from promo_codes WHERE code IN(?, ?, ?)");
>     p.setString(1, codeOne);
>     p.setString(2, codeTwo);
>     p.setString(3, codeThree);
>     p.executeUpdate();
>
> } catch (SQLException e) {
>     System.out.printf("sql state = [%s]\ncause = [%s]\nmessage = [%s]\n", e.getSQLState(), e.getCause(),
>             e.getMessage());
> }
> ```

> [tab] Python (psycopg2)
>
> ```python theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> # conn is a psycopg2 connection
>
> codeOne = '0_explain_theory_something'
> codeTwo = '100_address_garden_certain'
> codeThree = '1000_do_write_words'
>
> with conn.cursor() as cur:
>     cur.execute("DELETE from promo_codes WHERE code IN (%s, %s, %s)", (codeOne, codeTwo, codeThree)),
> ```

## Performance considerations

Because of the way CockroachDB works under the hood, deleting data from the database does not immediately reduce disk usage. Instead, records are marked as "deleted" and processed asynchronously by a background garbage collection process. Once the marked records are older than [the specified TTL interval](https://docs.cockroachlabs.com/docs/v24.1/configure-replication-zones.md#gc-ttlseconds), they are eligible to be removed. The garbage collection interval is designed to allow sufficient time for running [backups](https://docs.cockroachlabs.com/docs/v24.1/take-full-and-incremental-backups.md) and [time travel queries using `AS OF SYSTEM TIME`](https://docs.cockroachlabs.com/docs/v24.1/as-of-system-time). The garbage collection interval is controlled by the [`gc.ttlseconds`](https://docs.cockroachlabs.com/docs/v24.1/configure-replication-zones#gc-ttlseconds) setting.

The practical implications of the above are:

* Deleting data will not immediately decrease disk usage.
* If you issue multiple [`DELETE`](https://docs.cockroachlabs.com/docs/v24.1/delete) statements in sequence that each delete large amounts of data, each subsequent `DELETE` statement will run more slowly. For details, see [Preserving `DELETE` performance over time](https://docs.cockroachlabs.com/docs/v24.1/delete#preserving-delete-performance-over-time).

For more information about how the storage layer of CockroachDB works, see the [storage layer reference documentation](https://docs.cockroachlabs.com/docs/v24.1/architecture/storage-layer.md).

## See also

Reference information related to this task:

* [`DELETE`](https://docs.cockroachlabs.com/docs/v24.1/delete)
* [Bulk-delete data](https://docs.cockroachlabs.com/docs/v24.1/bulk-delete-data.md)
* [Batch Delete Expired Data with Row-Level TTL](https://docs.cockroachlabs.com/docs/v24.1/row-level-ttl.md)
* [Disk space usage after deletes](https://docs.cockroachlabs.com/docs/v24.1/delete.md#disk-space-usage-after-deletes)
* [`TRUNCATE`](https://docs.cockroachlabs.com/docs/v24.1/truncate)
* [`DROP TABLE`](https://docs.cockroachlabs.com/docs/v24.1/drop-table)
* [Transaction Contention](https://docs.cockroachlabs.com/docs/v24.1/performance-best-practices-overview.md#transaction-contention)

Other common tasks:

* [Connect to the Database](https://docs.cockroachlabs.com/docs/v24.1/connect-to-the-database.md)
* [Insert Data](https://docs.cockroachlabs.com/docs/v24.1/insert-data.md)
* [Query Data](https://docs.cockroachlabs.com/docs/v24.1/query-data.md)
* [Update Data](https://docs.cockroachlabs.com/docs/v24.1/update-data.md)
* [Run Multi-Statement Transactions](https://docs.cockroachlabs.com/docs/v24.1/run-multi-statement-transactions)
* [Troubleshoot SQL Statements](https://docs.cockroachlabs.com/docs/v24.1/query-behavior-troubleshooting.md)
* [Optimize Statement Performance](https://docs.cockroachlabs.com/docs/v24.1/make-queries-fast.md)
* [Example Apps](https://docs.cockroachlabs.com/docs/v24.1/example-apps)
