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

Search Discussions

  • Warwick Hunter at Aug 15, 2002 at 11:27 pm
    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
  • Aurangzeb M. Agha at Aug 16, 2002 at 4:28 pm
    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
  • Chad Thompson at Aug 16, 2002 at 4:53 pm
    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
  • Aarni Ruuhimäki / Megative Tmi / KYMI.com at Aug 16, 2002 at 11:59 pm
    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
  • Warwick Hunter at Aug 18, 2002 at 11:04 pm
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedAug 15, '02 at 5:41p
activeAug 18, '02 at 11:04p
posts6
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase