Delete Data

On this page

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 Postgres client.
• 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 the cockroach 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 to TRUE or FALSE (e.g., =).
• {filter_value} is the matching value for the filter.

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 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.
• 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';

// '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))

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');

// '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 subsequent DELETE statement will run more slowly. For details, see Preserving DELETE 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
• Disk space usage after deletes
• TRUNCATE
• DROP TABLE
• Transaction Contention

Other common tasks:

• Connect to the Database
• Insert Data
• Query Data
• Update Data
• Run Multi-Statement Transactions
• Error Handling and Troubleshooting
• Optimize Statement Performance
• Example Apps

Was this helpful?