Is there any way I an delete more than 20k mutation in Google Cloud Spanner?

Question

I have millions of records in Spanner table and I would like to delete rows from Spanner using some query condition. For Eg: delete from spanner table where id > 2000. I'm not able to run this query in Spanner UI, because of Spanner 20k mutation limit in single op's. So is there any way I could delete this record from spanner table by doing some tweaks in api level code or do we have a work around for this type of use-case.

Answer 1

You can use gcloud command line as :

gcloud spanner databases execute-sql <database_id> --instance=<instance_id> --enable-partitioned-dml --sql="delete from YourTable where id > 2000"

NOTE: SQL query must be fully partitionable and idempotent

Answer 2

According to the official documentation Deleting rows in a table, I think you should consider Particioned DML execution model:

If you want to delete a large amount of data, you should use Partitioned DML, because Partitioned DML handles transaction limits and is optimized to handle large-scale deletions

Partitioned DML enables large-scale, database-wide operations with minimal impact on concurrent transaction processing by partitioning the key space and running the statement over partitions in separate, smaller-scoped transactions.