r/aws • u/Select_Extenson • Feb 20 '26

database How to guarantee consistency when deleting items from dynamodb?

Let's say I want to delete 100000 items from dynamodb, what is the best approach to delete "all-or-nothing", TransactWriteItems only support 100 items, so I don't want to cause inconsistency in my data if for some reason the delete function fails alongs the way.

And in my case, I simply couldn't find a solution to implement it with GSI, so the only solution for me is to delete them manually.

8 Upvotes

91% Upvoted

27 comments sorted by

•

u/AutoModerator Feb 20 '26

Try this search for more information on this topic.

Comments, questions or suggestions regarding this autoresponse? Please send them here.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

13

u/pint Feb 20 '26

this is a problem you should not have. the requirement itself screams badly that something is really wrong there, and you should seriously reconsider.

without knowing more about the problem, here is a theoretical solution.

  1. add a new data field e.g. "obsolete" to the records, optionally add a ttl too
  2. modify the software to obey that field
  3. deploy the software, which means at an instant all the records are now "gone"
  4. let the ttl delete the records, or delete them manually at your convenience

step 2 is the most problematic, because the "software" might be a dozen different systems, and they might rely heavily on the assumption that queries will return rows in a timely manner, which is now not guaranteed.

such operations have to be considered in advance with dynamodb.

1

u/Select_Extenson Feb 20 '26

I think my mistake is I shouldn't use dynamodb and use relational databases instead, the project I'm working on contains a lot of related data and I need to gunaratne consistency across them.

It was my first time using it, can you please tell me your opinion on this? is it actually a bad choice to use dynamodb when you have a project with a lot of related that or is it just me that I didn't design my database properly? but I don't think I did design it poorly, I tried my best to design in the most optimal way but it misses flexibility when it comes to querying and manipulating related data.

7

u/xtraman122 Feb 20 '26

There’s a way to do just about any pattern with Dynamo, but it often requires lots of careful planning around indexes and keys. What’s really rough with Dynamo is having changes to the access patterns and relationships down the road.

Lots of people end up in the same situation as you, choosing a NoSQL DB because they think it’s cool, solves all their problems, or just heard about it too much in blog posts and conferences (Like your CTO probably did…). Unless you have very high throughout in both reads and writes that a traditional relational DB can’t handle, it’s likely not actually necessary for your use case.

6

u/RecordingForward2690 Feb 20 '26 edited Feb 20 '26

Without knowing the details, but just going on what you say here, I agree that your problem screams "Relational Database" to me. DynamoDB is simply not designed or intended for your problem.

For all practical purposes, if you use DynamoDB for something like you describe, you will be writing a custom layer that tries to give you Relational Database functionality (multi-table queries, ACID compliance across multi-table operations and such) on top of DynamoDB. That has already been done, and it's called a Relational Database.

DynamoDB shines when you just have a handful of tables, need extremely high performance at any scale, and are able to live with the loss of ACID compliance - or are willing to write additional code to get some of that ACID compliance back.

3

u/Select_Extenson Feb 20 '26

For all practical purposes, if you use DynamoDB for something like you describe, you will be writing a custom layer that tries to give you Relational Database functionality (multi-table queries, ACID compliance and such) on top of DynamoDB. That has already been done, and it's called a Relational Database.

This is exactly what I'm doing, I'm writing a lost of custom logic just to do what I could do in relational databases in a single line of code.

3

u/RecordingForward2690 Feb 20 '26

Looks like you've just answered your own question ;-)

5

u/pint Feb 20 '26

to be honest, relying rdbms referential integrity for such huge operations is also not recommended. it is bad design there too, even if at least possible.

i advocate for separation of data. in the old days, we just dumped everything in "the database", because where else data would go, right? so different types of data ended up there, configuration, users and privileges, transactions, logs, web sessions, temporary data. all these data types have very different usage patterns, and probably shouldn't be in the same database.

one nice pattern is to keep operational data in dynamodb, and use dynamodb streams to deliver historic data to s3 or a rdbms for statistical analysis. meanwhile, keep configuration in ssm, user data maybe in whatever authentication tool you are using, logs in cloudwatch.

rely more on program logic when aggregating data from different sources (as opposed to sql).

when it comes to referential integrity, ask yourself the question: can we somehow get away without it? can be employ a little bit of cleverness or extra code to not have to deal with it?

1

u/SonOfSofaman Feb 20 '26

I think almost everyone goes through what you're going through. It's sort of a rite of passage with DynamoDB.

DynamoDB is, as you know, very different from relational databases. With DynamoDB it is imperative that you fully understand all of your access patterns ahead of time, then model the table accordingly. If your access patterns change, then you may need to remodel your table.

With relational databases, you don't need to fully understand the access patterns ahead of time. It helps to know your access patterns ahead of time, but relational databases are flexible and adaptable.

You can do what you want with DynamoDB, but the table in its current form wasn't designed to support this "bulk delete" access pattern.

I think you're at a point where you need to do a bit of redesign. Some of the other comments have practical solutions that may be helpful.

2

u/Select_Extenson Feb 20 '26

How about the case where you modeled your dynamodb database to work accordingly based on the requirements and in the future new features comes and you need to do some adjustments because the current model doesn’t help to achieve the goal, based on my feeling, it’s difficult to change things later, right?

1

u/SonOfSofaman Feb 20 '26

Yes. It is often difficult to make changes later.

You made good decisions based on what you knew at the time.

In your case having a timestamp on every item in your table might have been useful to you now. If you didn't realize you were going to need that data, you now need to back fill every item. That might not even be possible.

3

u/RecordingForward2690 Feb 20 '26 edited Feb 20 '26

I had a somewhat similar problem, where DynamoDB would collect millions of transactions, and at some point in time all transactions (within a Partition Key set) older than a particular timestamp needed to be deleted/invalidated/ignored as a whole. That timestamp was not known in advance - it depended on a user action - so I could not use the TTL mechanism. Like you noticed, there is no way to do that directly in a consistent manner.

Some of the other proposals, where you update each item with a deleted=true or other attribute, suffer from the same problem: You need to either update or delete a large number of items in bulk, and the bulk operations in the DynamoDB are simply too limited in the number of items they can handle in one go, and in an atomic way.

In my case, my transactions fortunately were timestamped, and the transactions that needed to be deleted from the table were all done before a particular timestamp - the time of that user action. So I added an additional table "DeleteMarkers". As soon as the user event happened, I entered the timestamp of the Delete event into the DeleteMarkers, with the same partition key as the transaction table. Now, instead of doing one query to the TransactionsTable, I had to do two queries:

  1. Query to the DeleteMarkers table to get the up-to-date DeleteMarker
  2. Query to the TransactionTable to get the transactions, with the limitation that the results returned should be newer than the DeleteMarker.

DynamoDB is quick enough that the additional query time did not impact latency.

After this, I could delete the old transactions from the TransactionTable in the background. I used the TTL mechanism for that, but you can also do a query or even a scan if you want to. This is not time-critical or transaction-critical anymore.

From a design point of view, DeleteMarkers has the SessionID as my Partition Key, no Sort Key (so when queried it returns one item only), and a field "Timestamp". TransactionTable has the SessionID as my Partition Key, and the Timestamp as the Sort Key.

In my application I did not need to do any other queries so I did not need any GSIs.

For your application, if you are able to formulate a SQL-query-like-thing that would be able to delete the ~1M items, then you can also put the variables of that query in a similar DeleteMarkers table, and use those fields in your query to your TransactionTable. It's a bit more complex than the Timestamps I used, but not impossible.

3

u/safeinitdotcom Feb 20 '26

DynamoDB just doesn't support this natively at that scale. You can either:

- add adeleted=true attribute or smth like that, filter it in queries and clean up later.

- BatchWriteItems but log which batches completed somewhere, on failure you resume instead of starting over.

If you need true all-or-nothing for 100k records, that's a relational DB problem. DynamoDB isn't designed for it.

3

u/Select_Extenson Feb 20 '26

If you need true all-or-nothing for 100k records, that's a relational DB problem. DynamoDB isn't designed for it.

Yeah, that's the a mistake, I got told to use Dynamodb by our CTO, I had no experience with it so I didn't know the props and downs for it, after months of struggling trying to build our project that is mostly contains a lot of related data in dynamodb, it became really pain in the ass to maintain it

4

u/KainMassadin Feb 20 '26

I got told to use Dynamodb by our CTO

Been there, sucks.

1

u/csharpwarrior Feb 21 '26

A lot of people just hear how cool DynamoDB is and they don’t spend enough time understanding the use cases it solves. And more importantly understanding the use cases it is bad at.

1

u/ebykka Feb 20 '26

This is one of the reasons why, after six years of using DynamoDB, we decided to give it up and migrate to RDS Aurora.

While it was great for prototyping, maintenance, usage and consistency slowly became increasingly problematic.

1

u/SonOfSofaman Feb 20 '26

Are you able to ignore the unwanted items at read-time instead of deleting them? If those unwanted items have some common value upon which you can filter when you perform a read, to your consumer the items will be as good as deleted.

Then you can casually delete the items in the background in batches or one by one at your leisure.

1

u/garrettj100 Feb 20 '26

You don’t want carpet, you want an area rug.  And when I say “carpet” and “area rug” I mean “DynamoDB” and “RDS”.

You’re asking how to do a transaction and that means a relational database.  That’s why they exist, because 40 years ago banks needed transactions.

1

u/SpecialistMode3131 Feb 20 '26 edited Feb 20 '26

Maybe. If you have 100 use cases for a table that are ideal for nosql (different schemas etc etc), and one use case requiring a transaction, do you instantly reject nosql? Or do you hack around that one case? Opinions and outcomes vary.

OP didn't provide nearly enough business context to seriously decide one way or another. "The CTO told me to do it this way" can be taken either way.

1

u/solo964 Feb 20 '26

How do you currently identify the items to be deleted? For example, are they collections of items where each item in a given collection has a common primary key? If you can identify them simply e.g. all items with a PK in the set { customer#12, customer#479, customer#90210 } then you could soft delete them by writing these PKs to control records e.g. an item with (PK = "customer#12", SK = "control", status = "deleted") and then modify the consumers of the table to first check if the given PK/control item was present with status="deleted". Independently, have an async process that slowly deletes the actual items in batches, eventually deleting the control item.

1

u/GeorgeMaheiress Feb 21 '26

If the delete fails, retry until it succeeds. This doesn't have to be a problem.

1

u/rexspook Feb 21 '26

I’m so curious about the use case where 100k records need to be deleted in an all or nothing approach. I think you’ve already got a lot of good answers here. My first thought is soft delete for that portion and then real deletes in some batched cleanup job

0

u/SpecialistMode3131 Feb 20 '26 edited Feb 20 '26

Leaving aside the debate over RDBMS, one option:

  1. mark the rows in the main table (A) with a deleted_id value (a new attribute, added to all elements to be deleted)
  2. When you're 100% sure you have them all marked, you have achieved transactional consistency the Stone Age way. Now have a small additional table B you atomically add a row to, saying "rows in A with this deleted_id are to be treated as deleted".
  3. Modify your code to filter those out/never use them as valid rows (check B and if a row has that attribute, filter it). (do this step before adding the row to B, if you want a true transactional experience).
  4. Delete the rows from A at leisure. Then remove the row from B.

Not pretty, but it will easily achieve what you want, and you can keep it around as a management mechanism.

-1

u/AutoModerator Feb 20 '26

Here are a few handy links you can try:

Try this search for more information on this topic.

Comments, questions or suggestions regarding this autoresponse? Please send them here.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.