From: Jim C. Nasby <jnasby@pervasive.com>
To: Jan Peterson <jan.l.peterson@gmail.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: Sat, 11 Feb 2006 14:32:28 -0600
Message-ID: <20060211203228.GS57845@pervasive.com> (raw)
In-Reply-To: <72e966b00602091514p21332e7fw60fdec07c2ab56ef@mail.gmail.com>
References: <3cf983d0601311632t4a2f068au1f958746c2b092a7@mail.gmail.com>
<200602060930.31144.albert@sedifa.com>
<43EB475C.3040708@qabal.org>
<43EB5D59.1030207@modgraph-usa.com>
<20060209192621.GU57845@pervasive.com>
<72e966b00602091514p21332e7fw60fdec07c2ab56ef@mail.gmail.com>
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@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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: jnasby@pervasive.com, jan.l.peterson@gmail.com, cjames@modgraph-usa.com, nate@qabal.org
Subject: Re: Storing Digital Video
In-Reply-To: <20060211203228.GS57845@pervasive.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