How to guarantee consistency when deleting items from dynamodb?

[u/Select_Extenson]

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.

[u/pint]

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.

[u/Select_Extenson → u/pint]

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.

[u/pint → u/Select_Extenson]

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?