Tomas Vondra

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-… And all transactions were the same, and there were no long-lived ones, no distributed ones, and no rollbacks, and so on.

I always increase the seq scan cost, but another thing that helped me more was updating the table statistics. In that way you can make the planner better aware of your indexes. In my case I increased STATISTICS to 5000 and the planner immediately start using the index instead of full table scan. https://blog.pgaddict.com/posts/common-issues-with-planner-statistics

On the other hand on database workloads, for example PostgreSQL, XFS and EXT4 are about equal these days. ZFS (at least on Linux) and Btrfs are both clearly slower on those workloads. Here is one benchmark, but I have seen plenty of similar benchmark results for PostgreSQL showing the same thing: https://blog.pgaddict.com/posts/postgresql-performance-on-ext4-and-xfs

>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/18… 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-… and this: https://www.phoronix.com/scan.php?page=news_item&px=CoreOS-Btrfs-T… and this (old but a few of the points still apply): http://coldattic.info/shvedsky/pro/blogs/a-foo-walks-into-a-bar/posts/… That being said I'm sure there are plenty of use cases for which btrfs in its current capacity is more than sufficient.

deadline seems less useful now that a bunch of edge cases were fixed in cfq (the graphs used to look way worse than this): http://blog.pgaddict.com/posts/postgresql-io-schedulers-cfq-noop-deadl…

Interesting ReiserFS comment http://blog.pgaddict.com/posts/friends-dont-let-friends-use-btrfs-for-…

http://blog.pgaddict.com/posts/postgresql-performance-with-gcc-clang-a… https://www.phoronix.com/scan.php?page=article&item=clang-gcc-broa… Show gcc and clang to be broadly competitive without a clear winner in terms of speed of execution.

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… 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.

Similar tests (and results) for Postgres http://blog.pgaddict.com/posts/compiler-optimization-vs-postgresql

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… https://bartsjerps.wordpress.com/2013/02/26/zfs-ora-database-fragmenta…

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-… 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.