Thread: [GENERAL] hidden maintenance_work_mem limitations of a Windows build
Hi PG developers, it has recently come to my attention that Win and Nix flavors or Postgres (including upcoming 10.0 beta 3) are not equal in terms of limits on maintenance_work_mem parameter. Even when you try to set it to miserable 2gb, on Windows you get "outside the valid range for parameter "maintenance_work_mem" (1024 .. 2097151)". But i never knew before that Linux users can set this parameter to any amounts they like, without such artificial restriction.
I used to wait for index creation for WEEKS on Windows using only 2Gb for maintenance_work_mem while having a server with 128Gb RAM. Especially "nice" feelings I have experienced when the server had a power outage, and 8 day's work of index creation (of course, single-threaded, while again, server had 32 cores) was lost.
Section https://www.postgresql.org/docs/current/static/runtime-config-resource.html of the PG docs does not say a word that Windows is not a first-class citizen when dealing with maintenance_work_mem, which would save me and presumably many other developers tons of time and nerves.
Here are my questions:
1) Is this really impossible to permit usage of more than 2Gb of maintenance_work_mem in Windows version?
2) If still not, could you please update docs to reflect this limitation of a Windows build so that people don't waste their time with Windows version and go for Linux one right away if they
have large tables (starting form ~100Gb) and want to index them for faster searches? Necessity to wait for indexing to complete for weeks while having 32 cores 128Gb RAM SSD-featured rig, given that RDBMS is not really using available resources, really plays against PG reputation.
3) Any plans to make indexing operation multi-threaded? I hoped to see this feature in the upcoming release, without it it's not 10.0 but 9.7 in my humble opinion :-)
4) Any plans to make indexing operation resumable after an unexpected postmaster shutdown (ie periodical saving of current indexing operation progress)?
5) Any plans to allow inclusion of index data into backup & restore ecosystem?
Regards,
Eugen
I used to wait for index creation for WEEKS on Windows using only 2Gb for maintenance_work_mem while having a server with 128Gb RAM. Especially "nice" feelings I have experienced when the server had a power outage, and 8 day's work of index creation (of course, single-threaded, while again, server had 32 cores) was lost.
Section https://www.postgresql.org/docs/current/static/runtime-config-resource.html of the PG docs does not say a word that Windows is not a first-class citizen when dealing with maintenance_work_mem, which would save me and presumably many other developers tons of time and nerves.
Here are my questions:
1) Is this really impossible to permit usage of more than 2Gb of maintenance_work_mem in Windows version?
2) If still not, could you please update docs to reflect this limitation of a Windows build so that people don't waste their time with Windows version and go for Linux one right away if they
have large tables (starting form ~100Gb) and want to index them for faster searches? Necessity to wait for indexing to complete for weeks while having 32 cores 128Gb RAM SSD-featured rig, given that RDBMS is not really using available resources, really plays against PG reputation.
3) Any plans to make indexing operation multi-threaded? I hoped to see this feature in the upcoming release, without it it's not 10.0 but 9.7 in my humble opinion :-)
4) Any plans to make indexing operation resumable after an unexpected postmaster shutdown (ie periodical saving of current indexing operation progress)?
5) Any plans to allow inclusion of index data into backup & restore ecosystem?
Regards,
Eugen