Re: BLOBs

Chad

I am using PostgreSQL to store BLOBS. I can't say that the
performance I am getting is wonderful, but it is adequate
for my purposes for now. Few people are every satisfied
with the performance of their database anyway :-)

I have done some performance testing on PostgreSQL and have
discovered that on an Linux Intel machine with reasonably fast
SCSI discs I can get average performance numbers like these:

Binary BLOB Size
300 KB read=24  ms write=185 ms del=105 ms
150 KB read=11  ms write=90  ms del=51  ms
   5 KB read=2.5 ms write=14  ms del=11  ms

I have done a small amount of tuning of the PostgreSQL
configuration. I plan to do some more.

> Or is storing BLOBs in a DB a good idea?

I have found PostgreSQL to be quite robust. I have a
test that deliberately kills the postmaster and postgres
subprocesses while doing write operations to see if I
can cause the database to be corrupted. This test
rarely fails.

This test fails more regularly if you rewrite the
data of an existing BLOB rather than create a new
one and delete the old one. So now I only ever create
a new BLOB.

I have seen one instance when the power on a machine
failed while writing a BLOB caused one BLOB to not
be restored correctly when the database came up.
I plan to investigate this and report a bug if
appropriate.

I looked at the bytea data type for storing my objects
as regular columns. However I was unable to make it work
with the normal SQL statements, the binary data always
upset the SQL command and the escaping routines didn't
seem to help.

Warwick
--
Warwick Hunter                    Agile TV Corporation
Voice: +61 7 5584 5912            Fax: +61 7 5575 9550
mailto:whunter@agile.tv           http://www.agile.tv