pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chad Wagner <chad.wagner@gmail.com>
To: Steve Holdoway <steve.holdoway@firetrust.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: blobs
Date: Wed, 31 Jan 2007 18:45:15 -0500
Message-ID: <81961ff50701311545h50e457c4scb108037d6cf5c16@mail.gmail.com> (raw)
In-Reply-To: <20070201104606.5ea9e44e.steve.holdoway@firetrust.com>
References: <20070201104606.5ea9e44e.steve.holdoway@firetrust.com>

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/

view thread (9+ messages)  latest in thread

Message-ID: <81961ff50701311545h50e457c4scb108037d6cf5c16@mail.gmail.com>
Permalink:  ../81961ff50701311545h50e457c4scb108037d6cf5c16@mail.gmail.com/
Also on:    postgresql.org/message-id/81961ff50701311545h50e457c4scb108037d6cf5c16@mail.gmail.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: chad.wagner@gmail.com, steve.holdoway@firetrust.com
  Subject: Re: blobs
  In-Reply-To: <81961ff50701311545h50e457c4scb108037d6cf5c16@mail.gmail.com>
  References: <20070201104606.5ea9e44e.steve.holdoway@firetrust.com> <81961ff50701311545h50e457c4scb108037d6cf5c16@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