Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Okay, so this is somewhat away from the other answers, but... it feels to me like if you have the data in a file system (one stock per file, perhaps) with a fixed record size, you can get at the data <em>really</em> easily: given a query for a particular stock and time range, you can seek to the right place, fetch all the data you need (you'll know exactly how many bytes), transform the data into the format you need (which could be very quick depending on your storage format) and you're away.</p> <p>I don't know anything about Amazon storage, but if you don't have anything like direct file access, you could basically have blobs - you'd need to balance large blobs (fewer records, but probably reading more data than you need each time) with small blobs (more records giving more overhead and probably more requests to get at them, but less useless data returned each time).</p> <p>Next you add caching - I'd suggest giving different servers different stocks to handle for example - and you can pretty much just serve from memory. If you can afford enough memory on enough servers, bypass the "load on demand" part and just load all the files on start-up. That would simplify things, at the cost of slower start-up (which obviously impacts failover, unless you can afford to always have <em>two</em> servers for any particular stock, which would be helpful).</p> <p>Note that you don't need to <em>store</em> the stock symbol, date or minute for each record - because they're implicit in the file you're loading and the position within the file. You should also consider what accuracy you need for each value, and how to store that efficiently - you've given 6SF in your question, which you could store in 20 bits. Potentially store three 20-bit integers in 64 bits of storage: read it as a <code>long</code> (or whatever your 64-bit integer value will be) and use masking/shifting to get it back to three integers. You'll need to know what scale to use, of course - which you could probably encode in the spare 4 bits, if you can't make it constant.</p> <p>You haven't said what the other three integer columns are like, but if you could get away with 64 bits for those three as well, you could store a whole record in 16 bytes. That's only ~110GB for the whole database, which isn't really very much...</p> <p>EDIT: The other thing to consider is that presumably the stock doesn't change over the weekend - or indeed overnight. If the stock market is only open 8 hours per day, 5 days per week, then you only need 40 values per week instead of 168. At that point you could end up with only about 28GB of data in your files... which sounds a lot smaller than you were probably originally thinking. Having that much data in memory is <em>very</em> reasonable.</p> <p>EDIT: I think I've missed out the explanation of <em>why</em> this approach is a good fit here: you've got a very predictable aspect for a large part of your data - the stock ticker, date and time. By expressing the ticker <em>once</em> (as the filename) and leaving the date/time entirely implicit in the <em>position</em> of the data, you're removing a whole bunch of work. It's a bit like the difference between a <code>String[]</code> and a <code>Map&lt;Integer, String&gt;</code> - knowing that your array index always starts at 0 and goes up in increments of 1 up to the length of the array allows for quick access and more efficient storage.</p>
    singulars
    1. This table or related slice is empty.
    1. POHow to store 7.3 billion rows of market data (optimized to be read)?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJon Skeet
    1. USJon Skeet
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COAgain this depends on how he is using the data. If his query is to pull a particual data across the board (stock symbol wise) then this would incur reading every file and having specific date encodings to pull the correct data from each. Or if he wants the best performing stock per week, then that would be a nightmare with this kind of set up with having to read all records sort and compare. Without such information, we can only guess that this is for fixed storage - maybe as a bulk DW that will feed a reporting DW at some point (ETL source).
      singulars
      1. PO
      1. USWolf5370
    2. CO@Wolf5370: Yes, we certainly need to know what the queries will be, but we have at least some indication from the question: 'Most of the queries will be like "Give me the prices of AAPL between April 12 2012 12:15 and April 13 2012 12:52'. It would be nice to know what the *other* queries would be, as well as relative frequencies and performance requirements.
      singulars
      1. PO
      1. USJon Skeet
    3. CO@JonSkeet it really depends on the workload, but I've got some domain knowledge of this kind of system, and it's rarely just "select one stock over one range": it's much more often "select stocks in this portfolio over this range, compute &beta; then try this list of possible stocks and see what &beta; is then." That's why it drives you toward something OLAP-like.
      singulars
      1. PO
      1. USCharlie Martin
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload