On Feb 11, 2006, at 9:41 AM, Stefan Arentz wrote:
Hi there,
I'm looking at the design for an application where i have to store
potentially many gigabytes worth of small blobs of varying sizes
(8K - 64K) in a database. I've always been told that it is smarter
to store references to files in the database, and I think that is
true in a lot of cases. But here I need this data to be
transactional and probably replicated so I'm looking into storing
it directly in the db.
I know this is all rather vague but I'm interested in more
experienced pgsql user's opinions about this. Is it smart? Would it
work? Or would something like this blow up the db?
It would work just fine. Postgresql is pretty good at handling larger
data elements like this, and will transparently behind the scenes store
them in a look-aside table, compressed and pointed to by the main
table (look for TOAST in the manual for the full info).
For smaller chunks of data I'd use text fields if they're text, bytea
fields id they're non-text.
You can use "large objects" too, but I suspect the additional
features they offer (the ability to read just part of the object, say)
would be far outweighed by the inconvenience in using them
compared to bytea for your app. (They also tend to be less
well-supported by some client interfaces).
Cheers,
Steve
