{"id":111615,"date":"2023-08-15T10:34:59","date_gmt":"2023-08-15T10:34:59","guid":{"rendered":"https:\/\/www.timestored.com\/b\/?p=111615"},"modified":"2023-08-15T10:35:17","modified_gmt":"2023-08-15T10:35:17","slug":"questdb-open-source-kdb-alternative","status":"publish","type":"post","link":"https:\/\/www.timestored.com\/b\/questdb-open-source-kdb-alternative\/","title":{"rendered":"QuestDB &#8211; Review 2023"},"content":{"rendered":"<p>Our latest product <a href=\"\/pulse\">Pulse<\/a> is for displaying <span style=\"text-decoration: underline;\"><strong>real-time<\/strong><\/span> interactive data direct from any database. To get most benefit, the underlying databases need to be fast (&lt;200ms queries). For our purposes databases fall into 2 categories:<\/p>\n<ol>\n<li><strong>Really really fast<\/strong>, can handle queries every 200ms or less and seamlessly show data scrolling in<\/li>\n<li><strong>All Other Databases. <\/strong>The 95%+.<\/li>\n<\/ol>\n<p>It&#8217;s very exciting when we find a new database that meets that speed requirement. I went to the website, downloaded QuestDB and ran it. Coming from kdb+ imagine my excitement at seeing this UI:<br \/>\n<img loading=\"lazy\" class=\"alignleft\" style=\"padding: 10px 25px 30px;\" src=\"\/pulse\/tutorial\/img\/questdb-install-terminal.png\" alt=\"QuestDB console\" width=\"410\" height=\"200\" \/><\/p>\n<div class=\"alignleft\">\n<p><span style=\"text-decoration: underline;\"><strong>Good News:<\/strong><\/span><\/p>\n<ul>\n<li>A <strong>very tiny<\/strong> download (7MB .jar file)<\/li>\n<li>There&#8217;s a <strong>free open source<\/strong> version<\/li>\n<li>They are focussed on <strong>time-series queries<\/strong><\/li>\n<li>Did I mention it&#8217;s <strong>fast<\/strong><\/li>\n<\/ul>\n<\/div>\n<p><br style=\"clear: both;\" \/>I wanted to take it for a spin and to test the full ingestion-&gt;store-&gt;query cycle. So I decided to prototype a crypto dashboard. Consume data from various exchanges and produce a dashboard of latest prices, trades and a nice bid\/ask graph as shown below.<\/p>\n<h3><a title=\"questdb database cryto dashboard\" href=\"\/pulse\/tutorial\/img\/questdb-crypto-charting.png\" target=\"_blank\" rel=\"lightbox[a] noopener\"><img loading=\"lazy\" class=\"dropshadow\" src=\"\/pulse\/tutorial\/img\/questdb-crypto-charting.png\" alt=\"questdb database cryto dashboard\" width=\"512\" height=\"313\" \/><br \/>\n<\/a><span style=\"text-decoration: underline;\"><strong>Good Points<\/strong><\/span><\/h3>\n<ul>\n<li>It simply worked.<\/li>\n<li>QuestDB chose to be <strong>PostgreSQL wire and query compatible<\/strong>. A great technical choice as:\n<ul>\n<li>It will work with many tools including <a href=\"\/pulse\">Pulse<\/a> without complication<\/li>\n<li>Many people already know SQL. I&#8217;ve been teaching q\/kdb for years and when people learn it, you can use it for absolutely amazing things that <a href=\"https:\/\/www.timestored.com\/b\/kdb-qsql-query-vs-sql\/\">standard SQL is terrible<\/a> at. However most people do not reach that level of expertise. By using standard SQL more people can reuse their existing knowledge.<\/li>\n<\/ul>\n<\/li>\n<li>They then added <strong>Time-series specific extensions<\/strong> ontop for querying, including:\n<ul>\n<li>&#8220;<a href=\"https:\/\/questdb.io\/docs\/reference\/sql\/latest-on\/\">Latest on<\/a>&#8221; &#8211; that&#8217;s equivalent to kdbs &#8220;last by&#8221;. It&#8217;s used to generate the &#8220;latest prices&#8221; table in the dashboard with a 1\/5\/15 minute lag.<\/li>\n<li><a href=\"https:\/\/questdb.io\/docs\/reference\/sql\/join\/\">ASOF Joins <\/a><\/li>\n<\/ul>\n<\/li>\n<li>QuestDB can <strong>automatically create tables<\/strong> when you first send data, there&#8217;s no need to send &#8220;Create Table &#8230;&#8221;. This was useful when I was tweaking the data layout from the crypto feeds.<\/li>\n<li>At parts my SQL was rusty and I asked for help on their slack channel. Within an hour<strong> I got helpful responses<\/strong> to both questions.<\/li>\n<\/ul>\n<p>Within a very short time, I managed to get the database populated and the dashboard live running. This is the first in a long time that a database has gotten me excited. It seems these guys are trying to solve the same user problems and ideas that I&#8217;ve seen everywhere. There were however some significant feature gaps.<\/p>\n<h3><span style=\"text-decoration: underline;\"><strong>Feature Gaps<\/strong><\/span><\/h3>\n<ul>\n<li><strong>No nested arrays.<\/strong> If I want to store bid\/asks, I can only currently do it with columns bid1\/bid2\/bid3, no arbitrary length arrays.<\/li>\n<li><strong>Very limited window analytics<\/strong>. Other than &#8220;LATEST ON&#8221; QuestDB won&#8217;t let me perform analysis within that time window or within arrays in general.<\/li>\n<li>I really missed my\n<pre><code>`time xasc (uj\/)(table1;table2)<\/code><\/pre>\n<p>pattern for combining multiple tables into one. For the graph I had to use a lengthy SQL UNION.<br \/>\nIn general kdb+ has array types and amazingly lets you use all the same functions that work on columns on nested structures. I missed that power.<\/li>\n<li><strong>No security on connections<\/strong>. It seems security integration will be an enterprise feature.<\/li>\n<\/ul>\n<h4><span style=\"text-decoration: underline;\"><strong>Open Source Alternative to kdb+ ?<\/strong><\/span><\/h4>\n<h3>Overall I would say <span style=\"text-decoration: underline;\">not yet<\/span> but they seem to be aiming at a similar market and they are moving fast.<strong><br \/>\n<\/strong><\/h3>\n<p><img loading=\"lazy\" class=\"size-medium wp-image-111617 alignright\" src=\"https:\/\/www.timestored.com\/b\/wp-content\/uploads\/2023\/02\/questdb-database-structure-300x248.png\" alt=\"QuestDB Database Structure\" width=\"300\" height=\"248\" srcset=\"https:\/\/www.timestored.com\/b\/wp-content\/uploads\/2023\/02\/questdb-database-structure-300x248.png 300w, https:\/\/www.timestored.com\/b\/wp-content\/uploads\/2023\/02\/questdb-database-structure-768x634.png 768w, https:\/\/www.timestored.com\/b\/wp-content\/uploads\/2023\/02\/questdb-database-structure.png 776w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/p>\n<p>In fact, if you look at their architecture on the right, it&#8217;s obvious some of their team have used kdb+. Data is partitioned on date, with a separate folder per table and a column per file. Data is mapped in when read and appended when new data arrives.<\/p>\n<p>In some ways this architecture predates kdb+ and originates from APL. It&#8217;s good to see new entrants like QuestDB and apache arrow pick up these ideas, make them their own and take them to new heights. I think kdb+ and q are excellent, I was always frustrated that it has remained niche while inferior technical solutions became massively popular, if QuestDB can take time-series databases and good technical ideas to new audiences, I wish them the best of luck!<\/p>\n<p>Please leave any of your thoughts or comments below as I would love to hear what others think.<\/p>\n<p>If you want to see how to setup QuestDB and a crypto dashboard yourself, we have a <a href=\"\/pulse\/tutorial\/questdb-live-charts\">video tutorial<\/a>:<\/p>\n<p><a href=\"\/pulse\/tutorial\/questdb-live-charts\"><img loading=\"lazy\" class=\"aligncenter size-medium\" src=\"https:\/\/www.timestored.com\/pulse\/video\/questdb-charting-tutorial.png\" width=\"320\" height=\"180\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Our latest product Pulse is for displaying real-time interactive data direct from any database. To get most benefit, the underlying databases need to be fast (&lt;200ms queries). For our purposes databases fall into 2 categories: Really really fast, can handle queries every 200ms or less and seamlessly show data scrolling in All Other Databases. The [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0},"categories":[2,109,23],"tags":[61,90,103,111,24,25],"_links":{"self":[{"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/posts\/111615"}],"collection":[{"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/comments?post=111615"}],"version-history":[{"count":28,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/posts\/111615\/revisions"}],"predecessor-version":[{"id":111758,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/posts\/111615\/revisions\/111758"}],"wp:attachment":[{"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/media?parent=111615"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/categories?post=111615"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/tags?post=111615"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}