Re: BLOB performance test FYI

From: Peter V Cooper <pvcooper(at)adelphia(dot)net>
To: "Joe Shevland" <jshevland(at)j-elite(dot)com>, "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 16:15:32
Message-ID: 5.1.0.14.0.20020418082850.00afe408@mail.dc3.adelphia.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

This sounds reasonable and like something I might use as I am doing this myself
manually already in Postgres & Oracle. I only have one install of postgres
and many w/ Oracle. We manage Objects like large images manually. It would
be nice to have this in postgres. I would use it now as I have to use the
os filesystem
and synchronize using a sequence already. Security and especially
non-cleanup on
delete are real problems. Just one user opinion.

BTW, I have never used a DBMS for this in the past because I was
comfortable with
having a sequence in the DB/hashed set of dirs and binary files in those
dirs. The
reason for hashed directories is twofold one to keep the number of directory
entries lower when working with millions of rows/files and two when you overrun
the filesystem then it is possible to symlink some of the dirs to another
file system.
This could create problems if you lost some of the files to a drive failure
but it
would be temporary as the system admin would replace/repair and recover. My
next task is to give my FE Java class multiple filesystem places to
automatically
manage - this would be cool as well.

PS, I have done development since 1979 at OS(UNIX) DB (Informally Helped Oracle
in 1983) and Application levels. I have never added code/my help to an Open
Source
project. Where do I find info on this? I might be inclined to work on something
like this if anyone would want me to. I have a lot of source which does much of
what I have spoken of. Any suggestions or should I 'butt out' so to speak.

At 02:49 PM 4/18/2002 +1000, Joe Shevland wrote:
>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.
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html

In response to

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Justin Clift 2002-04-18 16:40:49 Re: BLOB performance test FYI
Previous Message David Wall 2002-04-18 15:36:40 Re: BLOB performance test FYI