Re: Please recommend postgresql.conf improvements for osm2pgsql loading Europe

On 2024-03-30 05:53, Alexander Farber wrote:
<snip>
> I use the following postgresql.conf in my Dockerfile
> ( the full version at https://stackoverflow.com/a/78243530/165071 ),
> when loading a 28 GByte large europe-latest.osm.pbf
<snip>
> Is anybody please able to spot any improvements I could
> apply to the postgresql.conf config values at the top of my mail,
> that could reduce the loading time of almost 2 hours?

Not specific conf file improvements, but for an initial data load
have you done things like turning off fsync(), deferring index
creating until after the data load finishes, and that kind of thing?

You don't want fsync() off when you're using the database in
production, but for long data load scenarios it seems like it'd
be a decent fit.

With .pbf files, from skimming over how they're described here:

https://wiki.openstreetmap.org/wiki/PBF_Format

... they don't seem to be optimised for loading into a database. (?)

It kind of looks like they'd be stored into individual records,
which probably means they'd be getting imported as individual
INSERT statements rather than something that's optimised for
bulk loading. :(

Regards and best wishes,

Justin Clift