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

71–80 of 102 posts

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

#71

Post by manigandham »

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…

How's that any different than something that talks SQL? It's the most universal data language there is.

Why do you have to make new tables? It's 1 table with timestamp, name, value to store all your metrics and you can use an array or json column if you have extra non-structured data. Add in the SQL joins and analysis and you get a much better tool for timeseries.

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

#72

Post by trevman »

jnordwick wrote: post #64
RobAtticus wrote: post #15

Earlier quoted context omitted.

We are working on benchmarks comparing ourselves to other solutions so hopefully we'll have concrete numbers on those soon. (Incidentally, we did a blog post on us vs plain PostgreSQL today: https://blog.timescale.com/timescaledb-vs-6a696248104e ) At a high level though, we do find that having native support for full SQL to be a big win. Also, if you already store metadata or other relational data that you want to co…

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.

¯\_(ツ)_/¯

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

#73

Post by mfreed »

koffiezet wrote: post #70
mfreed wrote: post #67

Earlier quoted context omitted.

Well...sorta. Postgres/Timescale have pretty rich support for JSON these days (and its more efficient "binary" JSONB), so there are a whole range of options you can do that make it feel much more schema-less than before. In fact, last month we released a beta version of a Prometheus connector for Timescale/Postgres that allows you to store arbitrary Prometheus metrics without pre-defining all these varied schemas: ht…

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 performance against Influx, it really depends. We're working on releasing more complex benchmarks soon.

But overall, they have a performance edge if it's a single column scan that precisely matches their particular architecture (e.g., WHERE clause or GROUP BY by a very specific label set). But, we've found that Timescale actually gets higher query performance across a whole set of pretty natural queries (sometimes ridiculously so, as Influx doesn't index numerical values). Plus higher insert rates and full SQL. Not only is the latter point important for enabling many more types of complex queries, but it means that any viz or reporting tool that already speaks to Postgres should just work with us.

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

#74

Post by _joel »

fouadf wrote: post #69
sandGorgon wrote: post #66

Earlier quoted context omitted.

Postgres has jsonb - which allows you for full schema free operations. Postgres+jsonb is a very viable alternative to conventional nosql like mongodb.

Isn't mongodb still favored though for its scaling capabilities?

You can scale postgresql quite well with https://www.citusdata.com/ (other methods are available). The amount of horror I've seen with MongoDB over the years I'm surprised people still chose it (regardless of if it passes Jepsen tests now)

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

#75

Post by mfreed »

shady-lady wrote: post #62

What is the extra size on disk as a result of using this? I'm guessing there's some overhead?

There's not really any meaningful extra size for Timescale compared to standard Postgres.

I mean, there's a little extra information about each chunk (table name, its constraints, triggers, etc), and we cache this information in memory to speed up the query/insert side of things. But it's pretty common for these chunks to be on the order of 100s MB to GB, so this is just noise compared to the underlying data/indexing size.

So on the real storage size, the only potential difference is index size: say 50 indexes over 2GB data each vs 1 index over 100GB data? Haven't really looked into this for all different index types, but seems rather modest. Can try to dig up some more data.

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

#77

Post by mfreed »

mfreed wrote: post #75
shady-lady wrote: post #62

What is the extra size on disk as a result of using this? I'm guessing there's some overhead?

There's not really any meaningful extra size for Timescale compared to standard Postgres. I mean, there's a little extra information about each chunk (table name, its constraints, triggers, etc), and we cache this information in memory to speed up the query/insert side of things. But it's pretty common for these chunks to be on the order of 100s MB to GB, so this is just noise compared to the underlying data/indexing…

Looking at the tables we used for recent benchmarking comparing Postgres vs. Timescale (https://blog.timescale.com/timescaledb-vs-6a696248104e):

PG 100M rows: 30.98GB (1 table)

TS 100M rows: 30.93GB (1 hypertable, 6 chunks)

Same results for the 1B table, just 10x larger (and TS has 10x more chunks).

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

#78

Post by gtrubetskoy »

mfreed wrote: post #33
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...

To my understanding, Tgres is really more of a "middleware" layer that collects metrics and performs aggregations on them that are stored back into Postgres (e.g., generates aggregate rates for evenly spaced time intervals a la RRDTool), rather than being a scalable time-series DB itself. That's useful in many dashboard-based server monitoring applications, but time-series DB have many other applications (and can ben…

As the author if Tgres, I can chime in here - dr. mfreed is correct.

While Tgres is an application layer, the main motivation behind developing it was to answer the question "can TS be stored in Postgres efficiently, ideally without requiring an extension". Not that there is anything wrong with custom extensions, but I wanted to keep the requirements to the absolute minimum.

I always had issues with people saying that relational databases are fundamentally not suitable for TS storage, and Tgres debunks this by demonstrating that you can sustain very high rates of incoming data by simply organizing the data in a more creative way.

The graphite functionality that Tgres emulates is just there to prove the point - as in, look it does all these things, and it's all in the database.

Hypothetically Tgres could work on top of Timescaledb with a few changes, I just only have so much spare time to tinker with this experimental stuff that I haven't tried it.

Another interesting thing I came across is PgPointCloud [1], it's designed for LIDAR data but is perfectly suitable for time series as well. It is a C extension. It's performance advantage comes from storing large numbers of data points in a variety of compact/compressed formats.

[1] https://github.com/pgpointcloud/pointcloud

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

#79

Post by riku_iki »

gtrubetskoy wrote: post #78
mfreed wrote: post #33

Earlier quoted context omitted.

To my understanding, Tgres is really more of a "middleware" layer that collects metrics and performs aggregations on them that are stored back into Postgres (e.g., generates aggregate rates for evenly spaced time intervals a la RRDTool), rather than being a scalable time-series DB itself. That's useful in many dashboard-based server monitoring applications, but time-series DB have many other applications (and can ben…

As the author if Tgres, I can chime in here - dr. mfreed is correct. While Tgres is an application layer, the main motivation behind developing it was to answer the question "can TS be stored in Postgres efficiently, ideally without requiring an extension". Not that there is anything wrong with custom extensions, but I wanted to keep the requirements to the absolute minimum. I always had issues with people saying tha…

It is still not very clear at this point if Tgres approach is sufficient enough, and if timescale adds significant performance advantage over native array based Postgres implementation.

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

#80

Post by mfreed »

riku_iki wrote: post #79
gtrubetskoy wrote: post #78

Earlier quoted context omitted.

As the author if Tgres, I can chime in here - dr. mfreed is correct. While Tgres is an application layer, the main motivation behind developing it was to answer the question "can TS be stored in Postgres efficiently, ideally without requiring an extension". Not that there is anything wrong with custom extensions, but I wanted to keep the requirements to the absolute minimum. I always had issues with people saying tha…

It is still not very clear at this point if Tgres approach is sufficient enough, and if timescale adds significant performance advantage over native array based Postgres implementation.

I think the broader point is that Tgres is generally focused on computing regular aggregations. Once you do that aggregation, you lose significant information about the relation between data collected at the same time, which eliminates your ability to ask a variety of questions. For some basic dashboarding/monitoring applications you don't need this, for other applications you absolutely do.

So, it's pretty common in Timescale to store raw data in one hypertable (with a shorter data retention policy), and aggregations in a separate table (with a longer data retention).

Regarding native storage, the insert rate really goes down as your table gets large, e.g., Timescale gets 20x higher throughput than PG: https://blog.timescale.com/timescaledb-vs-6a696248104e

I don't see a reason this wouldn't apply to Tgres' use of native storage as well...but once you do aggregations (say, per minute), your tables are just much smaller (only 525K minutes / year), so it perhaps matters less.

Post reply on HN