X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 833019DC87F for ; Thu, 9 Feb 2006 19:14:55 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 58053-03 for ; Thu, 9 Feb 2006 19:14:57 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from xproxy.gmail.com (xproxy.gmail.com [66.249.82.198]) by postgresql.org (Postfix) with ESMTP id 477A09DCCBB for ; Thu, 9 Feb 2006 19:14:53 -0400 (AST) Received: by xproxy.gmail.com with SMTP id i30so204685wxd for ; Thu, 09 Feb 2006 15:14:56 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=CndRNFNxaf+y24SOMpTifFOWMfW3oNSlx9QydiQ8ww0wnfQVBSvaVoIVoTr1x1pfu8Zy+9zTnSDsSp4Bd67GmuwPUomS3d/HW2x3y+a6QGl70RllmPoSCfIgnbCivD/GTurLWXNN0jyQlELXQ6UyFhOPpb7SBCElTgI528OQ9Uw= Received: by 10.70.109.20 with SMTP id h20mr2415079wxc; Thu, 09 Feb 2006 15:14:10 -0800 (PST) Received: by 10.70.116.7 with HTTP; Thu, 9 Feb 2006 15:14:09 -0800 (PST) Message-ID: <72e966b00602091514p21332e7fw60fdec07c2ab56ef@mail.gmail.com> Date: Thu, 9 Feb 2006 16:14:09 -0700 From: Jan Peterson To: "Jim C. Nasby" Subject: Re: Storing Digital Video Cc: "Craig A. James" , Nate Byrnes , pgsql-performance@postgresql.org In-Reply-To: <20060209192621.GU57845@pervasive.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <3cf983d0601311632t4a2f068au1f958746c2b092a7@mail.gmail.com> <200602060930.31144.albert@sedifa.com> <43EB475C.3040708@qabal.org> <43EB5D59.1030207@modgraph-usa.com> <20060209192621.GU57845@pervasive.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200602/89 X-Sequence-Number: 17075 In my experience, you don't want to store this stuff in the database.=20 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