Re: Storing Digital Video

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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461