Thread: BLOBs
I have always been advised that storing BLOBs in a database (any DBMS) creates too much of a problem (i.e. query time).
The recent thread pg_restore problem has me believing otherwise.
I have long been considering this for my current application, but have been fearful of implementing it.
Is there anyone using BLOBs that is having a problem with access times? Or is storing BLOBs in a DB a good idea?
TIA
Chad
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
I hope this isn't getting too academic, but why not store the path to
blobs in the DB and access them in some other fashion?
Rgs,
Aurangzeb
On Fri, 16 Aug 2002, Warwick Hunter wrote:
: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
:
--
Aurangzeb M. Agha | Email : aagha@bigfoot.com
| Home : +3 010 8959.558
34 Nafsika St. | Direct: +3 010 8995.875
Voula 16673 | Mobile: TBD
Greece | Voice : 415 412 4234 (US Only)
| e-Fax : 978 246.0770
PGP ID: 0x68B3A763 |
"Those who would give up essential liberty to purchase a little
temporary safety deserve neither liberty nor safety."
- Benjamin Franklin
That is exactly what i have been doing to date. I have always thought that it would be cool to manage these files (.wav in my case) in a database, w/o the problem of some user mucking with a directory and screwing up the files. I believe that i'll give it try. Thanks for all your responses Chad ----- Original Message ----- From: "Aurangzeb M. Agha" <aagha@bigfoot.com> To: "Warwick Hunter" <whunter@agile.tv> Cc: "Chad Thompson" <chad@weblinkservices.com>; "pgsql-novice" <pgsql-novice@postgresql.org> Sent: Thursday, August 15, 2002 10:26 PM Subject: Re: [NOVICE] BLOBs > I hope this isn't getting too academic, but why not store the path to > blobs in the DB and access them in some other fashion? > > Rgs, > Aurangzeb > > On Fri, 16 Aug 2002, Warwick Hunter wrote: > > :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 > : > > -- > Aurangzeb M. Agha | Email : aagha@bigfoot.com > | Home : +3 010 8959.558 > 34 Nafsika St. | Direct: +3 010 8995.875 > Voula 16673 | Mobile: TBD > Greece | Voice : 415 412 4234 (US Only) > | e-Fax : 978 246.0770 > PGP ID: 0x68B3A763 | > > "Those who would give up essential liberty to purchase a little > temporary safety deserve neither liberty nor safety." > > - Benjamin Franklin > >
Hi ! With some experience and a general eye on various forums I decided some time ago not to use blobs, but a path to a file in your DB field. BR, aarni On Friday 16 August 2002 07:52 pm, you wrote: > That is exactly what i have been doing to date. I have always thought that > it would be cool to manage these files (.wav in my case) in a database, w/o > the problem of some user mucking with a directory and screwing up the > files. > > I believe that i'll give it try. > > Thanks for all your responses > Chad > ----- Original Message ----- > From: "Aurangzeb M. Agha" <aagha@bigfoot.com> > To: "Warwick Hunter" <whunter@agile.tv> > Cc: "Chad Thompson" <chad@weblinkservices.com>; "pgsql-novice" > <pgsql-novice@postgresql.org> > Sent: Thursday, August 15, 2002 10:26 PM > Subject: Re: [NOVICE] BLOBs > > > I hope this isn't getting too academic, but why not store the path to > > blobs in the DB and access them in some other fashion? > > > > Rgs, > > Aurangzeb > > > > On Fri, 16 Aug 2002, Warwick Hunter wrote: > > :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 > > > > -- > > Aurangzeb M. Agha | Email : aagha@bigfoot.com > > > > | Home : +3 010 8959.558 > > > > 34 Nafsika St. | Direct: +3 010 8995.875 > > Voula 16673 | Mobile: TBD > > Greece | Voice : 415 412 4234 (US Only) > > > > | e-Fax : 978 246.0770 > > > > PGP ID: 0x68B3A763 | > > > > "Those who would give up essential liberty to purchase a little > > temporary safety deserve neither liberty nor safety." > > > > - Benjamin Franklin > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
Aurangzeb > I hope this isn't getting too academic, but why not store the path to > blobs in the DB and access them in some other fashion? Not too academic at all. That is my next plan of attack. I didn't do it initially because I was being a bit lazy and was happy to let PostgreSQL look after cleaning up a transaction that is rolled back etc. Warwick -- Warwick Hunter Agile TV Corporation Voice: +61 7 5584 5912 Fax: +61 7 5575 9550 mailto:whunter@agile.tv http://www.agile.tv