On Thu, Feb 09, 2006 at 04:14:09PM -0700, Jan Peterson wrote: > In my experience, you don't want to store this stuff in the database. > In general, it will work fine, until you have to VACUUM the > pg_largeobject table. Unless you have a very powerful I/O subsystem, > this VACUUM will kill your performance. Good point about the vacuum issue; I haven't had to deal with vacuuming very large objects. > > You're forgetting about cleanup and transactions. If you store outside > > the database you either have to write some kind of garbage collector, or > > you add a trigger to delete the file on disk when the row in the > > database pointing at it is deleted and hope that the transaction doesn't > > rollback. > > Our solution to this problem was to have a separate table of "external > files to delete". When you want to delete a file, you just stuff an > entry into this table. If your transaction rolls back, so does your > insert into this table. You have a separate thread that periodically > walks this table and zaps the files from the filesystem. Sure, there's lots of ways around it. My point was that there *is* a tradeoff. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@xxxxxxxxxxxxx Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
