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 3DA6E9DC955 for ; Sat, 11 Feb 2006 16:32:31 -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 91162-08 for ; Sat, 11 Feb 2006 16:32:30 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id A38469DC941 for ; Sat, 11 Feb 2006 16:32:27 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id 38B0739853; Sat, 11 Feb 2006 20:32:29 +0000 (UTC) Received: (hashcash-sendmail, from uid 1001); Sat, 11 Feb 2006 14:32:28 -0600 Date: Sat, 11 Feb 2006 14:32:28 -0600 From: "Jim C. Nasby" To: Jan Peterson Cc: "Craig A. James" , Nate Byrnes , pgsql-performance@postgresql.org Subject: Re: Storing Digital Video Message-ID: <20060211203228.GS57845@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> <72e966b00602091514p21332e7fw60fdec07c2ab56ef@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <72e966b00602091514p21332e7fw60fdec07c2ab56ef@mail.gmail.com> X-Operating-System: FreeBSD 6.0-RELEASE amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060211:jan.l.peterson@gmail.com::Qt8VEbM4F0cc0C3o:0000000000000 0000000000000000000000001Awx X-Hashcash: 1:20:060211:cjames@modgraph-usa.com::HX/RamFjt5aP7EcZ:00000000000000 000000000000000000000000194d X-Hashcash: 1:20:060211:nate@qabal.org::QTcIokvzYxdbYiAo:0001cMD X-Hashcash: 1:20:060211:pgsql-performance@postgresql.org::Bm9yD4ABR9fVxCuV:00000 0000000000000000000000005t5R X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.104 required=5 tests=[AWL=0.104] X-Spam-Score: 0.104 X-Spam-Level: X-Archive-Number: 200602/130 X-Sequence-Number: 17116 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