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@xxxxxxxxx>
