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?

Some time ago I've presented an article about various ways to store file in a PostgreSQL database, and at the end of the article I've promised a benchmark comparing the ways described. It took me some time, but the benchmark is here ...

This article extends an article about reasons to store user files in a database and not in a filesystem, and describes several ways how to do that in PostgreSQL. There are BLOBs, BYTEA columns, custom solutions, and all of them have advantages / disadvantages, so let's investigate them.

Imagine you are working on a web application that requires storing of files (e.g. a photo gallery). And there comes a question "Should I store the files in a database, or directly on the filesystem?" Maybe you've asked this question on a discussion forum / mailing list and I guess the answer you've received was something like "It's much more efficient to store files outside the database." Well, I'll try to explain why I prefer storing files in a database. This article is based on a discussion I've had with James Lewis, after I posted a question to pg-php mailing list.