Date: Wed, 4 Aug 2010 09:43:33 -0500
From: Kenneth Marshall <ktm@rice.edu>
To: Joshua Gooding <JGooding@ttitech.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Some quick questions
Message-ID: <20100804144333.GW12093@aart.is.rice.edu>
References: <4C597794.80300@ttitech.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4C597794.80300@ttitech.net>
User-Agent: Mutt/1.5.16 (2007-06-09)

On Wed, Aug 04, 2010 at 10:22:12AM -0400, Joshua Gooding wrote:
>  I am using Postgres 8.4 with 10 partition tables.  We'll call them 
> reports_00 through reports_09.
>
> I have a field that is a BIGINT which is a 13 digit number that is the 
> epoch time, which is the constraint that the table is partitioned on. 
> (Between time x and y).  All of the partitions hold 10 weeks of data.   The 
> idea is that I would like to write a script that would truncate and drop 
> the oldest week's table (after 10 weeks), rename the oldest remaining 9 
> tables, create a new table, with the current and future epoch date in the 
> constraint, and continue on my merry way.
>

We are using a mod() in the CHECK constraint and the trigger to
load a set of tables in cycle: 1->2->3->1... Then we use a cronjob
to truncate the "next" table before data starts to be loaded into
it. The tables are never actually renamed because the mod() function
takes care of the reuse in the correct order.

> Is there anything like this already in postgres?  Secondly can it be done 
> without manual intervention?  Can I do this in a function and have it auto 
> run at a certain "time" based on epoch?  This is something that I have 
> never gotten into so this is new territory for me, so please forgive me if 
> I am asking any newbie questions here.

PostgreSQL does not have built-in time scheduled jobs. You will
need to use cron for that.

>
> I've tweaked the server that I am testing postgres on.  I'm basically doing 
> side by side comparisons with Oracle, trying to see if we can get the same 
> or close to Oracle's performance.  I've read the Wiki article on tuning the 
> PostgreSQL server, and I believe that I have gotten it close, but there is 
> still a substantial gap.  Say I have a machine with a 4 core processor and 
> 16GB of ram (across 4 sticks), can I tweak the configuration to  use all 4 
> cores and 1GB of ram from each physical stick on the machine?  This is 
> running on a Fedora Core - 12 machine.  Is that an OS issue or is than a 
> Postgres configuration question?
>

The OS should manage system resources. PostgreSQL should be tuned
based on the amount of resources available to it.

Cheers,
Ken