On Tue, 31 Aug 1999, The Hermit Hacker wrote:
>
> I've been asked about the performance/stability of using BLOBs (lo_*)
> under PostgreSQL, and having no experience with them myself, I'm looking
> for examples of sites that are, including such stats like size of the
> database, max BLOB size, performance and such...
i was planning on moving a mysql database that makes extensive use of
BLOBs to postgres, but the LO support is very space consuming. otherwise,
pgsql is great, dont get me wrong ( this is actually the only db i run
that's been left in mysql-land ). the lo support is stable, or at least
it seemed to be, when i was using it. there was a NULL bug/problem with
the lo package in the contrib dir, though.
for automatic deletes when the lo's corresponding row was deleted, one
would need to use the contrib/lo/ pkg and have a trigger on the table.
unfortunately, this trigger goes nuts when the lo column is null. should
be an easy fix; check for NULL before trying lo_unlink(). you'd want to
use the lo pkg; it just makes life easier.
the size of the database ( ie: tables ) doesnt get significantly larger
since the LO is stored as an OID. there's a physical file, xinv_<oid#>,
under the db dir, however. i never dug into the code, but the file seemed
to be some sort of custom structure/format; the imported object was 1.5k,
but the xinv_<oid> file was larger. if you need specifics i can get those
for you.
working with LO's was somewhat easy; lo_import() reads in the data, makes
a file under the db dir, and returns an oid. lo_export() takes that oid
and exports the data to the filesystem. unfortunately, that brings space
considerations and fs performance into play; in our app, just viewing an
image required querying the db ( granted ), exporting the object from the
db into the filesystem, read()ing & displaying that object, then
unlink()ing it. its a round-about way of doing it, but Oracle's pretty
much the same. to physically remove a LO, one would need to lo_unlink()
it or use the previously mentioned lo pkg in the contrib dir.
overall, the filesize of the LO's ( when compared to the actual data we
sent it ) and having to 'export' the LO into the filesystem were the two
reasons that the db is still mysql-based. mysql does all the BLOB stuff
internally, storing the data in the table. makes for a rather large table
( ours is currently just under 200m, the pgsql-based version came in at
over 500m ) and some odd displays if one did a 'select *' from the
blob-table, but otherwise works nicely.
just fyi, db2 has the ability to store LONG ( aka blob ) data in a
separate tablespace. might be something to look into once postgres
supports tablespaces. else your db dir/partition fills up _very_ quickly.
---
Howie <caffeine@toodarkpark.org> URL: http://www.toodarkpark.org
"The distance between insanity and genius is measured only by success."
