Tuning Debian / Ubuntu for PostgreSQL

May 26, 2021

When it comes to PostgreSQL performance, tuning the operating system gives you extra opportunities for better performance. In this blog post, I will explain how you can tune PostgreSQL for Debian / Ubuntu. Please refer to this post, Tuning Red Hat Enterprise Linux family for PostgreSQL, if you want to learn how to tune PostgreSQL on that platform. 


Most of the tuning on Debian and Ubuntu is done with the sysctl. The postgresql-common and edb-as-common packages already provide a minimal set of settings, but we need to add more on top of that.

sysctl first reads files under the /etc/sysctl.d directory, and then reads sysctl.conf. Please make sure that there are no overlapping parameters in sysctl.conf which will override the ones with the file that we will create now. The files under /etc/sysctl.d are read by numeric order, so files with larger numbers will override files with smaller numbers.

Run these commands as root:

# Create sysctl config file:
echo "
vm.swappiness = 10
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 250
vm.dirty_ratio = 10
vm.dirty_background_ratio = 3
" > /etc/sysctl.d/40-edbpostgres.conf

With these changes, we aim to achieve the following:

  • Linux will be much less likely to swap.
  • The kernel will help Postgres to flush dirty pages, reducing load of bgwriter and checkpointer. 
  • The pdflush daemon will run more frequently
  • It is a good practice to turn TCP timestamps off, to avoid (or, at least, reduce) spikes caused by timestamp generation.

To enable these changes, run this command:

sysctl --system

The changes will take effect immediately.


Disabling transparent hugepages

Unlike hugepages, transparent hugepages are allocated dynamically during runtime, and they are swappable. This is not good for most database systems, as it decreases performance. Disabling transparent huge pages is a great benefit to PostgreSQL performance. On Debian / Ubuntu, first edit /etc/default/grub and add transparent_hugepage=never to GRUB_CMDLINE_LINUX_DEFAULT line:

GRUB_CMDLINE_LINUX_DEFAULT="biosdevname=0 net.ifnames=0 console=tty0 
console=ttyS0,115200 earlyprintk=ttyS0,115200 consoleblank=0 
systemd.show_status=true transparent_hugepage=never "

Now, make that setting permanent:


Every kernel update will pick up this value from now on.

Finally reboot your system with the new kernel:

systemctl start reboot.target

Optimizing the filesystem

Another tuning point is disks. PostgreSQL does not rely on atime for  the data files, so disabling them will shave cpu cycles.

Open /etc/fstab, and add atime,nodiratime just next to the defaults value for the drive that PostgreSQL data and WAL files are kept.

/dev/mapper/pgdata-01-data /pgdata xfs 	defaults,noatime 1 1

To activate it immediately, run:

mount -o remount,noatime,nodiratime /pgdata

Please note that these suggestions are good for a start, and you need to monitor both the operating system and PostgreSQL to gather more data for finer tuning.


Huge pages

By default, the page size on Linux is 4kb. A typical PostgreSQL instance may allocate many GBs of memory, which will end up with potential performance problems with such a small page size. Also, given that these pages will be defragged, using them for large data sets will end up with extra time for mapping them.

Enabling huge pages on Linux will give a performance boost to PostgreSQL. 

By default, huge pages are not enabled on Linux, which is also suitable for PostgreSQL’s default huge_pages setting, which is “try”, basically means “use huge pages if available on the OS, otherwise no.”

There are two aspects to setting up huge pages for PostgreSQL: Configuring OS, and configuring PostgreSQL.

Let’s start with finding how many huge pages are needed on your system for PostgreSQL. When a PostgreSQL instance is started, postmaster creates a file called postmaster.pid file in $PGDATA (for example /var/lib/postgresql/13/main/ for PostgreSQL 13) . You can find the pid of the main process there:

$ head -n 1 $PGDATA/postmaster.pid

Now, find VmPeak for this instance:

$ grep -i vmpeak /proc/1991/status
VmPeak:     8823028 kB

Tip: If you are running more than one PostgreSQL instance on the same server, please calculate the sum of all VmPeak values in the next step.

Let’s confirm the huge page size:

$ grep -i hugepagesize /proc/meminfo
Hugepagesize:     2048 kB

Finally let’s calculate the number of huge pages that the instance(s) will need:

8823028 / 2048 = 4308.12

The ideal number of huge pages is just a bit higher than this -- just a bit. If you increase this value too much, processes that need small pages that also need space in the OS will fail to start. This may even end up with the operating system failing to boot or other PostgreSQL instances on the same server failing to start.

Now edit the /etc/sysctl.d/40-edbpostgres.conf file we created above, and add the following line to 


and run the following command for this new setting to be effective. Please note that this may take some time while Linux is busy with setting up huge pages:

sysctl --system

Now you can set


in postgresql.conf, and (re)start PostgreSQL.


Just like tuning RHEL for Postgres, tuning the Debian and Ubuntu family for PostgreSQL involves both hardware and software. Unlike RHEL which uses tuned, Debian and Ubuntu utilize sysctl for tuning the operating system. By following the steps in this blog post you should be able to optimize the tuning of your operating system for running PostgreSQL.

Want to dive deeper on how to get the most out of PostgreSQL? Visit EDB Docs for more information on a variety of topics. 

Share this

Relevant Blogs

What is pgvector and How Can It Help You?

With pgvector extension, you don’t need a specialized vector database, you can just use Postgres! Using pgvector you can now store vectors (embeddings), query them, use special index types to...
November 03, 2023

PostgreSQL 16 Update: Grouping Digits in SQL

One of the exciting new features in PostgreSQL 16 is the ability to group digits in numeric literals by separating them with underscores. This blog post covers the details.
October 17, 2023

More Blogs

pgAdmin CI/CD

Almost exactly three years ago I wrote a blog on my personal page entitled Testing pgAdmin which went into great detail discussing how we test pgAdmin prior to releases. Back...
August 24, 2023