Correct Concept On Table Partition

Started by Yan Cheng Cheokabout 16 years ago2 messagesgeneral
Jump to latest
#1Yan Cheng Cheok
yccheok@yahoo.com

Currently, I plan to use table partition to solve the following problem.

I have a table which is going to grow to a very huge row, as time goes on.

As I know, as table grow larger, the read operation will be slower.

Hence, I decide to use table partition, in order to improve read speed.

I have parent table named measurement.

Then I will have child tables named measurement_1, measurement_2, ....

First 1st millions rows will be write to measurement_1, 2nd millions into measurement_2, ....

My understanding is,

(1) measurement table will act as a virtual table, which make me easy for me to perform query read and query write.

(2) measurement_1, measurement_2 will be "real" table.

(3) when viewing the 2nd millions row (1,000,001 - 2,000,000) of measurement,

before partition
================
instead of reading total 2 millions row, and displaying the (1,000,001 - 2,000,000)

after partition
===============
we will just need to access table measurement_2 only, which is smaller, and shall be faster.

(4) extensive join operation will be involve. I am more concern into read speed.

Is this the correct expectation, on table partition?

Thanks and Regards
Yan Cheng CHEOK

#2A. Kretschmer
andreas.kretschmer@schollglas.com
In reply to: Yan Cheng Cheok (#1)
Re: Correct Concept On Table Partition

In response to Yan Cheng Cheok :

Currently, I plan to use table partition to solve the following problem.
I have a table which is going to grow to a very huge row, as time goes on.
As I know, as table grow larger, the read operation will be slower.

Hence, I decide to use table partition, in order to improve read speed.
...

First 1st millions rows will be write to measurement_1, 2nd millions into measurement_2, ....

Is this the correct expectation, on table partition?

Depends on your selects. You needs an attribute to decide which
child-table contains your data.

For instance, create tables for every month. Now you can 'select ...
where date >= '2010-01-01'::date and date < '2010-02-01'::date to select
all data for this particular month.

Your child-tables should contains contraints to enforce this
partitioning-schema.

There are a lot of examples in the internet how to do that, for instance:
http://www.if-not-true-then-false.com/2009/11/howto-create-postgresql-table-partitioning-part-1/

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99