Re: Strategies/Best Practises Handling Large Tables

On Tue, Oct 16, 2012 at 09:26:09PM +1100, Chitra Creta wrote:
> 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?
I just wrote a one-time function to move it.

> 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?
The data *is* in the child tables. Queries on the parent tables, by
default, affect data in the child tables. So, issuing a SELECT against
your parent table will also query the child tables. DELETE will,
similarly, delete data in the child tables. You may target just the
parent table using ONLY, e.g. SELECT * FROM ONLY foo. This behavior is
also controlled by the GUC sql_inheritance, though I encourage you not
to change this value. To get a better idea of what it happening, look at
the output from EXPLAIN to see all the tables that are being included in
your plan.

> 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?
Well, you can do it with EXPLAIN ANALYZE, or you can do it by timing
your query, so that the overhead of EXPLAIN ANALYZE does not come into
play.

Also, I assume you've read this:
http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html

That link will be helpful in understanding how partitioning could
benefit you.

-Ryan Kelly