[openstreetmap/openstreetmap-website] Store all GPS traces as LineStrings (Issue #7339)

[Ruben L. Mendoza]

Rub21 created an issue (openstreetmap/openstreetmap-website#7339)

We discussed this idea in the ops calls of August 6 and August 20, and I ran some tests to see what we could expect.

The idea is to store each track segment as a single geometry in a new `gpx_tracks` table, instead of one row per point. Segments are capped at 500 points, so a bbox request does not have to decompress a whole trace. I put the schema, the test results and a possible migration path (dual write, backfill, read switch) here: https://hackmd.io/61pAPKpuRSeXxqyLn4K6Ag. In my tests the storage was around 4.5 times smaller and bbox reads were much faster.

Talking with @1ec5 , we think we should migrate all the traces, not only trackable and identifiable, visibility only changes what the API returns, not how the points are stored, so a public or private trace can be a LineStrings too [#7146](https://github.com/openstreetmap/openstreetmap-website/pull/7146). Then all the GPS points live in one table and we could drop `gps_points`.  the database table would go from about ~2.9 TB today to about ~640-740 GB.

The reason `gps_points` is so big is not the coordinates, it is the metadata around them. Every point is a row, it pays the Postgres row header  and repeats `gpx_id`, `trackid` and `tile`, and both indexes carry that too. In `gpx_tracks` we pay that once per segment of 500 points, and Postgres compresses the geometry, so the table is much smaller.

For querying, I ran the same cursor query on both tables (`gps_points` and `gpx_tracks`). The times and the query plans are in this comment: https://github.com/openstreetmap/openstreetmap-website/pull/7322#issuecomment-5344603705. A bbox selection performs better on `gpx_tracks` than on `gps_points`. But I ran everything on a small dev database, on my laptop, so these numbers may not show what happens at planet size, as mentioned in this [comment](https://github.com/openstreetmap/openstreetmap-website/pull/7322#issuecomment-5390080518).

WIP in this branch: https://github.com/Rub21/openstreetmap-website/commits/gpx-tracks/. I still need to clean up the commits so they are easy to read, and then open a PR.

Any other idea to improve this is welcome.


-- 
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/openstreetmap-website/issues/7339
You are receiving this because you are subscribed to this thread.

Message ID: <openstreetmap/openstreetmap-website/issues/7339 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/rails-dev/attachments/20260824/810a4a2a/attachment.htm>