Re: BLOBs

From: Warwick Hunter <whunter(at)agile(dot)tv>
To: Chad Thompson <chad(at)weblinkservices(dot)com>
Cc: pgsql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: BLOBs
Date: 2002-08-15 23:27:10
Message-ID: 3D5C38CE.4030102@agile.tv
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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(at)agile(dot)tv http://www.agile.tv

In response to

  • BLOBs at 2002-08-15 17:41:30 from Chad Thompson

Responses

  • Re: BLOBs at 2002-08-16 04:26:27 from Aurangzeb M. Agha

Browse pgsql-novice by date

  From Date Subject
Next Message Aurangzeb M. Agha 2002-08-16 04:20:48 Re: Is my database done for?
Previous Message Clinton Adams 2002-08-15 18:40:23 Re: Distinct On