cevian

I have to say that a sentiment similar to this is why we built Timescale as a Postgres extension and not started a new database from scratch. We didn't want a new shiny thing just for kicks but rather a product focused on solving a particular problem.

One more thing: If this is the kind of thing that excites you, please come join us: http://www.timescale.com/careers. We have open positions in engineering (core database, R&D, customer success and Frontend) as well as content marketing, evangelism, recruitment, and partnership.

Clickhouse is a very cool project but is more analytical than OLTP. Percona had a nice writeup[1] (the comparison to mysql is especially apt -- note the lack of real-time updates and deletes in Clickhouse).

[1] https://www.percona.com/blog/2017/02/13/clickhouse-new-opens...

A lot of time-series data has complex relations and cross-correlations that make it unsuitable for column stores. Take for example a device that reports temperature and humidity. You may want to enforce foreign key relations between devices and readings, or you may want to do analysis for device in a particular location (which may be part of readings or devices table) secondary indexes help here. Or you may want to easy access to all data where temperature is above 90 degrees (partial indexes help here). You often want this in a real-time dashboard where the kind of latencies you see with MPP are problematic. Pretty much the columnar/MPP stuff is good for simple aggregates for ad-hoc non-real-time queries but TimescaleDB is geared to more complex analysis to power dashboards.

Plus, when you know which particular aggregates you want to regularly query, TimescaleDB makes it easily to perform continuous roll-ups so you can store in a separate (hyper)table.

Timescale | New York, NY | Stockholm, Sweden | ONSITE | FULL-TIME

Time-series data is everywhere, and the powerful time-series database we are developing (TimescaleDB) is a key building block across a range of industries: IoT, DevOps, monitoring, eventing, finance, industrial, logistics, transportation, and via the edge, on premise, and cloud deployments. It powers users' core products.

We need extraordinary engineers and customer success professionals to join our team in NYC and Stockholm.

- Core database engineers

- R&D engineers

- Support engineers

- Customer success manager

- Office/marketing manager

Check out our careers page for openings: http://www.timescale.com/careers

TimescaleDB is actually a lot better for backfilled/out-of-order data then a lot of NoSQL options. This is because in TimescaleDB data is fundamentally organized by data time instead of insert time (as it is for LSM trees, for instance).

Hey,

Not sure if this is part of the question but if you are querying the event data by something other than time (like a property of the event itself -- maybe the program return code in your example) than prometheus requires a full table scan since it does not have secondary indexes. With TimescaleDB you could add indexes on properties of the event data itself.

(TimescaleDB developer here)

Yeah, so on-disk compression is one area where we aren't as competitive with NoSQL column stores.

However, two things to note:

1) Often many of those column-oriented DBs, based on LSM trees, actually need to consume a lot more memory to index all of their disjoint SSTables. So it's a tradeoff of memory vs. disk.

2) There are various things we have on our TODO to test, like just running Postgres on ZFS. We'll write up the results when we do.

Vacuuming has traditionally been a problem with large table sizes. In TimescaleDB we break up the tables so that they are smaller. That, combined with the new freeze map feature in Postgres (since PG 9.6: http://rhaas.blogspot.jp/2016/03/no-more-full-table-vacuums....) make vacuums not an issue for us. Certainly we've never seen this issue (even on default autovacuum settings) and we've tested some huge datasets.

Ah, sounds like you are again thinking about bitemporal reasoning over relational data.

We are not really built for that use case. Instead of focusing on fundamentally relational data such as people or jobs (which you are free to store alongside our stuff, using plain postgres functionality), we store event or metrics data. This data can be easily joined against the relational data (e.g., using a person_id stored in both event and relational tables).

I think these are good points about cstore_fdw and real-time analysis (although we don't have personal experience with this).

The usual thing that prevents indexes from scaling with large data is that inserts slow down as soon as the index BTrees can't fit into memory. TimescaleDB gets around this problem by auto-sizing of tables: we start new tables as old tables (and their indexes) grow so large that they can no longer fit in memory. This allows us to have tables that are not so big that inserts become slow but big enough that queries don't have to touch many tables (and are thus efficient).

However, as data sizes grow, you may want to convert data to column-stores to save disk-space though, as you allude to. We are looking at how best to do this and the best "archive" format to use. Stay tuned.

We actually really like (and use) Postgres inheritance. Our extensions add functionality specifically geared for time-series workloads. Namely, we add:

- Automatic table-sizing. This includes dynamically creating new tables for new data, closing old tables as soon as they get too big, etc. (more challenging than meets the eye because of deadlocking issues, etc.).

- Related to above, optimized inserts to correct chunks. In Postgres 9.6, this is slow using rules/triggers. In upcoming Postgres 10 the situation is supposed to be better (waiting for finalization of 10 to test). But in Postgres 10, auto-closing and table creation are still not supported, and implementing them would probably require the slow path. In contrast, Our inserts are very fast.

- Hash-partitioning query pruning according to partition key

- (Time based pruning supported also, but that's standard Postgres)

- Time-based query optimizations. For example, we enable the planner to group by time-intervals using indexes on time-columns. Plain postgres can't support that because it does not know that truncating/rounding time is order-preserving. Other optimizations in active development.

- Our upcoming clustered version will include distributed query optimizations. In addition our scale-out design takes advantage of many workload features unique to time-series (see white-paper for more details)

This is a great usage idea and something that should be fairly easy once we support close and update triggers (on our list of todos)

One more thing to add: Postgres index usage via index-only-scans go a long way to mitigating performance issues of wide rows (although, admittedly not disk-space issues). This allows good performance on columnar rollups.

Yes we distribute the child tables among the cluster. Our default distribution mode uses "sticky" partitioning where a partition prefers to stay on the same node. This allows you to control data colocation via the partition key. Our clustered solution is not released yet but we plan to handle node failures via regular postgres STONIH mechanisms. Once node failure is detected, the system reconfigure which children to use.

Query parallelization works in single and multi-node cases.

Hi elvinyung, we have three cases (1) chunks from the same hypertable are effectively UNIONized (not JOIN) when collecting results. (2) If JOINING 2 hypertables we have an upcoming feature of allowing the user to specify two hypertables as colocated, which will put chunks for same time/space partition on same node. Otherwise we will transfer the smaller of the chunks across the network (pretty much the usual join logic. (3) For joining a hypertable with a regular table, the regular tables is fetched if it does not exists locally. We will have an option to materialize regular tables across nodes (great if the table is small and read-only).