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?