Better approach to either hit the database in the main process or delegate to an external one

Questions / Help
1

Hey folks.

I have one question related to Ecto. I have one endpoint that is required to hit the database once to compute and provide information to the user, but I would like to hit the database again to get data the user does not need to see, in this second case is just to compare a couple of information and send them via telemetry. I can see alternatives, but I would like to know which one is better:

1 - Hit the database twice in the same process that is getting the request and compare the data in an external process. That is a little easier to test because I don’t have ownership problems with ecto sandbox since the same process is calling the database… but the user needs to wait for something he does not need.

2 - Hit the database in an external process and compute there too. This is good because the user does not need to wait for the second query since I just want to compare it in background, but this is getting a new connection from the pool right? It’s also harder to test because the external process is not the owner of the main connection… so sandbox gets tricky.

3 - Is there another better approach I can do?

4 - Is it bad to have multiple processes hitting the database? Is it better to use just one process to be able to share the same connection? Actually I am not sure if one process is always going to share the same connection or if external processes will always getting a new connection from the pool… one for each process. What do you guys think about it?

5 - What if I created a transaction and opened multiple processes inside of it. Would they share the same connection?

Thanks

2

I’d go with your item 2. It’s inevitable that a new connection must be taken from the pool – can’t change that and it’s working as intended.

Try it and if you have trouble testing it, post a new thread and we’ll try to help. :slight_smile:

1 Like
3

I second this. I believe the sandbox is only used for testing? If that is true then your code should not be designed around the test environment but the production one.

1 Like
4

Thank you guys.

5

Or you can use liveView. A liveview’s controller is just a GenServer, so you can send self() another message to do the extra work. It will be done after the rendering sent to the client, yet still in the same process using the same ecto connection (I assume). User gets the rendering fast, and the extra computation is hiding between user interactions.

2 Likes