title: kdb+ historical database partitioning design - daily data on thousands of symbols
body: <p>What would be the optimal partitioning (if any) of <code>kdb+</code> database storing daily price/return data on <code>10,000</code> symbols (e.g. stocks, indices, ETFs)? </p>
  
  <p>We are talking about  <code>25</code> years of daily data (median) per symbol (<code>25 * 200 = 5,000 records</code>). So the overall size will be <code>10,000</code> symbols x <code>5,000</code> days = <code>50,000,000</code> records.</p>
  
  <p>The database will <em>write</em> a new price for each symbol at the end of the day.</p>
  
  <p>The most typical query will be to <em>read</em> the whole daily price history for a subset (10s even 100s) of symbols into the memory for further time series analysis/portfolio simulation.</p>
  
  <p>I was thinking about partitioning by symbol, but didn't find anyone on the web doing it that way, so not sure if this is the best idea.
  I've found plenty of solutions for higher-than-daily frequency where they partition tick data by <strong>day</strong> (each date has own partition, e.g. <code>2015-02-12</code>), or partition by <strong>symbol range</strong> (e.g. <code>ABC DEF GHI ...</code>), but not by <strong>individual symbols</strong>.</p>
  
  <p>In the backtesting/portfolio simulation application<sup>[1]</sup>, I suspect that partitioning by individual symbols may have some later advantages by being able to calculate price transformations (such as moving average or RSI) in parallel by giving each worker node it's own symbol to process. On the contrary, partitioning by day/week/year instead would not provide that advantage.</p>
  
  <p><sup>[1]</sup><sub> Which is essentially first loop each symbol and pre-process its time-series into transformed time series to generate signal (e.g. calculate moving average of price). Then loop day-by-day in a path-dependent (where portfolio is a stateful object) fashion to check and act upon signals each day </sub></p>