Re: Storing images in database for web applications

Thom,

> I'm wondering if anyone has experience of storing and getting images to and
> from a database?

Yes. For a customer I have one application running for ~8 years which
stores images (and various other BLOBS) within a PostgreSQL database.
Started with 7.something, now running on 8.3; allways used BYTEA.

It is a intranet and extranet application, so it does not get
slashdotted or reddited.

Another application is a public website, also storing HTML(fragments)
and images within a PostgreSQL database.

> The benefits I can see is that images are managed in one place and
> accessibly easily by all servers.  The problem is putting everything in one
> place (all eggs in one basket), so if the server goes down, all sites lose
> their images once their cache has expired... unless we implemented a system
> that falls back to cache if connection fails, even if cache has expired.

Your analyzes is correct. Other benefits are:

- no filesystem fuss (rights, sharing, locking, names (windows / unix)
- options for security: in my application, editing happens within a
strongly firewalled VPN. The webserver in the big, bad internet only
accesses the database; so the attack vectors are limited.
- transactional save: no problems with "partially saved images",
"images locked by whatever"
- mime information etc. can be stored alongside the bytes
- reporting options

drawbacks are:

- scaling is more challenging: static content from filesystems can be
replicated ( as you do), replicating a database is more difficult
- different performance characteristics then filesystem (search
usually faster (index), but access to image contents has more layers
to the disc)
- backups of the database grow huge, and usually the dump of a file in
the database is bigger then the file itself. (usually, because the
effects of compression are hard to judge before)
- dump / restore times grow

Best wishes,

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
%s is too gigantic of an industry to bend to the whims of reality