5 ms·
A 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
by mnutt 9y ago
A 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.
- qaq 9y agoThank you for the explanation looks like you have dedicated a good bit of effort to making a good ts solution, will be testing it out :)
- mamcx 9y agoThis would be good for a eventsourcing storage? Fast events insert and fast reads?
- mfreed 9y agoYep, it can be used for either "irregular" events or "regular" time-series like monitoring data. For event sourcing, just make sure you index on the proper user/session/thing (Docs or Slack for more info).
- javajosh 9y agoCurious about your comment about "upserts" WRT time series data. When and why would you update a record in such a table?
- mfreed 9y agoNot sure about parent's use case, but we've seen scenarios where users want to synchronize data from downstream (say, they are collecting data on a hub in an IoT setting, and even using Timescale both on the hub and in the cloud). 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 :)