pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Martin Foster <martin@ethereal-realms.org>
To: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: [NOVICE] Extreme high load averages
Date: Thu, 10 Jul 2003 00:43:22 -0600
Message-ID: <3F0D0B0A.70506@ethereal-realms.org> (raw)
In-Reply-To: <3F0D5550.21678.4E122CF@localhost>
References: <Pine.LNX.4.44.0307071102390.1516-100000@zigo.dhs.org>
	<3F0D5550.21678.4E122CF@localhost>

Shridhar Daithankar wrote:
> 
> 
> I have an idea.
> 
> How about creating a table for each day. Use it for a while and rename it. 
> Since you can rename a table in transaction, it should not be a problem.
> 
> You can use inheritance if you want to query all of them. Using indexes and 
> foregin keys on inherited tables is a problem though.
> 
> That way deletion would be avoided and so would vacuum. It should be mich 
> lighter on the system overall as well.
> 
> Tell us if it works.
> 
> Bye
>  Shridhar
> 


Generally I won't be pulling 250K rows from that table.   It's 
maintained nightly during the general cleanup process where stale users, 
rooms and posts are removed from the system.   Then the system runs a 
normal VACUUM ANALYSE to get things going again smoothly.

Once a week a more detailed archiving takes place which runs an all out 
vaccume and re-index.    That's the so called plan at least.

As for creating a new table, that in itself is a nice idea.   But it 
would cause issues for people currently in the realm.   Their posts 
would essentially dissapear from site and cause more confusion then its 
worth.

Inheritance would work, but the database would essentially just grow and 
grow and grow right?

BTW, I can't thank you all enough for this general advice.   It's 
helping me get this thing running very smoothly.

	Martin Foster
	Creator/Designer Ethereal Realms
	martin@ethereal-realms.org





view thread (22+ messages)  latest in thread

Message-ID: <3F0D0B0A.70506@ethereal-realms.org>
Permalink:  ../3F0D0B0A.70506@ethereal-realms.org/
Also on:    postgresql.org/message-id/3F0D0B0A.70506@ethereal-realms.org

 · 

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-performance@postgresql.org
  Cc: martin@ethereal-realms.org, shridhar_daithankar@persistent.co.in
  Subject: Re: [NOVICE] Extreme high load averages
  In-Reply-To: <3F0D0B0A.70506@ethereal-realms.org>
  References: <Pine.LNX.4.44.0307071102390.1516-100000@zigo.dhs.org> <3F0D5550.21678.4E122CF@localhost> <3F0D0B0A.70506@ethereal-realms.org>

* 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