Reply-To: <dpandey@secf.com>
From: "Dinesh Pandey" <dpandey@secf.com>
To: <weigelt@metux.de>, <pgsql-sql@postgresql.org>
Subject: Re: Table PARTITION
Date: Fri, 8 Apr 2005 10:22:14 +0530
Organization: Second Foundation India P Ltd.
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AcU73sEyKLQ3jSsmRKSrpF/nZaSylgAF+i/w
In-Reply-To: <20050408014744.GB18450@nibiru.borg.metux.de>
Message-Id: <20050408045521.DEAEB53C77@svr1.postgresql.org>

Yes, I will do in this way.... 


Thanks
Dinesh Pandey

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Enrico Weigelt
Sent: Friday, April 08, 2005 7:18 AM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Table PARTITION

* Richard Huxton <dev@archonet.com> wrote:
> Sean Davis wrote:
> >This is a totally selfish question, but IF someone has a few minutes, 
> >could he/she explain why table partitioning is such an important tool?
> 
> Say you have a large log-table, you could partition it by month. If 
> most queries only search the last month or two, a lot of your 
> partitioned data could be moved to cheaper/slower disks (via tablespaces).

You can solve this problem with multiple tables rules quite easily.
At this point you can also filter out some unused data (often historical
data requires less information than live data, because only the end result
of certain finished things is interesting for the future, but many things
needed as long as things are open are completely irrelevant for later usage,
i.e. an archive of accounting information for webhosters wont require
datails of single http requests)

Lets give me some examples on one of my customer's projects:

At fXignal - an forex market trading platform - we're maintaining an
long-time archive of all run orders. An "open" trade (you've bought some
position) has one order, while an "closed" trade (things are sold again) has
two. 
I.g we've got two kind of accesses to trade information:
a) viewing and manipulating open trades - active trading (must be fast!)
b) only viewing closed trades for reports (account report, etc) Also we've
got some information which are only interesting for open trades, ie. limits
(points where trade should be closed automatically).

We've solved this by having two tables: one for open trades and one for
archived (closed) trades. When an trade is opened, it goes to the open-trade
table and resides there until it goes to closed state (by setting a "closed"
flag). Once the trades has reached closed state its copied to the archive
table and removed from the open trade table by an rule. (see CREATE RULE). 

When archived trades get old (3 month) we need less information from that,
which has to be kept very long (several years). For that we catch the DELETE
on the archive table and copy data to the longtime archive before it gets
removed from the archive table.

For long time analyses we've got some views which map together interesting
information from all tables.


Well, this way we've got the same benefits as with partitions, with a 
little bit more coding work, but then with better control and filtering
out unneeded stuff.


cu
-- 
---------------------------------------------------------------------
 Enrico Weigelt    ==   metux IT service

  phone:     +49 36207 519931         www:       http://www.metux.de/
  fax:       +49 36207 519932         email:     contact@metux.de
  cellphone: +49 174 7066481
---------------------------------------------------------------------
 -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
---------------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster