Обсуждение: Correct Concept On Table Partition
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
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