TimescaleDB vs. Amazon Timestream • Hacker News Board

Re: TimescaleDB vs. Amazon Timestream

#141

Post by Darkphibre » Thu, Dec 03, 2020, 8:50 PM UTC

ryanbooz wrote: post #108

drpebcak wrote: post #94

Earlier quoted context omitted.

It is a postgresql extension that you install on top of a normal postgresql server, so it is not worse in any way. Timescale works by creating a 'hypertable', which is an aggregate of a lot of smaller 'chunk' tables. These chunk tables are automatically split by date or incrementing id. This means that for queries that specify IDs or a date range within a certain range, you only have to query results within a few chu…

Great summary, thanks! The two main things most developers will benefit from is how we manage the automatic partitioning of your incoming data (hypertables), something which is non-trivial to do yourself even though other tools exist for it. And because we do it with a time-based focus, we can be really efficient and smart about it. Second, we've improved the query planner in PostgreSQL around the parts that relate t…

This is fascinating!

Back in 2015, I'd architected and deployed a system for a AAA game that handled 24B events/day on launch without breaking a sweat, and supported 200ms round-trip ingestion-to-aggregation SLAs with no windowing (the protocol and ingestion layer did most of the heavy lifting: sequentially ordered _guarantees_ on events even when loadbalanced/connection migration meant no need for windowed batch ordering)... but the scenario for which it was designed was cut and we ended up using it for just 15m slices. :eyeroll:

Still, it was used by a dozen+ games, including a few more AAA titles, and still in use today, and portions of the tech have been cannibalized into other products. I still get the occasional inquiry about memory fencing or memory boundaries on Console X for the 5-15μs event generation API (improperly aligned memory could cause interlocked increment corruption!).

Annnyways:

I had an opportunity to chat with one of the founders at Snowflake in 2017? 2018? for a few hours. I tried to convey how imperative I felt true-realtime time series engines would be critical moving forward, an the reception was rather lukewarm. If they had been as excited as I, it'd have been one of the few opportunities to pull me away from my dream job.

I still feel the world will need this architecture, as we start moving towards more ML/AI driven decision making, and that the company which can get traction will be in a pivotal position moving forward.

Sometimes I wonder about feeling pressured to shift into Data & Applied Science to stay at that org (there just didn't seem to be vertical opportunities in the dev track). I excel in this job too, and I love what I work on... but dang sometimes I feel that the architect career path had even bigger impact potential. It was a fun couple decades. :P

Re: TimescaleDB vs. Amazon Timestream

#142

Post by aantix » Thu, Dec 03, 2020, 9:12 PM UTC

ryanbooz wrote: post #129

aantix wrote: post #114

Doesn't look like Timescale supports Postgres 13. Hmmm.

We've been hard at work with lots of great features for TimescaleDB 2.0, which should be GA in the next couple of weeks which makes multi-node available for anyone to use! There is a branch for PostgreSQL 13 support available for beta testing if you want to build it yourself. It's important to us and we'll focus on completing that integration soon! https://github.com/timescale/timescaledb/pull/2498

Thanks Ryan!

Re: TimescaleDB vs. Amazon Timestream

#143

Post by djk447 » Thu, Dec 03, 2020, 9:40 PM UTC

aforwardslash wrote: post #125

eloff wrote: post #82

Earlier quoted context omitted.

That's surprising to me since Postgres does less efficient writes compared to MySQL in order to optimize for read queries[1] - a sensible tradeoff since most of the time reads are more common than writes in OLTP workloads. TimescaleDB essentially solves this problem for the specific case of time-series inserts. [In Postgres] if we have a table with a dozen indexes defined on it, an update to a field that is only cove…

The parent post didn't have enough detail, but I'd assume the dump would be using COPY for inserts. AFAIK when using COPY, the indexes are only built at the end of the command, not on each insert.

Indexes are built during the inserts for copies and for normal inserts, in general copies are faster because many people use single row inserts that need to instantiate executor nodes and other bits for every row. I think there used to be some other differences, but in general we've seen that multi-row inserts are around as fast as copies and I'm honestly not sure which one our benchmarking tool uses. (I'm at Timescale, btw). Aside from that, the MySQL vs Postgres comment above actually has to do with updates not with inserts and the main difference here is that PG stores rows in the heap whereas MySQL has primary keys that actually organize tables (and also things around copy-on-write MVCC model). So, in some ways, PG should actually be faster for raw inserts, because you don't have to deal with as many page splits and other bits for the organization of the heap, things are just written there (and indexes are secondary). We limit the overhead of building indexes by making sure our chunks are "right-sized" so that you don't end up swapping too much as you're doing inserts into recent data.

Re: TimescaleDB vs. Amazon Timestream

#144

Post by djk447 » Thu, Dec 03, 2020, 9:42 PM UTC

momothereal wrote: post #83

mfreed wrote: post #74

Earlier quoted context omitted.

You can create a hypertable with a UNIQUE composite key on (row_id, time). Otherwise, you are correct in that we require your partitioning keys to be at least _part_ of unique constraints; otherwise, we'd need to build global indexes across all your chunks (which would inhibit scalability)...this would only be worse with multi-node =)

Wouldn't that just make the pair unique? That would still allow you to insert rows with the same row_id, but different time.

Why not create an index on user_id, time? Why do you need a unique reference back to the row? We find that they're often (though certainly not always) kind of meaningless and/or unnecessary for timeseries workloads.

Re: TimescaleDB vs. Amazon Timestream

#145

Post by deanCommie » Thu, Dec 03, 2020, 9:46 PM UTC

superzamp wrote: post #9

jpgvm wrote: post #5

Not a surprising result at all. Timestream is another case of AMS trying to make something for the sake of having it (see here Kinesis instead of just doing Kafka and many other products) instead of just adopting something industry standard. That isn't to say they don't do some good stuff just they also ship a lot of crap, very much 2 tiers of products in the AWS catalog. I would eventually like to see comparison for…

I remember the announcement of Sagemaker at an AWS summit, a moment of profound embarrassment - the whole thing was barely a Jupyter notebook glued to some EC2 stuff with duct tape. You're right, they're definitely doing things for the sole purpose of locking you in.

...have you looked at it recently? It's a MASSIVE suite of services, tools, and capabilities...https://aws.amazon.com/sagemaker/

Re: TimescaleDB vs. Amazon Timestream

#146

Post by momothereal » Thu, Dec 03, 2020, 9:50 PM UTC

djk447 wrote: post #144

momothereal wrote: post #83

Earlier quoted context omitted.

Wouldn't that just make the pair unique? That would still allow you to insert rows with the same row_id, but different time.

Why not create an index on user_id, time? Why do you need a unique reference back to the row? We find that they're often (though certainly not always) kind of meaningless and/or unnecessary for timeseries workloads.

Yes that's what the reply suggested. However the business requirement is that the row_id should be unique, such that looking up a row by ID is guaranteed to have 0-1 results. A unique constraint on (row_id, time) doesn't satisfy that.

Re: TimescaleDB vs. Amazon Timestream

#147

Post by intellix » Thu, Dec 03, 2020, 9:53 PM UTC

mfreed wrote: post #44

intellix wrote: post #38

Dying to use this on Google Cloud SQL but they just won't support the extension. There's an issue with upvotes to add support but still no official response as to whether support is coming... extremely frustrating

Although even if Google were to add support for TimescaleDB, it would only be for the Apache-2 version of the database, which lacks many of its key features (compression, continuous aggregates, multi-node scale out, data retention policies, job scheduling framework, various analytical functions, etc.) See this HN discussion about Timescale's "cloud protection" licensing [0]. Of course, Timescale Cloud is available ac…

It all looks amazing and I want it but am personally quite weak at Database administration. I think I'll have to wait until there's a guide which allows me to migrate from a 9.6 Cloud SQL instance to TimescaleDB Cloud without any downtime. I'm doing a quick google for "Cloud SQL migrate to Timescale". There's a guide from Google themselves but it's not without downtime.

I read that you can't easily migrate 9.6 to another version of PG via streaming replication until 10 :(

Re: TimescaleDB vs. Amazon Timestream

#148

Post by overcast » Thu, Dec 03, 2020, 10:11 PM UTC

rubiquity wrote: post #122

Disclosure: I work at AWS but not on Timestream. Opinions my own. Unless I'm missing something this is not an apples to apples benchmark. TimescaleDB is running as a single node without any replication whereas Amazon Timestream is replicated[0] to three AWS Availability Zones for durability. I've only skimmed the TSBS[1] repo and the start/stop scripts for TimescaleDB. Can someone confirm this? 0 - https://aws.amazon…

Would that really explain the difference between 5 minutes on Timescale and 2 weeks on Timestream? Timeseries databases need the ability to ingest data rapidly.

Re: TimescaleDB vs. Amazon Timestream

#149

Post by ryanbooz » Thu, Dec 03, 2020, 10:16 PM UTC

rubiquity wrote: post #139

mfreed wrote: post #136

Earlier quoted context omitted.

On our managed Timescale Forge, it's multiple AZ. (On some benchmarking equipment on Digital Ocean, it's advertised as "multiple racks", but managed to reduce blast radius.)

This contradicts what is in the blog post. From the machine configuration section: > 1 remote client machine, 1 database server, both in the same cloud datacenter > Disk Size: 4.8TB of disk in a raid0 configuration (EXT4 filesystem) Both those statements lead me to believe it's a single server with locally attached SSDs in a RAID0. Which is it? I know benchmarking is hard, and it's difficult to test certain aspects o…

> Another suggestion I'd make is to run the Amazon Timestream clients across multiple AZs if you aren't already. The blog post doesn't mention whether all the t3 instances are in the same AZ or not.

They were all run in the same AZ. This brings up a good point, however, that we discussed internally when the first results came back. If there are tricks like this that might improve performance, it's not (currently) spelled out in the documentation so there's no way to know that. And we reached out for help in various forums with no response.

It's worth noting that since we performed this analysis, Amazon did release their own tooling for a similar benchmark and created a post[0]. While neither it, nor the tooling documentation[1] specifically spell out how many threads or instances they ran to achieve their results, it's hard to draw an apples-to-apples comparison. It does reveal that they used (had to use??) an m5.24xlarge instance (96 vCPU,384GB) to run their tests. As discussed in the article, one much smaller t3 instance was able to add >1 million metrics/second into TimescaleDB running in Timescale Forge.

[0] https://aws.amazon.com/blogs/database/deriving-real-time-ins... [1] https://github.com/awslabs/amazon-timestream-tools/tree/mast...

Re: TimescaleDB vs. Amazon Timestream

#150

Post by BenoitP » Thu, Dec 03, 2020, 10:17 PM UTC

ryanbooz wrote: post #62

BenoitP wrote: post #48

Pre-reading hypothesis: TimescaleDB is declared orders of magnitude faster because the benchmark is serving results they're computing at writing time? Is it just like the ClickHouse benchmark from earlier, where they read from a `CREATE TABLE [...] ENGINE = AggregatingMergeTree`? Post-reading: "faster queries via continuous aggregates". So is this it? I couldn't find how tables / materialized views were created in th…

(Disclaimer: post author and Timescale employee) I'm sorry you feel like we were trying to be dishonest in the post. On the contrary, we put a lot of effort (and 7,000+ words) into trying to explain everything that we did - just as we've done with other benchmarks which others have linked to. The TimescaleDB test did not use continuous aggregates for these test, only raw time-series data stored in hypertables. For ea…

Sorry for the harsh comment.

I've been reading about your columnar compression pipeline [1], and it sort of makes sense if the comparison is against a regular row-oriented DB. AWS Timestream must really be doing something wrong here, or serving an entirely different use case.

5-175x faster queries and 150x-220x cheaper I do get it. But 6000x higher inserts does not make sense to me. It is insane, and literally unbelievable to me.

Storage savings are at 96% for "IT metrics (DevOps dataset from TSBS)", so it should be closer to 25x higher insert rate. Where is the missing 240x? Is this from some distributed replication overhead? Is this from local vs remote insertion? Is this from bulk inserts vs per row?

Anyway I wanted to thank you for your kind efforts in writing the blog post and providing answers here; and for the patience that you show to the audience here, me included.

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