Re: BLOB performance test FYI - Mailing list pgsql-jdbc

Joe,

No I don't think it is crazy when you look at the other side of the
coin.  LargeObject's have their own set of problems and you need to
compare the pros and cons of each solution.  The reason I believe that
bytea wins over LargeObjects is because of the symantics of delete.
When you delete a row containing a bytea column the bytea data is gone.
  However when you delete a row containing an oid that references a
LargeObject you only delete the reference.  The LargeObject does not get
deleted.  If you want to use LargeObjects you need to code your own
deletes so that the LargeObjects get cleaned up, or you need to create
triggers on your tables so that it happens automatically.  Also
LargeObjects have essentially no security over them.  If you have access
to the database you can read all LargeObjects in that database
regardless of whether or not you have access to the table that contains
the oid for the LargeObject.  The casual user of binary data (which can
range from a few bytes to hundreds of megs) should have something out of
the box that works symantically as they would expect IMHO.  I find that
many users of bytea are storing gifs and html pages for web sites which
generally are small enough such that bytea works fine.  However there
certainly are circumstances where bytea is not the correct choice of
datatype for storing the data.

This is all explained to some degree in the documentation for 7.2.  It
is left as a choice to the user to use the datatype best suited for
their circumstances given the pros and cons of the different alternatives.

thanks,
--Barry

Joe Shevland wrote:
> Well, isn't it a bit crazy that bytea is the default JDBC BLOB type given the performance issues shown?
>
> The general public that start to pump binary data into a PostgreSQL database are going to be confused if performance
isthat bad... and that was a grunty server too with bulkloads of RAM, I shudder to think what'd happen to a client. 
>
> I mean, generally if you're storing binary data it is for things such as image or document storage (of course there's
many,many other purposes), so the binary data wil be large. 
>
> Joe
>
>
>>-----Original Message-----
>>From: Dave Cramer [mailto:Dave@micro-automation.net]
>>Sent: Wednesday, 17 April 2002 21:29
>>To: Joe Shevland
>>Cc: pgsql-jdbc@postgresql.org
>>Subject: Re: [JDBC] BLOB performance test FYI
>>
>>
>>Joe,
>>
>>Yes, Barry has commented on the reasons for this recently. The short
>>version is that when you are using bytea, the backend parses the input.
>>
>>Dave
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>