Re: Strategies/Best Practises Handling Large Tables

On Fri, Oct 12, 2012 at 10:00 AM, Lonni J Friedman <netllama@gmail.com> wrote:
> On Fri, Oct 12, 2012 at 7:44 AM, Chitra Creta <chitracreta@gmail.com> wrote:
>> Hi,
>>
>> I currently have a table that is growing very quickly - i.e 7 million
>> records in 5 days. This table acts as a placeholder for statistics, and
>> hence the records are merely inserted and never updated or deleted.
>>
>> Many queries are run on this table to obtain trend analysis. However, these
>> queries are now starting to take a very long time (hours) to execute due to
>> the size of the table.
>>
>> I have put indexes on this table, to no significant benefit.  Some of the
>> other strategies I have thought of:
>> 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
>>
>> Does anyone know what is the best practice to handle this situation?
>>
>> I would appreciate knowledge sharing on the pros and cons of the above, or
>> if there are any other strategies that I could put in place.
>
> Partitioning is prolly your best solution.  3 & 4 sound like
> variations on the same thing.  Before you go that route, you should
> make sure that your bottleneck is really a result of the massive
> amount of data, and not some other problem.  Are you sure that the
> indices you created are being used, and that you have all the indices
> that you need for your queries?  Look at the query plan output from
> EXPLAIN, and/or post here if you're unsure.

Partitioning is not a strategy to improve query performance unless you
are exploiting the data structure in some way through the partition.

merlin