From: Jan Peterson <jan.l.peterson@gmail.com>
To: Jim C. Nasby <jnasby@pervasive.com>
Cc: Craig A. James <cjames@modgraph-usa.com>
Cc: Nate Byrnes <nate@qabal.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Storing Digital Video
Date: Thu, 9 Feb 2006 16:14:09 -0700
Message-ID: <72e966b00602091514p21332e7fw60fdec07c2ab56ef@mail.gmail.com> (raw)
In-Reply-To: <20060209192621.GU57845@pervasive.com>
References: <3cf983d0601311632t4a2f068au1f958746c2b092a7@mail.gmail.com>
<200602060930.31144.albert@sedifa.com>
<43EB475C.3040708@qabal.org>
<43EB5D59.1030207@modgraph-usa.com>
<20060209192621.GU57845@pervasive.com>
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>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jan.l.peterson@gmail.com, jnasby@pervasive.com, cjames@modgraph-usa.com, nate@qabal.org
Subject: Re: Storing Digital Video
In-Reply-To: <72e966b00602091514p21332e7fw60fdec07c2ab56ef@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox