Re: Best way to store and retrieve photo from PostGreSQL

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

Dumb question: the picture data is written twice, right?  Once in the
WAL and once into the table?  So the argument can be reduced to:

1)  Load into tables for transactional support, con is the write-time hit
2)  Load into filesystem for faster load, but you have to provide
integrity by another route



--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com / www.andromeda-project.org
Office: 631-689-7200   Cell: 631-379-0010

::Think you may have a problem with programming? Ask yourself this
::question: do you worry about how to throw away a garbage can?