0

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

EXAMPLE

EXISTING DATA

--------|-------------|-------

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?

Improve this question
9
  • 3
    Have you identified an actual performance problem, or is this hypothetical?
    – Robert Harvey
    Sep 30, 2020 at 17:59
  • 2
    Presumably you picked MariaDB for other reasons. Have you considered running some trials on MariaDB to see how it holds up? It's hard to imagine taking more than about an hour to set something like that up.
    – Robert Harvey
    Sep 30, 2020 at 18:09
  • 1
    And you're probably going to have to perform that exercise anyway, given the specific nature of your use case, regardless of the database you choose.
    – Robert Harvey
    Sep 30, 2020 at 18:09
  • 1
    Well, OK. But your choice to use MariaDB was already made for the production database, presumably for good reasons. Those reasons still apply, and you can now add "we have experience with it" to the list. Note that MariaDB has OLAP support.
    – Robert Harvey
    Sep 30, 2020 at 18:18
  • 2
    I don't see any reasons why performance degradation should happen here by repeated deletes. Do you have any real evidence (for example, by some performance measurements) that in a correctly indexed DB a straightforward DELETE/INSERT sequence will not fullfill your performance requirements?
    – Doc Brown
    Oct 3, 2020 at 3:55

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.