Tomas Vondra

As zanny said, cow presents an issue for transactional workloads (databases, vm images, etc...) as each write fragments the file, by nature of the process of cow.

btrfs can handle database workloads but you have to disable cow for them (which you can do at the file, directory, or subvolume level in btrfs). You would specify the nodatacow mount option, or chattr +C (file/directory).

The btrfs autodefrag is still rather new, and needs some work. I expect that could be the long term fix (manual defrag is fine now, but you wouldn't want to call it frequently on a db file), I'm not sure how ZFS handles the fragmentation, but I do know in the past ZFS observed similar issues (seems to have been mostly resolved). I should also point out that disabling cow doesn't really fully disable it, snapshots can still function, etc... however, I'm sure that once you start to use the other cow functions you might observe slipping performance due to fragmentation of these types of files.

https://blog.pgaddict.com/posts/friends-dont-let-friends-use-btrfs-for-oltp
https://bartsjerps.wordpress.com/2013/02/26/zfs-ora-database-fragmentation/

There are lot of missing parts. Everything beyond using it for single disks or with RAID1 is still experimental. It's slow for a lot of use cases like VMs or OLTP ( http://blog.pgaddict.com/posts/friends-dont-let-friends-use-btrfs-for-oltp) and using nodatacow does not count!

RAID1 is implemented to use the oddness of the pid to decide which disk to read from... RAID5/6 have huge performance problems in certain use-cases. Write hole is apparently still a thing.

Quota seems to be still broken or difficult to handle, the tooling is not exactly nice or intuitive (this may change in the future).

Hardware failure handling is nonexistent - it can't detect a broken drive.

Lot's of other gotchas. Just read the mailing list. I've been fighting against btrfs for a while and I'm happy when I'm not having to deal with it. Works probably fine for desktop usage but don't bother stressing it - at the moment it will break horrible.

>We aren't sure why, but when we tried to delete a lot of data (~200GB from each machine which each contain several TB of data), our databases become unresponsive for an hour.

There used to be an issue where users hitting their quota couldn't delete files since for some reason deleting a file meant creating a file somehow. The trick was to find some reasonably large file and `echo 1 > large_file` which truncates the file and frees up enough space that you can begin removing files. Maybe this kind of trick could help you guys.

That said, it's inadvisable to run a database on a btree file system like ZFS or btrfs if you're keeping an eye on the write performance.
cf Postgres 9.0 High Performance by Gregory Smith (https://www.amazon.com/PostgreSQL-High-Performance-Gregory-Smith/dp/184951030X)

and

https://blog.pgaddict.com/posts/postgresql-performance-on-ext4-and-xfs

Probably rhymes with "btrfs balance".

I'v had the misfortune of using btrfs in production with a few hundred machines on Ubuntu 14.04. It's one of the most finicky filesystems I've ever used. It's probably better in newer kernels, but if you have a lot of churn it requires constant care and feeding and tends to cause kernel softlockups fairly commonly.

Also this: http://blog.pgaddict.com/posts/friends-dont-let-friends-use-btrfs-for-oltp
and this: https://www.phoronix.com/scan.php?page=news_item&px=CoreOS-Btrfs-To-EXT4-OverlayFS
and this (old but a few of the points still apply): http://coldattic.info/shvedsky/pro/blogs/a-foo-walks-into-a-bar/posts/70

That being said I'm sure there are plenty of use cases for which btrfs in its current capacity is more than sufficient.

In my experience the wisdom "GIN is slower to update than GiST" is mostly obsolete nowadays. At least that's what my benchmark results suggest http://blog.pgaddict.com/posts/performance-since-postgresql-7-4-to-9-4-fulltext

It's also a bit shortsighted to look at the index maintenance in isolation - when your workload is 99% reads, and queries with GIN indexes are way faster, it's probably more efficient to use GIN indexes.

Moreover, there are often more expensive parts when inserting/updating data (either inside the database, or in the application), so the index maintenance is not really the main problem.

I'm sure there are workloads where it's still true, though.

It depends on transactions obviously (among many other factors). MySQL would handle simple one-insert web-style ones pretty well. At the same time very simple (but slightly more realistic) read-write transactions barely got 2500 tps on PostgreSQL with Xeons and SSDs with as low as 32 clients and almost no contention at all (https://blog.pgaddict.com/posts/performance-since-postgresql-7-4-to-9-4-pgbench). And all transactions were the same, and there were no long-lived ones, no distributed ones, and no rollbacks, and so on.