Time Structured Merge Tree: From LSM Tree to B+Tree and Back Again
influxdb.com
influxdb.com
Need replication? Gotta write your own sharding logic or set up pg_shard.
Need aggregations? Gotta write your own logic. Will you do them on the fly? Use triggers? On demand?
Need to remove old data? Gotta set up a cron job. But wait, what if I want to age different series at different rates? Now you need a policy system. Sigh.
Not saying Postgres can't be the storage engine, but there's a lot of work to do on top of that.
The fallacy of the many time series db's out there is that they discarded the relational database as a viable storage option prematurely and are now trapped solving the very hard problem of horizontally scalable distibuted storage that takes many years to solve instead of focusing on the time-series aspect of it.
Sooner or later something along the lines of pg_shard will become standard in PostgreSQL and other databases, thus you don't really need to write your own sharding logic, you just have to wait. OR you can write it if you want. You have options.
Aggregations is what GROUP BY is for. Removing old data is a non-issue if you're using a round-robin approach (see my blog link), it also makes aging different series at different rates easy.
The circular buffer approach is fine, but it does have the drawback of being unable to represent variable-length data (like key-value pairs). It's also harder to compress the data.
Can a GROUP-BY do windowed aggregations? Like, take an average over ten-minute windows? My SQL knowledge is not great.
https://inductiveautomation.com/news/process-historians-vs-s...
[I have no data on how well their product performs in practice]
I'm sure writing would be no problem, the only thing I wonder about are complex (aggregating) queries in situations where you care about delays (interactive visualization perhaps).
mysql> CREATE TABLE test.polls_memory ( id INT(11) PRIMARY KEY AUTO_INCREMENT, poll_id INT(11), time DOUBLE, value INT(11)) ENGINE MEMORY;
mysql> CREATE TABLE test.polls_disk (id INT(11) PRIMARY KEY AUTO_INCREMENT, poll_id INT(11), time DOUBLE, value INT(11)) ENGINE MyISAM;
#> for i in `seq 1 500000`; do echo "INSERT INTO test.polls_memory (poll_id,time,value) VALUES ($i,UNIX_TIMESTAMP(),$RANDOM);" ; done | mysql -uroot
mysql> INSERT INTO test.polls_disk SELECT * from test.polls_memory;
Query OK, 510001 rows affected (0.95 sec)
Records: 510001 Duplicates: 0 Warnings: 0
Repeat the experiment with 200 different clients hitting the server via networking with 1500 insert statements, each second. Then you can compare MySQL with the InfluxDB use case cited as an example in the article.
And don't forget the index on the timestamp.
I know its not as sexy as InfluxDB, but the old work horses are tried and true and if you use them right they are as good as if not better then some of the shiny new toys.
Which pretty much exactly what timeriver is :) (That, and it can also do Graphite-like functions, e.g. movingAverage(), etc.)
Also, not sure it's wise to build system software (proxy) just to log data instead of using what already exists. Because that proxy you write will not be an old work horse either.
mysql> ALTER TABLE polls_disk ADD INDEX `timestamp` (poll_id,time);
mysql> INSERT INTO test.polls_disk SELECT * from test.polls_memory;
Query OK, 510001 rows affected (1.78 sec)
Records: 510001 Duplicates: 0 Warnings: 0
As far as a proxy in front of the datastore, the amount of effort to batch up data packets to hand them off to the data store is several orders of magnitude less complexity then a really well designed data store, so where do I want to custom roll software and where do I want to leverage the broader community. I don't think there isn't a place for the new tools, but I think we jump to them much more quickly then we should in a lot of situations.
(1) Table scans to extract just a few columns will be extremely inefficient. It's better to create one table per column, each being a pair [time, value].
(2) B-tree indexes will be inefficient you for large extracts. For example, aggregating by month for one year will basically not use the indexes. The index will only mostly useful for looking up individual metrics, which is not useful. The new BRIN indexes in 9.5 should help, however.
(3) You'll end up reaching for something like Postgres' partitioned tables, which allows you to create child tables that are visible from the top-level table. Each table needs to be sharded by something like the current day, or week, or month.
(4) You say you need a proxy to batch data packets. So it's queuing, and needs persistence because you don't want to lose data if Postgres is lagging behind — your proxy is now a time series database! However, if Postgres is lagging behind, you have a problem. If you're writing at 600k/s and Postgres can only handle 500k/s, you have a never-ending queue.
(5) PostgreSQL can only have one master, and its write capability has a fixed ceiling (for HDDs you can calculate the max possible tps from the rotational speed, not sure about SSDs). At some point you'll end up sharding (by column, probably) the master into multiple concurrent masters.
Much of the point of something like InfluxDB is that there's no single bottleneck. You scale linearly by adding more nodes, each of which can write data at top speed. You don't need a "proxy" because it's designed to perform that very same function.
That doesn't mean most people can't do well on relational databases as most people simply doesn't need both at the same time.
We were only getting ~1k tps with some spikes of 10k tps. I hope for the rest of the users, there was some work done of that front. Maybe in the future we will revisit out of curiosity, since we have no issues at the moment.
The people who are talking about Postgres in this thread are thoroughly confused. This isn't a competitor to Postgres, this is a platform for building a competitor to Postgres (or, hypothetically, something Postgres itself could use). I guess maybe they mean InfluxDB rather than the subject of the article.
Reading the article, it seems that they could have contributed some development to Rocksdb and saved themselves a lot of time rather than implementing their own system.