Re: [GENERAL] lo_* interface ...
От | Howie |
---|---|
Тема | Re: [GENERAL] lo_* interface ... |
Дата | |
Msg-id | Pine.LNX.3.96.990901045208.22274E-100000@rabies.toodarkpark.org обсуждение исходный текст |
Ответ на | lo_* interface ... (The Hermit Hacker <scrappy@hub.org>) |
Список | pgsql-general |
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."
В списке pgsql-general по дате отправления: