A recent project of ours involved storing/fetching some reasonably large datasets in a home-brew datatype. The datasets tended to range from a few megabytes, to several gigabytes. We were seeing some nonlinear slowness with using native large objects with larger datasets, presumably due to the increasing depth of the btree index used to track all the little pieces of the blobs.
After some careful consideration, we implemented an alternative to large objects, a system based on storing files in a particular directory, and storing a reference to the files in the database. It worked and gave us good and consistent performance. However, it doesn't support transactions (no isolation, no rollback). We can probably implement some backend code to support such functionality, but the trick is getting the postgres server to keep our code in the loop (so to speak) about when a rollback should be done (and to when).
Is anyone aware of any hooks to support schemes such as ours, or has solved a similar problem?
Thank you.
**********************************************
Eric Davies, M.Sc.
Barrodale Computing Services Ltd.
Tel: (250) 472-4372 Fax: (250) 472-4373
Web: http://www.barrodale.com
Email: eric@barrodale.com
**********************************************
Mailing Address:
P.O. Box 3075 STN CSC
Victoria BC Canada V8W 3W2
Shipping Address:
Hut R, McKenzie Avenue
University of Victoria
Victoria BC Canada V8W 3W2
**********************************************