Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 102D69FB308 for ; Wed, 31 Jan 2007 19:45:21 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 88017-08 for ; Wed, 31 Jan 2007 19:45:17 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.187]) by postgresql.org (Postfix) with ESMTP id 8CE759FB24D for ; Wed, 31 Jan 2007 19:45:17 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id x4so727745nfb for ; Wed, 31 Jan 2007 15:45:16 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=pu6jzXrvxlYMfFaoxWnmbbWKcMRjrAFXzoByWRDhWfTofsGrM6XBHMs+ojr45ffa96EVdtOtDtt9LGIvmn547d0JN5YFur3vp2O6Kyw6/ZGMmDTJ6jRtC09FweTYyBbQsMzFsbbnAiCQxmczX4KCFmqcjIIW8oUln8OCWEAlelM= Received: by 10.48.210.16 with SMTP id i16mr3490126nfg.1170287115358; Wed, 31 Jan 2007 15:45:15 -0800 (PST) Received: by 10.48.210.4 with HTTP; Wed, 31 Jan 2007 15:45:15 -0800 (PST) Message-ID: <81961ff50701311545h50e457c4scb108037d6cf5c16@mail.gmail.com> Date: Wed, 31 Jan 2007 18:45:15 -0500 From: "Chad Wagner" To: "Steve Holdoway" Subject: Re: blobs Cc: pgsql-admin@postgresql.org In-Reply-To: <20070201104606.5ea9e44e.steve.holdoway@firetrust.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_20878_30976289.1170287115019" References: <20070201104606.5ea9e44e.steve.holdoway@firetrust.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/309 X-Sequence-Number: 24465 ------=_Part_20878_30976289.1170287115019 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 1/31/07, Steve Holdoway wrote: > > The table I'm concerned with at the moment have (currently) 5 million > rows, with a churn of about 300,000 rows a week. The table has about a > million hits a day, which makes it the main potential bottleneck in this > database. Why would it be a "bottleneck"? If you are updating or deleting 300K a week, definitely make sure you take a look at autovacuum and turn it ON. We need to store some large ( 0 -> 100kB ) data with each row. Would you > recommend adding it as columns in this table, given that blobs will be > stored in the pg_largeobject table anyway, or would you recommend a daughter > table for this? Depends on how you are querying the table. This is really a database modeling question, and leads into many many more questions. I would say if your frequently range scanning the table (selecting several rows) and in those cases you rarely need the "blob", then I would fork it off into a child table. If the "blob" is rarely accessed, and only accessed directly, then definitely a child table in my book. The reason is if your frequently fetching rows from this table and rarely using the blob all you are doing is consuming memory that could be better used for other things, and spinning I/O when it is not necessary. Any other suggestions on how to avoid performance problems with this table ( > hardware is dual Xeon, 4GB mem, 2 hardware raid channels for storage + 1 for > logs, all running debian 32 bit ). > It really has to do with how you design your schema. -- Chad http://www.postgresqlforums.com/ ------=_Part_20878_30976289.1170287115019 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 1/31/07, Steve Holdoway <steve.holdoway@firetrust.com> wrote:
The table I'm concerned with at the moment have (currently) 5 million rows, with a churn of about 300,000 rows a week. The table has about a million hits a day, which makes it the main potential bottleneck in this database.

Why would it be a "bottleneck"?  If you are updating or deleting 300K a week, definitely make sure you take a look at autovacuum and turn it ON.


We need to store some large ( 0 -> 100kB ) data with each row. Would you recommend adding it as columns in this table, given that blobs will be stored in the pg_largeobject table anyway, or would you recommend a daughter table for this?

Depends on how you are querying the table.  This is really a database modeling question, and leads into many many more questions.  I would say if your frequently range scanning the table (selecting several rows) and in those cases you rarely need the "blob", then I would fork it off into a child table.  If the "blob" is rarely accessed, and only accessed directly, then definitely a child table in my book.

The reason is if your frequently fetching rows from this table and rarely using the blob all you are doing is consuming memory that could be better used for other things, and spinning I/O when it is not necessary.
 

Any other suggestions on how to avoid performance problems with this table ( hardware is dual Xeon, 4GB mem, 2 hardware raid channels for storage + 1 for logs, all running debian 32 bit ).

It really has to do with how you design your schema.


--
Chad
http://www.postgresqlforums.com/ ------=_Part_20878_30976289.1170287115019--