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?
