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

Re: BLOB performance test FYI

From: "Joe Shevland" <jshevland(at)j-elite(dot)com>
To: "Dave Cramer" <Dave(at)micro-automation(dot)net>
Cc: "Anders Bengtsson" <ndrsbngtssn(at)yahoo(dot)se>,<pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: BLOB performance test FYI
Date: 2002-04-18 04:49:14
Message-ID: HEECIHEEJDBMCCGMGIOBIECMCHAA.jshevland@j-elite.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Its an interesting topic... when I use BLOB's its generally for storage of large things, documents, images, data files etc. Thats one view though; other uses like small binary data or encrypted stuff would be common too, so 'bytea' is an important type, but used in moderation (with the current JDBC driver) like anything I guess ;)

I care about all the things that PostgreSQL does, including access control, referential integrity and whatnot, but I think the important thing is I'm not particularly concerned about speed of access (when dealing with whole binary data, certainly don't need it indexed): what I mean by that is the binary file could happily reside on the local file system and be streamed to the client... maybe a 'softlink' to the data (aka the Large Object manager with oids almost), but I guess this is old ground, I'm thinking a new datatype that still has ACL's but is only a string pointing to the file on the local filesystem. I should probably be thinking of all the complexity this might add to the parser, optimisations and stuff. I'm not - as a result of aforementioned hop water ;)

So a LO/link datatype that obeys ACL's, can be integrated into the BE/FE protocol, would be rw-only by the postmaster user and stored in the db data directory perhaps, that fits in with pg_dump|restore, that could be streamed and referenced in result sets, that would be deleted on deletion of the link, and that doesn't break any existing functionality, perhaps with extensions to the SQL standard. That's all I'm after :) :)

No, I've no idea what all the issues are yet, I'm sure there's heaps - I think I need to burn some rosemary and sacrifice a goat to the Tom Lane alter ;) Maybe a new datatype 'bytefs', rejig the parser and all the rest. Or, is large binary data really the province of an RDBMS?

Cheers,
Joe

PS. Someone raised the PG-Java procedural language the other day. When that idea comes to fruition it will be an Incredibly Great Thing(TM).... but then I thought about about the separation of data and logic, and thought that it's overkill for the database to do that. Thoughts? I was thinking maybe a pseudo

PPS. We need an RFC or a good MIME type that stops digital signatures interfering with automatic appended footers. Outhouse is causing me grief with this list.

> Joe, Anders,
> 
> Please keep this discussion online. The more people we have looking at a
> possible solution, the better.
> 
> Dave
[snip]
> > > Instead of streaming large data into the
> > > backend, the client reads them into memory before sending them.
> > > It seems the LO parts of the driver has a different solution, that
> > > streams the data directly.
> > > It would take some major reworking of the JDBC driver to fix 
> this. I've
> > > looked at it, and concluded that it is possible, but that it 
> was too big
> > > for me to fix.


In response to

Responses

pgsql-jdbc by date

Next:From: Joe ShevlandDate: 2002-04-18 05:48:29
Subject: Re: using Date and setObject()
Previous:From: Tom LaneDate: 2002-04-18 04:19:01
Subject: Re: BLOB performance test FYI

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