What's a good way to structure a 100M record table for fast ad-hoc queries?

Question

The scenario is quite simple, there are about 100M records in a table with 10 columns (kind of analytics data), and I need to be able to perform queries on any combination of those 10 columns. For example something like this:

• how many records with a = 3 && b > 100 are there in past 3 months?

Basically all of the queries are going to be a kind of how many records with attributes X are there in time interval Y, where X can be any combination of those 10 columns.

The data will keep coming in, it is not just a pre-given set of 100M records, but it is growing over time.

Since the column selection can be completely random, creating indexes for popular combinations is most likely not possible.

The question has two parts:

• How should I structure this in a SQL database to make the queries as fast as possible, and what are some general steps I can take to improve performance?
• Is there any kind of NoSQL database that is optimized for this kind of search? I can think of only ElasticSearch, but I'm not it would perform very well on this large data set.

Answer 1

Without indexes your options for tuning an RDBMS to support this kind of processing are severely limited. Basically you need massive parallelism and super-fast kit. But clearly you're not storing realtional data so an RDBMS is the wrong fit.

Pursuing the parallel route, the industry standard is Hadoop. You can still use SQL style queries through Hive.

Another noSQL option would be to consider a columnar database. These are an alternative way of organising data for analytics without using cubes. They are good at loading data fast. Vectorwise is the latest player in the arena. I haven't used it personally, but somebody at last night's LondonData meetup was raving to me about it. Check it out.

Of course, moving away from SQL databases - in whatever direction you go - will incur a steep learning curve.