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...
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.
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 , 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.
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.
I actually looked at this benchmark briefly, but couldn't find what kind of PostgreSQL schema you used there.
Did you use similar array based schema as was described by Tgress author in his post? https://grisha.org/blog/2015/09/23/storing-time-series-in-po...
So it went from:
series1, array[val1, val2, val3 ...] --> time direction
series2, array[val1, val2, val3 ...]
slot1, array[series1_val1, series2_val1, ...] |
slot2, array[series1_val2, series2_val2, ...] |
^ time dir
Thus, if I have 10,000 series, and my arrays are 1000-long, I can insert a data point for each of the 10K series in only 10 row inserts. This only works if the data points for all series for the same slot arrive at approximately same time, which in a monitoring-like scenario they usually do, but in other situations might not be the case.
The flip side of this approach is that querying the data then becomes less efficient because 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.
Also, tgres takes the round-robin approach, versus the timed partition approach and that's completely apples and oranges when it comes to performance. The round-robin approach also works only if the data points are evenly spaced (or transformed to be evenly spaced on the fly, which is what tgres Go code does), and again, it's hard to judge whether that's fundamentally "good" or "bad".
I can see how readers of this thread my be looking for which technique is faster, but it's just not that simple, and very much depends on the what the actual requirements are. The round-robin versus timed partition is also not mutually exclusive, you can combine the two, which may or may not be faster, not sure, the devil is in the details.
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.
Overall like the conclusion though :)
tl;dr: 20x higher inserts at scale, faster queries, 2000x faster deletes, more time-oriented analytical features
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.
For event sourcing, just make sure you index on the proper user/session/thing (Docs or Slack for more info).
But because they don't want to keep track exactly which batches they've uploaded already (in a fault-tolerant way), they want to execute the insert to the cloud DB as an UPSERT.
So most of the time it'll actually just be inserting, but in the rarer case that the data has already been merged, the 'ON CONFLICT' side of things (in Postgres speak) can take over: DO NOTHING, DO UPDATE, etc.
As aside, turns out the constraints you'd need for upserts aren't supported by Postgres table inheritance (the typical way you do sharding), nor in PG 10 partitioning. But, we did add special support for this in our latest release :)
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-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.
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), while the repo allows you to replicate our results.
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?
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.
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.
(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.
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 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.)
I want to give this a go for sure!
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.
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)
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!
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.
Might be easiest to discuss more on Slack (https://slack-login.timescale.com/) or email (mike at timescale) if you're interested.
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:
The same approach should work for Telegraf, we just haven't yet tried to generalize this plugin.
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 Prometheus extension and adapter however look nice! They seem to be a good drop-in replacement for whatever storage, and the missing link for anything that's able to talk to Prometheus (which is quite a lot, including Telegraf).
This obviously saves significant space just by avoiding denormalization, ignoring the indexing overhead as well. You can see that in our Prometheus extension, btw:
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.
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.
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.
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.
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!
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?
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.
Timescale isn't currently supported by RDS/Aurora though, so it looks like more influx for me wooohooooo!
Edit: emailed them, hopefully I matter a teensy bit.
Will have to look at more.
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 :)
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.
At first blush KDB is orders of magnitude faster, especially if using a GZIP card.
But Timescale is open source and not core locked.
Are there actual benchmarks that show KDB being orders of magnitude faster than Timescale? How many orders of magnitude are we talking about?
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.
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).
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.
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.
I was never able to fix this.
Citus is a another good alternative and SQL Server and MemSQL also have in-memory and columnstores if you need the performance and scalability.
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.
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.
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.