PostgreSQL and huge pages on Windows

Configuring the operating system for huge pages on Linux and make PostgreSQL using it is quite simple and straight forward. Here are two quite old post on how to do this, one for Debian systems, one for Red Hat based systems. We usually do it the same way on all distributions using TuneD, because then we only have one way of doing it, no matter the distribution.

One Windows, the story is a bit different, of course, and this is the topic of this post. In my example setup I am using Windows Server 2022, but I guess the procedure is the same for other versions on Windows Server.

I’ve used the standard PostgreSQL installer to install PostgreSQL and did not do anything special, just followed the wizard. Once that was done a new service was created and PostgreSQL was up and running in the default configuration.

If you try to turn “huge_pages” to on without configuring the operating system, the service will not be able to re-start:

postgres=$ select version();
Active code page: 1252
                          version
------------------------------------------------------------
 PostgreSQL 16.3, compiled by Visual C++ build 1938, 64-bit
(1 row)


postgres=$ alter system set huge_pages=on;
ALTER SYSTEM
postgres=$ exit
PS C:\Users\Administrator> Stop-Service postgresql-x64-16
PS C:\Users\Administrator> Start-Service postgresql-x64-16
Start-Service : Failed to start service 'postgresql-x64-16 - PostgreSQL Server 16 (postgresql-x64-16)'.
At line:1 char:1
+ Start-Service postgresql-x64-16
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OpenError: (System.ServiceProcess.ServiceController:ServiceController) [Start-Service],
   ServiceCommandException
    + FullyQualifiedErrorId : StartServiceFailed,Microsoft.PowerShell.Commands.StartServiceCommand

PS C:\Users\Administrator>

The reason for this is reported in the event log:

The error message is pretty clear, so what you need to do is to grant the “Lock pages in memory” privilege to the user which is running the service. The installer created the service with the “Network Service” username, which is a predefined local account:

This account needs the corresponding privilege, which can be done in “Local Security Policy”:

Once the user is added there:

… PostgreSQL will happily start and use huge pages:

PS C:\Users\Administrator> Start-Service postgresql-x64-16
PS C:\Users\Administrator> psql -U postgres postgres
Password for user postgres:
psql (16.3)
Type "help" for help.

postgres=$ show huge_pages;
 huge_pages
------------
 on
(1 row)


postgres=#