Re: Strategies/Best Practises Handling Large Tables

On Sat, Oct 13, 2012 at 01:44:02AM +1100, Chitra Creta 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?
Three and four will probably be your biggest wins. We do both. There are
trade-offs for both. If you need the absolute best in response times
(e.g., in a web application), summary tables are the way to go. If
you're regularly querying data in a specific date range, but response
times aren't as critical (e.g., daily report generated at the end of
day), partitioning will also help there.

Having the correct indexes is crucial, as always.

Additionally, you will be able to purge old data relatively quickly and
without bloat using the partitioning method.

> 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.
>
> Thanking you in advance.

-Ryan Kelly