Thread: Best Filesystem for PostgreSQL

On Wed, Apr 17, 2019 at 08:59:13PM +0000, Stephan Schmidt wrote:
> What would be the best filesystem to run PostgreSQL on, in Terms of Performance
> and data Integrity?

Uh, which operating system?  If it is Linux, many people like ext4 or
xfs.  Some like zfs.  ext3/ext2 are not recommended due to fsync
performance.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On 4/17/2019 18:03, Imre Samu wrote:
> test:    PostgreSQL v10.3 + Linux 5.0 File-System Benchmarks: Btrfs 
> vs. EXT4 vs. F2FS vs. XFS
> https://www.phoronix.com/scan.php?page=article&item=linux-50-filesystems&num=3
>
So looks like XFS won. I like XFS for its ease of use especially when 
growing.

Any ideas on how ZFS might do? ZFS is of course so much more flexible.

-Gunther

On Thu, Apr 18, 2019 at 02:19:28AM +0000, Stephan Schmidt wrote:
>my Question was meant for a Debian 9 environment with heavy read/wright
>load and very high requirements towards Performance and data Consistency
>

Well, that's like asking "which car is the best" unfortunately. There's no
good answer, as it very much depends on your expectations, hardware etc.
Everyone wants good performance, reliability and consistency.

Simply said, if you're on current Linux and you don't have any additional
requirements (like snapshotting), then ext4/xfs are likely your best bet.
There are differences between these two filesystems, but it depends on the
workload, hardware etc. Overall the behavior is pretty close, though. So
either you just go with either of those, or you do some testing with your
application on the actual hardware.

If you need something more advanced (like better snapshotting, etc.) then
maybe ZFS is the right choice for you. It also allos various advanced
configurations with ZIL, L2ARC, ...

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 4/17/19 6:38 PM, Gunther Schadow wrote:
> So looks like XFS won. I like XFS for its ease of use especially when
> growing.
> 
> Any ideas on how ZFS might do? ZFS is of course so much more flexible.


That would totally depend on your data sets and expectations. If you're
doing a LOT of random inserts/updates/deletes, etc then you would have
to tune the hell out of ZFS along with right caching layers in place.
Same could be said of reads, but if you have a TON of memory in the
server that's greatly mitigated and work well.

If you're looking to warehouse big blobs of data or lots of archive and
reporting; then by all means ZFS is a great choice.

ZFS certainly can provide higher levels of growth and resiliency vs
ext4/xfs.

-- 
inoc.net!rblayzor
XMPP: rblayzor.AT.inoc.net
PGP:  https://inoc.net/~rblayzor/