Re: Calculate hardware needs based on desired writes per second

[Jan Bromberger]

Hi Rick,

been on vacation, so I just read your reply. Thanks for being so thoughtful.

I have meanwhile changed my schema and started a refactoring. Now, I have two tables:

One holds only the current day of offers. It has all the indices, but will only have 7M rows. The replace will happen with same other_id. "other" is my main data provider, and there are a few records entered by users.

Then there is a second table, historical_offers, that contains only a price and availability history for each day and offer in the other table. After Aftab's suggestions, I stripped that table of all but one index and made (offer_id, valid_at) the primary index.

I also used smaller column types where appropriate.

Now the offers table has 600M data and 560M indices and historical_offers has 300M/day and 0 index size.

The refactoring was possible, because only price, price_with_shipping and availability changes over time, but all those indices columns like product_id or shop_id don't change.

I will have to rewrite my application to always load the offers first and then with a second query load all matching historical_offers, but that will only slow down my applications a few ms and it won't be an issue.

I could certainly partition historical_offers by valid_at, i.e. the 90-92 days (3 months), if you suggest it helps?