bluestreak

SQL 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 :)

the use of where clause right now turns off vectorization completely is falls back on simple single threaded execution. This is the reason why the query is slow right now. We are going to vectorize 'where' really soon.

multi-key aggregation is next on our list to optimise. It will perform similar to single key one in most cases.

This demo is showing raw database performance. None of the results are cached or pre-calculated.

Thank 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.

This 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.

We 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

while 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

Ordering 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.

someone already brought this up, there is rationale for it at bottom of this thread

Thank 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.

All 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

the reason was grid scroll using touchpad on OSX, when you swipe the grid from left to right there is 50% chance of losing web console due to back button being triggered by the same motion.

The 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

this 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.

Thanks, not all queries are optimal yet. We are going to get there.

Our 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.

Athena has been benchmaked here with similar dataset (1.1B vs our 1.6Bn):

https://tech.marksblogg.com/benchmarks.html

Author 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-b...

[2] https://www.agner.org/optimize/vectorclass.pdf

[3] https://www.youtube.com/watch?v=ncHmEUmJZf4

[4] https://github.com/abseil/abseil-cpp

[5] https://github.com/questdb/questdb/blob/master/core/src/main...

[6] https://github.com/questdb/questdb/blob/master/core/src/main...

[7] https://questdb.io/blog/2020/03/15/interthread

[8] https://github.com/questdb/questdb

It is possible, we've done it.

I have not worked on this project, so I don't have details. Mongo is in a nutshell to replace a database trader setup under their desk and that grew quite important. There is nothing pretty about that. I'm quite sure they will have issues and workarounds for years to come :(

This was in London HSBC HQ, Equities

I was there in Aug 2019 and I did hear them talking about mongo, There was a programme at the time to "refactor" their estate, a part of that they wanted to replace bunch of ad-hoc databases with clunky- or no data governance with one (or fewer) central places, which will be under Data IT control. The rationale was to reduce risk of data loss, leak or general business continuity issue in what was 6-12 month project.

The cost of architecting relational model that would be cover for 65 mini databases in a bank will be astronomical. It is far easier to setup no-SQL entry point that would auto-conform for whatever requirements upstream applications might have.

It is a technical win for Mongo, but I don't believe this is an attempt by HSBC to get up to speed with database trends. HSBC are removing internal audit strikes, nothing more than that.

We take data integrity very seriously. This is one of the reasons QuestDB actually exists. We haven't had reports of data corruption or loss yet. We can commit durably if needed too.

To manipulate data we support "add" and "delete" column on the fly. We can also add column replace and type change if needed. This is pretty easy to do.

PostgreSQL wire is in beta. It works with JDBC driver and we will add metadata support quite soon.

Doesn’t redirect me :/

Thank you Radford! This is a long piece and it would take me a while to absorb properly. Does your algorithm lend itself to vectorised implementation?

Thank you for taking time to elaborate on the subject. This is both informative and enjoyable to read! We knew that all real numbers cannot be represented using IEEE-754. It wasn’t all clear though because toString() appears to be perfectly transitive for double 5.1 yet “sum” is non-associative.

I guess it would be more accurate to say that arithmetic is wrong at some probability value. I just feel that in this instance it is appropriate to call “wrong” what is not 100% right :)

Thank you! QuestDB is in production. Scaling is a part of commercial product, built using QuestDB.

Author here.

About a month ago, I posted about using SIMD instructions to make aggregation calculations faster. I am very thankful for the feedback so far, this post is the result of the comments we received last time.

Many comments suggested that we implement compensated summation (aka Kahan) as the naive method could produce inaccurate and unreliable results. This is why we spent some time integrating kahan and Neumaier summation algorithms. This post summarises a few things we learned along this journey.

We thought Kahan would badly affect the performance since it uses 4x as many operations as the naive approach. However, some comments also suggested we could use prefetch and co-routines to pull the data from RAM to cache in parallel with other CPU instructions. We got phenomenal results thanks to these suggestions, with Kahan sums nearly as fast as the naive approach.

A lot of you also asked if we could compare this with Clickhouse. As they implement Kahan summation, we ran a quick comparison. Here's what we got for summing 1bn doubles with nulls with Kahan algo. The details of how this was done are in the post.

QuestDB: 68ms Clickhouse: 139ms

Thanks for all the feedback so far and keep it going so we can continue to improve. Vlad