I am going to recommend something that I have no doubt will get people completely up in arms and probably get people to attack me. It happened in the past and I lost many points on StackOverflow as people downvoted my answer. I certainly hope people are more open minded in the quant forum.
Note - It seems that this suggestion has created some strong disagreement again. Before you read this I would like to point out that this suggestion is for a "Small buy side firm" and not a massive multiuser system.
I spent 7 years managing a high-frequency trading operation and our primary focus was building systems just like this. We spent a huge amount of time trying to figure out the most efficient way to store, retrieve and analyze order level data from both the NYSE, NASDAQ and a wide variety of ECNs. What I am giving you is the result of that work.
Our answer was Don't Use a Database. A basic structured file system of serialized data chunks works far better. Market time series data is unique in many ways, both in how it is used and how it is stored. Databases were developed for wildly different needs and actually hurt the performance of what you are trying to do.
This is in the context of a small to mid-sized trading operation that is focused on data analysis related to trading strategies or risk analytics. If you are creating a solution for a large brokerage, bank or have to meet the needs of a large number of simultaneous clients then I imagine that your solution would differ from mine.
I happen to love databases. I am using MongoDB right now for part of a new project allowing us to analyze options trades, but my market timeseries data, including 16 years of options data, is all built into a structured file store.
Let me explain the reasoning behind this and why it is more performant.
First, let's look at storing the data. Databases are designed to allow a system to do a wide variety of things with data. The basic CRUD functions; Create, Read, Update and Delete. To do these things effectively and safely, many checks and safety mechanisms must be implemented. Before you read data the database needs to be sure the data isn't being modified, it is checking for colisions, etc.. When you do read the data in a database the server puts a lot of effort into caching that data and determining if it can be served up faster later. There are indexing operations and replicating data to prepare it to be viewed in different ways. Database designers have put huge amounts of effort into designing these functions to be fast, but they all take processing time and if they are not used they are just an impediment.
Market time series data is stored in a completely different way. In fact, I would say it is prepared rather than stored. Each data item only needs to be written once and after that never needs to be modified or changed. Data items can be written sequentially, there is no need to insert anything in the middle. It needs no ACID functionality at all. They have little to no references out to any other data. The time series is effectively its own thing.
As a database does all the magic that makes databases wonderful it also packs on the bytes. The minimum space data can take up is its own original size. They may be able to play some tricks with normalizing data and compression, but those only go so far and slow things down. The indexing, caching and referencing the data ends up packing on the bytes and chewing up storage.
Reading is also very simplified. Finding data is as simple as time & symbol. Complex indexing does it no good. Since time series data is typically read in a linear fashion and a sequential chunk at once, Caching strategies actually slow the access down instead of help. It takes the processor cycles to cache the data you aren't going to read again anytime soon.
This is the basic structures that worked for us. We created basic data structures for serializing the data. If your major concern is speed and data size you can go with a simple custom binary storage. In another answer, omencat suggested using TeaFiles and that looks like it has some promise also. Our recent need is for more flexibility so we chose to use a fairly dense, but flexible JSON format.
We broke the data up into fairly obvious chunks. The EOD stock data is a very easy example, but the concept works for our larger datasets also.
We use the data for analysis in fairly traditional time series scenarios. It could be referenced as one quote or out to a series containing years of data at a time. It was important to break the data down to bite-sized chunks for storage so we chose to make one "Block" of our data equal one year of EOD stock time series data. Each block is one file that contains a year of OHLC EOD data serialized as JSON. The name of the file is the Stock symbol prefixed by an underscore. Note - the underscore prevents issues when the stock symbol conflicts with DOS commands such as COM or PRN.
Note, make sure you understand the limitations of your file system. We got in trouble when we put too many files in one place. This led to a directory structure that is effectively its own index. It is broken down by the year of data and then also sorted by the first letter of the stock symbol. This gives us roughly 20 to a few hundred symbol files per directory. It looks roughly like this;
\StockEOD\{YYYY}\{Initial}\_symbol.json
AAPL data for 2015 would be
\StockEOD\2015\A\_AAPL.json
A small piece of its data file looks like this;
[{"dt":"2007-01-03T00:00:00","o":86.28,"h":86.58,"l":81.9,"c":83.8,"v":43674760},
{"dt":"2007-01-04T00:00:00","o":84.17,"h":85.95,"l":83.82,"c":85.66,"v":29854074},
{"dt":"2007-01-05T00:00:00","o":85.84,"h":86.2,"l":84.4,"c":85.05,"v":29631186},
{"dt":"2007-01-08T00:00:00","o":85.98,"h":86.53,"l":85.28,"c":85.47,"v":28269652}
We have a router object that can give us a list of filenames for any data request in just a handful of lines. Each file is read with an Async filestream and deserialized. Each quote is turned into an Object and added to a sorted list in the system. At that point, we can do a very quick query to trim off the unneeded data. The data is now in memory and can be used in almost any way needed.
If the query size gets too big for the computer to handle it isn't difficult chunking the process. It takes a massive request to get there.
I have had programmers who I described this to almost go into a rage telling me how I was doing it wrong. That this was "Rolling my own database" and a complete waste of time. In fact, we switched from a fairly sophisticated database. When we did our codebase to handle this dropped to a small handful of classes and less than 1/4 of the code we used to manage the database solution. We also got nearly a 100x jump in speed. I can retrieve 7 years of stock end of day data for 20 symbols in a couple of milliseconds.
Our old HF trading system used similar concepts but in a highly optimized Linux environment and operated in the nanosecond range.
