Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1rqPTb-00CFkJ-6Z
	for pgsql-general@arkaria.postgresql.org; Sat, 30 Mar 2024 03:33:51 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1rqPTZ-006SIx-SY
	for pgsql-general@arkaria.postgresql.org; Sat, 30 Mar 2024 03:33:49 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <justin@postgresql.org>)
	id 1rqPTZ-006SIo-DN
	for pgsql-general@lists.postgresql.org; Sat, 30 Mar 2024 03:33:49 +0000
Received: from meldrar.postgresql.org ([2a02:c0:301:0:ffff::31])
	by magus.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <justin@postgresql.org>)
	id 1rqPTV-007OJD-Kq
	for pgsql-general@lists.postgresql.org; Sat, 30 Mar 2024 03:33:48 +0000
DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed;
	d=postgresql.org; s=20171124; h=Content-Transfer-Encoding:Content-Type:
	Message-ID:References:In-Reply-To:Subject:Cc:To:From:Date:MIME-Version:Sender
	:Reply-To:Content-ID:Content-Description;
	bh=XaER/uzeKbEVAFg4FUftKB/UDOd+BoBprRHk+cpPJ5Q=; b=dTh6zVkExCIkwtZyplTDs8pnB7
	ia1en5VZyGCenPGSdmTdkSx+H5N/qro9uLMe7NdLGJxp6DVqo9cmT1j6pRV9EqcugQjmru9bWYtJV
	976EJ7rxlWqP+SYh+iKTmI7K3XhLBEj3ZtFvrRVDCZnJUlYDxQyNueMBOqIklRhTIAzNBmllJVmtz
	43+1j/ZIK8xuhwAMJY7bhOUov5/QU1jNda+qKE4SCQns3Sza7iMKrG8MhzF1OBBPJf7TxN1xeJoNK
	VoFoBusbtykREpNaH1cF6js140CE59cGppxwiCyWGe7i4J+ItP24WWMdQxolf8BxCPESXiHFH69I6
	JJ0dWMrg==;
Received: from meldrar.postgresql.org ([87.238.57.231] helo=webmail.postgresql.org)
	by meldrar.postgresql.org with esmtpsa  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <justin@postgresql.org>)
	id 1rqPTS-00H0nj-VP; Sat, 30 Mar 2024 03:33:45 +0000
MIME-Version: 1.0
Date: Sat, 30 Mar 2024 13:33:42 +1000
From: Justin Clift <justin@postgresql.org>
To: Alexander Farber <alexander.farber@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Please recommend postgresql.conf improvements for osm2pgsql
 loading Europe
In-Reply-To: <CAADeyWibmmfvBhJS2P2mPUOLx57WJ0h+qM5xBQPqFehh-Sna4g@mail.gmail.com>
References: <CAADeyWibmmfvBhJS2P2mPUOLx57WJ0h+qM5xBQPqFehh-Sna4g@mail.gmail.com>
User-Agent: Roundcube Webmail/1.4.15
Message-ID: <98df75338710b3cb63817f3db532cdf6@postgresql.org>
X-Sender: justin@postgresql.org
Content-Type: text/plain; charset=US-ASCII;
 format=flowed
Content-Transfer-Encoding: 7bit
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/98df75338710b3cb63817f3db532cdf6%40postgresql.org>
Precedence: bulk

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