5 ms·

I came across QuestDB in the past, but never tried myself. At my company, we use kx and onetick. Could you please elaborate why you are also comparing with Post

by quod_2058 6y ago

I came across QuestDB in the past, but never tried myself. At my company, we use kx and onetick. Could you please elaborate why you are also comparing with Postgres since it's not really a time-series database nor revendicating to be part of the "high performance" club?

bluestreak 6y ago
You are right, PostgreSQL is not necessarily optimal for time-series workloads. In this case, the benchmark is a simple task, and not related to time-series. It's consists of reading 1 billion values to a table and sum them together. It doesn't get simpler than that. The reason we are showcasing this instead of other more complex queries is because this is a simple, easily reproducible benchmark. It provides point of reference for performance figures.
jnordwick 6y ago
because they seem to only support that, from their page: "As of now, SIMD operations are available for non-keyed aggregation queries, such as select sum(value) from table." not even sure if they support where clauses on that, sums of functions of a column, or even other things like stddev of the column. their storage format though looks good and simple (similar to kdb actually), but they really should have an 8-byte char instead of the 16-byte one since that would be far more used. their partitioning scheme is only on time so less advanced than other system. single designated timestamp column (so no bitemporal), but do support asof joins which is nice. they totally screwed up on dates and time. dates only to milli and timestamps only to micros. huge mistake. long256 which is nice, but strangely no long128s (which wind up being nice when you have base-10 fixed-point numbers normalized to a large number of decimals). i didn't see any fixed-width string/byte columns. Does have 32-bit symbols (i assume similar to kdb?) that might cover some of those use cases. some good and some bad in there. never going to compete with kdb or onetick on performance (and nobody competes with kdb on query language/extensibity) , but could find a niche based on price and having simpler more easily adapted to querying and more human interface.
- bluestreak 6y ago
  Good summary, thank you. - we will extend SIMD to where clause, keyed aggregations, sampling, ordering, joins etc. It is a matter of time. - do you mind elaborating on how we screwed up date and time? - what makes you think we are never going to compete on performance with kdb+?
  - jnordwick 6y ago
    - nano are important for keeping ordering. while you may return results in insert order it is nice to have them so any other operations done in them outside the db you can retain (or recreate) that ordering. in financial systems nanos have become a sort of defacto standard for this. for examsple, all our messaging timestamps at places i've worked are always nano for anything written in the last 5-10 years. Also when you are trying to do calculations on high-frequency data (tick, iot) it ruins your ability to take meaningful deltas (eg, arrival rates) since you get a lot of 0s and 1s for the time deltas. Its difficult to take weighted averages with weights of 0s. the issues solving that (if you really need a wider range) are easier to solve that having to force everything down to micros and creating ways around that. (eg, kdb uses multiple date, time, and timestamp types and it doesn't use the unix epoch since it isn't very useful for tick, censor, or any high-frequency data i've seen). better than a double that some systems still use. -kdb's secret sauce that people don't seem to understand is its query language that more naturally fits the time series domain. It isn't really a database as more it is an array language with a database component. (eg, try to write an efficient sql query that calculates 5 minute bars on tick data). I actually like Java too - I've written or worked on a couple trading systems written in core java. just get good devs who understand what it means to write zero gc code, abuse off-heap memory, and understand what hotspot can intrinsify. If you can stay in the simd code for all the heavy lifting loops (filters, aggregates, etc), I don't think java will be an impediment. I think you have parts going in the correct direction, and you seem to have good experience from looking at the bios. Nothing really un-fixable (or un-addable) in what I saw glancing at your docs. I did bookmark you to see how the db goes. Will prob check out soon.
    - patrick73_uk 6y ago

bluestreak 6y ago

You are right, PostgreSQL is not necessarily optimal for time-series workloads. In this case, the benchmark is a simple task, and not related to time-series. It's consists of reading 1 billion values to a table and sum them together. It doesn't get simpler than that. The reason we are showcasing this instead of other more complex queries is because this is a simple, easily reproducible benchmark. It provides point of reference for performance figures.

jnordwick 6y ago

because they seem to only support that, from their page: "As of now, SIMD operations are available for non-keyed aggregation queries, such as select sum(value) from table." not even sure if they support where clauses on that, sums of functions of a column, or even other things like stddev of the column. their storage format though looks good and simple (similar to kdb actually), but they really should have an 8-byte char instead of the 16-byte one since that would be far more used. their partitioning scheme is only on time so less advanced than other system. single designated timestamp column (so no bitemporal), but do support asof joins which is nice. they totally screwed up on dates and time. dates only to milli and timestamps only to micros. huge mistake. long256 which is nice, but strangely no long128s (which wind up being nice when you have base-10 fixed-point numbers normalized to a large number of decimals). i didn't see any fixed-width string/byte columns. Does have 32-bit symbols (i assume similar to kdb?) that might cover some of those use cases. some good and some bad in there. never going to compete with kdb or onetick on performance (and nobody competes with kdb on query language/extensibity) , but could find a niche based on price and having simpler more easily adapted to querying and more human interface.

bluestreak 6y ago
Good summary, thank you. - we will extend SIMD to where clause, keyed aggregations, sampling, ordering, joins etc. It is a matter of time. - do you mind elaborating on how we screwed up date and time? - what makes you think we are never going to compete on performance with kdb+?
- jnordwick 6y ago
  - nano are important for keeping ordering. while you may return results in insert order it is nice to have them so any other operations done in them outside the db you can retain (or recreate) that ordering. in financial systems nanos have become a sort of defacto standard for this. for examsple, all our messaging timestamps at places i've worked are always nano for anything written in the last 5-10 years. Also when you are trying to do calculations on high-frequency data (tick, iot) it ruins your ability to take meaningful deltas (eg, arrival rates) since you get a lot of 0s and 1s for the time deltas. Its difficult to take weighted averages with weights of 0s. the issues solving that (if you really need a wider range) are easier to solve that having to force everything down to micros and creating ways around that. (eg, kdb uses multiple date, time, and timestamp types and it doesn't use the unix epoch since it isn't very useful for tick, censor, or any high-frequency data i've seen). better than a double that some systems still use. -kdb's secret sauce that people don't seem to understand is its query language that more naturally fits the time series domain. It isn't really a database as more it is an array language with a database component. (eg, try to write an efficient sql query that calculates 5 minute bars on tick data). I actually like Java too - I've written or worked on a couple trading systems written in core java. just get good devs who understand what it means to write zero gc code, abuse off-heap memory, and understand what hotspot can intrinsify. If you can stay in the simd code for all the heavy lifting loops (filters, aggregates, etc), I don't think java will be an impediment. I think you have parts going in the correct direction, and you seem to have good experience from looking at the bios. Nothing really un-fixable (or un-addable) in what I saw glancing at your docs. I did bookmark you to see how the db goes. Will prob check out soon.
  - patrick73_uk 6y ago