Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group