Re: Any have tested ZFS like PostgreSQL installation filesystem?

[Ivan Voras <ivoras(at)freebsd(dot)org>]

Ing . Marcos Luís Ortíz Valmaseda wrote:
> Regards to all the list.
> ZFS, the new filesystem developed by the Solaris Development team and 
> ported to FreeBSD too, have many advantages that can do that all 
> sysadmins are questioned
> about if it is a good filesystem to the PostgreSQL installation.
> Any of you haved tested this filesystem like PostgreSQL installation fs?

It will work but as to if it is a good file system for databases, the 
debate still goes on.

Here are some links about ZFS and databases:

http://blogs.sun.com/paulvandenbogaard/entry/postgresql_on_ufs_versus_zfs
http://blogs.sun.com/paulvandenbogaard/entry/running_postgresql_on_zfs_file
http://blogs.sun.com/realneel/entry/mysql_innodb_zfs_best_practices
http://dev.mysql.com/tech-resources/articles/mysql-zfs.html
http://blogs.smugmug.com/don/2008/10/13/zfs-mysqlinnodb-compression-update/

A separate issue (I think it is not explored enough in the above links) 
is that ZFS writes data in a semi-continuous log, meaning there are no 
in-place modifications of files (every such write is made on a different 
place), which leads to heavy fragmentation. I don't think I have seen a 
study of this particular effect. OTOH, it will only matter if the DB 
usage pattern is sequential reads and lots of updates - and even here it 
might be hidden by internal DB data fragmentation.