Re: Best way to store and retrieve photo from PostGreSQL

On Feb 25, 2007, at 9:26 AM, Tomas Vondra wrote:

>
>> 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).

You just need to implement it correctly. I've done this by using an
in-database delete queue that's polled by an external process to
delete the image files.

For external image files you don't need to be perfectly
transactional, as long as the failure mode is occasionally leaving a
file in place when it shouldn't be, as all that does is leak a little
filesystem space which can easily be recovered by a periodic task.

> 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).

The overhead of serving images from postgresql is much higher than
serving them directly from the filesystem (as is the overhead of
backing the data up). For simple, static images the advantages of
full transaction support on the data as well as the metadata are
likely to be fairly minimal.

Both approaches work, which is going to be better will depend on the
details of what you're doing with the images and how far you want it
to scale. (But I can't think of any case where *my* preferred
approach would be to keep them in the DB).

Cheers,
Steve