What is the recommended way to delete a large number of items from DynamoDB?

Question

I'm writing a simple logging service in DynamoDB.

I have a logs table that is keyed by a user_id hash and a timestamp (Unix epoch int) range.

When a user of the service terminates their account, I need to delete all items in the table, regardless of the range value.

What is the recommended way of doing this sort of operation (Keeping in mind there could be millions of items to delete)?

My options, as far as I can see are:

A: Perform a Scan operation, calling delete on each returned item, until no items are left

B: Perform a BatchGet operation, again calling delete on each item until none are left

Both of these look terrible to me as they will take a long time.

What I ideally want to do is call LogTable.DeleteItem(user_id) - Without supplying the range, and have it delete everything for me.

Solution 1

What I ideally want to do is call LogTable.DeleteItem(user_id) - Without supplying the range, and have it delete everything for me.

An understandable request indeed; I can imagine advanced operations like these might get added over time by the AWS team (they have a history of starting with a limited feature set first and evaluate extensions based on customer feedback), but here is what you should do to avoid the cost of a full scan at least:

1. Use Query rather than Scan to retrieve all items for user_id - this works regardless of the combined hash/range primary key in use, because HashKeyValue and RangeKeyCondition are separate parameters in this API and the former only targets the Attribute value of the hash component of the composite primary key..

   • Please note that you''ll have to deal with the query API paging here as usual, see the ExclusiveStartKey parameter:

     Primary key of the item from which to continue an earlier query. An earlier query might provide this value as the LastEvaluatedKey if that query operation was interrupted before completing the query; either because of the result set size or the Limit parameter. The LastEvaluatedKey can be passed back in a new query request to continue the operation from that point.

2. Loop over all returned items and either facilitate DeleteItem as usual

   • Update: Most likely BatchWriteItem is more appropriate for a use case like this (see below for details).

---

Update

As highlighted by ivant, the BatchWriteItem operation enables you to put or delete several items across multiple tables in a single API call [emphasis mine]:

To upload one item, you can use the PutItem API and to delete one item, you can use the DeleteItem API. However, when you want to upload or delete large amounts of data, such as uploading large amounts of data from Amazon Elastic MapReduce (EMR) or migrate data from another database in to Amazon DynamoDB, this API offers an efficient alternative.

Please note that this still has some relevant limitations, most notably:

• Maximum operations in a single request — You can specify a total of up to 25 put or delete operations; however, the total request size cannot exceed 1 MB (the HTTP payload).

• Not an atomic operation — Individual operations specified in a BatchWriteItem are atomic; however BatchWriteItem as a whole is a "best-effort" operation and not an atomic operation. That is, in a BatchWriteItem request, some operations might succeed and others might fail. [...]

Nevertheless this obviously offers a potentially significant gain for use cases like the one at hand.

Solution 2

According to the DynamoDB documentation you could just delete the full table.

See below:

"Deleting an entire table is significantly more efficient than removing items one-by-one, which essentially doubles the write throughput as you do as many delete operations as put operations"

If you wish to delete only a subset of your data, then you could make separate tables for each month, year or similar. This way you could remove "last month" and keep the rest of your data intact.

This is how you delete a table in Java using the AWS SDK:

DeleteTableRequest deleteTableRequest = new DeleteTableRequest()
  .withTableName(tableName);
DeleteTableResult result = client.deleteTable(deleteTableRequest);

Solution 3

If you want to delete items after some time, e.g. after a month, just use Time To Live option. It will not count write units.

In your case, I would add ttl when logs expire and leave those after a user is deleted. TTL would make sure logs are removed eventually.

When Time To Live is enabled on a table, a background job checks the TTL attribute of items to see if they are expired.

DynamoDB typically deletes expired items within 48 hours of expiration. The exact duration within which an item truly gets deleted after expiration is specific to the nature of the workload and the size of the table. Items that have expired and not been deleted will still show up in reads, queries, and scans. These items can still be updated and successful updates to change or remove the expiration attribute will be honored.

https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/TTL.html https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/howitworks-ttl.html

Solution 4

The answer of this question depends on the number of items and their size and your budget. Depends on that we have following 3 cases:

1- The number of items and size of items in the table are not very much. then as Steffen Opel said you can Use Query rather than Scan to retrieve all items for user_id and then loop over all returned items and either facilitate DeleteItem or BatchWriteItem. But keep in mind you may burn a lot of throughput capacity here. For example, consider a situation where you need delete 1000 items from a DynamoDB table. Assume that each item is 1 KB in size, resulting in Around 1MB of data. This bulk-deleting task will require a total of 2000 write capacity units for query and delete. To perform this data load within 10 seconds (which is not even considered as fast in some applications), you would need to set the provisioned write throughput of the table to 200 write capacity units. As you can see its doable to use this way if its for less number of items or small size items.

2- We have a lot of items or very large items in the table and we can store them according to the time into different tables. Then as jonathan Said you can just delete the table. this is much better but I don't think it is matched with your case. As you want to delete all of users data no matter what is the time of creation of logs, so in this case you can't delete a particular table. if you wanna have a separate table for each user then I guess if number of users are high then its so expensive and it is not practical for your case.

3- If you have a lot of data and you can't divide your hot and cold data into different tables and you need to do large scale delete frequently then unfortunately DynamoDB is not a good option for you at all. It may become more expensive or very slow(depends on your budget). In these cases I recommend to find another database for your data.

Solution 5

We don't have option to truncate dynamo tables. we have to drop the table and create again . DynamoDB Charges are based on ReadCapacityUnits & WriteCapacityUnits . If we delete all items using BatchWriteItem function, it will use WriteCapacityUnits.So better to delete specific records or delete the table and start again .

View more solutions

Comments

[ivant]

I think it would make sense to use batch delete for the second step (it's "masked" as a batch write operation)

[Steffen Opel]

@ivant - thanks much for the hint, this "masked" delete functionality of BatchWriteItem indeed escaped me back then; I've updated the answer accordingly.

[Sergio Marcelo C Figueiredo]

I like this answer too but caution: this could create many tables in your system and we pay per table provision. So, you need to reduce the provisioning after the end of month (if your table is per month) while this table is not deleted.

[SegFaults McGee]

This answer is also very nice considering that storing 'dead' rows in dynamo can only make your read throughput worse (it might not, but it can't help). Making a single 'hot' table with a bunch of old 'cold' tables with lower throughput is likely cheaper in the long run than keeping everything in the same table.

[Ihtsham Minhas]

agree with this answer, its applied if you need to delete all records form the table, but here the questioner want to delete the user base entries not the whole table.

[André Werlang]

Having a separate table table for each user would be expensive given DynamoDB pricing. One table per month would actually make things worse. This is clearly an answer for a different, very specific problem.

[Neil]

for deleting with BatchWriteItem items need to be specified via TableWriteItems

[brabster]

Deleting the table may also not be an attractive option if you use automated provisioning such as CloudFormation to manage your table as part of a stack. I'm not aware of a simple way to make CloudFormation recreate a table that you deleted by hand.

[Tony]

The link to BatchWriteItem is now docs.aws.amazon.com/amazondynamodb/latest/APIReference/…

[André Werlang]

This approach takes quite a bit of time to delete and recreate (when needed) the table, making it unavailable during the whole time. The question clearly states removing user data, which would be impractical spliting into separate, per-user tables.

[Davos]

I realise this is old, and the OP didn't mention a specific language SDK, but in Python there is a high level batch_writer() as part of the boto3.resource.Table API that will "automatically handle buffering and sending items in batches. In addition, the batch writer will also automatically handle any unprocessed items and resend them as needed" i.e. it's a wrapper around BatchWriteItem that manages the annoying parts. boto3.amazonaws.com/v1/documentation/api/latest/reference/…

[Blundell]

I believe deleting and recreating a table would stop the monitoring of changes by any lambda's (or other systems) that are using the table ARN

[Tomer]

adding TTL is an "update" (write operation). I'm not sure there is any gain for doing an "update" instead of a "delete".

[Lukas Liesis]

you can have that data inserted with original write and updated with any other update action. Of course, it's not an option if you have a bunch of data and then you want to delete it. But this is a valid option for cases where you can have ttl for the data you insert or update.

[Tomer]

I agree, if there is already TTL configured and cleanup can wait up to 48 hours that's definitely the optimal option. My apologies if I was unclear.

[Nishit]

@SergioMCFigueiredo This can be easily taken care of by enabling auto scaling of read and write capacity.

[Laren Crawford]

For Python, Boto3's batch_writer is the way to go for batch deletes. I put a working example on GitHub https://github.com/awsdocs/aws-doc-sdk-examples.

[Steve Owens]

Deleting the whole table is like hitting a fly with a sledge hammer, how about the 90% use case which is deleting all items with the same given hash key but leaving the rest of the items untouched?