6

I stumbled over an algorithmic / data-structures question over on SO, which I'll short quote:

(...) opinions regarding best of breed data structures to be used for indexing time-series (aka column-wise data, aka flat linear).

Queries that will be required:

All values in the time range [t0,t1]

All values in the time range [t0,t1] that are greater/less than v0

All values in the time range [t0,t1] that are in the value range[v0,v1]

The data sets consist of summarized time-series (...) The data set(s) in question are about 15-20TB in size, hence processing is performed in a distributed manner - because some of the queries described above will result in datasets larger than the physical amount of memory available on any one system.

Distributed processing in this context also means dispatching the required data specific computation along with the time-series query, so that the computation can occur as close to the data as is possible - so as to reduce node to node communications (somewhat similar to map/reduce paradigm) - in short proximity of computation and data is very critical.

I'll readily admit that problems of this scale are way over my head, but my first hunch (even with the data sizes mentioned) given this problem would have been to ask whether they checked if large-scale RDBMS (well, I guess Oracle, or Oracle, right?) could handle this in a sane way.

So here's the question: Can a (enterprise?) RDBMS handle this kind of problem today with acceptable performance vs. a "hand-coded" solution.

Note: Hope this isn't too vague, and feel free to re-tag as appropriate :-)

Improve this question
5
  • Buy yourself an Exadata box :)
    – Philᵀᴹ
    Commented Apr 16, 2012 at 19:56
  • @Phil - looking at the Exadata specs it would superficially appear that, generally speaking, using right sized hardware with a modern OS should be able to cope with this(OP's) problem without going to extremes like the Exadata box's performance characteristics. :-)
    – Martin
    Commented Apr 16, 2012 at 20:15
  • Exadata, Netezza, Greenplum, etc. These are some of the popular SQL DBMSs for handling 10s or 100s of Terabytes of data.
    – nvogel
    Commented Apr 16, 2012 at 21:18
  • Have you considered using Vertica? It is designed to store time series, and it does it extremely well. It is a brainchild of the database legend Michael Stonebraker.
    – A-K
    Commented Apr 17, 2012 at 1:35
  • @sqlvogel - Just a quick thanks for fixing my spelling in the title. I appreciate this. Good to see people caring about the small details.
    – Martin
    Commented Apr 17, 2012 at 4:31

1 Answer 1

Reset to default
1

Answering this from one point of view: SQL Server 2012 columnstores could easily handle this. I've seen it working and once segment elimination and batch processing kick in, those TBs are reduced to very few actual IOs an the results get churned in few ms (ie. most data is eliminated upfront and there is simply no need to scan 100s of TBs). The queries you asked about are exactly the kind columnstores are designed for. This is such an efficient storage/processing paradigm that there's simply no need for distributed computing, even at hundreds of TB of data.

Improve this answer
1
  • If I understand correctly how this columnstores thing is implemented, then it uses the same idea as Vertica. However, Vertica is basically made to store time series so it basically does one thing and it does it very well. Why pay money for all these features in SQL Server if you do not need them?
    – A-K
    Commented Apr 17, 2012 at 1:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.