Hello,

I was wondering what would be more prudent (performance-wise): to use the
bytea datatype in postgres 8.3 or to use an xfs filesystem for storing
images of about 10KB each?
The solution should be able to scale up to millions of images whilst
affecting performance as little as possible...
There will be some updates to a little subset of the images...probably every
minute.

I'm curious to read which solution others would prefer...

br,
Can Özdemir

Can Özdemir, 27.03.2009 15:21:
Personally I prefer storing them in the database for various reasons:

1) you get the full transactional control over the storage process
2) you don't have to worry about the number of files in a directory
3) you only need to backup a single source
4) you don't need to control (and manage) access to the data in two different places (O/S security for the files and DBMS security)

Whether the database solution has an impact on your application performance depends on the application.

We have successfully been using a servlet in a Tomcat container to serve images from the database, combined with an Apache in front that caches them for a certain amount of time, so that not each page hit gets them from the database.

Storing images in the file system has on big disadvantage: you will need to make sure they are distributed over several directories, especially with the large number you expect. Even modern filesystems seem to choke on directories with a large number of files in them.

The only show-stopper for database storage would be an application that has to retrieve the images from a filesystem and cannot handle images in a database.

Especially not having to worry about directory layouts or synchronizing access rights is a big plus in my eyes. Being able to store the data in a transactional manner is another benefit.

Thomas

Thomas Kellerer wrote:
True, but the down side should also be mentioned:
Many images in your database will make the database big.
Very big databases are difficult to handle and backup.

Particularly if only a small fraction of a large number of
images gets updated or deleted, it will be much easier to
backup files in a file system and use some incremental
backup technique.

Also, there will be some overhead reading an image from a database
compared to reading an operating system file. With images you might
even be able to have the database on one machine and the images on a
different machine, which might ease performance scaling.

Millions of 10K images should amount to something around 100GB.
Do you mind backing up a 100GB database?
Would the hurt outweigh the advantages Thomas Keller listed?

Yours,
Laurenz Albe

"Thomas Kellerer" <YQDHXVLMUBXG (AT) spammotel (DOT) com>
I found another solution to my specific problem - I create the images on
demand (could achieve response times of 100ms and less), which makes them
more current.
But even if I had to store them somewhere I'd opt for the file system
approach now.
There's a discussion about this topic on
http://stackoverflow.com/questions/3...age=1#sort-top.
And one comment I found is: "Blob fields are kind of like the off-road
capabilities of SUVs - most people don't use them, those who do usually get
in trouble, and then there are those who do, but only for the fun of it."
Filesystems are meant for serving files, so they should do a better job
compared to DBs. On the other hand the number of files might be a problem,
but there are 2 solutions that instantly come to my mind to this:
1) Distribute the files equally over a set of directories (by hashing).
2) Distribute the directories equally over multiple filesystems.

But of course, the advantages you named are true.

br,
Can Özdemir