Building a distributed time-series database on PostgreSQL • Hacker News Board

Re: Building a distributed time-series database on PostgreSQL

#41

Post by GordonS » Wed, Aug 21, 2019, 10:02 PM UTC

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 was going to post a very similar comment :)

For on-prem deployments I'm using TimescaleDB on a single node with up to 100 million events, and for SaaS I'm using it on Azure, and the performance is little short of amazing!

Something I particularly like is the almost instantaneous deletes (because of the "chunking" model) - perfect for data retention jobs that delete old data.

Re: Building a distributed time-series database on PostgreSQL

#42

Post by stalller » Wed, Aug 21, 2019, 10:03 PM UTC

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…

Thanks for the recommendation! And glad to hear TimescaleDB meets your needs. I wanted to add some color to hosting / deployment options around TimescaleDB.

Options are as follows:

[1] Fully-Managed TimescaleDB Enterprise on Timescale Cloud

TimescaleDB Open Source hosted on Public Clouds:

[2] Azure PostgreSQL

[3] DO Managed-Postgres (mentioned above)

[4] Alibaba Cloud

Not hosted, but managed service options:

[5] ClusterControl from our friends at Severalnines

---

[1]https://www.timescale.com/cloud [2]https://azure.microsoft.com/en-us/blog/power-iot-and-time-se... [3]https://www.digitalocean.com/docs/databases/postgresql/resou... [4]https://www.alibabacloud.com/blog/sql-and-timescaledb_595169 [5]https://severalnines.com/blog/advanced-database-monitoring-m...

Re: Building a distributed time-series database on PostgreSQL

#43

Post by malisper » Wed, Aug 21, 2019, 10:03 PM UTC

sroussey wrote: post #37

malisper wrote: post #29

Earlier quoted context omitted.

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?

"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 a chunk that's hot because a lot of writes are going to it, increasing replication will make things worse as you are doing just as much work per replica as you were before, but you're now doing it on more replicas.

Does that make sense?

Re: Building a distributed time-series database on PostgreSQL

#44

Post by notinventedhear » Wed, Aug 21, 2019, 10:04 PM UTC

TimescaleDB looks really very promising but this is a red flag:

"Hypertables support all standard PostgreSQL constraint types, with the exception of foreign key constraints on other tables that reference values in a hypertable"[1]

Naively I'd assume this could cause a two-colouring of your schema - the partition that can use referential integrity and another with hypertables that doesn't which feels like a pretty big trade-off.

[1] https://docs.timescale.com/latest/using-timescaledb/schema-m...

Re: Building a distributed time-series database on PostgreSQL

#45

Post by elvinyung » Wed, Aug 21, 2019, 10:05 PM UTC

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.

Right, but I'm assuming that with immutable data writes aren't a problem?

Re: Building a distributed time-series database on PostgreSQL

#46

Post by mfreed » Wed, Aug 21, 2019, 10:08 PM UTC

dima_vm wrote: post #38

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

Hi @dima_vm, we've found that users have really embraced the full SQL and reliability you get from TimescaleDB's approach leveraging PostgreSQL. But we're aware that its standard on-disk format can be more space intensive than others (although many do deploy with ZFS to trade-off some CPU for I/O).

Recognizing this, the engineering team has been hard at work bringing native compression to TimescaleDB, which is also in private beta right now.

Huge wins, but more details & performance numbers in a future blog post =)

Re: Building a distributed time-series database on PostgreSQL

#47

Post by benwilson-512 » Wed, Aug 21, 2019, 10:09 PM UTC

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…

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 time because of this. I've wondered if by splitting the timeseries tables onto their own server I could handle updates independently, with the main app gracefully handling the timeseries DB being offline for some period of time.

It's more than just about downtime though. If through poor querying or other issues the timeseries db is overloaded the customer impact of the slow down would be limited.

Re: Building a distributed time-series database on PostgreSQL

#48

Post by srazzaque » Wed, Aug 21, 2019, 10:09 PM UTC

cevian wrote: post #11

simplify wrote: post #3

What are some use cases for a time-series database?

[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+?

Re: Building a distributed time-series database on PostgreSQL

#49

Post by malisper » Wed, Aug 21, 2019, 10:11 PM UTC

elvinyung wrote: post #45

malisper wrote: post #29

Earlier quoted context omitted.

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.

Right, but I'm assuming that with immutable data writes aren't a problem?

It depends on if by "immutable" you mean the only operation you are performing on the dataset are reads.

Writes is a catch-all term usually used to describe either updates or inserts. If you are inserting new data and a single chunk is hot because a you are inserting a lot of data into it, then replicating won't help. You can imagine a scenario like a single device is going haywire and starts sending you a ton of data points.

If you are only performing reads on your dataset, then replicating will only improve performance.

Re: Building a distributed time-series database on PostgreSQL

#50

Post by cevian » Wed, Aug 21, 2019, 10:13 PM UTC

notinventedhear wrote: post #44

TimescaleDB looks really very promising but this is a red flag: "Hypertables support all standard PostgreSQL constraint types, with the exception of foreign key constraints on other tables that reference values in a hypertable"[1] Naively I'd assume this could cause a two-colouring of your schema - the partition that can use referential integrity and another with hypertables that doesn't which feels like a pretty big…

In practice this doesn't come up a lot. Say you have a hypertable with measurement(time, device_id, value) and a device table with (device_id, device_manufacturer, device_type). Timescale fully support a foreign-key from the measurement table into the devices table. This is a common usage. A FK from another table which references a measurement row is not supported, but is also uncommon. To see why note that a part of the primary-key of the measurement table is time and so conceptually the only type of table that would want a FK into it is also a time-based table, and so the only real usage is a 1-to-1 relation. That is also uncommon and can be gotten-around with normalization.