Repository navigation
Proposed faster approach for --create imports #2504
Description
Hello,
I would like to propose contributing an approach that significantly speeds up the initial planet import on "ordinary" hardware by avoiding the node location random reads. In particular, I have a prototype implementation that sped up processing time from 71 hours to under 2 hours, and the total time including post-processing from 77 hours to under 9 hours, on a box with 8 CPU cores and 32GB RAM. (I'm defining "ordinary hardware" to mean "node locations don't fit in RAM, so the current approach uses the flat file on disk").
| Phase | Time before | Time after |
|---|---|---|
| Scatter | n/a (new) | 0h 7m |
| Nodes | 0h 46m | 0h 19m |
| Ways | 45h 41m | 1h 7m |
| Relations | 24h 33m | 0h 8m |
| Processing total (sum so far) |
71h 0m | 1h 43m |
| Post-processing | 5h 59m | 6h 54m |
| Total | 76h 59m | 8h 37m |
Brief outline:
- In a new phase (
scatter), we read all the PBF ways, and save to disk all the(node_id, way_id)pairs. - Hybrid RAM+disk sort those by
node_id - Join the
(node_id, way_id)against the PBF(node_id, lat, lng). Both of these are now in ascending order bynode_id(the PBF has it sorted in the first place), so we can do it streaming, zippering them together. Also at this time we run the Luaprocess_nodein parallel. We now have entries of(way_id, node_id, lat, lng). - Hybrid RAM+disk sort those by
way_id(back to the original order). - Read these, in way order, and again join it against the PBF for the ways. Within each way, we sort (one last time) to get the
(lat, lng)list in the way's order. Luaprocess_waythen happens.
Relations happen similarly (node members scattered in scatter, way members scattered in ways, gathered in relations).
Middle and Lua still see all nodes, then all ways, then all relations. The tables all end up with the same contents and indexes and clustering.
Details (click to expand)
- Numbers from a
m6g.2xlargeinstance, with the boot disk increased to 2.5tb (EBS gp3) but nothing else changed. Full planet from Sept 14. OSM Carto flex style with--slim. Postgres 18. - The average CPU usage before (usr+sys) was 3.4% (only one core and lots of iowait). After the average was 82.7%. During processing. During post-processing unchanged at ~20%.
- It writes a lot more to disk as temp files, ~250gb, but it's all sequential, and it does not change the peak disk usage since we delete it as we go.
- Peak disk usage during the processing stage is 793gb before, 844gb after. The eventual peak is unchanged later on, 1346gb during clustering.
- EBS stats. Processing stage only. Beforehand 22.5TB read across 362mil requests average 62kb each (of which only a tiny fraction was used). Afterward 0.69TB read across 5mil requests average 138kb each (of which a much larger fraction was used).
- The
CLUSTER,CREATE INDEX, etc post-processing got about an hour longer because I dropped the primary key fromplanet_osm_waysfor faster loading, then added it back later. - Builds on top of Use binary COPY for flex and middle tables #2503
- Lua is now run multithreaded, each worker thread having its own Lua state and COPY connection.
- Currently only supports
--create, flex, one input file, no bbox, no two-stage processing. - The "Hybrid RAM+disk sort" means the writer makes a few dozen bucket files, partitioned by the most significant bits of the sorting key. pseudocode:
for way in ways: for node in way: bucket_files[node.id >> 28].buffered_write((node.id, way.id))in the initial write (buffered in that we only actually write to disk every megabyte-ish). Then on the reader side, each bucket is now small enough to comfortably sort in RAM, and each bucket is all entirely lesser than the next bucket, so this results in a fully sorted stream.
Prototype first draft code is here: leijurv@2b6d018 The main file is here (it's not crazy long; 1.3k lines).
Activity
joto commentedon Sep 27, 2026
Very interesting idea. I am currently working on an improved middle that is much smaller than the current database middle / flat node file, see Project CODA. There is a certain overlap between your work and my work, both will change how the processing of OSM data is done, so I think we'll need to push that idea back a bit until it is clear whether any changes I am working on affect this (or whether can be share some code here, because my code also dumps some data to disk and sorts it to create sorted indexes).