I want to develop a game that will let user pin objects to the world map with a timeout value after which that object will not be visible anymore (user can also set an infinite timeout that will leave the object visible on the map forever). Each user that is playing will see these objects coming and going, particularly they will see objects that are located on the visible map.
Right now the only query that I would like to serve FAST is (I don't care about super precise results):
"Given a rectangle of the user's map visible area, return all the objects that are inside it"
OR something similar:
"Given a user location, return all the objects that are within a certain radius specified by the user"
It's the first time I am dealing with geospatial data and geospatial indexes. I have seen that Elasticsearch is a good starting point for giant data and little queries. I have also taken a look at PostGIS which I liked a lot and seemed to be a good DBMS for the geospatial task above. What I am concerned about right now is scaling this service (which probably is not the priority right now but I want to be prepared in case this is going to work).
I've never tried to benchmark these kind of queries but this seems to be a very read-intensive task, I imagine that 500 queries per seconds is going to be very hard to keep up with, but this are just my unproven thoughts. Also keep in mind that I will probably need to remove rows that have their timeout value expired (maybe every 10-5 minutes?) so triggers will need to be fired over the giant database.
Again, I have no experience with this and this is probably a very bad implementation of the game, but I am here to ask you for advice on this.
What DBMS would you recommend for this task?
What kind of optimizations would you apply to this problem?
Right now the only query that I would like to serve FAST is (I don't care about super precise results): "Given a rectangle of the user's map visible area, return all the objects that are inside it"
OR something similar: "Given a user location, return all the objects that are within a certain radius specified by the user"
It's the first time I am dealing with geospatial data and geospatial indexes. I have seen that Elasticsearch is a good starting point for giant data and little queries. I have also taken a look at PostGIS which I liked a lot and seemed to be a good DBMS for the geospatial task above. What I am concerned about right now is scaling this service (which probably is not the priority right now but I want to be prepared in case this is going to work).
I've never tried to benchmark these kind of queries but this seems to be a very read-intensive task, I imagine that 500 queries per seconds is going to be very hard to keep up with, but this are just my unproven thoughts. Also keep in mind that I will probably need to remove rows that have their timeout value expired (maybe every 10-5 minutes?) so triggers will need to be fired over the giant database.
Again, I have no experience with this and this is probably a very bad implementation of the game, but I am here to ask you for advice on this.
What DBMS would you recommend for this task? What kind of optimizations would you apply to this problem?