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

81–90 of 98 posts

Re: Building a distributed time-series database on PostgreSQL

#81

Post by xz0r »

enordstr wrote: post #25
PeterCorless wrote: post #18

The biggest limit is that their "chunking" of data by time-slices may lead directly to the hot partition problem -- in their case, a "hot chunk." Most time series is 'dull time' -- uninteresting time samples of normal stuff. Then, out of nowhere, some 'interesting' stuff happens. It'll all be in that one chunk,which will get hammered during reads. Like, imagine all the telemetry data and video that was taken during a…

Blog post co-author and Timescale engineer here. Thanks for the advice. FWIW, though, TimescaleDB supports multi-dimensional partitioning, so a specific "hot" time interval is actually typically split across many chunks, and thus server instances. We are also working on native chunk replication, which allows serving copies of the same chunk out of different server instances. Apart from these things to mitigate the ho…

The link to join private beta isn't accessible, can you please look into it?

Re: Building a distributed time-series database on PostgreSQL

#82

Post by malisper »

ambicapter wrote: post #76
malisper wrote: post #43

Earlier quoted context omitted.

"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?

Referencing my copy of designing-data intensive applications[0], here are some approaches mentioned:

1) The naive approach is to assign all writes to a chunk randomly. This makes reads a lot more expensive as now a read for a particular key (e.g. device) will have to touch every chunk.

2) If you know a particular key is hot, you can spread writes for that particular key to random chunks. You need some extra bookeeping to keep track of which keys you are doing this for.

3) Splitting hot chunks into smaller chunks. You will wind up with varying sized chunks, but each chunk will now have a roughly equal write volume.

One more approach I would like to add is rate-limiting. If the reads or writes for a particular key crosses some threshold, you can drop any additional operations. Of course this is only fine if you are ok with having operations to hot keys often fail.

[0] https://www.amazon.com/Designing-Data-Intensive-Applications...

Re: Building a distributed time-series database on PostgreSQL

#83

Post by tartrate »

jnordwick wrote: post #30

Your time column in your hot chunck (e.g. current day/hour for trading) is going to basically bang on the time column for every query and partitioning isn't going to help much entire (probably hurt on writes) - other ts databases will part it out after period (e.g, end of day roll). how do you deal with this? also, while you can make columnar data, sql lacks a rich enough language to take advantage of it. your advanc…

I agree with your points. What alternatives do you suggest?

Re: Building a distributed time-series database on PostgreSQL

#84

Post by enordstr »

truth_seeker wrote: post #79

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?

It basically boils down to deleting a bunch of files on disk. The fact that it is distributed doesn't affect efficiency too much; it is basically a delete sent to all nodes, followed by a two-phase commit.

The upside of deleting entire tables (chunks) like this is that you don't pay the same PostgreSQL vacuuming cost normally associated with row-by-row deletes.

Re: Building a distributed time-series database on PostgreSQL

#85

Post by enordstr »

jadbox wrote: post #27

Does anyone have experience how this compares with citusdb (also postgres)?

We talk about sharding vs. chunking in the blog post and I would put CitusDB in the former category. More specifically, TimescaleDB is focusing on time-series workloads. To handle time-series workloads, CitusDB suggests combining their extension with a third-party extension (pg_partman) (see their docs).

I have no experience with this combination myself, so don't want to speculate about performance, etc., but when reading the docs it really seems like an afterthought.

Re: Building a distributed time-series database on PostgreSQL

#86

Post by keithnoizu »

theomega wrote: post #32

I can only recommend TimescaleDB. It solves the right problems (storing timeseries) while not creating new ones (deployment, backup, hot failover) as it relies on Postgres to provide the underlying infrastructure. I stored 100 million sensor samples in TimeScale and had not issues with scaling on medium sized boxes, despite issuing complex time-series queries. As for the hosting option, currently sadly AWS doesn’t of…

I use RiakTS although unfortunately with Basho's demise it's future is in a bit of jeopardy.

Re: Building a distributed time-series database on PostgreSQL

#87

Post by merb »

brootstrap wrote: post #35
theomega wrote: post #32

I can only recommend TimescaleDB. It solves the right problems (storing timeseries) while not creating new ones (deployment, backup, hot failover) as it relies on Postgres to provide the underlying infrastructure. I stored 100 million sensor samples in TimeScale and had not issues with scaling on medium sized boxes, despite issuing complex time-series queries. As for the hosting option, currently sadly AWS doesn’t of…

Interesting. My team currently uses (abuses?) postgres for timeseries data. You mind ansswering some general questions about your experience with timescale? You said 100 million sensor samples. What was the upload/download frequency? Our application is pushing hundreds of millions of rows across many different data sources every day. On top of that, we are also querying the shit out of this data to run models and we…

actually the last time I checked/used it timescaledb did use parallel queries and had a good partinoning so that the parallel queries would even scale really good on a single node.

Re: Building a distributed time-series database on PostgreSQL

#88

Post by truth_seeker »

enordstr wrote: post #84
truth_seeker wrote: post #79

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?

It basically boils down to deleting a bunch of files on disk. The fact that it is distributed doesn't affect efficiency too much; it is basically a delete sent to all nodes, followed by a two-phase commit. The upside of deleting entire tables (chunks) like this is that you don't pay the same PostgreSQL vacuuming cost normally associated with row-by-row deletes.

Thanks.

Re: Building a distributed time-series database on PostgreSQL

#89

Post by beagle3 »

mfreed wrote: post #74
beagle3 wrote: post #73

Earlier quoted context omitted.

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,…

Thanks. Interesting; I would be understanding if this was related to the “free” version, and the “bought and paid for” did not have such a restriction - but it might anyway.

I have used kdb in the past, and it is friendly in the Unix sense (picky about who it makes friends with) - first time users often write queries that use slow scalar loops.

Regardless, thanks; i’ll Be looking closely at timescaledb

Re: Building a distributed time-series database on PostgreSQL

#90

Post by enordstr »

mh- wrote: post #67
mfreed wrote: post #22

Earlier quoted context omitted.

Hi Peter, as the blog post talks about, our distributed hypertables typically partition by both time _and_ "space" (i.e., some other column like device id, etc.) as a way to better parallelize I/O (reads & writes) for the "current" time. That is, each time slice is typically spread across all nodes that existed when the time interval was opened. So this greatly ameliorates the interesting "time" problem you mention.…

I was disappointed to see that Adaptive Chunking is deprecated[1]. Are there future plans to ~replace this functionality? [1] https://docs.timescale.com/latest/api#set_adaptive_chunking

It would be great if you could share with us how this feature has been working out for you and how we can improve it in the future.
Post reply on HN