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

21–30 of 98 posts

Re: Building a distributed time-series database on PostgreSQL

#21

Post by elvinyung »

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…

Naive question: if time-series data is presumably immutable, shouldn't it be easy to just arbitrarily replicate chunks proportionate to load?

Re: Building a distributed time-series database on PostgreSQL

#22

Post by mfreed »

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…

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.

Now, if this time/space partitioning alone isn't sufficient (i.e., demand for a single device/userid/etc at a specific time overcomes the read capacity of K nodes), having time-series data being primarily insert heavy (or even immutable) also gives us a lot of flexibility about how we replicate (as a sibling comment also suggested). And what really helps is that, by design, the architecture we built tracks fine-grained chunk information (rather than just course-grained hash-partitions), which can enable dynamic replication of individual chunks. More on this to come.

Re: Building a distributed time-series database on PostgreSQL

#24

Post by dominotw »

TedLePoireau wrote: post #16

I'm currently using influxdb v1.x and I'm not very happy with it for many reasons (impossible to delete a value, no clustering in free version,...). Can anyone who migrated from influxdb to timescale share his opinion ?

There is no clustering in timescale either. One of the reasons I stopped exploring this option.

Re: Building a distributed time-series database on PostgreSQL

#25

Post by enordstr »

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 hot partition problem, it's usually a good thing to be able to serve the same data to many requests using a warm cache compared to having many random reads that thrashes the cache.

Re: Building a distributed time-series database on PostgreSQL

#26

Post by akulkarni »

ttsda wrote: post #23

Really nice that this is out, I've been following the github issues related to this for a long time and I think it might be time to start that meshed sensor network I was thinking of building with timescale

Glad to hear that :) Please let us know how we can help. We also have an active Slack community [1] if you want to chat with others who are storing sensor data in TimescaleDB.

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

Re: Building a distributed time-series database on PostgreSQL

#28

Post by techntoke »

hbcondo714 wrote: post #19

Still waiting for AWS RDS for PostgresSQL to support the TimescaleDB extension, 2 years and counting: https://github.com/timescale/timescaledb/issues/65

RDS is always behind when it comes to their extensions, especially for PostgreSQL.

Re: Building a distributed time-series database on PostgreSQL

#29

Post by malisper »

elvinyung wrote: post #21
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…

Naive question: if time-series data is presumably immutable, shouldn't it be easy to just arbitrarily replicate chunks proportionate to load?

There's a trade off here in that replicating data decreases the read load but increases the write load. If you have a chunk hot with writes, increasing the replication will make things worse, not better.

Re: Building a distributed time-series database on PostgreSQL

#30

Post by jnordwick »

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 advances queries seems like they aren't very good at exploiting the layout and you need to be specially written into the db (you cannot make your own high performance queries easily). I've never seen a decent LEAD/LAG query perform well, and they are too simplistic. I think you are fighting a losing war if you are trying to optimize sql down to good array based access.

A good tsdb isn't just changing the storage layer. Performance is also heavilty influenced by how queries are able to be expressed.

Post reply on HN