This page has instructions for deleting rows of data from CockroachDB, using the DELETE
SQL statement.
Before you begin
Before reading this page, do the following:
- Create a CockroachDB Serverless cluster or start a local cluster.
- Install a Driver or ORM Framework.
- Connect to the database.
- Create a database schema.
Insert data that you now want to delete.
In the examples on this page, we use sample
movr
data imported with thecockroach workload
command.
Use DELETE
To delete rows in a table, use a DELETE
statement 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:
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 that resolves toTRUE
orFALSE
(e.g.,=
).{filter_value}
is the matching value for the filter.
For detailed reference documentation on the DELETE
statement, including additional examples, see the DELETE
syntax page.
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 multipleDELETE
statements that each delete a single row. - Always specify a
WHERE
clause inDELETE
queries. If noWHERE
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 instead of aDELETE
statement. - To delete a large number of rows (i.e., tens of thousands of rows or more), use a batch-delete loop.
- When executing
DELETE
statements from an application, make sure that you wrap the SQL-executing functions in a retry loop that handles transaction errors that can occur under contention. - Review the performance considerations below.
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
values:
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
reference docs.
// '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
// 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());
}
# 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))
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 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:
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
reference docs.
// '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
// 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());
}
# 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, they are eligible to be removed. The garbage collection interval is designed to allow sufficient time for running backups and time travel queries using AS OF SYSTEM TIME
. The garbage collection interval is controlled by the gc.ttlseconds
setting.
The practical implications of the above are:
- Deleting data will not immediately decrease disk usage.
- If you issue multiple
DELETE
statements in sequence that each delete large amounts of data, each subsequentDELETE
statement will run more slowly. For details, see PreservingDELETE
performance over time.
For more information about how the storage layer of CockroachDB works, see the storage layer reference documentation.
See also
Reference information related to this task:
DELETE
- Bulk-delete data
- Batch Delete Expired Data with Row-Level TTL
- Disk space usage after deletes
TRUNCATE
DROP TABLE
- Transaction Contention
Other common tasks: