Re: Table PARTITION - Mailing list pgsql-sql

* 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.decellphone: +49 174 7066481
 
----------------------------------------------------------------------- DSL ab 0 Euro. -- statische IP -- UUCP --
Hosting-- Webshops --
 
---------------------------------------------------------------------