Delete Data

Cockroach Labs will stop providing Assistance Support for this version on May 12, 2021. Prior to that date, upgrade to a more recent version to continue receiving support. For more details, see the Release Support Policy.

This page has instructions for deleting data from CockroachDB (using the DELETE statement) using various programming languages.

Before you begin

Make sure you have already:


Your application should use a retry loop to handle transaction errors that can occur under contention.

Delete a single row

DELETE from accounts WHERE id = 1;

For more information about how to use the built-in SQL client, see the cockroach sql reference docs.

// 'db' is an open database connection

if _, err := db.Exec("DELETE FROM accounts WHERE id = 1"); err != nil {
    return err

For complete examples, see:

// ds is an org.postgresql.ds.PGSimpleDataSource

try (Connection connection = ds.getConnection()) {
    connection.createStatement().executeUpdate("DELETE FROM accounts WHERE id = 1");

} catch (SQLException e) {
    System.out.printf("sql state = [%s]\ncause = [%s]\nmessage = [%s]\n",
                      e.getSQLState(), e.getCause(), e.getMessage());

For complete examples, see:

# conn is a psycopg2 connection

with conn.cursor() as cur:
    cur.execute("DELETE FROM accounts WHERE id = 1",

For complete examples, see:

Delete multiple rows

You can delete multiple rows from a table in several ways:

  • Using a WHERE clause to limit the number of rows based on one or more predicates:

    DELETE FROM student_loan_accounts WHERE loan_amount < 30000;
  • Using a WHERE clause to specify multiple records by a specific column's value (in this case, id):

    DELETE FROM accounts WHERE id IN (1, 2, 3, 4, 5);
  • Using TRUNCATE instead of DELETE to delete all of the rows from a table, as recommended in our performance best practices.


Before deleting large amounts of data, see Performance considerations.

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. This process runs every 25 hours by default to allow sufficient time for running backups and running 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:

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:

Other common tasks:

YesYes NoNo