Re: Storing images in database for web applications

On 27/11/2009 7:04 PM, Thom Brown wrote:

> But now we wish to redesign our various image upload systems into one
> system which will also make images available across various
> sites/companies and therefore servers. So one solution is to store
> images in a database. What I'm wondering is if this is an appropriate
> solution? What are the downsides? Could we expect severe performance
> hits?

It depends a bit on your needs. If you need facilities provided by the
database, such as transactional updates of images and associated
metadata, then it's probably a good choice. Otherwise, you'll be paying
the costs associated with storing blobs in a database without gaining much.

There *are* costs to using a database over a network file system -
mostly increased management. It's harder to do incremental backups and
harder to work with those incremental backups (see PITR and continuous
archiving) than it is with a regular file system. Storage requirements
may be slightly increased. Deletion of images requires additional
cleanup work (VACUUM) to free the space. Etc.

With a database, you can't use the sendfile(...) system call or the like
to transmit the data, so there's more work for the system when
transmitting query results. It's also not generally transmitted as raw
binary, but hex- or octal- encoded, which can be ugly to work with. You
can request it as pure binary instead if you're using libpq directly,
though.

Why don't you use a HTTP-based image server? It's pretty low overhead,
is easily cached by commonly available tools, etc. Failing that, there
are plenty of network file systems out there (NFSv4 and CIFS come to
mind) that would do the job.

> To help mitigate the issue of many sites continuously querying a
> database for images, we'd implement a caching system, so images would
> only be queried from the database probably around once an hour.

Memcached ?

--
Craig Ringer