Re: Best way to store and retrieve photo from PostGreSQL

> Store the pictures in the filesystem and only the path, description and
> other metadata in the database. My suggestion ;-)
>
>
> Andreas
>
Don't do that - the filesystems are not transactional (at least not the
usual ones), so you'll lose the ability to use transactions. Imagine
what happens when you do an unlink() and then the transaction fails for
some reason - there's no way to 'rollback' the filesystem operation.
I've seen this solution (storing images in filesystem) mostly in MySQL
applications, but that's because of (a) lack of transactions in MySQL
and (b) somehow sub-optimal handling of binary data as MySQL loads all
the data even if it's not needed (this was true for MySQL 3.23 - I'm not
sure about the current releases).

Anyway, I do recommend storing images in the database, using a 'bytea'
column for the binary data (and load them only if reallly needed, using
proper projection). You can do some benchmarks, but I've never head
performance problems with it on PostgreSQL and the ability to use
transactions was invaluable (it saved us hundreds of hours when the
machine went down for some reason).

Tomas