Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Building a distributed time-series database on PostgreSQL

blog.timescale.com

Post reply on HN
Print view View original

71–80 of 98 posts

Re: Building a distributed time-series database on PostgreSQL

#71

Post by akulkarni »

dfischer wrote: post #68

Anyone have examples of using this for financial / trading algorithm based needs? I've been investigating solutions for a while now and haven't had much luck on a winner. Is it better to do aggregations with the DB or through some MapReduce method (Google Dataflow?) and write that to a DB?

I believe we have some in our Slack channel [0].

Also one of our investors is Two Sigma so this is an area of interest to us.

If you're open to it, I'd love to learn more about your specific use case. Want to chat sometime? ajay (at) timescale.com

[0] https://slack.timescale.com

Re: Building a distributed time-series database on PostgreSQL

#72

Post by srazzaque »

mfreed wrote: post #52
srazzaque wrote: post #48

Earlier quoted context omitted.

I've been eyeing TimescaleDB at a distance for some time now. I'm curious if you have seen it used in finance as an alternative to KDB+ installations anywhere? Have you thought to release any benchmarks against KDB+?

To my understanding, KDB+'s license explicitly forbids benchmarking: "1.3 Kdb+ On Demand Software Performance. End User shall not distribute or otherwise make available to any third party any report regarding the performance of the Kdb+ On Demand Software, Kdb+ On Demand Software benchmarks or any information from such a report unless End User receives the express, prior written consent of Kx to disseminate such repo…

That's a shame. I'm not sure what the rationale is for such clauses - especially where performance of your tech is known to be pretty good (as is the case for KDB+).

There's definitely a huge opportunity to displace KDB+ as the mainstay for timeseries in capital markets. It is a premium product, but it obviously comes with a cost - both for the product and its operators.

Assuming all use cases can be catered for, if one needs an extra N machines if using TimescaleDB to cater for the same workloads, it might nullify any savings. If only there were a way to understand that without breaking their EULA...

Re: Building a distributed time-series database on PostgreSQL

#73

Post by beagle3 »

mfreed wrote: post #52
srazzaque wrote: post #48

Earlier quoted context omitted.

I've been eyeing TimescaleDB at a distance for some time now. I'm curious if you have seen it used in finance as an alternative to KDB+ installations anywhere? Have you thought to release any benchmarks against KDB+?

To my understanding, KDB+'s license explicitly forbids benchmarking: "1.3 Kdb+ On Demand Software Performance. End User shall not distribute or otherwise make available to any third party any report regarding the performance of the Kdb+ On Demand Software, Kdb+ On Demand Software benchmarks or any information from such a report unless End User receives the express, prior written consent of Kx to disseminate such repo…

Does this also apply to a locally installed version? It didn't in the past.

"On Demand" is IIRC the cloud kdb+, which is thus much less predictable and easy to misrepresent.

Re: Building a distributed time-series database on PostgreSQL

#74

Post by mfreed »

beagle3 wrote: post #73
mfreed wrote: post #52

Earlier quoted context omitted.

To my understanding, KDB+'s license explicitly forbids benchmarking: "1.3 Kdb+ On Demand Software Performance. End User shall not distribute or otherwise make available to any third party any report regarding the performance of the Kdb+ On Demand Software, Kdb+ On Demand Software benchmarks or any information from such a report unless End User receives the express, prior written consent of Kx to disseminate such repo…

Does this also apply to a locally installed version? It didn't in the past. "On Demand" is IIRC the cloud kdb+, which is thus much less predictable and easy to misrepresent.

Not my specialty, but website shows two versions available for download (at least for free non-commercial use): 64-bit "on-demand" or 32-bit. Both have a similar no benchmarking clause.

https://kx.com/connect-with-us/download/

32-bit version: "(c) 32 Bit Kdb+ Software Evaluations. User shall not distribute or otherwise make available to any third party any report regarding the performance of the 32 Bit Kdb+ Software, 32 Bit Kdb+ Software benchmarks or any information from such a report unless User receives the express prior written consent of Kx to disseminate such report or information."

But more broadly, feedback from the finance/capital markets suggest that the choice of kdb's proprietary Q query language vs. standard SQL is top-of-mind, expanding access and insights to time-series data from a small set of highly-specialized engineers to any of their developers / analysts / tools.

Re: Building a distributed time-series database on PostgreSQL

#75

Post by AdamProut »

mfreed wrote: post #66
einpoklum wrote: post #63

Earlier quoted context omitted.

Please link to those benchmarks, and we'll see. Also, a link to the relevant SIGMOD/VLDB/ICDE/DaMoN/ADMS/etc. submission arguing in favor of TimeScaleDB's design would also be appreciated. On the linked-to article I only see references to irrelevant transactional DBMSes...

Open-source Time-Series Benchmarking Suite: https://github.com/timescale/tsbs InfluxDB: https://blog.timescale.com/blog/what-is-high-cardinality-how... https://blog.timescale.com/blog/timescaledb-vs-influxdb-for-... Cassandra: https://blog.timescale.com/blog/time-series-data-cassandra-v... MongoDB: https://blog.timescale.com/blog/how-to-store-time-series-dat...

I think the ask was for comparisons against traditional analytics databases (redshift, Vertica, etc.). Columnstores are substantially faster for table scans + aggregations then rowstores (and they use a lot less storage) [1].

[1] http://db.csail.mit.edu/projects/cstore/vldb.pdf

Re: Building a distributed time-series database on PostgreSQL

#76

Post by ambicapter »

malisper wrote: post #43
sroussey wrote: post #37

Earlier quoted context omitted.

Yeah, but what about increasing the replication factor only for “hot” chunks?

"Hot" is lingo for describing a chunk that is being operated on at a rate much higher than other chunks. Depending on what exactly is making the chunk "hot" increasing replication can either make things better or worse. If you have a chunk that's hot because there are a lot of reads going to it, yes, increasing replication will help because you are decreasing the amount of work you have to do per replica. If you have…

So what's the strategy for chunks that are "hot" with writes? Partitioning?

Re: Building a distributed time-series database on PostgreSQL

#77

Post by AdamProut »

ryanworl wrote: post #2

Are you afraid the Postgres tuple-at-a-time iterator architecture is going to be limiting for your long term performance optimizations? Presumably the custom operators you’ve implemented in your query plan can push projection and predicates down to a more efficient architecture without so many indirect function calls / branches / etc, but once you get up past that, aren’t you back in iterator land?

I'm guessing the use cases they see don't involve query shapes that need to scan a lot of data. A columnstore along with vectorized or batched execution is a much better storage layout for fast scans with filters and aggregations (i.e., the top TPC-H and TPC-DS analytics benchmark results are from columnstore databases). A B-tree with tuple at a time execution is orders of magnitude slower [1][2].

[1] http://cidrdb.org/cidr2005/papers/P19.pdf [2] http://db.csail.mit.edu/projects/cstore/vldb.pdf

Re: Building a distributed time-series database on PostgreSQL

#78

Post by einpoklum »

mfreed wrote: post #66
einpoklum wrote: post #63

Earlier quoted context omitted.

Please link to those benchmarks, and we'll see. Also, a link to the relevant SIGMOD/VLDB/ICDE/DaMoN/ADMS/etc. submission arguing in favor of TimeScaleDB's design would also be appreciated. On the linked-to article I only see references to irrelevant transactional DBMSes...

Open-source Time-Series Benchmarking Suite: https://github.com/timescale/tsbs InfluxDB: https://blog.timescale.com/blog/what-is-high-cardinality-how... https://blog.timescale.com/blog/timescaledb-vs-influxdb-for-... Cassandra: https://blog.timescale.com/blog/time-series-data-cassandra-v... MongoDB: https://blog.timescale.com/blog/how-to-store-time-series-dat...

Yes, it's just like I thought. You're comparing against transaction-oriented DBMSes, or ones which handle documents rather than tabular data (and hence slow on tabular data).

One possible exception is InfluxDB - I'm not familiar enough with it.

Anyway, try running TSBS on columnar DBMSes like Actian VectorH, Vertica, SAP HANA etc. ClickHouse may also be relevant; they don't support any possible schema, but it may be enough to run TSBS.

Re: Building a distributed time-series database on PostgreSQL

#79

Post by truth_seeker »

Wow. For me personally, that sounds like Distributed Event Sourcing Storage at scale.

I don't know if anybody observed but the article is so damn intuitive, it literally covered almost all the things. Often times when such articles are published I have to google it deeper to get a sense of its practical use.

I have one query: How efficient is the deletion (from disk) of chunks in a new distributed model?

Re: Building a distributed time-series database on PostgreSQL

#80

Post by einpoklum »

einpoklum wrote: post #64
pvorb wrote: post #58

Earlier quoted context omitted.

Which databases are good for analytics from your point of view? In my experience, being able to do advanced ad-hoc SQL queries is priceless for analytics. Timescale helps in scaling time series use-cases that used to scale badly in plain PostgreSQL.

It's not just my point of view - it's well known in the research community, and has been for decades. For FOSS, have a look at MonetDB. For research-oriented systems, look for publications regarding HyperDB or VectorWise/Actian Vector (VectorH in the cluster version). Other commercial offerings are Vertica (formerly C-Store) and SAP Hana. PostgreSQL is not even something anyone compares against in analytics...

Oh yea, MemSQL and ClickHouse are also indeed relevant and in this category, except that ClickHouse doesn't support all of SQL and any table structure, so it's not a full-fledged DBMS.
Post reply on HN