Time Series Databases to Watch

Then our stack is primarily in AWS. So when it comes to DBs, I'd prefer to keep it all within our VPC for security / latency reasons - for sensitive data at least.

Also worth noting that TimescaleDB is far more memory-efficient than other time-series databases (eg InfluxDB [1]), and for some folks the memory cost savings is significant (b/c memory cost $$$ >> disk cost $).

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

Also I don’t like the phrase “true real time applications” at all. It is less useful than even Microsoft’s OLAP-Cube-ETL-OLTP word salad, and embedded developers in particular are going to be cringing if they read that.

The second is as a clustered columnstore index. This stores the rows directly as a columnstore. That's the way I'd store time series data. In this case, all the inserts go into a "delta rowstore" and are moved by a background service into the clustered columnstore structure. Your inserts proceed as fast as any insert into a rowstore. There is overhead to move the data since it's doing a fair bit of compression. But that's going to be true of anything that does compression.

If you want really fast ingest, you can use an InMemory OLTP (aka Hekaton) structure and layer a column store index on top of that. And that InMemory structure can be persisted to disk. That gives you much better performance than a plain rowstore. You'd probably want to write something to move the data to a longer term structure but that would be the fastest way to ingest it I can think of. Using SQL Server :)

This gives insert performance on par with row storage (it is using row storage!). This can be combined with their in memory database functionality as well.

Correct. Some ETL inserts to specific nodes when sharding is necessary, in other cases Kafka engine tables on a group of nodes subscribe to common topics, and we simply let the whole cluster participate in queries. This works just fine when table scans are acceptable.

Rebalancing is a missing option here, short of moving partitions manually. But in my specific use cases, I have not yet needed to rebalance across nodes.

Note using native Clickhouse replication is still an option if we need it. One cost to it is the extra work needed in the database cluster, so addressing it in an eariler layer works for us.

> How do you handle cross-node queries like joins?

If I understand your question, since we are using the Distributed view type across the cluster definition, a query on any node will receive data from the others as part of a join-less SELECT, and federate on the node with the client connection.

We are not doing any database-side JOINs currently. Plans are to augment data in ETL, or join data post-query (potentially Spark). Clickhouse dictionaries handle simple cases.

[1] https://github.com/Vertamedia/chproxy

A single generator has well over a hundred properties you would care about and although we're talking about 5 minute data, there are many many overlapping simulations being run around the clock and many of those simulations look into the future for many days. Then you have load data, topology data, equipment outages, load and wind forecast, thousands of pricing points that can be further decomposed into constituent prices, changing bids and offers, energy transactions...I could type for the next hour and only cover a small amount of what I know which is still a small percentage of what we actually store.

From a time-series perspective, a single generator can show well over a hundred line-charts and you can do that off of multiple study types...and you might have well over a thousand generators in your market, not to mention all the other logged objects such as transmission lines which also have lots of properties logged. You're right that time-series data can be compressed, but the SQL data is somewhat different I think.

In short, energy markets are hugely complex creatures. There is just so much going on that I have yet to get bored and have a non-busy day in nearly a decade of employment. It's an extremely exciting field for sure as there is just so much going on and so much change occurring. In the not so distant future, many experts think the lines between transmission and distribution will become blurred and we'll all need even more data to run the grid at peak efficiency and reliability.