12 ms·
Show HN: Query 1.6B rows in milliseconds, live
- bluestreak 6y agoAuthor here. A few weeks ago, we wrote about how we implemented SIMD instructions to aggregate a billion rows in milliseconds [1] thanks in great part to Agner Fog’s VCL library [2]. Although the initial scope was limited to table-wide aggregates into a unique scalar value, this was a first step towards very promising results on more complex aggregations. With the latest release of QuestDB, we are extending this level of performance to key-based aggregations. To do this, we implemented Google’s fast hash table aka “Swisstable” [3] which can be found in the Abseil library [4]. In all modesty, we also found room to slightly accelerate it for our use case. Our version of Swisstable is dubbed “rosti”, after the traditional Swiss dish [5]. There were also a number of improvements thanks to techniques suggested by the community such as prefetch (which interestingly turned out to have no effect in the map code itself) [6]. Besides C++, we used our very own queue system written in Java to parallelise the execution [7]. The results are remarkable: millisecond latency on keyed aggregations that span over billions of rows. We thought it could be a good occasion to show our progress by making this latest release available to try online with a pre-loaded dataset. It runs on an AWS instance using 23 threads. The data is stored on disk and includes a 1.6billion row NYC taxi dataset, 10 years of weather data with around 30-minute resolution and weekly gas prices over the last decade. The instance is located in London, so folks outside of Europe may experience different network latencies. The server-side time is reported as “Execute”. We provide sample queries to get started, but you are encouraged to modify them. However, please be aware that not every type of query is fast yet. Some are still running under an old single-threaded model. If you find one of these, you’ll know: it will take minutes instead of milliseconds. But bear with us, this is just a matter of time before we make these instantaneous as well. Next in our crosshairs is time-bucket aggregations using the SAMPLE BY clause. If you are interested in checking out how we did this, our code is available open-source [8]. We look forward to receiving your feedback on our work so far. Even better, we would love to hear more ideas to further improve performance. Even after decades in high performance computing, we are still learning something new every day. [1] https://questdb.io/blog/2020/04/02/using-simd-to-aggregate-billions-of-rows-per-second https://questdb.io/blog/2020/04/02/using-simd-to-aggregate-b... [2] https://www.agner.org/optimize/vectorclass.pdf https://www.agner.org/optimize/vectorclass.pdf [3] https://www.youtube.com/watch?v=ncHmEUmJZf4 https://www.youtube.com/watch?v=ncHmEUmJZf4 [4] https://github.com/abseil/abseil-cpp https://github.com/abseil/abseil-cpp [5] https://github.com/questdb/questdb/blob/master/core/src/main/c/share/rosti.h https://github.com/questdb/questdb/blob/master/core/src/main... [6] https://github.com/questdb/questdb/blob/master/core/src/main/c/share/vec_agg.cpp#L155 https://github.com/questdb/questdb/blob/master/core/src/main... [7] https://questdb.io/blog/2020/03/15/interthread https://questdb.io/blog/2020/03/15/interthread [8] https://github.com/questdb/questdb https://github.com/questdb/questdb
- osipov 6y agoCool project! You posted a comparison to Clickhouse on the front page. Do you have any insights on how QuestDB compares performance-wise to PrestoDB? Also, PrestoDB is known to be manageable at scale, for example as part AWS Athena. What are you thoughts on building out an Athena-like service using QuestDB?
- bluestreak 6y agoAthena has been benchmaked here with similar dataset (1.1B vs our 1.6Bn): https://tech.marksblogg.com/benchmarks.html https://tech.marksblogg.com/benchmarks.html
- TheTank 6y agoWe will definitely make QuestDB distributed in the near future. Today, our focus is to extract the maximum performance out of a single node. If we start scaling out too early, it may become harder to improve performance after the fact. So all improvements we get today are foundational: they will have multiplicative effects once you start to scale out.
- seektable 6y agoHi Vlad, what BI tools currently can connect to QuestDB for ad-hoc reporting (without need to write SQL queries by hands)? I guess popular solutions like Tableau/PowerBI doesn't support QuestDB. Is there a demand for BI tools that can use QuestDB as a data source? I'm asking about that because we can add a connector for QuestDB to our BI tool (SeekTable) with a little effort, but this has sense only if someone really will use it.
- y42 6y agoI cannot talk for the whole industry, but I know for sure, that I (working in BigData / OnlineMarketing) was looking for exactly this recently: A BI tool that is agile and a database that is fast. I ended up with Tableau Server and ClickHouse (which is impressivly fast). Problem was: Tableau does not really support CH and Tableau is not that agile. So to answer your question: Yes. If QuestDB is that fast, there is a demand.
- lux 6y agoI was looking for dev/production specs for QuestDB the other day and didn't see them in the docs. Being that it's in Java, which can be quite memory-hungry, what's the minimum and recommended RAM/CPU required to run it?
- bluestreak 6y agoOur startup sequence is sub-optimal. We query JDK to index timezone database and JDK generates a tonne of garbage in the process. The whole feat requires roughly 500MB RAM. Once out of startup sequence all this is collected and we can operate in 128MB heap. The data itself is memory mapped. Columns are kept as primitives so that they take as much memory as their unit size times rows.
- lux 6y agoThanks for the explanation, makes sense! Sounds like I could get away with 1GB RAM for development and probably want a bit more of a buffer to be safe in production.
- tpayet 6y agoCool project :) How would you compare QuestDB against TimescaleDB?
- j1897 6y agoTimescale is built on top of Postrgres. QuestDB has been built from the ground up. Both are SQL. QuestDB is primarily focused on performance while timescale has more features today.
- dvnguyen 6y agoAsk HN: what’s the market for a new Database? Who do you build it for and how do you sell it? Asking as an uninformed users who haven’t needed a niche database.
- woodgrainz 6y agoIt's a really good question IMHO. The DB market has huge players that have been around for decades.
- dls2016 6y agoIf you’re talking about time series databases then there are a number of them used in industrial settings called “process historians”, for instance, OSI PI.
- ramraj07 6y agoValid question. I was rhe guy who embraced and optimized a full offering in our org, first on redshift and then on snowflake. Even merely evaluating a new db (on top of actually parsing their landing pages to understand what does what) for our data needs is exhausting to say the least. I still occassionally get enthusiastic with some new cool db (most recently materialize) and spend a weekend trying to spin it up and load our data, but I invariably walk away feeling redshift/snowflake is fine/better while simultaneously feeling guilty I didn't give the tool a real chance at all. In the end, I'm probably going to give up on trying new db ideas unless their pitch absolutely blows me away. I've gotten much better ROI just doubling down on optimizing our code to use these popular offerings (which çontinue to add features every day) to our use cases and just throw a bit more money at it. For example, we actually query a 115 billion row, 1.5 TB table in snowflake and barring some cluster warmup time, queries that return less than a million rows run in a couple hundred ms max. Until the day we need this to speed up to just a few ms, I'll probably stick here.
- woodgrainz 6y agoI don't know anything about this database, but this link did break the browser <back> button. Perhaps you can look into fixing that in a future release.
- TheTank 6y agoThanks for the feedback, we'll look into it!
- rkagerer 6y agoI tried these two: select * from trips; /* completed quickly */ select * from trips where tip_amount = 0.56; /* stalled */ Hope I didn't break anything ;-)
- bluestreak 6y agoAll good so far! Thank you for the feedback. Filter's are single threaded and not very fast. We will take them to the next level shortly
- sirffuzzylogik 6y agoThanks for the feedback. A PR was created to fix this :) : https://github.com/questdb/questdb/pull/434 https://github.com/questdb/questdb/pull/434
- cdubzzz 6y agoInteresting. This little bug comes up a lot on HN and is usually just that -- a bug. In this case, it seems that the "breaking" of the back button here was intentional based on the line comment[0]. What was the initial reasoning for that? [0] https://github.com/questdb/questdb/pull/434/files#diff-c9ec35dd22bf29a8a9ea540b1ab1aeb7L18 https://github.com/questdb/questdb/pull/434/files#diff-c9ec3...
- bluestreak 6y ago
- lykr0n 6y ago`select distinct pickup_latitude, count(*) from trips` takes 241.37s. But some of the more top level queries are quite fast.
- bluestreak 6y agoThanks, not all queries are optimal yet. We are going to get there.
- sa46 6y agoHow timely! I've done a deep dive into column store databases for the past couple of weeks. Reading through the Quest docs, I'd give it the following characteristics. Are these accurate? - single node database, not [yet] distributed - primary focus is time-series data, specifically in-order time series data (the `designated timestamp` extension) - physical data layout is an append-only column store - Implements a small subset of SQL with some affordances for time series (LATEST BY, SAMPLE BY). - Doesn't support explicit GROUP BY or HAVING clauses. Instead, questdb implicitly assumes GROUP BY or HAVING based on presence of aggregation functions in the select clause. - Small standard library of functions: only 4 text functions. Based on these characteristics it seems the quest db is well positioned against Influx. It's probably faster than Timescale DB but significantly less flexible given that Timescale has all of Postgres behind it. Quest DB might eventually compete with clickhouse but it's long ways out given that it's not distributed and implements a much smaller subset of SQL. I'd love to get any insight into join performance. Quite a few column stores handle large joins poorly (clickhouse, druid).
- bluestreak 6y agothis is accurate, thank you. You can try asof join: SELECT pickup_datetime, cab_type, trip_type, tempF, skyCover, windSpeed FROM trips ASOF JOIN weather; This joins 1.6B to 130K roughly. Generally we provide both row and column based access despite being column store. So anything a traditional database does we can do too.
- sa46 6y ago> Generally we provide both row and column based access despite being column store. So anything a traditional database does we can do too. Does quest DB use the same physical data layout for the row based access? My understanding was providing an entire row of data from a columnar file is expensive because you have to assemble the row from num_columns different slices of the columnar file.
- bluestreak 6y agoThe data layout is indeed the same for both cases. We don't assemble row as such. In QuestDB row is a collection of data addresses, each of which is calculated lazily using row_id and bitshift
- Bedon292 6y agoAny plans for geo support? Looks like you have lat/lon stored as two independent doubles, which does not lend itself well to any sort of geo operations.
- TheTank 6y agoYes, this is part of the roadmap. Right now, we don't have any interesting query to showcase that would use these columns - they are here because they are part of the original dataset and we are keeping them for when we demo further releases.
- helltone 6y agoIf anyone from questdb is around, and want tips on how to go around implementing GIS support for a database, hit me up! Email on my profile.
- TheTank 6y agoWill do. Thanks for offering help!
- deleted 6y ago[deleted]
- twoodfin 6y agoVery cool. Major props for putting this out there and accepting arbitrary queries. Couple comments / questions: - Correct that there’s no GROUP BY support? - EXPLAIN or similar would be nice, both to get a peek at how your engine works & to anticipate whether a query is using parallel / SIMD execution and will take milliseconds vs. minutes.
- bluestreak 6y agoThank you! there is no 'GROUP BY' keyword support. Optimiser works out group by queries without it. We are planning to put a noop there, just to be nice to finger memoory. EXPLAIN would be quite boring in our case, we use a myriad of methods to make query fast. We would love to keep up the performance so that no one ever needed to be interested in query plan.
- rzzzt 6y agoCheck the example queries, one of them had aggregation and simple column selection in the same query with an "order by" clause at the end. I can't tell from the output whether there is an implicit "group by" going on over the non-aggregated columns, but eg. this query produced some results without any errors: select windDir, avg(windSpeed) from weather
- LordOfWolves 6y agoHow nice of you to hijack the back-button (to return to HN after some sample queries) despite that being how myself & others found this in the first place. I was really liking the product up until that point.
- dewey 6y agoThere are nicer ways to say that, it might just be a tiny bug on a brand new project that someone is sharing.
- bluestreak 6y agosomeone already brought this up, there is rationale for it at bottom of this thread
- shreddit 6y agowell, ‘breaking’ basic browser functionality because it breaks the design of your page is not really something i would call rational... As stated some comments below, the code literally says ‘disable back button’
- bluestreak 6y agoWe will find a way to fix this and have grid scroll without problems at the same time. This is not a dedicated website. This is exactly the same web console that is packages with our free product. It wasn't meant to run on the internet and confuse people. Sorry
- santafen 6y agoAs someone else pointed out, and a QuestDB engineer responded, it’s been noted and an issue has been created for it. 5e reasoning for it was actually solid.
- MarcellusDrum 6y agoYeah an extremely pathetic move.
- timbowhite 6y agoJust a heads up - I tried this random query: select * from trips where pickup_latitude < -74 order by pickup_longitude desc limit 10; Didn't get a result set. The little box in the upper right hand corner stated "Maximum number of pages (16) breached in MemoryPages"
- bluestreak 6y agoOrdering would be memory intensive. There are configurable artificial limits in place on this demo instance. This is just because database takes internet traffic directly. In any other settings these limits can be relaxed, which is default behaviour anyway.
- deleted 6y ago[deleted]
- gubby 6y agoFYI, this query took 81 seconds: select count(*) from trips where fare_amount > 0 Presumably this is Hacker News load?
- bluestreak 6y agowhile HN load is high, this is just because poor implementation on our side. Filters are single threaded, not yet vectorized in any way. This will be fixed eventually to bring in line with queries that we've already optimised
- TheTank 6y agoTanks for the feedback. WHERE queries are slow indeed. This is not because of the load but rather because WHERE clauses are not yet implemented under the fast path. Currently they run under an old model which is single-threaded which is why they are painfully slow. We will make these as fast as the rest soon.
- deleted 6y ago[deleted]
- yumraj 6y agoA peek into the hardware specs behind this: CPU, memory, storage, network would be nice.
- bluestreak 6y agoThis is running on a single AWS c5.metal instance. It has 196GB RAM, but we are using only 40GB at peak. The instance provides two 24-core CPUs, but we are only using one of them on 23-threads. So far, it's handling the flow of connections and requests from HN fine. We over provisioned just in case as we were not sure what to expect :-) Storage is an AWS EBS volume.
- deleted 6y ago[deleted]
- strikelaserclaw 6y agoVery impressive, i think building your own (performant) database from scratch is one of the most impressive software engineering feats. Can you let me know a little bit of how an interested person with a cs background can approach this daunting topic?
- bluestreak 6y agoThank you for the kind words. Databases can be daunting but for a variety of reasons. In my experience, it's not so much because of the sheer amount of features and function that need to be implemented. It's because you have to make sure that the every component is fast and reliable. Ultimately, the software can only be as fast as the slowest part of the stack. So you are going to spend a lot of time hunting for the slow part and looking for ways to accelerate it. More generally, the way I approached this was to break down the project in small pieces, making each as fast as can be. This makes the problem more approachable. Also, you have to know and love how hardware works.
- heybrendan 6y agoCould you take a moment to comment on why one would choose to use QuestDB in lieu of Google's Bigtable or BigQuery? Furthermore, how does this compare to other proprietary and open source solutions currently available in the marketplace? In short, I'm struggling to understand where this technology fits and the over all value proposition. Asked a different way: If I were a lead of IT, director-level executive, CTO, etc. how and why should I begin to evaluate the QuestDB service / solution? Thank you for sharing, bluestreak and team, very interesting and exciting work!
- TheTank 6y agoThanks for your question! The main value proposition is raw performance which translates into hardware efficiency and ultimately, costs at scale. To get a rough idea of how we compare, you could try to running `select cab_type, count() from trips` and `select passenger_count, avg(total_amount) from trips`. They are the equivalent of queries (1) and (2) of this benchmark [1]. In this benchmark, BigQuery took 2 seconds for each query. Our server runs both in hundreds of milliseconds. And this is actually slow because this is the same server which is currently being hammered by HN users. Also, we are scanning more rows. Our dataset contains 1.6 billion rows while the benchmark attached runs on 1.1 billion. Lastly, we only use one 24-core CPU from one server while the top of this benchmark is clusters or GPUs. Of course this is only approximation but I hope to be given the chance to feature in this benchmark when we are ready. [1] https://tech.marksblogg.com/benchmarks.html https://tech.marksblogg.com/benchmarks.html
- ramraj07 6y agoIs your data indexed or clustered? Redshift queries on sorted data is just as fast if not faster.
- calibas 6y agoI abused LEFT JOIN to create a query that produces 224,964,999,650,251 rows. Only 3.68ms execution time, now that's impressive!
- DevoidSimo 6y agoInteresting, I tried to make a query taking a long time using join and got 37.43s: SELECT * FROM trips JOIN gas_prices ON gas_prices.timestamp = trips.pickup_datetime;
- calibas 6y agoSeeing the same results on my end for that query. I'm a little puzzled, when I use the same query but with != instead of = to produce a 1.6 trillion row result, it's about 3-4ms execution. I don't know why it's taking 10,000x longer using = instead of !=. Something about how timestamp comparisons are handled?
- bluestreak 6y agoSQL optimiser works out that this is a cross join and renders execution path as such. Cross join implementation is streaming with pre-determined result set size. We would not be able to render this much data this quickly without optimiser :)
- jinmingjian 6y agosharing some thoughts here, in that I am recently developing a similar thing: 1. "Query 1.6B rows in milliseconds, live" is just like "sum 1.6B numbers from memory in ms". In fact, if not full SQL functionalities supported, a naive SQL query is just some tight loop on top of arrays(as partitions for naive data parallelism) and multi-core processors. So, this kind is just several-line benchmark(assumed to ignore the data preparing and threading wrapping) to see how much time the sum loop can finish. In fact again, this is just a naive memory bandwidth bench code. Let's count: now the 6-channel xeon-sp can provide ~120GB/s bandwidth. Then sum loop with 1.6B 4-byte ints without compression in such processors' memory could be finished about ~1.6*4/120 ~= 50ms. Then, if you find that you get 200ms in xxx db, you in fact has wasted 75% time(150ms) in other things than your own brew a small c program for such toy analysis. 2. Some readers like to see comparisons to ClickHouse(referred as CH below). The fact is that, CH is a little slow for such naive cases here(seen at web[1] been pointed by guys). This is because CH is a real world product. All optimizations here are ten- year research and usage in database industry and all included in CH and much much more. Can you hold such statement in the title when you enable reading from persistent disk? or when doing a high-cardinality aggregation in the query(image that low-cardinality aggregation is like as a tight loop + hash table in L2)? [1] https://tech.marksblogg.com/benchmarks.html https://tech.marksblogg.com/benchmarks.html
- sirffuzzylogik 6y agoThank you for your thoughts! 1. We do plan to support full SQL functionalities, we have a pretty good subset already [1]. I think that what we do is more than "naive memory bandwidth bench code", however I am happy to listen and when the time is right implement functionalities/features that you think we are missing. 2. "Can you hold such statement in the title when you enable reading from persistent disk?" We already persist to disk, the numbers you see imply reading from disk. We do this by using memory mapped files [2]. [1] https://questdb.io/docs/sqlExecutionOrder https://questdb.io/docs/sqlExecutionOrder [2] https://questdb.io/docs/storageModel https://questdb.io/docs/storageModel EDIT: fix typos
- RedShift1 6y agoWill we be able to put data from the past into it at some point?
- joan4 6y agoYes, out of order inserts are on our roadmap
- gregwebs 6y agoThis seems very similar to Victoria Metrics. Victoria Metrics is very much based on the design of Clickhouse and currently shows best of class performance numbers for time series data: it would be a lot more interesting to see a comparison to Victoria Metrics than ClickHouse (which is not fully optimized for time series). Victoria Metrics is Prometheus compatible whereas Quest now supports Postgres compatibility. Both have compatibility with InfluxDB.
- patelh 6y agoAny comparisons to Druid or Pinot?
- yingw787 6y agoVery cool and impressive!! Is full PostreSQL wire compatibility on the roadmap? I like postgres compatibility :)
- sirffuzzylogik 6y agoWe already support the Postgres Wire protocol, it is still in alpha though. You can check the documentation here: https://questdb.io/docs/guidePSQL/ https://questdb.io/docs/guidePSQL/ We do not implement 100% of the protocol yet, but the plan it to do so or at least implement all the features that people need.
- yingw787 6y agoNice! It'd be cool to support everything from PostgREST, that way people can build API-based tools on top of it and directly take advantage of the performance increases: postgrest.org/ I'm working on a...spreadsheet thing or something that requires PostgREST and it'd be really nice to simply direct the PSQL_URI configuration underneath the hood to whatever database has my data. Then there's no need for ETLing back and forth.
- sirffuzzylogik 6y agoPostgREST is an interesting project, thanks! I will check how good is our support for them. It really depends on how tailored to Postgres they are, if they only rely on the Wire protocol then it should be fine!
- greatNespresso 6y agoMind blowing, did not know about questDB. The back button seems broken on chrome mobile.
- joan4 6y agoThanks for the kind words. We are aware of the broken back button and we have a raised an issue for it.
- dnadler 6y agoIm not much of a database expert by an stretch, but this query took about 80 seconds, which seems like quite a long time, but maybe its more complicated than it appears? My understanding is that the group by is handled automatically, and the results seem to support that: select cab_type, payment_type, count() from trips;
- bluestreak 6y agomulti-key aggregation is next on our list to optimise. It will perform similar to single key one in most cases.
- pachico 6y agoIt looks very proming, congrats. I use ClickHouse in production and I'd love to see how this project evolves. My main disappointment is the amount of aggregation functions: https://questdb.io/docs/functionsAggregation https://questdb.io/docs/functionsAggregation Clickhouse provides hundreds of functions, many of which I use. It would be hard to even consider QuestDB with this amount of functions. I'll stay tuned, anyway. Keep up the good work!
- TheTank 6y agoThanks! Indeed we are still light on available aggregations. Right now we are focused on building a fast infrastructure to support them, for example Swissmap / "rosti" for keyed aggregations. Next steps will include multi-pass algorithms. Once the infrastructure is done (and fast), adding functions will be much easier.
- keshavmr 6y agoSELECT vendor_id, cab_type, avg(fare_amount) from trips; This takes ~86 seconds. Ran it multiple times. SIMD is one of the ingredients for better query performance, but NOT THE ONLY ONE. See this for more info: https://www.youtube.com/watch?v=xJd8M-fbMI0 https://www.youtube.com/watch?v=xJd8M-fbMI0