Re: Blobs in Postgresql

"Ron Olson" <tachoknight(at)gmail(dot)com> writes:

> Hi all-
>
> I am evaluating databases for use in a large project that will hold image
> data as blobs. I know, everybody says to just store pointers to files on the
> disk...

Well not everyone. I usually do, but if you're not handling these blobs under
heavy load independent of the database (like web servers) then either approach
works.

> So turning to Postgresql, can I get any recommendations, suggestions and
> tips on blob handling in the database? The image sizes will be pretty
> variable, from a few kilobytes to several hundred megabytes, so I need
> something that will handle the various file sizes, hopefully transparently.

There are basically two options. If you are not handling data that are too
large to copy around in memory, and you don't need to upload and download the
data in chunks (usually these are the same issue) then you can just store your
images in a bytea. Postgres transparently treats *all* large variable-sized
data whether text, bytea, arrays, like a blob. It stores it in a separate
table outside the main table.

If your data can sometimes be so large that you cannot manipulate the whole
thing in memory all at once (Keep in mind that Postgres expects to be able to
handle a few copies of the data at the same time. Conservatively expect 5
simultaneous copies to have to fit in memory.) then you'll have to look into
the large object interface which is a set of functions starting with lo_*

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com