Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Timescale, an open-source time-series SQL database for PostgreSQL

timescale.com

Post reply on HN
Print view View original

41–50 of 102 posts

Re: Timescale, an open-source time-series SQL database for PostgreSQL

#41

Post by buremba »

mfreed wrote: post #35
buremba wrote: post #34

Why do you usually advertise the write performance? Let's say that I have "100+ billion rows (the number in your landing page)", how much time it takes to run a simple GROUP BY query? The benchmark repo doesn't actually include the performance comparison between Timescale and Postgres: https://github.com/timescale/benchmark-postgres#benchmark-qu... This blog post ( https://blog.timescale.com/timescaledb-vs-6a69624810…

Write performance is a much simpler metric than query performance, which is HIGHLY dependent on the actual query being performed. Plus, in many time-series settings, you actually need to support high-write rates, which vanilla RDBMS tables can't support. On the query side, we find that most queries to a time-series DB actually include a time predicate, LIMIT clause, etc. It's pretty rare that you do a full table scan…

I mentioned about the benchmark repo because I wanted to learn why you usually advertise on the write performance instead of query performance. The benchmark repo shares the results for write performance but not query performance. Later on, I saw the benchmark for query part in your blog post, which was great.

I agree that full-table scan is not common in time-series use-case and you can't improve the performance in that case unless you use a different storage format. The confusing part for me is that if I have 100B rows, I would probably use a distributed (multi-node) solution unless the dataset includes 50 years of data and I want to query the last week because Postgresql is not good enough when aggregating huge amount of datasets.

Do you have any plan to release distributed version (the chunks may be distributed among the nodes in cluster) or implement columnar storage format?

Re: Timescale, an open-source time-series SQL database for PostgreSQL

#42

Post by mfreed »

buremba wrote: post #41
mfreed wrote: post #35

Earlier quoted context omitted.

Write performance is a much simpler metric than query performance, which is HIGHLY dependent on the actual query being performed. Plus, in many time-series settings, you actually need to support high-write rates, which vanilla RDBMS tables can't support. On the query side, we find that most queries to a time-series DB actually include a time predicate, LIMIT clause, etc. It's pretty rare that you do a full table scan…

I mentioned about the benchmark repo because I wanted to learn why you usually advertise on the write performance instead of query performance. The benchmark repo shares the results for write performance but not query performance. Later on, I saw the benchmark for query part in your blog post, which was great. I agree that full-table scan is not common in time-series use-case and you can't improve the performance in…

Yes, we're working on a distributed version of Timescale as you describe.

But two clarifications:

1. It can aggregate better than you might think. We've had people run single-node Timescale with 20+ disks, then couple that with query parallelization, and you can do pretty good aggregation over larger datasets.

Plus because the way the data is partitioned, a GROUPBY will actually get good localization over the disjoint data (i.e., groups can be local to a chunk) and generate more efficient plans given the smaller per-chunk indexes.

(And the various cloud platforms make it really easy to attach many disks to a single machine. Our our published benchmarking is on network-attached SSDs.)

2. You can use read-only clustering today, i.e., with standard Postgres synchronous or asynchronous replication. So you can scale your query rates with the replicas as well.

Re: Timescale, an open-source time-series SQL database for PostgreSQL

#43

Post by mfreed »

riku_iki wrote: post #40

How timescale fits postgres maintenance patterns(replication, backup)?

Basically just looks like a postgres database on the admin side.

Replication works (we aren't munging with the WAL), docs for backup/restore (http://docs.timescale.com/api#backup), and you can just use pgAdmin.

Re: Timescale, an open-source time-series SQL database for PostgreSQL

#44

Post by buremba »

mfreed wrote: post #42
buremba wrote: post #41

Earlier quoted context omitted.

I mentioned about the benchmark repo because I wanted to learn why you usually advertise on the write performance instead of query performance. The benchmark repo shares the results for write performance but not query performance. Later on, I saw the benchmark for query part in your blog post, which was great. I agree that full-table scan is not common in time-series use-case and you can't improve the performance in…

Yes, we're working on a distributed version of Timescale as you describe. But two clarifications: 1. It can aggregate better than you might think. We've had people run single-node Timescale with 20+ disks, then couple that with query parallelization, and you can do pretty good aggregation over larger datasets. Plus because the way the data is partitioned, a GROUPBY will actually get good localization over the disjoin…

Thanks for the clarification.

1. Do you use Postgresql 9.6 query parallelization (https://www.postgresql.org/docs/9.6/static/parallel-plans.ht...) or your own method for processing chunks parallelly? When we have >1B rows with >20 columns, the IO usually becomes a huge the bottleneck in our experience. If you use multiple disks and parallelize the work among different CPU cores, it would help I guess.

Re: Timescale, an open-source time-series SQL database for PostgreSQL

#46

Post by mfreed »

buremba wrote: post #44
mfreed wrote: post #42

Earlier quoted context omitted.

Yes, we're working on a distributed version of Timescale as you describe. But two clarifications: 1. It can aggregate better than you might think. We've had people run single-node Timescale with 20+ disks, then couple that with query parallelization, and you can do pretty good aggregation over larger datasets. Plus because the way the data is partitioned, a GROUPBY will actually get good localization over the disjoin…

Thanks for the clarification. 1. Do you use Postgresql 9.6 query parallelization ( https://www.postgresql.org/docs/9.6/static/parallel-plans.ht... ) or your own method for processing chunks parallelly? When we have >1B rows with >20 columns, the IO usually becomes a huge the bottleneck in our experience. If you use multiple disks and parallelize the work among different CPU cores, it would help I guess.

Currently support 9.6 query parallelization. Also considering extending with some of our own methods on chunks as well.

(Timescale supports multiple disks either through RAID or tablespaces. Unlike PG, you can add multiple tablespaces to a single hypertable.)

Happy to also go into more details on Slack (https://slack-login.timescale.com) or email.

Re: Timescale, an open-source time-series SQL database for PostgreSQL

#47

Post by mfreed »

anemic wrote: post #45

Can this be queried with Grafana or some other visualization tool?

If the viz tool can speak to Postgres, it works with Timescale. Tableau, Superset, SQLPad, Mode, Plot.ly, etc.

We internally use Grafana through a REST interface to a timescale backend. (And in fact, that's how we visualize the Prometheus data we store in Timescale: https://github.com/timescale/pg_prometheus )

But, Grafana Labs is still working on a native Postgres connector (MySQL released earlier this year). They promise us soon :)

Re: Timescale, an open-source time-series SQL database for PostgreSQL

#48

Post by joaodlf »

I've come to rely heavily on Cassandra, but I miss good old SQL and adhoc functionality. Systems like Cassandra bring orher requirements when you need flexible data (Spark, for example), technical debt is always a worry for me.

I want to give this a go for sure!

Re: Timescale, an open-source time-series SQL database for PostgreSQL

#49

Post by sureshv »

subleq wrote: post #38
Throaway786 wrote: post #24

We have a requirement of saving 100million data points every 5 mins. What options should we explore for real time system for last 15 days of data and archival system for last 3 years of data?

I don't have any experience with this type of thing, so that sounds like an incredibly large amount of data. What are you doing that requires it? What type of useful queries are you even able to perform over 432 billion records?

300k+ sensors sampling at 1 hertz would get you there.
Post reply on HN