I want to install PostgreSQL 9.x version in an Ubuntu 14.04 workstation and I'm looking for a recommendation on how to set it up in order to

  • get a good read-write (50/50-balance) performance

  • keep a decent performance for overall conventional system usage.

My idea is to have two main partitions in an SSD drive:

  • [A] an ext4 for the root system ("/") with OS recommended/default settings.

  • [B] an ext4 for postgres database files ("/var/lib/pgsql") with a large block size (eg. 1MB) and a large readahead size (how much?) and proper flags (which?).

I have no clue whether separating postgres db files from general system is a good idea. In such a case, any piece of advice on [B] block size, readahead size, filesystem flags?

Furthermore any helpful hint?

  • I realized this is a good idea, moreover disabling fs accessed/modified time record (atime, relatime) while creating the filesystem also increases the performance. – Cássio Jandir Pagnoncelli Jan 6 '16 at 3:34

Since you're on SSD you're better of disabling readahead. Seek time is not an issue for flash based drives. That's for the spinning era. It's more important to align the filesystem properly.

Postgresql is using 8k pages so best performance generally comes with matching blocksize.

With XFS use nobarrier and make the filesystem with proper agcount and logging. (https://serverfault.com/questions/222305/trying-to-determine-the-correct-number-of-xfs-allocation-groups-for-postgresql-s).

You can also benefit from having compressed file system like ZFS especially if your data cannot fit in cache and has to be read from disk. Usually I get 2-4x for databases which results in 2-4 times less blocks read from disk but that's very use case dependant. If you use ZFS use recordsize=8k, compression=lz4, primarycache=all and check if you need to set ashift for the particular disk (if it is using 512 byte or 4k blocks by running cat /sys/block/[device]/queue/physical_block_size). http://zfsonlinux.org/faq.html#PerformanceConsideration


generally, it should not be a problem in term of the postgres functionality. I assume you know that you have to stop the postgres service for the purpose. copy all the data dir to a new location and modify the data_dir in postgresql.conf. never did it by myself. If I would be you, first, would do it in test ENV with some load test. Hope it will help

take a look: https://serverfault.com/questions/310350/postgresql-alternate-database-location

  • It is a staging db, so no big deal on the transition. The issue is whether there is a substantial performance gain to create a partition for PGDATA (one that has, say, large block size, readahead, etc). – Cássio Jandir Pagnoncelli Jan 4 '16 at 4:34
  • You're probably better off tuning other parameters: stick pg_wal on its own disk, stick stats_tmp on its own disk, stick your tmp_tablespaces on a very "loose" filesystem (i.e. disable barriers and set commit to a very high number on ext4). Could stick your logs on their own disk too if you're using the builtin logging. Setting huge_pages can help too but that will affect the whole machine. – DylanYoung Apr 1 '20 at 15:09

Keep it simple. I have never heard that any ext4 tuning has made a significant difference in a benchmark.

You can do the safe things: use a block size of 4KB and mount with noatime, but don't expect a performance boost.

You should not have the database on the root file system. Create a special file system for the database, so that the operating system is not affected if the file system runs full.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.