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