2

I realize that these questions should probably be directed to a DBA but I feel like you might have some experience in managing the same kind of data.

We have an existing system where we are storing positions in a MySQL server with x & y. Our two main use cases for our data:

  • Get all positions within a bounding box. Today we only support square areas but we would like to support polygons if it doesn't affect performance to much.
  • Get all the positions for the last X hours for a specific target.

With a 16 billion row and 600GB table performance is a bit low. We're looking to move to a database better suited for larger datasets. Currently we are looking at PostgreSQL and SQL Server.

  • Are there any other databases worth looking into? (Both RDBMS and NoSQL)
  • Are there advantages when using PostgreSQL over SQL Server or the other way around?
  • Should we continue using integers for x & y or should we be moving to a geom? What are the advantages / performance differences?

closed as too broad by PolyGeo, Vince, Mapperz May 20 '14 at 13:32

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.