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:
- Set up a local cluster.
- Installed a Postgres client.
- Connected to the database.
- Inserted data that you now want to delete.
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",
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: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 ofDELETE
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:
- 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. - To delete all of the rows in a table, it's faster to use
TRUNCATE
instead ofDELETE
.
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
- Disk space usage after deletes
TRUNCATE
DROP TABLE
- Understanding and Avoiding Transaction Contention
Other common tasks: