I want to create a table in a database. The table should have a key and a value. The table should have entries around 100million but each row is just a 10 digit key and a 6 digit value corresponding to it. I want to load the database in memory. How can i do that?
I had thought about using a hashtable but how do i ensure that all of it is in memory at an instant. When updates are done, they are written to the disk.
Keeping the data in memory shouldn't be a problem. Most DBMSs do that automatically and since your data is quite small it shouldn't be difficult to serve your data reads from cache. Populating the cache from cold may take a few seconds but if you choose your hardware then fast disk can comfortably take you to 300 - 500 megabytes / sec. If you really need more than that then look at Solid State drives.
Thank you. I take your point.
Just wanted to ask: is the hashtable approach a correct one? Would I be able to achieve what I want, with it?
You may want to use a hash index, but that depends on what optimisations your DBMS supports. I would have expected that the way you store the data in the table would look more like what you described: a 10 digit key and a 6 digit value.
I would be using mysql. Can i use 'The MEMORY storage engine which creates tables with contents that are stored in memory'?
With 16 digits, the size of the data will be around 1.6 GB for 100million customers. With a RAM size of 2GB would this option be okay?
It depends what else requires RAM on that machine. If there are other frequently accessed tables in your database or other applications then you should consider how much RAM they might need. Databases work best with lots of RAM.
I'm not familiar with the Memory engine so I can't help you there.