Best Filesystem for PostgreSQL

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Best Filesystem for PostgreSQL

schmidt

What would be the best filesystem to run PostgreSQL on, in Terms of Performance and data Integrity?

 

Best regards,

 

stephan

Reply | Threaded
Open this post in threaded view
|

Re: Best Filesystem for PostgreSQL

Bruce Momjian
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  <[hidden email]>        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 +


Reply | Threaded
Open this post in threaded view
|

Re: Best Filesystem for PostgreSQL

Imre Samu
In reply to this post by schmidt
>  best filesystem to run PostgreSQL on, in Terms of Performance ...

test:    PostgreSQL v10.3 + Linux 5.0 File-System Benchmarks: Btrfs vs. EXT4 vs. F2FS vs. XFS

Stephan Schmidt <[hidden email]> ezt írta (időpont: 2019. ápr. 17., Sze, 22:59):

What would be the best filesystem to run PostgreSQL on, in Terms of Performance and data Integrity?

 

Best regards,

 

stephan

Reply | Threaded
Open this post in threaded view
|

Re: Best Filesystem for PostgreSQL

Gunther Schadow
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




Reply | Threaded
Open this post in threaded view
|

AW: Best Filesystem for PostgreSQL

schmidt
In reply to this post by Bruce Momjian

my Question was meant for a Debian 9 environment with heavy read/wright load and very high requirements towards Performance and data Consistency

 

Stephan

 


Von: Bruce Momjian <[hidden email]>
Gesendet: Wednesday, April 17, 2019 11:07:21 PM
An: Stephan Schmidt
Cc: [hidden email]
Betreff: Re: 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  <[hidden email]>        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 +
Reply | Threaded
Open this post in threaded view
|

Re: Best Filesystem for PostgreSQL

Tomas Vondra-4
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



Reply | Threaded
Open this post in threaded view
|

Re: Best Filesystem for PostgreSQL

Robert Blayzor
In reply to this post by Gunther Schadow
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/