public inbox for [email protected]  

help / color / mirror / Atom feed

From: Justin Clift <[email protected]>
To: Alexander Farber <[email protected]>
Cc: [email protected]
Subject: Re: Please recommend postgresql.conf improvements for osm2pgsql loading Europe
Date: Sat, 30 Mar 2024 13:33:42 +1000
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAADeyWibmmfvBhJS2P2mPUOLx57WJ0h+qM5xBQPqFehh-Sna4g@mail.gmail.com>
References: <CAADeyWibmmfvBhJS2P2mPUOLx57WJ0h+qM5xBQPqFehh-Sna4g@mail.gmail.com>

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

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Please recommend postgresql.conf improvements for osm2pgsql loading Europe
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox