Loading the World! OpenStreetMap Import In Under 4 Hours

The OpenStreetMap (OSM) database builds almost 750GB of location data from a single file download. OSM notoriously takes a full day to run. A fresh open street map load involves both a massive write process and large index builds. It is a great performance stress-test bulk load for any Postgres system. I use it to stress the latest PostgreSQL versions and state-of-the-art hardware. The stress test validates new tuning tricks and identifies performance regressions.

Two years ago, I presented (video / slides) at PostGIS Day on challenges of this workload. In honor of this week’s PostGIS Day 2024, I’ve run the same benchmark on Postgres 17 and the very latest hardware. The findings:PostgreSQL keeps getting better! Core improvements sped up index building in particular.The osm2pgsql loader got better too! New takes on indexing speed things up.Hardware keeps getting better! It has been two years since my last report and the state-of-the-art has advanced.Tune Your Instrument

First, we are using bare metal hardware—a server with 128GB RAM—so so let’s tune Postgres for loading and to match that server:max_wal_size = 256GB shared_buffers = 48GB effective_cache_size = 64GB maintenance_work_mem = 20GB work_mem = 1GB

Leave a Comment