TimescaleDB 2.0 released, now distributed multi-node

Thanks for all the questions @eloff! I might try to separate responses for hopefully some threading :)

The software in our architecture is actually unified: You spin up K instances of TimescaleDB, log into one of them and then call "add_data_node" to attach others and data nodes, and "create_distributed_hypertable" to make this node the access node _for that particular hypertable_.

In fact, you can explicitly specify different data nodes as the "backend" for different distributed hypertables, e.g., same access node, but data nodes {A, B} for distributed hypertable 1; data nodes {C, D, E, F} are for distributed hypertable 2. You could also have different nodes play the role of access nodes for different hypertables and vice-versa. Lots of flexibility here, although we imagine most users won't need it, so by default your distributed hypertable is created across all connected data nodes.

Now, because these nodes are just running TimescaleDB, if you were to create a regular table or even a regular (non-distributed) hypertable on that access node, it would be just stored locally. Then, any JOIN between the distributed hypertable data and regular table happens transparently on the access node.

Now, that's what in the current release, but not surprisingly, we plan to add support for better local JOINs. For regular tables, one approach will likely be to provide an option to replicate the table on all data nodes, so that you can perform the JOINs locally (and because TimescaleDB 2.0 already supports 2PC for individual rows across data nodes to support replication factors > 1, that machinery somewhat already exists). A second approach for regular tables that are also partitioned -- particularly if using the same "space" partitioning key as your distributed hypertable -- is to collocate partitions of the regular table across the data nodes, so that JOINs on the space partition keys (e.g., server_id, user_id, etc.) can again be local.

Regarding offerings on Timescale Cloud around "IO-optimized", "compute-optimized", and "storage-optimized", and particularly that Timescale Cloud runs on all three major clouds (AWS, Azure, GCP) across 76 regions:

This indeed reflects that these different options are using different VM instance types, as well as different storage options, including both local instance storage _and_ remote block storage (EBS).

You can partially see this also in the maximum storage capacity of options, where some service types can do 10TB of storage, while others max out at 500GB. As well as in variety in which options we can offer in which clouds/regions based on the underlying hardware capacities that are available.

(Note this is the maximum sized "disk". Because TimescaleDB's compression on average gets 94-97% storage reductions -- measured across the field -- the amount of raw data you can actually store on these options is much higher. And why also it's not an apples-to-apples price comparison against RDS!)

So it's exposing this tradeoff in performance/price/capacity to users to hopefully find these exact needs. And the good thing is there is a "one-click" fully-online migration between instance types (and regions/clouds), so switching is always quite easy, especially as your usage grows.

Thank you! Great set of use cases as well :-)

Clickhouse and Timescale are different types of databases -- Clickhouse is a columnar store and Timescale is a row-oriented store that is specialized for time series data with some benefits of columnar stores[0].

Something like InfluxDB is a better thing to compare to TimescaleDB (and TimescaleDB does very well, though the benchmark was a bit old[1] and influx might have improved in the meantime).

Database types aside, what really gets me excited about Timescale is that it's just another Postgres extension. If you're already running a Postgres cluster for your OLTP workloads (web-app-y workloads) and have just a bit of fast-moving time series data (ex. logs, audit logs, event streams, etc), Timescale is only an extension away. You get the usual time-tested battle hardened Postgres, with all it's features and also support for your time series workloads. Yeah you could set up declarative partitioning yourself (it is a postgres feature after all) but why bother when Timescale has done the heavy lifting?

[EDIT] - see the response below -- the benchmark is up to date, and Timescale does even better against the purpose-built tool that is InfluxDB.

> Note: This study was originally published in August 2018, updated in June 2019 and last updated on 3 August 2020.

[0]: https://blog.timescale.com/blog/building-columnar-compressio...

[1]: https://blog.timescale.com/blog/timescaledb-vs-influxdb-for-...

Based on more mature codebase.

/When I tried Clickhouse, I managed to segfault it with NULL pointer dereference error. Ok it is anecdotal, and maybe I just had bad luck, same could happen with Postgres etc etc... But anyway: it can be a deciding factor. (And they fixed it quickly - issue 7955 on github) /

Native column-oriented data warehouses designed for OLAP queries will always be faster. There are multiple alternatives from Clickhouse to Redshift that will be faster.

Originally I didn't like Timescale because it didn't offer anything new but the product has improved greatly over the years. Today it's close on performance by using a custom column-oriented data layer that stores the actual chunks in PostgreSQL rows and has several time-related processing and analytical features (continuous aggregates, time bucketing, smoothing values, etc) that make it easier than doing it yourself in raw SQL.

One of the big advantages is that it allows you to use Postgres which means you can continue to use it as your main OLTP operational database as well. This avoids a lot of complicated polyglot issues like syncing datasets or using different querying systems with different syntax. It's one of the better examples of using Postgres as a data platform rather than a simple database.

There are other alternatives that combine this OLAP+OLTP functionality like Citus (another automatic sharding distributed database extension for Postgres), Vitess (automatic sharded mysql), TiDB (natively distributed mysql interface on top of key/value store), MemSQL (proprietary distributed mysql interface with ram-based rowstores and disk-based columnstores) and SQL Server (with hekaton column-stores, in-memory tables, and scale out).

Can you expand on what you mean by an acyclical hierarchal relationship?

actually I think the typical application should mix "NoSQL" paradigmas and relational.

consider a Offer model, do I really need OfferPosition's as an own table or can I include them via something like "jsonb".

One of interesting things we’ve found is that the typical data models we’ve seen in TimescaleDB (and time-series workloads in general) is that, while having JOINs and additional tables with metadata, business info, etc. is super useful -- and missing from most other time-series databases, leading to frustrating polyglot solutions that deploy them alongside Postgres/MongoDB/whatever -- it’s much less common to see the terribly deep snowflake schema patterns that came to dominate OLAP, with levels-upon-levels of normalization.

And certainly the fact that your time-series hypertable in TimescaleDB can also store JSON, given Postgres’ underlying capabilities, also helps here and thus provides developers with flexibility given their application/performance needs.

It's a weasel statement that only claims to "a lot" of user sentiment. It's not something you can easily disagree with because the claim is so vague.

Postgres is hell of a no-sql database :)

I think NoSQL works for a very limited number of cases. Most of the time you just end up moving all the schema checking and integrity checking (foreign key enforcement) to the application level and then you're squarely worse off.

Plus MongoDB, which is the poster child of the revolution, has been a technical pile of shit for a decade. It's slower than JSONB on Postgres, and loses data in the default configuration, was insecure in the default configuration, failed Aphyr's Jepsen distributed system consistency tests every time - in other words promised things about consistency that were lies in their documentation.

They have improved a lot, but I feel so sorry for people who jumped on that bandwagon early.

My interest lies in databases and database internals, and I spent 8 years building databases in my spare time. I always advise everyone that they should use a relational database unless they are very, very sure their requirements don't require it and won't ever require it. It's much easier to start with Postgres and switch to NoSQL later if you really need to go that way than to do it the other way around. Most times you find you don't need to switch to NoSQL. The imagined hordes of users never materialized to overwhelm your trusty relational database.

I was on the relational side (I worked for MySQL) when MongoDB first sprung up, and IMHO it was to some extent a knee-jerk reaction.

MongoDB claimed far superior performance for web-applications simply by not persisting data to disk. MongoDB offered one thing that was very useful to web-developers: a good API for storing JSON data directly into the database. Not having transactional semantics was of course a terrible idea once critical data was stored into the databases and the systems started to have crashes, but it worked for small applications and while developing the applications nothing looked amiss.

Since web-developers are not database experts and many did not have experience with deploying large-scale applications, it was of course very appealing to have both performance and simplicity. But as the downsides of not persisting data started to become a real problem, many applications moved away from MongoDB and into other systems that offered transactional capabilities and durability of data. At the same time, MongoDB started to implement transactional support, which of course affected performance, but allowed applications to remain on MongoDB.

All in all, I think the takeaway is that database APIs matter to developers (not really very surprising) and it is important to have a good API to the database, but transactional behavior is important for real applications and it is essential to have that as well.

With JSON being added to PostgreSQL and MySQL, I think that some of that simplicity that MongoDB offered is available in PostgreSQL and MySQL as well. (The API is still an SQL API, but at least you can easily store and retrieve JSON documents directly from the database.)

I think the main problem during that time is that the issues of performance and API was conflated and a general assumption grew that NoSQL also meant high performance, which is not necessarily the case.

What spectrum? The actual database types are relational, key-value/wide-column, document-store, graph, etc.

NoSQL is a meaningless buzzword but one good outcome was more attention to non-relational systems and where they can be a better fit, as well as creating more progress for those systems as well like better usability and APIs to match the relational dbs.

That is pretty awesome. I'd love to see a postgres-aware cache plugin that keeps the latest versions of data in memory, SAP HANA-style.

Yes, works with SQLAlchemy. (Like many ORMs there are a couple things to be aware of, but plenty StackOverflow questions about these already :)

And yes, people use it for event sourcing. Often see this in various product analytics use cases.

Yes. We’ve used Timescale with SQLAlchemy and overall everything works.

TimescaleDB is purpose built for time-series data on Postgres. We offer native compression on this data and smart, easily adjustable partitioning, continuous and real-time analytics, automated time-series data management (retention, reordering, tiering, etc) and so forth... again, all focused on time-series data. With a few specific commands, we abstract all of the time-series partitioning for you and provide time-series specific functions to make queries more efficient and useful.

Citus is a great product that primarily focused on multi-tenant SaaS analytics, where you typically shard your queries on the tenant_id, but didn't focus on time-series. So, their current documented solution for time-series uses other extensions (pg_partman), doesn't provide for compression, and all the other essential tools to make massive amounts of time-series data useful, which TimescaleDB is hyper-focused on solving.

Our docs cover some of the data node management [0] and native replication [1] particular in more detail.

Right now you can use Postgres physical replication for the access node, with one as the primary (and handling DDL/writes), while Access Node replicas can scale reads to the rest of the cluster. As alluded to in the blog post, we'll probably look to enable clients to write directly to data nodes as a form of further scaling writes (so that the AN "primary" would only be strictly required for DDL), as opposed to a multi-master approach.

[0] https://docs.timescale.com/v2.0/using-timescaledb/distribute...

[1] https://docs.timescale.com/v2.0/using-timescaledb/distribute...

The latest tag is 2.0.0-rc2 since it is technically a release candidate and not the final cut.