Delete Data

On this page

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.

Delete a single row

DELETE from accounts WHERE id = 1;

// 'db' is an open database connection

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

// 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());
}

# conn is a psycopg2 connection

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

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.

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 subsequent DELETE statement will run more slowly. For details, see Preserving DELETE performance over time.
• To delete all of the rows in a table, it's faster to use TRUNCATE instead of DELETE.

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:

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