pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTable PARTITION
10+ messages / 6 participants
[nested] [flat]
* Table PARTITION
@ 2005-04-07 11:38 Dinesh Pandey <dpandey@secf.com>
0 siblings, 1 reply; 10+ messages in thread
From: Dinesh Pandey @ 2005-04-07 11:38 UTC (permalink / raw)
To: pgsql-sql
How can we create oracle's table with partition in Postgres. (How to create
Table partion in postgres)
CREATE TABLE invoices
(invoice_no NUMBER NOT NULL,
invoice_date DATE NOT NULL,
comments VARCHAR2(500))
PARTITION BY RANGE (invoice_date)
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001',
'DD/MM/YYYY')) TABLESPACE users
Regards
Dinesh Pandey
----------------------------------------------------------------------------
------
Dinesh Pandey
Sr. Software Engineer
Second Foundation (India) Pvt. Ltd.
Plot# 52
Industrial Area, Phase II
Chandigarh. (India)
PH: (O) 0172-2639202, Extn: 233
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Table PARTITION
@ 2005-04-07 12:06 Richard Huxton <dev@archonet.com>
parent: Dinesh Pandey <dpandey@secf.com>
0 siblings, 2 replies; 10+ messages in thread
From: Richard Huxton @ 2005-04-07 12:06 UTC (permalink / raw)
To: dpandey@secf.com; +Cc: pgsql-sql
Dinesh Pandey wrote:
> How can we create oracle's table with partition in Postgres. (How to create
> Table partion in postgres)
>
> CREATE TABLE invoices
> (invoice_no NUMBER NOT NULL,
> invoice_date DATE NOT NULL,
> comments VARCHAR2(500))
>
> PARTITION BY RANGE (invoice_date)
> (PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001',
> 'DD/MM/YYYY')) TABLESPACE users
By hand, I'm afraid (although I think people are looking into this
feature for version 8.1)
There's lots of discussion in the mail archives about how people are
handling this. You're basically looking at some combination of
- partial/conditional indexes
- unions
- inheritance
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Table PARTITION
@ 2005-04-07 12:09 Dinesh Pandey <dpandey@secf.com>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 10+ messages in thread
From: Dinesh Pandey @ 2005-04-07 12:09 UTC (permalink / raw)
To: 'Richard Huxton' <dev@archonet.com>; +Cc: pgsql-sql
Hi Richard,
I am using postgres 8.0.1. It allows us to create TABLESPACE but I don't
know how to create PARTITION in postgres.
Can you pls tell me little more.
Thanks
Dinesh Pandey
-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Thursday, April 07, 2005 5:36 PM
To: dpandey@secf.com
Cc: 'PostgreSQL'
Subject: Re: [SQL] Table PARTITION
Dinesh Pandey wrote:
> How can we create oracle's table with partition in Postgres. (How to
> create Table partion in postgres)
>
> CREATE TABLE invoices
> (invoice_no NUMBER NOT NULL,
> invoice_date DATE NOT NULL,
> comments VARCHAR2(500))
>
> PARTITION BY RANGE (invoice_date)
> (PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001',
> 'DD/MM/YYYY')) TABLESPACE users
By hand, I'm afraid (although I think people are looking into this feature
for version 8.1)
There's lots of discussion in the mail archives about how people are
handling this. You're basically looking at some combination of
- partial/conditional indexes
- unions
- inheritance
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Table PARTITION
@ 2005-04-07 12:16 Sean Davis <sdavis2@mail.nih.gov>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 1 reply; 10+ messages in thread
From: Sean Davis @ 2005-04-07 12:16 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql; dpandey@secf.com
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?
Thanks,
Sean
On Apr 7, 2005, at 8:06 AM, Richard Huxton wrote:
> Dinesh Pandey wrote:
>> How can we create oracle's table with partition in Postgres. (How to
>> create
>> Table partion in postgres)
>> CREATE TABLE invoices
>> (invoice_no NUMBER NOT NULL, invoice_date DATE NOT NULL,
>> comments VARCHAR2(500))
>> PARTITION BY RANGE (invoice_date)
>> (PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001',
>> 'DD/MM/YYYY')) TABLESPACE users
>
> By hand, I'm afraid (although I think people are looking into this
> feature for version 8.1)
>
> There's lots of discussion in the mail archives about how people are
> handling this. You're basically looking at some combination of
> - partial/conditional indexes
> - unions
> - inheritance
>
> --
> Richard Huxton
> Archonet Ltd
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Table PARTITION
@ 2005-04-07 12:32 Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 10+ messages in thread
From: Richard Huxton @ 2005-04-07 12:32 UTC (permalink / raw)
To: dpandey@secf.com; +Cc: pgsql-sql
Dinesh Pandey wrote:
>
> Hi Richard,
>
> I am using postgres 8.0.1. It allows us to create TABLESPACE but I don't
> know how to create PARTITION in postgres.
>
> Can you pls tell me little more.
You can't I'm afraid. You can use inheritance / a view unioning tables
to do something similar though. Check the mailing-list archives for
notes on these.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Table PARTITION
@ 2005-04-07 12:37 Richard Huxton <dev@archonet.com>
parent: Sean Davis <sdavis2@mail.nih.gov>
0 siblings, 1 reply; 10+ messages in thread
From: Richard Huxton @ 2005-04-07 12:37 UTC (permalink / raw)
To: Sean Davis <sdavis2@mail.nih.gov>; +Cc: pgsql-sql; dpandey@secf.com
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).
After (say) 24 months, you can start dropping the oldest partitions, and
this should take place without affecting any other partitions.
How you partition your data depends on how you plan to access it. If you
don't query your log-tables by date there's not much point in
partitioning by date.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Table PARTITION
@ 2005-04-08 01:47 Enrico Weigelt <weigelt@metux.de>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 10+ messages in thread
From: Enrico Weigelt @ 2005-04-08 01:47 UTC (permalink / raw)
To: 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.de
cellphone: +49 174 7066481
---------------------------------------------------------------------
-- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
---------------------------------------------------------------------
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Table PARTITION
@ 2005-04-08 04:52 Dinesh Pandey <dpandey@secf.com>
parent: Enrico Weigelt <weigelt@metux.de>
0 siblings, 0 replies; 10+ messages in thread
From: Dinesh Pandey @ 2005-04-08 04:52 UTC (permalink / raw)
To: weigelt@metux.de; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* TABLE PARTITION
@ 2011-02-01 13:31 NEVIN ALEX <nevin.alex@hotmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: NEVIN ALEX @ 2011-02-01 13:31 UTC (permalink / raw)
To: pgsql-sql
Hi,
I am Nevin Alex and I am using postgresql database for a year . But I have’nt used table partitions: Please help me to do it in a better way. I got it from the documentation that it is an inheritance capability and the Trigger working but for dynamic data how can I implement that.
Thanks in advance =
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: TABLE PARTITION
@ 2011-02-06 12:10 Vibhor Kumar <vibhor.aim@gmail.com>
parent: NEVIN ALEX <nevin.alex@hotmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Vibhor Kumar @ 2011-02-06 12:10 UTC (permalink / raw)
To: NEVIN ALEX <nevin.alex@hotmail.com>; +Cc: pgsql-sql
On Feb 1, 2011, at 10:31 PM, NEVIN ALEX wrote:
> Hi,
> I am Nevin Alex and I am using postgresql database for a year . But I have’nt used table partitions: Please help me to do it in a better way. I got it from the documentation that it is an inheritance capability and the Trigger working but for dynamic data how can I implement that.
>
Please elaborate more about the data which you are talking about.
> Thanks in advance
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2011-02-06 12:10 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-07 11:38 Table PARTITION Dinesh Pandey <dpandey@secf.com>
2005-04-07 12:06 ` Richard Huxton <dev@archonet.com>
2005-04-07 12:09 ` Dinesh Pandey <dpandey@secf.com>
2005-04-07 12:16 ` Sean Davis <sdavis2@mail.nih.gov>
2005-04-07 12:37 ` Richard Huxton <dev@archonet.com>
2005-04-08 01:47 ` Enrico Weigelt <weigelt@metux.de>
2005-04-08 04:52 ` Dinesh Pandey <dpandey@secf.com>
2005-04-07 12:32 Re: Table PARTITION Richard Huxton <dev@archonet.com>
2011-02-01 13:31 TABLE PARTITION NEVIN ALEX <nevin.alex@hotmail.com>
2011-02-06 12:10 ` Re: TABLE PARTITION Vibhor Kumar <vibhor.aim@gmail.com>
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