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 Answer
I have old posts where I have discussed something like this before
Nov 30, 2012: How much does table size affect performance?Oct 07, 2011: How Does Table Partitioning Help?
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.
answered Nov 5, 2014 at 1:18
