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

91–100 of 102 posts

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

#91

Post by mfreed »

rohannair wrote: post #88
daurnimator wrote: post #23

Could you contrast this with the approaches mentioned in the series of blog posts starting here: https://grisha.org/blog/2015/09/23/storing-time-series-in-po... That blog post grew to be tgres http://github.com/tgres/tgres https://grisha.org/blog/2017/03/22/tgres-0-dot-10-dot-0b-tim...

The guy wrote something about TimescaleDB as well: https://grisha.org/blog/2017/07/13/timescaledb/

There are some confusions in that post -- perhaps understandable, as trying to deduce things from code review alone is tricky -- but Grisha and I have been communicating and hopefully it'll be updated.

Overall like the conclusion though :)

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

#92

Post by joaodlf »

elvinyung wrote: post #60
joaodlf wrote: post #55

Earlier quoted context omitted.

No limitations. Spark works and does a good job, it has many features that I can see us use in the future too. With that said, it's yet another piece of tech that bloats our stack. I would love to reduce our tech debt: We are much more familiar with relational databases like MySQL and Postgres, but we fear they won't answer the analytics problems we have, hence Cassandra and Spark. We use these technologies out of ne…

Ah, I see -- so a thing that I'm curious about is, what do you miss about relational databases? Are they mainly aspects on the operational side, or the usability/API side? Ultimately, the question that I'm interested in trying to answer is: would it help if there were more ways to make Spark feel like a traditional relational database? (e.g. being able to interact with the Spark driver using MySQL or Postgres wire pr…

Spark already does a good job at that, imo. It's increasingly easy to query information, at this point we are basically writing SQL-like queries with it. BUT, Spark isn't a relational db, or even a storage solution. What I miss is just having the one piece of technology that deals with both storing and querying: Actual relational databases.

It's interesting. 10 years ago I would have probably said something like that "relational dbs will just get better as data grows", quite the opposite happened... Relational has been pushed to the side and we now have to learn a lot of new technologies, in my case: Cassandra; Spark; Pandas (python). This whole stack used to be just MySQL :)... And I miss those days!

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

#93

Post by gtrubetskoy »

riku_iki wrote: post #83
gtrubetskoy wrote: post #81

Earlier quoted context omitted.

The not so obvious difference in the Tgres approach, and my blogs might not be doing a great job of explaining it, is that some time in Feb 2017 I significantly revamped the storage approach to what I dubbed "vertical" storage whereby a timeslot stores an array of points in which every array element represents an element of a(nother) series. So it went from: series1, array[val1, val2, val3 ...] --> time direction ser…

> to read one data point of a series you end up reading an array-length of data points you might not care about for this particular query. But DBs and FSs operate on pages of data and not individual records, so you will be reading that row anyway, and likely much more.

> But DBs and FSs operate on pages of data and not individual records, so you will be reading that row anyway, and likely much more.

Yes, when dealing with database performance understanding this goes with the territory.

The "game" here is to organize data in such way that the stuff you read inadvertently is something that you will need eventually (as in in a few microseconds). This is where things like CLUSTER and BRIN indexes become important, and this is also why partitioning is a win.

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

#94

Post by jnordwick »

koffiezet wrote: post #68
manigandham wrote: post #16

Any SQL database can do time-series well with more functionality then the specialized stuff like influxdb which doesn't really have much reason to exist at this point. Citus is a another good alternative and SQL Server and MemSQL also have in-memory and columnstores if you need the performance and scalability.

Not really true. I point whatever thing that talks something like influx to it with the right credentials and it outputs whatever metrics it wants to it. No need to manage/pre-create all your tables for every single possible metric out-there. I seriously dislike nosql databases for most purposes, and am absolutely a Postgres fan - but timeseries is the only thing I've encountered that benefits from a dedicated schema…

TS are heavily schema oriented unless you are talking about logs and then you aren't really talking about the classical notion of a TS database where aggregates are important. There isn't a NoSQL database that does them well.

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

#95

Post by continuations »

trevman wrote: post #72
jnordwick wrote: post #64

Earlier quoted context omitted.

I look forward to a KDB comparison. Please dont forget them.

Me too. At first blush KDB is orders of magnitude faster, especially if using a GZIP card. But Timescale is open source and not core locked. ¯\_(ツ)_/¯

> At first blush KDB is orders of magnitude faster

Are there actual benchmarks that show KDB being orders of magnitude faster than Timescale? How many orders of magnitude are we talking about?

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

#96

Post by elvinyung »

joaodlf wrote: post #92
elvinyung wrote: post #60

Earlier quoted context omitted.

Ah, I see -- so a thing that I'm curious about is, what do you miss about relational databases? Are they mainly aspects on the operational side, or the usability/API side? Ultimately, the question that I'm interested in trying to answer is: would it help if there were more ways to make Spark feel like a traditional relational database? (e.g. being able to interact with the Spark driver using MySQL or Postgres wire pr…

Spark already does a good job at that, imo. It's increasingly easy to query information, at this point we are basically writing SQL-like queries with it. BUT, Spark isn't a relational db, or even a storage solution. What I miss is just having the one piece of technology that deals with both storing and querying: Actual relational databases. It's interesting. 10 years ago I would have probably said something like that…

Ah, yeah. I have so many mixed thoughts on this. I also think that the open source world copying Google's super-decoupled GFS-Bigtable-MapReduce-Dremel-etc... architecture was really not great for operational complexity. Few teams can operate like Google, and maintain so many moving parts in production all at the same time.

At the same time, of course there are some very good points to be made for this sort of storage agnosticism -- mainly from an efficiency standpoint (i.e. being able to choose the storage format for the occasion). I'm really not quite sure if this argument is strong enough for completely sacrificing the simplicity of a traditional database.

Sometimes I think that MPP engines like Spark should take the philosophy of "batteries included but replaceable" -- that is, basically serving as an all-in-one "database" that provides a default storage engine (e.g. a basic columnstore and a basic rowstore), but still letting the user plug in other data sources to join, only if they want.

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

#97

Post by doublerebel »

mfreed wrote: post #7
Tostino wrote: post #2

This is something i've been meaning to look into for a personal project that has a lot of time series data. It'll be interesting to see what they eventually come up with to make time series data not take quite as much space.

Hi Tostino, please let us know how things work. Regarding compression: While we haven't yet built in any native compression, we regularly run on ZFS and typically get 3-4x compression using that. (Plus with ZFS, insert rates are actually a bit faster, at least when using a single disk (often 25%). It's definitely something to consider.) Another thing to consider is that Timescale supports easy data retention policies…

Really interesting to hear you use ZFS for those benefits. Are you using ZFS on Linux or a Solaris variant?

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

#98

Post by RobAtticus »

doublerebel wrote: post #97
mfreed wrote: post #7

Earlier quoted context omitted.

Hi Tostino, please let us know how things work. Regarding compression: While we haven't yet built in any native compression, we regularly run on ZFS and typically get 3-4x compression using that. (Plus with ZFS, insert rates are actually a bit faster, at least when using a single disk (often 25%). It's definitely something to consider.) Another thing to consider is that Timescale supports easy data retention policies…

Really interesting to hear you use ZFS for those benefits. Are you using ZFS on Linux or a Solaris variant?

We used it on an Ubuntu machine on Azure. We didn't do any real tuning yet, it was just an initial test to ballpark the benefit.

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

#99

Post by koffiezet »

mfreed wrote: post #73
koffiezet wrote: post #70

Earlier quoted context omitted.

Well, JSONB has a performance or afaik a pretty significant disk usage impact if you use GIN. It's pretty nice stuff, but I'm not sure about using it for timeseries. I haven't seen any benchmarks though, this could be useful. One advantage specialized db's like influx have is specialized/optimized storage layers for the type of data while timescale seems to use normal postgres tables behind the scenes. That Prometheu…

A common approach we find for storing devops-related time-series data (as you'd find with influx), is to not denormalize the "labels" or "tag set" into the main metrics table. This obviously saves significant space just by avoiding denormalization, ignoring the indexing overhead as well. You can see that in our Prometheus extension, btw: https://github.com/timescale/pg_prometheus#user-content-norm... Regarding perfor…

> But overall, they have a performance edge if it's a single column scan that precisely matches their particular architecture

To be honest if they didn't - stuff like Influx wouldn't really have a reason to exist. I think 99.9% of the operations in a devops environment will be pretty simple and predictable, it's a a trade-off they make. Queries are mostly there to draw graphs quickly.

I quite like the simplicity of Influx, and while our current use is pretty limited and far from hitting it's limits, for reliability and maintenance reasons I'd prefer Postgres, for which we have quite extensive in-house know-how and tools in place. Prometheus is one of the tools we are considering to add to our monitoring stack, and having the option of storing it's data transparently in Postgres could be very interesting.

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

#100

Post by d21d3q »

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?

What do you do with that data? Just store or do some analysis on it (how? With what kind of resources?)?
Post reply on HN