I have two types of tables, for sake of argument lets call it these:

1) product                  10,000,000 rows
2) product_activity  1,000,000,000 rows

90% of the type the product table is accessed by product_id, 80% of
the time that product id would be in the last 1,000,000 rows of the
table i.e. a recent product id.

90% of the time the product_activity table is accessed by product_id +
a date range - often the last 24 hours.

One option seems to be not to use partitions at all and have a
product_history table and a product_activity_history table.  The work
falls on the application to use UNION queries to extract data when
needed across both tables, implement a criteria for moving the records
into the history table and dealing with the issues of related tables
referencing product ids that are in other tables.

Alternatively I could partition the two tables by date range.  I am
not sure how effective that would be for product but I guess I could
hit that table first and if it hits, great - performance saved, and if
it doesnt oh well get it from the history table.

Certainly product_activity seems like a good partitioning contender.
Of course maintaining monthly partitions is a lot of work but I guess
you could create them in advance for several years.

Is partitioning the way to go in this case?

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to