Architecture for an app that shows posts in real time in a map based on location

Question

I would love to hear some more expert opinions on a problem related to bulk real time information, here some details:

Use Case

• 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.

Answer 1

With fewer components, the final solution is simpler and more resources can be deployed on machine performance.

1. Secondary Data -

I like to model such data as primary and secondary data:

• Primary: Post with location
• Secondary: Active posts in zone

The primary data is a point of data with all of the details. The secondary data is basically a materialized view where the result goes into a table. I usually put an X prefix before secondary tables.

2. Tiled data -

a map display system will use "tile" images to display images more efficiently (memory and bandwidth). This can also work with geozoned data. You can use the exact same scheme that a web-mercator based map tiling system would used - with Zoom-Level and (X,Y) tile number.

You would limit how far you can zoom out - limiting the mass of data. Beyond that, you might only report the "number of posts" as a group indicator, encouraging the user to zoom in.

When a valid zoom-level is reached, multiple "tiles" of zones will be in the "view port". You can then aggregate that data within the client application. As you pan around (and zoom), different tiles of data are loaded.

The tiles might only be the post IDs, or it might include the actual post data as well.

So in this case, the Secondary data would be these tiles. The tiles can be generated on demand; as new primary-data posts arrive (and expire). This means that a post will update a few Secondary records that contain that post-point at different zoom levels.

3. Read-Replica of Database

Generally, to scale, you should have additional read replicas of your database. Your DB access method of choice can then be used, such as RESTful or even direct

4. Subscribe to tile updates

The tile coordinates (zoom, x, y) would make for great distribution topic for updates. A viewport would have multiple.

You might have some sort of WebSocket system, perhaps some kind of message-queue to the client, a long-poll, or just interval polling of some sort.