12 ms·
Timescale, an open-source time-series SQL database for PostgreSQL
- Tostino 9y agoThis 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.
- mfreed 9y agoHi 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, which can also vary by hypertable (i.e., keep raw data for 1 month, aggregated data for 1 year). It also supports many disks per server, either via RAID or through Postgres' tablespace. But now you can have multiple tablespaces in a single "hypertable", rather than just one like normal. So especially in cloud settings, it's pretty easy to just add more and more disks to even a single machine.
- doublerebel 9y agoReally interesting to hear you use ZFS for those benefits. Are you using ZFS on Linux or a Solaris variant?
- RobAtticus 9y agoWe 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.
- craigkerstiens 9y agoGreat to see what you all are doing. Are there any plans to move timescale to be an extension as opposed to a fork? We've found ourselves at Citus that maintaining an extension lets us more easily stay up to date with current releases. Would love to see the same applied to timescale. Edit: Looks like it is already one, just was unclear in the docs on the setup steps to me. Well done all.
- noir-york 9y agoIt looks like it is indeed an extension: http://docs.timescale.com/getting-started/installation?OS=linux&method=yum%2Fdnf http://docs.timescale.com/getting-started/installation?OS=li...
- mfreed 9y agoHi Craig, Timescale person here. noir-york is correct. TimescaleDB was always an extension, never a fork. So all installations are just a `CREATE EXTENSION` and upgrades between versions just via `ALTER EXTENSION` commands. But you're absolutely right -- way better than doing a fork! (Say hello to Ozgun for us.)
- deleted 9y ago[deleted]
- freestockoption 9y agoAny support for RDS? :)
- mfreed 9y agoWe're talking with them...and you can help: https://github.com/timescale/timescaledb/issues/65 https://github.com/timescale/timescaledb/issues/65
- scrollaway 9y agoI submitted a request; my company's not huge but I hope it helps. Timescale looks like the most promising replacement to InfluxDB on the market. Influx has been a source of pain, data corruption and other various issues; what a world it would be if we could use timescale! The main blocker for us is Grafana support actually. I know Grafana is working on a Postgres connector; I am quite excited about this.
- pauldix 9y agoYou might have experienced data corruption in an old version of InfluxDB, but we haven't had any reports of that kind of thing in over two years. There are certainly still things to be improved, which we're doing all the time, but we take data integrity seriously.
- scrollaway 9y agoI shouldn't have said data corruption, I just meant time corruption: https://github.com/influxdata/influxdb/issues/8424 https://github.com/influxdata/influxdb/issues/8424 I was never able to fix this.
- mnutt 9y agoA project I work on has time series stats in postgres--it's essentially an interval, a period, a number of fields that make up the key, and the value. There's a compound index that includes most of the fields except for the value. It works surprisingly well, for tens of thousands of upserts per second on a single postgres instance. Easy app integration and joins are a huge plus. I'm really curious to check this out and see how it performs in comparison.
- mfreed 9y agoIn a funny bit of coincidence -- we didn't post this link to HN :) -- we just published a blog post today comparing Timescale vs. native Postgres: https://blog.timescale.com/timescaledb-vs-6a696248104e https://blog.timescale.com/timescaledb-vs-6a696248104e tl;dr: 20x higher inserts at scale, faster queries, 2000x faster deletes, more time-oriented analytical features
- mfreed 9y agoAnd actually, if you want to run the benchmarks yourself: https://github.com/timescale/benchmark-postgres https://github.com/timescale/benchmark-postgres
- qaq 9y agoOk and if one is partitioning by date and dropping partitions instead of deletes in vanilla postgres how does it compare ?
- mfreed 9y agoThe delete performance will probably be similar, but standard partitions in postgres have a bunch of current limitations. For example, the insert pipeline is still quite a bit slower, partition creation is still manual, can't do as good constraint exclusion at query time, can't do certain query optimizations we've built in, can't support user-defined triggers, can't handle UPSERTs, doesn't support various constraints, can't do VACUUMing across the hierarchy, etc. We plan to write a blog post comparing against PG10 partitioning in the future to expand on this a bit. All this said, we do love Postgres and realize that it's trying to provide a more general-purpose solution, so don't mean this as criticism. We can just build something more targeted at the time-series problem.
- MightySCollins 9y agoPlease stop tormenting me. This looks like exactly what we need (I was looking into manually partitioning the other day) it's just so annoying there is not yet Amazon RDS support.
- mfreed 9y agoCustomer requests help the process with RDS along :) https://github.com/timescale/timescaledb/issues/65 https://github.com/timescale/timescaledb/issues/65
- continuations 9y agoSo this is based on Postgresql. How does it compared to other solutions that are written from scratch to be a time series DB like influxDB?
- RobAtticus 9y agoWe 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 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 combine with your time-series data, it's great to be able to use one DB instead of separate solutions. Performance wise we do believe we are competitive and in some cases much better, and we have the 20 years of stability from PostgreSQL to build on.
- jnordwick 9y agoI look forward to a KDB comparison. Please dont forget them.
- trevman 9y agoMe 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. ¯\_(ツ)_/¯
- continuations 9y ago> 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?
- manigandham 9y agoAny 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.
- koffiezet 9y agoNot 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-less database engine.
- manigandham 9y agoHow'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.
- koffiezet 9y ago> How's that any different than something that talks SQL? It's the most universal data language there is. To use SQL you need to know the schema you're working against. Every single tool has to agree on a specific schema - and there are tons of existing tools that push infrastructure/system metrics into timeseries databases. For them it's simple, Influx uses some API, OpenTSDB uses another, Prometheus uses yet another - but they're all pretty simple to use. If you would point them to a Postgres database on the other hand, they wouldn't have a clue what fields to insert.
- jnordwick 9y agoTS 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.
- hof 9y agoHow would this work together with something like Stolon? https://github.com/sorintlab/stolon https://github.com/sorintlab/stolon
- mfreed 9y agoGenerally speaking, a TimescaleDB database just looks like Postgres to admin tools. So this looks interesting, and we are already internal Kubernetes users. Will have to look at more.
- daurnimator 9y agoCould 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-postgresql-efficiently/ https://grisha.org/blog/2015/09/23/storing-time-series-in-po... That blog post grew to be tgres http://github.com/tgres/tgres http://github.com/tgres/tgres https://grisha.org/blog/2017/03/22/tgres-0-dot-10-dot-0b-time-series-with-go-and-postgresql/ https://grisha.org/blog/2017/03/22/tgres-0-dot-10-dot-0b-tim...
- mfreed 9y agoTo 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 benefit from more complex queries even in monitoring). Tgres and Timescale are actually a bit complementary, and you might even be able to use Timescale as a better backend for Tgres.
- gtrubetskoy 9y agoAs 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 https://github.com/pgpointcloud/pointcloud
- Throaway786 9y agoWe 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?
- mfreed 9y agoWe've had users at that scale at least on the real-time side (e.g., 100M/5min, 500B rows), although requires some care. Might be easiest to discuss more on Slack (https://slack-login.timescale.com/ https://slack-login.timescale.com/) or email (mike at timescale) if you're interested.
- subleq 9y agoI 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?
- hotwagon 9y agoAt a higher level, is this the same concept as Datomic?
- vkrm 9y agoNot at all, although there are some similarities at a lower level (First class notion of time). Datomic has a much tighter focus on immutability, auditability, and database-as-a-value. Datomic isn't a good fit for use as a general purpose time series db where you have billions of observations.
- odammit 9y agoThis looks cool. I love things that get rid of extra dependencies. Influxdb is nice but then I have to support it, get stuff into it and get stuff out of it. Timescale isn't currently supported by RDS/Aurora though, so it looks like more influx for me wooohooooo!
- ericb 9y agoIs the business model to charge for the clustering release?
- enordstr 9y agoTimescaler here. Although we haven't done any official announcements w.r.t. clustering, our plan is for this to be open source, like the single-node version.
- pbowyer 9y agoLearning from the RethinkDB 'situation' [1] - do you have a viable business plan and are you monetising the database already? 1. https://news.ycombinator.com/item?id=12649414 https://news.ycombinator.com/item?id=12649414
- enordstr 9y agoWe wouldn't do this if we didn't believe we could make a business out of it. That said, we are at a pretty early stage at this moment and are looking at many different options. As you may know, and if you've been following the discussions around business models for open-source projects, there are many approaches as well as challenges. All I can say is that we have been following the discussions and we are drawing lessons from past failures and successes.
- buremba 9y agoWhy 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-queries-timescaledb-benchmark-query https://github.com/timescale/benchmark-postgres#benchmark-qu... This blog post (https://blog.timescale.com/timescaledb-vs-6a696248104e https://blog.timescale.com/timescaledb-vs-6a696248104e) has some query benchmarks and the main benefit it that the hypertable will partition the data smoothly and if we query the table by filtering with timestamp column, it will be fast since Timescale uses partitioning as an indexing method.
- mfreed 9y agoWrite 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 over the 100B rows. (And for these types of broad scans, performance depends on # disks and use of query parallelization.) Not sure I understand the comment about the benchmark repo doesn't include the performance comparison? That repo is meant to accompany a blog post, which discusses the results (https://blog.timescale.com/timescaledb-vs-6a696248104e https://blog.timescale.com/timescaledb-vs-6a696248104e), while the repo allows you to replicate our results.
- buremba 9y agoI 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?
- overcast 9y agoAlright, I'm excited to check this out. Been teetering on InfluxDB for a while, but not something I wanted to just introduce into corporate. Great work guys!
- riku_iki 9y agoHow timescale fits postgres maintenance patterns(replication, backup)?
- mfreed 9y agoBasically 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 http://docs.timescale.com/api#backup), and you can just use pgAdmin.
- anemic 9y agoCan this be queried with Grafana or some other visualization tool?
- mfreed 9y agoIf 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 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 :)
- joaodlf 9y agoI'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!
- elvinyung 9y agoHi, I'm curious what limitations you're running into with Spark SQL?
- joaodlf 9y agoNo 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 necessity, not love for them.
- elvinyung 9y agoAh, 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 protocol)
- joaodlf 9y agoSpark 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!
- _Codemonkeyism 9y agoWe have been using Postgres for a smaller event time series database (millions of rows) with good success. Tables are partitioned. Some user reports (aggregations) are ~5secs so we batch-pre generate them currently. Eeager to look into this to replace generation of reports with real time reports.
- dpen2016 9y agoWhy no redirect to https here?
- lurker456 9y agofurther evidence of how postgreSQL is eating noSQL. Every good concept first implemented in a custom noSQL solution eventually becomes an extension in postgres.
- egeozcan 9y agoI also do the same when developing: Proof of concept in NoSQL, then go wild with the schema, then watch it become a horrible mess, then refactor into SQL (That being pg or mssql depending on the needs/politics)
- shady-lady 9y agoWhat is the extra size on disk as a result of using this? I'm guessing there's some overhead?
- mfreed 9y agoThere'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.
- mfreed 9y agoLooking at the tables we used for recent benchmarking comparing Postgres vs. Timescale (https://blog.timescale.com/timescaledb-vs-6a696248104e 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).
- shady-lady 9y agoWow. impressive. would have thought there would have been more overhead.
- atemerev 9y agoWhoa, fantastic! I have managed to design a vanilla PostgreSQL solution, with partitions and BRIN indices, but there are too many hops to jump. I am excited to check if it will work out of the box. 100 billion rows per server sounds exciting!
- koffiezet 9y agoWhile nice, it suffers from the same problem storing timeseries in any sql database: you have to predefine your tables. For a fixed and known set of metrics, that's all fine, but if you look at the possible outputs of for example Telegraf, things become a bit more tricky to pre-create all tables/schemas...
- sandGorgon 9y agoPostgres has jsonb - which allows you for full schema free operations. Postgres+jsonb is a very viable alternative to conventional nosql like mongodb.
- fouadf 9y agoIsn't mongodb still favored though for its scaling capabilities?
- _joel 9y agoYou can scale postgresql quite well with https://www.citusdata.com/ 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)
- mfreed 9y agoWell...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: https://github.com/timescale/pg_prometheus https://github.com/timescale/pg_prometheus The same approach should work for Telegraf, we just haven't yet tried to generalize this plugin.
- koffiezet 9y ago
- gaius 9y agoHow does this compare to Vertica?
- orion138 9y agoGreat work! I was curious about a few things: 1) Are you planning on using citus for clustering? Or will you have your own clustering implementation separate from Citus? 2) Can you still use barman, wal-e, etc for backups? 3) What are you guys using to generate docs.timescale.com? :) 4) Do you use any sort of custom on disk format? 5) Do you plan on implementing any sort of delta compression? 6) Is there/do you plan to have support for creating roll up/aggregation tables? Cheers!
- RobAtticus 9y agoThanks, great questions! 1) We are currently exploring all options for clustering, though we are likely to try something on our own. No final decisions made yet though. 2) One of the next tutorials we'd like to do is how to setup using Timescale with wal-e for backups (we use this in a hosted service we have). Generally we should work with tools that work with PostgreSQL, we just want to make sure we cover all the caveats. 3) It's a custom solution we've built sort of organically that converts Markdown files (with some custom syntax) into HTML. :) 4) Currently we do not. 5) We have had high level talks about various ways to better compress data including delta compression, but nothing definitive yet. We do find just running on ZFS gives 3-4x compression, so that’s already a nice win if compression a priority. 6) This is definitely on our roadmap but again is also in the early stages. Thanks!