Split GPS points table from main database

[1ec5]

The gps_points database table is enormous, containing 31.9 billion records (up from 22 billion in 2023). These records are dominated by a few users: #931 (comment). The volume of data complicates efforts to optimize the database and make the database server farm more robust. Storing the trackpoint data directly in the main database also prevents us from instituting more stringent access controls around potentially sensitive personal location data.

This issue tracks investigating whether and how to migrate GPS trackpoints and file metadata to a separate database. The Rails port would need to juggle more than one database when managing traces. Should the new database be on the same server or a separate server?

@Rub21 is investigating this potential change.

[pnorman]

Storing the trackpoint data directly in the main database also prevents us from instituting more stringent access controls around potentially sensitive personal location data.

Is this an actual problem? The main database contains all the account information which is some of the most sensitive personal data we have. Is there any circumstance where we'd trust someone with access to that data but not tracepoint data?

[1ec5]

The main purpose of the change is the operational goal of performance and facilitating backups. Any potential privacy/security gains would be side effects. At the moment we have no plans for treating the separate database differently, though we are thinking through trace or account deletion scenarios.

The changes are happening in openstreetmap/chef#846.