We have a use case wherein we want to store incoming data and invalidate all the previously existing data pertaining to one parent entity.
ie Parent p1 --> has List< child> old_data ( already stored in the table ) now some new data comes in List new_data
PS the table only has one index is the foreign key p1.ID
--------|-------------|------- ID Parent.ID DATA 1. P1.ID. D1 2. P1.ID. D2 3. P1.ID D3
INCOMING DATA ( D4,D5,D6)
What we want
--------|-------------|------- ID Parent.ID DATA 4. P1.ID. D4 5. P1.ID. D5 6. P1.ID D6
so we want to delete all the old_data present in the table first and then insert new_data
The major concern we see here is performance degradation wrt to frequent delete operation which will occur every time(frequency of incoming data associated with the parent is high) new_data comes in. ( we are not going through the setting of isValid = 0 approach since it will bloat the table in no time ) What's the best possible way to approach this. We are currently planning to use MariaDB.
Do we move to any other RDBMS or NoSQL DB which gives better performance wrt deletes? or any other suggestions?