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

91–98 of 98 posts

Re: Building a distributed time-series database on PostgreSQL

#91

Post by akulkarni »

xz0r wrote: post #81
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…

The link to join private beta isn't accessible, can you please look into it?

Sorry about that. We made a change that temporarily took it offline. Back now.

Re: Building a distributed time-series database on PostgreSQL

#92

Post by oddtodd »

einpoklum wrote: post #78
mfreed wrote: post #66

Earlier quoted context omitted.

Open-source Time-Series Benchmarking Suite: https://github.com/timescale/tsbs InfluxDB: https://blog.timescale.com/blog/what-is-high-cardinality-how... https://blog.timescale.com/blog/timescaledb-vs-influxdb-for-... Cassandra: https://blog.timescale.com/blog/time-series-data-cassandra-v... MongoDB: https://blog.timescale.com/blog/how-to-store-time-series-dat...

Yes, it's just like I thought. You're comparing against transaction-oriented DBMSes, or ones which handle documents rather than tabular data (and hence slow on tabular data). One possible exception is InfluxDB - I'm not familiar enough with it. Anyway, try running TSBS on columnar DBMSes like Actian VectorH, Vertica, SAP HANA etc. ClickHouse may also be relevant; they don't support any possible schema, but it may be…

At the end of 2018 Altinity benchmarked ClickHouse against the TSBS and documented it.

https://www.altinity.com/blog/clickhouse-for-time-series

Re: Building a distributed time-series database on PostgreSQL

#93

Post by softwarelimits »

einpoklum wrote: post #78
mfreed wrote: post #66

Earlier quoted context omitted.

Open-source Time-Series Benchmarking Suite: https://github.com/timescale/tsbs InfluxDB: https://blog.timescale.com/blog/what-is-high-cardinality-how... https://blog.timescale.com/blog/timescaledb-vs-influxdb-for-... Cassandra: https://blog.timescale.com/blog/time-series-data-cassandra-v... MongoDB: https://blog.timescale.com/blog/how-to-store-time-series-dat...

Yes, it's just like I thought. You're comparing against transaction-oriented DBMSes, or ones which handle documents rather than tabular data (and hence slow on tabular data). One possible exception is InfluxDB - I'm not familiar enough with it. Anyway, try running TSBS on columnar DBMSes like Actian VectorH, Vertica, SAP HANA etc. ClickHouse may also be relevant; they don't support any possible schema, but it may be…

Thank you very much for your valuable input - it is very important that people understand the differences and look into this!

Performance comparisons to the candidates you named would be very interesting to see.

Downvoters: you should be happy that people with more knowledge than the average javascript-aws-webdevops-guy that is needed to operate a startup invest time to inform you about alternatives you might not know about.

Also it is important to keep this site attractive to people that have a different opinions and experiences - do not do that trump thing! Thanks!

Of course, for each claim replicable facts are needed.

Re: Building a distributed time-series database on PostgreSQL

#95

Post by XuMiao »

einpoklum wrote: post #78
mfreed wrote: post #66

Earlier quoted context omitted.

Open-source Time-Series Benchmarking Suite: https://github.com/timescale/tsbs InfluxDB: https://blog.timescale.com/blog/what-is-high-cardinality-how... https://blog.timescale.com/blog/timescaledb-vs-influxdb-for-... Cassandra: https://blog.timescale.com/blog/time-series-data-cassandra-v... MongoDB: https://blog.timescale.com/blog/how-to-store-time-series-dat...

Yes, it's just like I thought. You're comparing against transaction-oriented DBMSes, or ones which handle documents rather than tabular data (and hence slow on tabular data). One possible exception is InfluxDB - I'm not familiar enough with it. Anyway, try running TSBS on columnar DBMSes like Actian VectorH, Vertica, SAP HANA etc. ClickHouse may also be relevant; they don't support any possible schema, but it may be…

I am curious about that too. As a separate topic, if the operational dbs can be compatible with parquet type storage (backup and restore), the offline analytics and machine learning would be seamlessly integrated together. Offline analytics usually can simplify online analytics. Discovering new dimensions, normalization/denormalization, and optimization of indices and partitions. Operational dbs shouldn't have to stress themselves at the gunpoint.

Re: Building a distributed time-series database on PostgreSQL

#96

Post by valyala »

brootstrap wrote: post #35
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…

TimescaleDB could fit your workload if PostgreSQL fits you. The main issue with PostgreSQL and TimescaleDB is big amounts of storage space required for huge time series data volumes. There are reports that storing data on ZSF can reduce the required storage space.

Probably, ClickHouse [1] would fit better your needs. It can write millions of rows per second [2]. It can scan billions of rows per second on a single node and it scales to multiple nodes.

Also I'd recommend taking a look at other open-source TSDBs with cluster support:

- M3DB [3]

- Cortex [4]

- VictoriaMetrics [5]

These TSDBs speak PromQL instead of SQL. PromQL is specially optimized query language for typical time series queries [6].

[1] https://clickhouse.yandex

[2] https://blog.cloudflare.com/http-analytics-for-6m-requests-p...

[3] https://www.m3db.io/

[4] https://github.com/cortexproject/cortex

[5] https://github.com/VictoriaMetrics/VictoriaMetrics/

[6] https://medium.com/@valyala/promql-tutorial-for-beginners-9a...

Re: Building a distributed time-series database on PostgreSQL

#97

Post by RobAtticus »

einpoklum wrote: post #78
mfreed wrote: post #66

Earlier quoted context omitted.

Open-source Time-Series Benchmarking Suite: https://github.com/timescale/tsbs InfluxDB: https://blog.timescale.com/blog/what-is-high-cardinality-how... https://blog.timescale.com/blog/timescaledb-vs-influxdb-for-... Cassandra: https://blog.timescale.com/blog/time-series-data-cassandra-v... MongoDB: https://blog.timescale.com/blog/how-to-store-time-series-dat...

Yes, it's just like I thought. You're comparing against transaction-oriented DBMSes, or ones which handle documents rather than tabular data (and hence slow on tabular data). One possible exception is InfluxDB - I'm not familiar enough with it. Anyway, try running TSBS on columnar DBMSes like Actian VectorH, Vertica, SAP HANA etc. ClickHouse may also be relevant; they don't support any possible schema, but it may be…

We're happy to take pull requests for new databases, we have so far from Clickhouse, CrateDB, and SiriDB (and one pending). We've tried to make it relatively easy for new databases to hook in.

We usually implement ones that we hear about a lot from customers, and so far those haven't come up a ton. We'll keep it in mind though as we look to keep adding new ones.

Re: Building a distributed time-series database on PostgreSQL

#98

Post by akulkarni »

valyala wrote: post #96
brootstrap wrote: post #35

Earlier quoted context omitted.

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…

TimescaleDB could fit your workload if PostgreSQL fits you. The main issue with PostgreSQL and TimescaleDB is big amounts of storage space required for huge time series data volumes. There are reports that storing data on ZSF can reduce the required storage space. Probably, ClickHouse [1] would fit better your needs. It can write millions of rows per second [2]. It can scan billions of rows per second on a single nod…

We are actively working on native compression in TimescaleDB, with really promising early results. More to come.
Post reply on HN