# Is there any thing out there as a substitute for KDB?

thanks a lot for your discussions on the original post.

following your suggestions, let me re-phrase a bit :

kdb is known for its efficiency, and such efficiency comes at a terrible price. However, with computational power so cheap this days, there must be a sweet spot where we can achieve a comparable efficiency of data manipulation, at a more reasonable cost.

For instance, if a KDB license cost a small shop $200K per year (I dont know how much it actually cost, do you know?), maybe there is a substitute solution: e.g., we pay 50K to build a decent cluster, storing all data onto a network file system, and parallelize all the data queries. This solution might not be as fast or as elegant as KDB, but it would be much more affordable and most important -- you take full control of it. what do you think? is there anything like this? - I'm not sure what you mean. The main trick used by KDB is to store the data in columns instead of rows. This has the advantage that if one column is selected all the data can be read in one long read. This can also be done in Python. – Bob Jansen Apr 1 '12 at 10:52 1) Storing each tick as a separate record/row is neither sensible nor feasible (as you already mentioned). The most common approach is to split tick data by day and store days of data as plain arrays, either in files or in database LOBs. Such approach makes the amount of data perfectly manageable. 2) possible duplicate of What is the best data structure/implementation for representing a time series? – wburzyns Apr 1 '12 at 18:50 The minimal 2-core setup of kdb+ is actually pretty reasonable:$25K per year including maintenance. –  user2303 Apr 19 '13 at 1:53

KDB is useful for two reasons: - Storage of data; and easy access to the data (i.e. querying ticks..etc) - Rich query language that supports many Quant functions

however; what KDB does not do well; is the quant query language.

I have evaluated KDB, Matlab, and R. So far R is the winner.

I have not found any fast solution for storing and retrieving data; compared to using flat binary files; which are divided by month for ease of acces. My app can read 1 million of tick data in 3 seconds; and do backtesting accordingly.

for retail traders; i suggest you use flat files (binary instead of text; for quick read/write). MT4 data structure is a good example to follow.

It is cheap, free, and fast!!!

-
How do you compare a database system and two scientific-oriented programming languages? –  SRKX Apr 2 '12 at 15:56
can you rephrase your question? –  alpha Apr 3 '12 at 0:05
KDB is a database. Matlab and R are programming languages/environments. Q is KDB's programming language and would be somewhat comparable strictly as a language to J. –  Steve Apr 8 '12 at 22:20
@Chuu, do you have any data to back up your claim? –  Datageek Apr 7 '14 at 11:07
@Datageek Most KDB users are intimately aware that KDB+ is a column store because administrating it requires in-depth knowledge of the on-disk structure of the database. Also, keep in mind that KDB is an in-memory database, and the insert performance numbers cited do not include persisting to disk. –  Chuu Apr 7 '14 at 14:10

At discretelogics we just released a file format to store time series in flat files called "TeaFiles". In addition to raw data they can store the binary item layout and a description of the contents.

C#, C++, Python APIs are available open source, licensed under the GPL, see discretelogics.com/teafiles/

Using memory mapping, read performance reaches that of in-memory array processing for sequential usage of a file, as is the case for back testing.

The C# API at Codeplex holds micro benchmarks. Summing up a file with a single 8 byte double reaches 500 million operations per second on an older test machine. Using a Tick Item with int64 / double / int for Time/Price/Volume is 100 million operations.

Disclosure: self promotion + interest in discussing time series persistence

-
+1 I like the idea. How do you handle cross-sectional time series? Will we be able to embed you visualization API into a custom app? –  SRKX Apr 8 '12 at 9:07
thank you! by cross-sectional time series you mean the analysis of several series like computing an index or stat arb, right? about embedding - our code base would allow embed-able components. we thought already about offering that. i take your question as valuable input for our product road map. at the moment our visualization tools TeaHouse/TeaShell are windows / XAML. our persistence layer is cross OS/app and we plan visualization on html5 architecture for the future. our decisions will be customer driven, so your vote counts. we are thankful for any feedback and inside into your needs. –  thomas - discretelogics Apr 8 '12 at 11:58
are there any benchmarks say this vs hdfs or other formats? –  pyCthon Jan 22 '13 at 22:25
Benchmarks about raw time series data access using TeaFiles are included in the C#/C++ sources, see teafiles.codeplex.com/wikipage?title=Benchmarks. A benchmark against HDFS would first need to specify what to measure. TeaFiles perform 1:1 to raw data structs in files, as they are nothing else + allow memory mapping. This is hardly to beat at first. Read ahead, prefetching allow going beyond in some situations. HDFS would be a layer above raw file storage and we have it on our roadmap to take simple file persistence to the next level. For management not for performance reasons tough. –  thomas - discretelogics Jan 23 '13 at 0:06
A little late to the party here but the link in the answer is now dead. –  chollida Jun 23 '14 at 17:22

You could look into Pandas, a Python library that integrates with PyTables. It was created by someone at AQR and has some similar features as kdb.

-

As of April 2014, the 32-bit version of kdb+ ist now free (as in beer). KX systems have removed:

• the 4h timeout
• the requirement to reinstall every 3 months

The only limitation vs. the 64-bit version is that you can only address up to 4GB of memory per process. But work around this by writing a multiprocessing system (and you'll get to use your CPUs in parallel too).

-
I was not aware of that, thanks a lot for this update, +1 –  Matt Wolf May 20 '14 at 7:39

I don't like KDB+/q. For KDB+ experts, I am not picking a fight. The following is just my own understanding on KDB+ and TimeSeries Database. You're warmly welcome to correct me if anything wrong in your eyes :).

First of all, during my near one year's KDB+/q development experience, I never ever find a paper based benchmark result indicating KDB+/q significantly out performs other storage system in time. By storage system, I don't limit the scope to disk based RDBMS. So I don't know why everybody in quant finance is talking about KDB+ and impressed by its efficiency without data backing their points.

Second, I once talked with an Oracle Certificated DBA about KDB+. From my description, the first word he came up is an in-memory cache not even a full fledge database. Maybe he is right to some extent, isn't he?

Next is the so-called TimeSeries. More than one manager advocating KDB+ from different positions and companies talked to me KDB+ is a TimeSeries Database. However, I don't think TimeSeries is a feature of KDB+, whose real feature is the column oriented storage engine. That is to say with a column oriented storage engine, that KDB+ is friendly to store TimeSeries data. While in these days, column oriented storage engine does not belong to KDB+ exclusive. For traditional RDBMS, like MySQL, you can also find corresponding column storage engines in the open source communities.

The last and the most important, I want to talk about the developer friendliness. KDB+ is distributed along with a DSL, which is q. q is very developer unfriendly. When an error encountered, it just raise the error type, without any line information for you to anchor, which increase the difficulty to bug fixing and code maintenance.

Alternatives to KDB+ I can come up with:

1. to give the system column oriented storage, a traditional K-V store works, even a column storage backed RDBMS;
2. for the in-memory feature, a lot of open source implementation of in-memory cache, like redis, memcached, etc.

The key feature for the alternative, I think, is that the API is written in standard C/C++. You could have a lot of utilities to ease your development.

-
While not definitive as far as speed goes, it certainly helps that the entire executable is roughly 400kb, depending on the version, meaning it can live entirely in CPU cache. –  afekz Jun 23 '14 at 11:04
@afekz Thank you for your comments. But if not for speed, why do people bothering the cache? :p Yes, you're right this may be one of the pros for KDB. –  Summer_More_More_Tea Jun 23 '14 at 12:12
One reason KDB is so friendly to Time Series data is that queries are implicitly stable in terms of row ordering. Many other solutions listed don't offer this guarantee. –  Chuu Jun 2 at 19:16
@Chuu Thank you for your comment. By "other solutions", may I know your alternatives? :) –  Summer_More_More_Tea Jun 3 at 2:05

Have a look at Kona which is a FOSS project trying to be compatible. Also Tom Szczesny has done some work on its predecessors namely A. I hope this helps.

Also if you are not looking for a perfect substitute you can have a look at other Time Series Databases like InfluexDB, Java Chronicles, OpenTSDB, KairosDB which are all Open Source. There are commercial ones as well out of which OneTick is targeted at Tick Data management.

-

One of the reasons q/kdb+ is attractive for tick data is that it can process data tick by tick as well as using the SQL-like query language.

The only system I've used that can process a tick-feed like q/kdb+ is Esper. It is very difficult to work with because it's an embedded language invoked by an Esper runtime environment within a JVM. There are extra tools available at a fee.

I've used q/kdb+ extensively. The licence changes are really good, I can now use it as part of my tool-kit at work and use it within business processes. If you persevere with it, you do appreciate it is an amazing system.

-

Lots of people focus on data storage ability and compare KDB with other SQL/query-based databases. Such comparison is like considering "Is a Ferrari good for running a bus route?"

KDB+ is capable of manipulating and querying large data set. The performance is fast (in comparison to most RDBMS) due to its column based storage, but it's not her strongest suit. In fact, it could become a serious pain if the query is not well written or when the server just doesn't have enough memory. From my experience, lots of KDB applications do not even host Realtime/Historical databases.

With her simple messages publish/subscribe mechanism and flexible IPC calls, one could build complex event processing engines (and even a network of engines) to operate on these time series data easily. Its performance can easily outmatch other engines written in Java/C due to Q vector processing and loads of functions that tailored made for time series data.

-
Hi MK Lee, what do you mean by 'her' in 'With her simple messages'? –  Bob Jansen Jan 5 at 12:36

We've created a roundup of the top column-oriented database systems: http://www.timestored.com/time-series-data/column-oriented-databases

This includes kdb+ and some open source alternatives.

We have done some initial work at benchmarking common time-series queries and found open source monetDB to be particularly fast...we will hopefully be able to publish some of those results at a later date.

-