Delete Data

On this page Carat arrow pointing down
Warning:
CockroachDB v19.2 is no longer supported as of May 12, 2021. For more details, refer to 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:

Note:

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

Delete a single row

icon/buttons/copy
DELETE from accounts WHERE id = 1;

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

icon/buttons/copy
// 'db' is an open database connection

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

For complete examples, see:

icon/buttons/copy
// 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:

icon/buttons/copy
# conn is a psycopg2 connection

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

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:

    icon/buttons/copy
    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):

    icon/buttons/copy
    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.

Note:

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:


Yes No
On this page

Yes No