0

What is the pros and cons in designing a RDB table to store both current data and historical data together? Is it scaleable?

Is this approach better than having one table for current data and one table for historical data?

1

I have old posts where I have discussed something like this before

The pros can never outweigh the cons. The reason ? Should you attempt to scale hardware and cache settings, you make room for both current and historical data to be cached. This introduces the possibility of the following:

  • Caching old data
  • Not using the old data that was recently cached
  • Having to prune that old data from caches to make room for current and historical data
  • Start these three problems over again

Even if you tune queries, caches could still suffer. How?

MyISAM

Recall that there are session-level caches for reading ahead for data. This is set by read_buffer_size and read_rnd_buffer_size. This affects only MyISAM tables since MyISAM only caches indexes system-wide. Each thread caches its own data.

InnoDB

Retrieving pages of data you may not need is also possible if your queries do full table scans or index range scans. Since each page for InnoDB is 16K, a page could end up with both current and historical data if you are not paying attention and just loading both willy-nilly.

SUGGESTIONS

Keep current data in one table and historical in another. It will be faster to retrieve current data. You can also scale caches for current data only.

If you really want old and new data in one table, do the following:

  • Create an empty database
  • Load all historical data first, sorted by datetime
  • Load all current data next, sorted by datetime

As an added bonus, you should consider partitioning tables by month. This will spread out table data and corresponding indexes. This makes it easier to defragment or delete by partition.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.