Search Postgresql Archives

Re: Strategies/Best Practises Handling Large Tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thank you all for your suggestions. Since all of you recommended the Partition option, I decided to try it out myself.

I found a function that created partition tables for every month in the last two years. A trigger was also added to the parent table to ensure that every insert into it from hence forth will be inserted into the approapriate child table.

However, there were a few observations that I made which I would appreciate your comments on:

1. Since existing data was in the parent table, I had to do a pg_dump on it, drop it, and then to a restore on it to force the trigger to work on existing data. Is this how partitioning existing data should be done?

2. I noticed that there are two copies of the same record - i.e the one that was inserted into the parent table and another that was inserted in the child table. If I delete the record in the parent table, the child record gets automatically deleted. I was under the impression that partitioning meant that my parent table will not be large anymore because the data will be moved to smaller child tables. Is this the case?

3. Is there a way for me to evaluate the effectiveness of the partitioned table? Would performing an Explain Analyse allow me to determine whether querying the parent table for statistics is quicker than querying against a massive non-partitioned table?

Thank you.

On Oct 13, 2012 3:49 AM, "John R Pierce" <pierce@xxxxxxxxxxxx> wrote:
On 10/12/12 7:44 AM, Chitra Creta wrote:

1. Purge old data
2. Reindex
3. Partition
4. Creation of daily, monthly, yearly summary tables that contains aggregated data specific to the statistics required


if most of your queries read the majority of the tables,  indexing will be of little help

parittioning will aid in purging old data, as you can partitions by date (for instance, by week) and drop whole partitions rather than deleting individual records.

aggregate tables likely will be the biggest win for your statistics if they reduce the mount of data you need to query.


--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux