Thread: Storing a kazillion small blobs in postgresql. Smart?
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? S.
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
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? > > S. > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > > well I store images and other files on the Database with ByteA fields works great leonel