> what is the recommended way to work with LOBs in Postgres 7.2?
>
> there are certain performance related issues with BYTEA if it is read as a
> whole and not by chunks
From what I can see in the code, bytea field data for the entire result set are sucked into memory when the query is
executed,which is a potential performance hit, whereas with LOB's (using an oid as the field type), the input stream is
openedonly when the column is referenced. I hope I'm right in saying that.
> on the other hand storing LOBs as Large Objects and using a propriety API
> does not seem like a good idea to me (besides the docs mention more
> limitation of the Large Objects method)
Working with LOB's (using an oid column type) does use a proprietary API internally, but that's hidden from view when
usingthe getBytes() and getBinaryStream() methods. I believe there's a flag that controls how the driver behaves with
thesemethods (I think it defaults to looking for bytea columns if it detects a 7.2 backend, other it'll try the LOB
API).With LOB's you need to worry about cleaning the unreferenced LOB data from the database however often it suits, as
deletingthe tuple won't delete the referenced data (there's a contrib/vacuumlo, not sure if its still maintained).
There's potentially security risks with LOB's too (not bytea); they are all stored in one rotund global table, and I
thinkthat any user that can access any database has access to the LOB data. Again, I hope I haven't got that wrong.
> so I wander - what method do u people use to store LOBs when working with
> JDBC applications? BYTEA is not quite the JDBC type LOB where u expect to
> work with LOB locators and not retrieve the whole data record (similar to
> Oracle's LONG), on the other hand working with Large Objects
> induces certain
> inefficiency (custom API, certain limitations)
>
> any advice?
I've moved to bytea for the current thing I'm working on, as that seems to be the preferred path, but haven't done any
testsof performance yet vs LOB's.
Cheers,
Joe
(I wonder if there's some way we can implement 'lazy' loading of the bytea data? Like obtain the input stream/entire
chunkfrom the backend only when the column is referenced. Happy to have a crack at this if it sounds feasible.)