many to many – Strategies to get more performance in a specific scenario


Hi Guys, my name’s Joao, i’m currently working as backend and that’s my first post.
I’ll appreciate any help!

My team are working on a new feature. During the process of development we face a doubt about database performance, and so far we have some divided opinions… so here i’m rs

Tabble structure

For the example, a many-to-many relationship:

enter image description here

The scenario

Considering tables above, our API must serve an POST endpoint that, receive a Domain ID as a path variable and a json containing a list of Users Id, something like that:

Path : /domain/1

Body Json : 

With that list we must persist on the many-to-many domain_users that relationship between users and domains. Resume, in one endpoint we must manager the many-to-many table registers.

The question

We made this in two different ways, i’m here to ask your opinion what’s the more efficiently, performatic etc.. by the database side, thinking bout I/O

Strategie A (Clean and Insert)
First we delete ALL the registers in domain_users where the field domain_id is equals the Domain ID of the request, and then we insert in domain_users all users of the request list for that domain. Considering the above Json example, in this strategie we’ll perform one delete and one insert with two registers on the table domain_users.

Strategie B (Logical relationship)
First we compare the list of users that came with the request and the list of users that are already linked for that Domain ID, by a select on the domain_users where the field domain_id is equals the Domain ID of the request, so we execute these conditionals:
1 – There’s a user that came in the Json Request list and aren’t present in database list? Insert that!
2 – There’s a user that are present in the database list and aren’t present in the Json Request ? Delete that!
3 – There’s a user that are present in both list (database and Json Request) ? Do nothing.
In this strategie, the number of operations performed on the database depends of registers already present on the domain_users many-to-many table.

Which of the two strategies should we follow, thinking about better performance of database?

Obs : Our backend are in Java and the database we are working now is a PostgreSQL version 12