Обсуждение: Some quick questions

  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.

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.

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?

Any advice or guidance would be greatly appreciated.

-- 
Joshua Gooding

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