Comment by sa46 | Hacker News Reader

Comment by sa46 | Hacker News Reader

How 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).

Very accurate!

Regarding the theme of joins, I would just add that QuestDB also has a concept called SYMBOL which allows to store repetitive strings into a table as an integer in the background, while letting the user manipulate strings. This means that instead of storing something like 'sensor_id' and then looking up the corresponding 'sensor_name' using a join, you can manipulate 'sensor_name' directly and skip using joins.

SYMBOL concept sounds like ClickHouse LowCardinality (or maybe implicit 'dictionary').

If you're interested in columnar database that can handle joins, Microsoft has one. It is called Azure Data Explorer(Kusto) and it is really fast. Here's the usual Uber Taxi Rides bench, you can see joins in the end: https://yonileibowitz.github.io/kusto.blog/blog-posts/analyz...

Interesting, I hadn't come across Kusto. Looks like a competitor to Snowflake, perhaps with stronger analysis capabilities. Do you know if there's an academic paper? I found a white paper: https://azure.microsoft.com/mediahandler/files/resourcefiles...

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.

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

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

Greenplum DB is open source, based on Postgres, and is a proper sharded OLAP database in the vein of Teradata. Its answer to these types of large analytic queries is that it stores the answers and as long as the underlying table data doesn't change, the query can run at near 0 cost. I believe the same approach is probably in use here.

Shame generate_series() isn't a valid function here - joining to an artificial table is a classic way to break the pre-aggregated values, and force a re-calc.

Redshift does something similar IIRC.

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

Reply on news.ycombinator.com