cevian

(TimescaleDB engineer here). This is a really unfair critique.

The project you cite is super-optimized for the Prometheus use-case and data model. TimescaleDB beats InfluxDB on performance even without these optimization. It's also not possible to optimize in this way in most other time-series databases.

These scripts also work hard to give users a UIUX experience that mimicks PromQL in a lot of ways. This isn't necessary for most projects and is a very specialized use-case. That takes up a lot of the 2000 lines you are talking about.

(Timescale-Prometheus team lead here) I'd suggest using Timescale-Prometheus to connect Prometheus with TimescaleDB.

Repo: https://github.com/timescale/timescale-prometheus Design Doc: https://tsdb.co/prom-design-doc

To be clear, TimescaleDB does NOT throw away and indexing capabilities or ACID features. Its only limitation is that UNIQUE constraints need to include the time column. Also, its query optimizations are far from trivial to implement.

Just as a point of clarification: timescale materialized views don't allow joins during materialization. But you can join materialized views arbitrarily at query time. (TimescaleDB engineer here)

Another thing to mention is that TimescaleDB has much stronger ACID guarantees than ClickHouse. Which means you get more clear semantics for consistency

The article covers this in more depth, but the short answer is: The hybrid approach has benefits when your workload has both shallow-and-wide (fetch all data for user X) and deep-and-narrow queries (calculate the average number of logins for all users). Row stores excel on shallow queries, column stores excel on deep queries. For time-series data you want row-oriented structures when the data just comes in and convert it to a column store after it ages.

Hey Timescale engineer here. Happy to answer any questions.

In practice this doesn't come up a lot. Say you have a hypertable with measurement(time, device_id, value) and a device table with (device_id, device_manufacturer, device_type). Timescale fully support a foreign-key from the measurement table into the devices table. This is a common usage. A FK from another table which references a measurement row is not supported, but is also uncommon. To see why note that a part of the primary-key of the measurement table is time and so conceptually the only type of table that would want a FK into it is also a time-based table, and so the only real usage is a 1-to-1 relation. That is also uncommon and can be gotten-around with normalization.

Well as far as I understand, temporal tables usually have either a valid time or system time. TimescaleDB is geared towards something you'd call measurement time, and most modifications are inserts to recent measurement time. In contrast, temporal tables are often still update-heavy and there is often no correlation with recent time, especially for valid time fields.

[Timescaledb engineer here] We like to say that time-series data is any data that is insert-mostly with data associated with the most recent time period. That's a pretty broad definition, intentionally so. We see usage in telecoms, heavy industry, science, health, IoT, etc. It's really about recording the history of your data as it evolves, instead of just the current state.

We have a tool to migrate from Influx. https://docs.timescale.com/v1.3/tutorials/outflux

Clarification: this license is only for Community and Enterprise Features that live in the `tsl` subdirectory. The vast majority of our code (everything not under `tsl`) is licensed under Apache 2.

(TimescaleDB engineer here) There are major feature and capabilities available in TimescaleDB that are not available in pg_partman.

On the query side we implement a whole bunch of planner and execution time optimizations that don't come with plain PostgreSQL (and pg_partman does not implement any query optimizations AFAIK). These include optimizations that have to do with ordering based on time_bucket/date_trunc, execution-time chunk exclusion, etc. These result in query speedups of more than 1000x on many common time-series queries.

TimescaleDB is much more automated than pg_partman and thus easier to maintain and administer. There are a lot less knobs to tune and a lot less things to go wrong in TimescaleDB.

We implement analytical features necessary for time-series analyis: gap-filling, common time-series functions liked time_bucket, first, last, etc.

We also implement a lot of data management functionality geared towards time-series data: scheduled data reordering, schedule data dropping/expiration, etc.

This past Monday we released major feature called continuous aggregates. That automatically maintain a materialized view of aggregates over your time-series data, updating it as new data comes in and correctly handling backfilled data as well.

The two projects are really not comparable in breadth or scope IMHO.

This seems like it could fit well with TimescaleDB but obviously would take testing. My only concern would be with Full-text search on JSON which I think is possible but I have never done. I would start with a timescaleDB hypertable on the Even table (time TimestampTz, object JSONB) with the following indexes (or some of them depending on testing):

- BTREE(time DESC)

- BTREE(time DESC, object)

- GIN(object)

- some kind of full text index

I don't know why the slow counting problem would be a problem with WHERE clauses since indexes are highly optimized to work with MVCC (e.g. hint bits etc). The wiki article itself says this isn't much of a problem when using indexes. But maybe you can elaborate?

In addition to JSON support we also support EAV type schemas which look like:

- time | metric_name | value

- or time | metric_id | value

- or time | tags_id | value where the tags table contain normalized tags and metric names

While it's true column stores perform better on single-column aggregates, they perform worse on multi-column operations, thresholding queries, and other types of complex analytics that one often sees on time-series workloads. We have published benchmarks on some common column stores that show these tradeoffs.

Yes TimescaleDB handles event data. I'd need more information about the exact nature of your queries to really give a good answer but there are multiple possible designs here:

- keep the event as a json object and use a GIN index (this would then be combined with constraint-exclusion on the time field for faster queries if using timescaledb)

- convert each JSON key to a column and use multi-column indexes or bitmap scans

- Normalize out the unique object json into a separate table with the columns id, object (json) and have your time-series table as time, event, object_id.

Happy to talk more on our slack support channel https://slack.timescale.com/

Thanks for trying out TimescaleDB. Glad you're having a good experience. :)

We think that declarative partitioning is a great step forward for Postgres partitioning. That said the process is still quite manual (including in PG11) -- unless I am mistaken, auto-creating partitioned tables is not in PG11, but perhaps may get released with PG12 at the end of 2019.

But even with auto-creating partitioned tables, TimescaleDB is still necessary for a few reasons:

- Support for multiple time-based query optimization in the planner

- More efficient handling of both queries and inserts when there are many partitions,

- Time-based analytical function like time_bucket,

- Support for changing partition sizes, etc.

We are also working on many new features, optimizations, and capabilities slated for the next year which will improve time-series workloads in PostgreSQL even more.

The latter. Because of the table partitioning there is not much dependency between old and new data so we've seen consistent performance as you add more and more data.

To put a number on this claim. TimescaleDB at this point can handle up to 100 TB of data.

(Timescale engineer here) We are an extension on top of Postgres and not a whole new DBMS. That means that we inherit a lot of the reliability / stability of Postgres (critically we do not change the WAL/data on disk format which leads to the fact that even as a young database we don't loose data). What we provide on top of Postgres are significant optimization aimed at time-series data. Postgres is optimized for relational data, and it requires significant work to optimize it for time-series data workloads on both the insert and query side.

TimescaleDB provides automatic, adaptive partitioning of time-series tables to make management of time-series tables both easier and more efficient. We add new time-based analytical functions as well as improve query planning and optimization.

I believe what akulkarni meant to talk about is relation accesses (and not just locks). While the partition pruning certainly improved things, two sources of inefficiency still remain in PG 11:

1) Fetching statistics for each table during queries (which happens by reading from the data file off of disk). This happens /before/ pruning, even on PG 11.

2) The overhead of locking each table is still there. Although it's a smaller issue than (1).

We at TimescaleDB found (1) to be the most significant overhead and in fact we have significantly improved things there [1].

[1]https://github.com/timescale/timescaledb/commit/b7257fc8f483...

From kdb+ license agreement:

"1.4 Kdb+ Software Evaluations. End User shall not distribute or otherwise make available to any third party any report regarding the performance of the Kdb+ Software, Kdb+ Software benchmarks or any information from such a report unless End User receives the express, prior written consent of Kx to disseminate such report or information."

Postgres has great support for non-sharded HA.

The issue for an operational workload like this with Clickhouse might be the lack of direct support for UPDATE and DELETE. The workarounds required would add additional complexity, I think.

Clickhouse is very cool. But note that it does not support transactional and relational semantics and does not have real-time updates or deletes. Thus, its meant for very different applications than TimescaleDB. I would classify Clickhouse more in the data-warehouse space...

Hi, you've posted this notion that partitioning a column store by time would yield the same result as TimescaleDB a few times, so thought we'd jump in and clear things up. We fully agree that column stores have their place, particularly if you have a massive number of metrics, and all you care are roll-ups on single column axes. There are some major differences between TimescaleDB and column stores. Namely, TimescaleDB supports a lot of features that column stores in general do not.

- Secondary Indexes.

- Transactional semantics.

- Can operate on data sets greater than available memory (doesn't have to be all in-memory unlike memSQL and some others). Time-series data is voluminous.

- A whole bunch of specialized time-based optimizations that optimize query plans when working with time-based indexes and data.

- Constraints - Including foreign keys.

- Triggers

- Joins with relational data.

- Full SQL - allowing you to use complex queries and window functions

- Compatible with data tools that use SQL - which gets you gets you the richest ecosystem of tools in the data world

- The full gamut of Postgres datatypes including JSON/B and GIS location data

- 20+ years of reliability, tested backups, live streaming replication, etc.

- Geospatial support through best-in-class PostGIS

And of course, we're only getting started :)

That's not always true. There are tons of cases where time-series data gets corrected later. It is true that time-series is INSERT-mostly but UPDATES do happen.

We've had some clients try this, find consistency issues between elastic nodes or postgres and elastic and start using TimescaleDB as a way to simplify the stack and application. But obviously YMMV and this is highly dependent on your query needs.

Yeah, to underscore some differences more completely, timescale has:

- Full indexing and secondary index support.

- Support for transactional semantics

- Support for living along side relational data - including foreign keys to the relational data.

- Full trigger and constraint support

- Support for all of Postgres's native data types including JSON(B) and GIS location data.