Changing parameter maintenance_work_mem = 10GB results in error Postgresql 17

[pimmierevrev]

What version of osm2pgsql are you using?

osm2pgsql version 2.1.1
Build: RelWithDebInfo
Compiled using the following library versions:
Libosmium 2.22.0
Proj 9.6.0
Lua 5.4.7

What operating system and PostgreSQL/PostGIS version are you using?

Windows 11 Pro 24H2
Postgis bundle 3.5.3
Postgresql 17.5

Tell us something about your system

64GB RAM
AMD Ryzen 7 9800X3D
MSI MPG X870E CARBON WIFI (MS-7E49)
M2 disk for installation

What did you do exactly?

Stopped the postgresql service. Change the postgresql.conf parameters as advised on the osm2pgsql website and tried to restart the service

What did you expect to happen?

I expcted the postgresql just to work again when I restart the service.

What did happen instead?

I found out that changing the maintenance_work_mem into 10GB (as advised by osm2pgsql), postgresql doesnt get restarted. It gives the error 'connection refused getsockopt'

What did you do to try analyzing the problem?

Changing the parameter again in the default value .... and the service gets restarted! So far I have the value up to 256MB and the service still gets restarted, but there is a value that is too much for the system....

[lonvia]

This isn't an issue of Postgresql 17. I have multiple machines running Postgresql 17 with that setting enabled. Might be a Windows thing or something else specific to your setup.

Please have a look at the Postgresql server logs. There should be an error message there telling you why it refuses to start up.

[ImreSamu]

Tell us something about your system
64GB RAM

Hi @pimmierevrev ,

The example configuration
at https://osm2pgsql.org/doc/manual.html#tuning-the-postgresql-server
is intended for a machine with "128 GB RAM and a fast SSD".

If you have less memory (e.g., 64 GB in your case),
the values should be scaled down accordingly.
As the documentation mentions:

" If your machine has very little memory, you might consider setting autovacuum_max_workers = 1 and reduce autovacuum_work_mem even further. This will reduce the amount of memory that autovacuum takes away from the import process."

for example:

# Options for fast SSD
shared_buffers = 1GB
work_mem = 50MB
wal_level = minimal
checkpoint_timeout = 60min
max_wal_size = 10GB
checkpoint_completion_target = 0.9
max_wal_senders = 0
random_page_cost = 1.0

# Scaled-down memory settings (for less RAM)
maintenance_work_mem = 3GB
autovacuum_work_mem = 1GB
autovacuum_max_workers = 1

And since PostgreSQL’s memory usage and performance can differ slightly on Windows compared to Linux, you may need to reduce these values even further.
This is especially true if, in addition to the PostgreSQL server and osm2pgsql, you are also running a browser and many other programs in the background, as they all share the same 64 GB of RAM.

And as mentioned before, it is also worth checking the PostgreSQL log.

[pimmierevrev]

Hello, Thank you, didn't notice that. Above all: wonderfull piece of software, osm2pgsql. Recently switched to lua file. I use visual studio code and python to fully automate The download off Europe The extraction of the Netherlands The import into postgresql The creationist of views Additional stuff Apart from the above mentioned hickup, all works like a charm. Kind regards, Pim Verver Verzonden vanaf Outlook voor Android<https://aka.ms/AAb9ysg>

…

________________________________ From: ImreSamu ***@***.***> Sent: Saturday, August 9, 2025 6:16:23 PM To: osm2pgsql-dev/osm2pgsql ***@***.***> Cc: pimmierevrev ***@***.***>; Mention ***@***.***> Subject: Re: [osm2pgsql-dev/osm2pgsql] Changing parameter maintenance_work_mem = 10GB results in error Postgresql 17 (Discussion #2375) Tell us something about your system 64GB RAM Hi @pimmierevrev<https://github.com/pimmierevrev> , The example configuration at https://osm2pgsql.org/doc/manual.html#tuning-the-postgresql-server is intended for a machine with "128 GB RAM and a fast SSD". If you have less memory (e.g., 64 GB in your case), the values should be scaled down accordingly. As the documentation mentions: " If your machine has very little memory, you might consider setting autovacuum_max_workers = 1 and reduce autovacuum_work_mem even further. This will reduce the amount of memory that autovacuum takes away from the import process." for example: # Options for fast SSD shared_buffers = 1GB work_mem = 50MB wal_level = minimal checkpoint_timeout = 60min max_wal_size = 10GB checkpoint_completion_target = 0.9 max_wal_senders = 0 random_page_cost = 1.0 # Scaled-down memory settings (for less RAM) maintenance_work_mem = 3GB autovacuum_work_mem = 1GB autovacuum_max_workers = 1 And since PostgreSQL’s memory usage and performance can differ slightly on Windows compared to Linux, you may need to reduce these values even further. This is especially true if, in addition to the PostgreSQL server and osm2pgsql, you are also running a browser and many other programs in the background, as they all share the same 64 GB of RAM. And as mentioned before, it is also worth checking the PostgreSQL log. — Reply to this email directly, view it on GitHub<#2375 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AVFYIEJDQPVOA4REO7OQT3D3MYNFPAVCNFSM6AAAAACDQCO6NKVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTIMBVHAYTSMQ>. You are receiving this because you were mentioned.Message ID: ***@***.***>

[joto]

@ImreSamu The example configuration in the manual is for a 128 GB RAM machine, that's right. But that doesn't mean it is wrong for other RAM sizes or that's the reason why setting maintenance_work_mem to 10GB does not work.

I simply seems there is a limit to how large work_mem and maintenance_work_mem can be on Windows. A limit that's not there on Linux. See for instance here and here. I don't know why there is this limit and why it isn't documented in the PostgreSQL manual and whether that's something that depends on the PostgreSQL version.

[pimmierevrev]

I can confirm that maintenance_work_mem on 1024MB is ok, but 2048 gives the above mentioned error. Maybe it's good to state that in the manual

[mboeringa]

I simply seems there is a limit to how large work_mem and maintenance_work_mem can be on Windows. A limit that's not there on Linux. See for instance here and here. I don't know why there is this limit and why it isn't documented in the PostgreSQL manual and whether that's something that depends on the PostgreSQL version.

I can confirm that maintenance_work_mem on 1024MB is ok, but 2048 gives the above mentioned error. Maybe it's good to state that in the manual

I doubt this is a Windows issue. Maybe @pimmierevrev has forgotten to allocate Windows virtual memory though on his machine, to allow some more wiggle room for Windows to deal with memory issues.

As @ImreSamu justly pointed out, this is most likely simply a misconfigured PostgreSQL instance, that doesn't properly take into account the (limited) server's resources. There is little sense in adjusting the osm2pgsql documentation for that, users should consult the PostgreSQL docs instead.

[joto]

@mboeringa You are on Windows, can you set maintenance_work_mem to more than 1 GB?