I would love to hear some more expert opinions on a problem related to bulk real time information, here some details:
- The users view in real time a map with determined places.
- The users can create a post if they find themselves in that place (via geolocation)
- Posts for a place will get removed after some time (e.g. 1h), and the number of posts are limited to X (e.g.100). When the limit is reached, FIFO comes to play
- The count of the posts has to be displayed in the map on top of each place
What I've come up with
Technology stack decided so far (debatable): Java Spring or Quarkus, PostgresQL, Redis, RabbitMQ
- Each of the place displayed in the map will be stored in a relational database.
- Each row will contain the coordinates and a foreign key to a "room" table. The room table contains the posts.
- Each user will be connected to the server via websockets and store the session in redis. Every X seconds they query the server which fetches the count of the posts in each room, in their boundary box.
- On creation of the post, it gets sent to RabbitMQ which will delay the message for 1h, then dispatches the message to the server which will then delete the post from the room table.
Problems This solution though brings problems that I wouldn't know yet how to solve and they are the following:
- Each user requesting an update on the data every X seconds will also be very costly to the database, how can I solve this problem? Considering that the boundary box can change based on how the user zooms in / out the map. I thought about creating a snapshot of the entire data every X seconds or so and keep it in memory, all the users will request the latest snapshot rather than querying the database for each request. But there will be thousands of entries and it could be demanding to keep it in memory at all times. Maybe it makes sense if it's in one city. But what if there's multiple cities involved? e.g. If I'm in London I want to retrieve the results for London, but the snapshot will contain data from say NewYork as well.
- Also when the user wants to see the actual posts from a place, it will need to query the database constantly to check for any new updates.
Thanks to anyone who will take the time to give some thoughts to this problem.