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

51–60 of 98 posts

Re: Building a distributed time-series database on PostgreSQL

#51

Post by mfreed »

benwilson-512 wrote: post #47
enordstr wrote: post #25

Earlier quoted context omitted.

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…

Hey Erik, thanks for the post. In this vision, would this cluster of servers be reserved exclusively for timeseries data, or do you imagine it containing other ordinary tables as well? We're using postgres presently for some IoT, B2B applications, and the timeseries tables are a half dozen orders of magnitude larger than the other tables in our application. Certain database operations, like updates, take a very long…

Hi @benwilson-512:

We commonly see hypertables (time-series tables) deployed alongside relational tables, often because there exists a relation between them: the relational metadata provides information about the user, sensor, server, security instrument that is referenced by id/name in the hypertable.

So joins between these time-series and relational tables are often common, and together these serve the applications one often builds on top of your data.

Now, TimescaleDB can be installed on a PG server that is also handling tables that have nothing to do with its workload, in which case one does get performance interference between the two workloads. We generally wouldn't recommend this for more production deployments, but the decision here is always a tradeoff between resource isolation and cost.

Re: Building a distributed time-series database on PostgreSQL

#52

Post by mfreed »

srazzaque wrote: post #48
cevian wrote: post #11

Earlier quoted context omitted.

[Timescaledb engineer here] We like to say that time-series data is any data that is insert-mostly with data associated with the most recent time period. That's a pretty broad definition, intentionally so. We see usage in telecoms, heavy industry, science, health, IoT, etc. It's really about recording the history of your data as it evolves, instead of just the current state.

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 report or information."

Re: Building a distributed time-series database on PostgreSQL

#53

Post by mfreed »

jnordwick wrote: post #39
mfreed wrote: post #34

Earlier quoted context omitted.

Hi @jnordwick: I talk about this in another response and in the parent article. Individual time intervals are also spread across the cluster. So if you are collecting data from a lot of, say, sensors, servers, or financial instruments, then reads/writes for the same time interval are then parallelized across servers. https://blog.timescale.com/blog/building-a-distributed-time-...

I saw, my understanding is that you basically have a sort on device, then time: this helps some for individual queries devices to some extend (but prob hurts when you are inserts at 500 places instead of 1 or when you have queries that span too many instruments). Point wast (and the others's i think) was the you often have as very hot segment and yesterday's data is only used at night for example. and you can have a…

It's not hierarchical as you describe. You don't first partition on device, then on time. They are done simultaneously -- see this older post for an illustration/comparison: https://blog.timescale.com/blog/time-series-data-postgresql-...

So this architecture fully allows various striping or distribution options across time and space, even though the default might collocate chunks belonging to the same device on the same machine (at least since the last elasticity event).

Re: Building a distributed time-series database on PostgreSQL

#54

Post by 120bits »

I just came here to tell how happy I am with Timescaledb.

I have almost 8 tables with over 60Million rows and I'm very happy with the performance. Considering I have a t2.medium instance(2 CPUs with 4GB RAM). Like, everyone else mentioned, having AWS hosted option will be awesome!

Is there a way to optimize storage? I have set chunk size to 1 day interval . About 2 million rows per day writes.

My many thanks to the engineering team.

Re: Building a distributed time-series database on PostgreSQL

#55

Post by einpoklum »

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

Re: Building a distributed time-series database on PostgreSQL

#56

Post by einpoklum »

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?

They probably don't care, otherwise they wouldn't have gone with PostgreSQL in the first place.

Re: Building a distributed time-series database on PostgreSQL

#57

Post by akulkarni »

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?

Re: Building a distributed time-series database on PostgreSQL

#58

Post by pvorb »

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 reply on HN