From: | Jan Peterson <jan(dot)l(dot)peterson(at)gmail(dot)com> |
---|---|
To: | "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com> |
Cc: | "Craig A(dot) James" <cjames(at)modgraph-usa(dot)com>, Nate Byrnes <nate(at)qabal(dot)org>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Storing Digital Video |
Date: | 2006-02-09 23:14:09 |
Message-ID: | 72e966b00602091514p21332e7fw60fdec07c2ab56ef@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(dot)l(dot)peterson(at)gmail(dot)com>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-02-09 23:22:00 | Re: Help with optimizing a sql statement |
Previous Message | Rafael Martinez | 2006-02-09 22:44:42 | Re: Help with optimizing a sql statement |