Building a distributed time-series database on PostgreSQL

Post by manigandham » Wed, Aug 21, 2019, 11:07 PM UTC

pvorb wrote: post #58

einpoklum wrote: post #55

> Building a distributed time-series database on PostgreSQL Next order of business: Making mud pies. PostgreSQL is geared towards transactional work. With time series, you basically just append data occasionally, and do analytics. PostgreSQL is terrible for analytics - its architecture is all wrong. 2 or 3 orders of magnitude slower than the state of the art if not more.

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.

Post by einpoklum » Wed, Aug 21, 2019, 11:19 PM UTC

akulkarni wrote: post #57

einpoklum wrote: post #55

> Building a distributed time-series database on PostgreSQL Next order of business: Making mud pies. PostgreSQL is geared towards transactional work. With time series, you basically just append data occasionally, and do analytics. PostgreSQL is terrible for analytics - its architecture is all wrong. 2 or 3 orders of magnitude slower than the state of the art if not more.

If you take a look at any of our benchmarks, you’ll see that this is not the case. PostgreSQL in fact can scale quite well for time-series analytics, if architected correctly. But why don’t you just try out TimescaleDB and see for yourself?

Post by hbcondo714 » Wed, Aug 21, 2019, 11:25 PM UTC

aeyes wrote: post #59

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

Probably not gonna happen as AWS wants to sell its own solution.

Post by mh- » Wed, Aug 21, 2019, 11:39 PM UTC

mfreed wrote: post #22

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

Post by akulkarni » Thu, Aug 22, 2019, 1:06 AM UTC

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