Re: Storing Digital Video

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.

> 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.

We found that using a procedural language (such as pl/Perl) was fine
for proof of concept.  We did find limitations in how data is returned
from Perl functions as a string, combined with the need for binary
data in the files, that prevented us from using it in production.  We
had to rewrite the functions in C.

        -jan-
--
Jan L. Peterson
<jan.l.peterson@gmail.com>