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

31–40 of 98 posts

Re: Building a distributed time-series database on PostgreSQL

#31

Post by manigandham »

mfreed wrote: post #15
manigandham wrote: post #9

Timescale has improved greatly since first released and is pretty solid on a single-node. Wish they would tone down the hype in the blog posts though, a shard/chunk/partition are all the same. How you define the splits is completely arbitrary and every database uses its own algorithm, including multiple levels.

Hey @manigandham thanks for the complements on database overall =) I understand conceptually that this is all about splitting data, but I think if you look at most scalable databases that use sharding, it’s really meant as a partitioning of primary keyspace over servers, and then you just globally map this sharding through client libraries, some transparent proxy, or some map that every node maintains, because O(map)…

Hey Michael,

I get it, you guys are using the primary keys for data => chunk and a second level for chunk => server/node. Other databases do this as well to abstract physical and logical partition placement.

Anyways, nice to see the SQL interface and AN/DN role implementation. Easier and more usable overall compared to some other solutions like Citus.

Re: Building a distributed time-series database on PostgreSQL

#32

Post by theomega »

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 offer Timescale as part of RDS. There are two options: Azure offers Timescale now as part of their hosted Postgres. Or you go with aiven.io who can host you postgres with TimeScaleDB on all cloud providers (AWS, GCP, Azure, DO, ?) as a service, including replicas and backups.

Overall, I’m very happy to see the Postgres ecosystem growing.

Re: Building a distributed time-series database on PostgreSQL

#33

Post by mfreed »

dominotw wrote: post #24
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.

@dominotw: See the parent article =)

Re: Building a distributed time-series database on PostgreSQL

#34

Post by mfreed »

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…

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

Re: Building a distributed time-series database on PostgreSQL

#35

Post by brootstrap »

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 need VERY quick queries. like 10-100ms speed.

How do you think timescaleDB would handle that size and also velocity of data?

Re: Building a distributed time-series database on PostgreSQL

#36

Post by laughinghan »

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…

DigitalOcean Managed Databases also comes with TimescaleDB built-in: https://www.digitalocean.com/docs/databases/postgresql/resou...

Re: Building a distributed time-series database on PostgreSQL

#37

Post by sroussey »

malisper wrote: post #29
elvinyung wrote: post #21

Earlier quoted context omitted.

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.

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

Re: Building a distributed time-series database on PostgreSQL

#38

Post by dima_vm »

We at VictoriaMetrics recognized importance of splitting up storage and query nodes as well. We went even further -- separated insert nodes from storage nodes. So for cluster version we have 3 types of nodes:

  * vminsert (stateless)
  * vmselect (stateless)
  * vmstorage (stateful)
However, we found out that PostgreSQL storage layer takes incredibly huge amount of space -- 28 bytes/metrics versus 0.4 b/m with VictoriaMetrics (70x difference!) for typical real-world data. That's why we didn't consider PostgreSQL for our storage layer, which otherwise could be awesome.

(see Disk Usage benchmark graph at [1])

That also hurts not only storage, but performance, as queries bottleneck becomes disk IO, check out this benchmark we conducted with TimescaleDB v1.2.2: [2]

Good job on going multi-node in v2! Can't wait to benchmark it with VM cluster version :)

[1] https://medium.com/@valyala/measuring-vertical-scalability-f...

[2] https://medium.com/@valyala/high-cardinality-tsdb-benchmarks...

Re: Building a distributed time-series database on PostgreSQL

#39

Post by jnordwick »

mfreed wrote: post #34
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…

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 hot device (eg, top 10 symbols). the parting doesn't help a lot there until you can spread the time around and rejoin (netezza used to do something similar and it wasn't very good at it). Do you ever rebalance the partitions? getting you top 10 symbols accidentally stuck on the same partition would be painful especially without a way to control it.

splaying the record column-wise helps in this, but i'm not sure if you are doing this.

Re: Building a distributed time-series database on PostgreSQL

#40

Post by akulkarni »

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…

If you are looking for hosted TimescaleDB, eg on AWS, you may want to check out Timescale Cloud [1], which we launched a couple months ago.

Fully-managed TimescaleDB, including community and enterprise capabilities, high-availability, etc, available on AWS, GCP, and Azure.

There are other options as well (Azure, DigitalOcean, Alibaba, Aiven) but they only offer the OSS version of TimescaleDB.

More here (including a pricing calculator): https://www.timescale.com/cloud

[1] https://blog.timescale.com/blog/timescale-cloud-first-fully-...

Post reply on HN