Deleting by query

Delete by query no longer accepts wildcard queries, including queries that match all documents (for example, <delete><query>*:*</query></delete>). Instead, use the CQL TRUNCATE command.

Delete by query supports deleting data based on search criteria. After you issue a delete by query, documents start getting deleted immediately and deletions continue until all documents are removed. For example, you can delete the data that you inserted using this command:

curl http://localhost:8983/solr/mykeyspace.mysolr/update --data '<delete><query>color:red</query></delete>' -H 'Content-type:text/xml; charset=utf-8'

Using &allowPartialDeletes parameter set to false (default) prevents deletes if a node is down. Using &allowPartialDeletes set to true causes the delete to fail if a node is down and the delete does not meet a consistency level of quorum. Delete by queries using : are an exception to these rules. These queries issue a truncate, which requires all nodes to be up in order to succeed.

Best practices

DataStax recommends that queries for delete-by-query operations touch columns that are not updated. For example, a column that is not updated is one of the elements of a compound primary key.

Delete by query problem example

The following workflow demonstrates that not following this best practice is problematic:

  • When a search coordinator receives a delete-by-query request, the request is forwarded to every node in the search datacenter.

  • At each search node, the query is run locally to identify the candidates for deletion, and then the LOCAL_ONE consistency level deletes the queries for each of those candidates.

  • When those database deletes are perceived at the appropriate nodes across the cluster, the records are deleted from the search index.

For example, in a certificates table, each certificate has a date of issue that is a timestamp. When a certificate is renewed, the new issue date is written to the row, and that write is propagated to all replicas. In this example, let’s assume that one replica misses it. If you run a periodic delete-by-query that removes all of the certificates with issue dates older than a specified date, unintended consequences occur when the replica that just missed the write with the "certificate renewal" matches the delete query. The certificate is deleted across the entire cluster, on all datacenters making that delete unrecoverable.

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com