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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
