Re: To Blob or Not to Blob? THAT is the question.

We've done A LOT of database work where images (as well as other large
MIME-type files) are part of the equation. We've almost always stuck with
the approach of keeping images outside the database. Here are the pros and
cons we consider.

Blob PROS:

1) At least you need to maintain only a single I/O path to data. This can
be especially useful in distributed environments. Opening a machine to
JDBC access on a port is easier to trust than opening a native file system
to the net. It also addresses file system access issues driven by
differing OS's for image consumers like GUI apps running under MSWindows
living together with batch/background apps running under Linux.

2) If transaction processing that mixes images and regular data is a
requirement, then a DBMS is going to give it to you. However, in cases
where you are dealing with basic business documents: scanned or rendered
sheets of paper, situations requiring MST requirements have been very rare.

Blob CONS/ Filesystem PROS:

1) Blobs elevate you to an entirely different level in terms of managing
database sizes, backups, restores, recoveries and replication.

2) Blob data types and functionality is, IMHO, an area that is not handled
consistently across most available products. You risk tying yourself to a
specific product. In our case, maintaining openness and flexibility is
important.

3) Allowing filesystem access to images/documents can give very simple
benefit to users without inflicting the overhead of program. If you know
how to find the image you are looking forward, you can very easily insert
it into a word document or email it to a co-worker.

Overall, I'd say there is no single answer to this question. It depends on
your situation and specific requirements. It's all a matter of the types
of slings and arrows your dealing with and just how outrageous your
fortune.

Hope this helps,

Marc Mitchell - Senior Application Architect
Enterprise Information Solutions, Inc.
Downers Grove, IL 60515
marcm(at)eisolution(dot)com

----- Original Message -----
From: "working4aliving" <working4aliving(at)hotmail(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>
Sent: Thursday, October 31, 2002 7:17 AM
Subject: [ADMIN] To Blob or Not to Blob? THAT is the question.

I have a java app that (is currently using MYSQL, but we're converting to
Postgre soon) references small (62kb) images for viewing on the screens of
the app.

Does anyone have any thoughts on database design that they can share with
me that would validate why I would want to put Images into blobs in the
database, as opposed to just using the filesystem? I haven't seen much
info on (philosophically speaking, I guess) pro's and con's of using blobs
vs. the filesystem reference to the images, so I don't have much data to
make a decision on if blobs for this purpose are a good idea, other than
the fact that I feel IMO that maintenance for them would be much easier in
the filesystem.

Thanx in advance.