Fuzzy.cz

Recently I've been asked how is the database performance affected by the block size (for filesystem and database), especially with respect to SSD drives that have 512kB internal blocks while traditional spinning HDD use 512B or 4096B sectors.

When someone asks about this on pgsql-performance list, the usual answer is "Use 8kB, Luke!" because 8kB is a reasonable compromise and using a different block size probably won't give you much. But is that really true? Is that true for all file systems? And does that hold for all workloads? And what about the SSD drives? And what about the filesystem block size?

Interesting questions, but it's impossible to answer them without hard data ... time for a benchmark!

In an article I wrote two years ago I've discusses reasons why to store files in a relational database, based on the premise that file systems are "fast but dumb" regarding transactional features. Journaling file systems are interesting for many reasons, but it does not provide the level of transactions available in the database systems - .e.g. individual transactions are atomic but you can't group them into bigger transactions, it's not possible to do a rollback, you can't use it within an XA transaction, etc. How did the situation change since the time I wrote the article?