Bottomless, consumption-based storage for PostgreSQL built on Amazon S3(timescale.com)
timescale.com
Bottomless, consumption-based storage for PostgreSQL built on Amazon S3
https://www.timescale.com/blog/expanding-the-boundaries-of-postgresql-announcing-a-bottomless-consumption-based-object-storage-layer-built-on-amazon-s3/
8 comments
This is a great observation. As you point out, this was designed for the workload patterns we typically see with time-series, events, and analytical data, where the query (& insert) patterns differ across time.
So I agree that it's good for cold storage, but it's a bit nuanced. For example, you rarely see small random queries to old historical data, but you do often see larger scans over historical data. And in those cases, the throughput you get from S3 is actually quite high (especially that we've engineered with with proper columnar compression and row group/columnar exclusion). Which is very different from a latency bounded workload where you have a lot of small random reads, which is much more common in CRUD-like workloads.
Also, with Timescale, you have the ability to build continuous aggregates (incrementally materialized views). So you can have the raw data (or even lower levels of rollups) that get tiered into S3, while the more frequently accessed rollups can remain in hot storage.
(Timescale co-founder)
So I agree that it's good for cold storage, but it's a bit nuanced. For example, you rarely see small random queries to old historical data, but you do often see larger scans over historical data. And in those cases, the throughput you get from S3 is actually quite high (especially that we've engineered with with proper columnar compression and row group/columnar exclusion). Which is very different from a latency bounded workload where you have a lot of small random reads, which is much more common in CRUD-like workloads.
Also, with Timescale, you have the ability to build continuous aggregates (incrementally materialized views). So you can have the raw data (or even lower levels of rollups) that get tiered into S3, while the more frequently accessed rollups can remain in hot storage.
(Timescale co-founder)
Another option as a "PG backed by S3" is neon.tech
This is already the second Postgres solution with bottomless storage in S3 as Neon provides that as well. For me, the neatest feature of Neon is that it can scale to zero whenever there are no requests (so truly cloud-native and serverless in that sense too), but I don't see a mention whether Timescale will have the same.
> For me, the neatest feature of Neon is that it can scale to zero
As far as I understood, Neon is only scale-to-zero from the perspective of a tenant ("don't bill for idle"), not from the perspective of who's running the software.
Neon's compute nodes can scale down to zero, but the pageservers and safekeepers don't.
And since safekeepers are a Paxos quorum, there's at least 3 of them!
As far as I understood, Neon is only scale-to-zero from the perspective of a tenant ("don't bill for idle"), not from the perspective of who's running the software.
Neon's compute nodes can scale down to zero, but the pageservers and safekeepers don't.
And since safekeepers are a Paxos quorum, there's at least 3 of them!
To my understanding, Neon stores all its data in pageservers and s3 (where together they are used as a write-back page cache, with S3 as the bottom-most layer).
Timescale gives users a lot more control. S3 is used for storage of older data while newer data is stored on faster disks. The workload patterns for use cases like time-series, events, and analytics fit this well. The time dimension often gives a clear separation between what should be stored in hotter storage and what should be stored in cooler storage, based on time-oriented nature of data.
We also leverage a compressed columnar format that makes our data space and scan efficient for time series workloads. Neon stores a database page friendly format that best supports their workloads.
(Timescale engineer)
Timescale gives users a lot more control. S3 is used for storage of older data while newer data is stored on faster disks. The workload patterns for use cases like time-series, events, and analytics fit this well. The time dimension often gives a clear separation between what should be stored in hotter storage and what should be stored in cooler storage, based on time-oriented nature of data.
We also leverage a compressed columnar format that makes our data space and scan efficient for time series workloads. Neon stores a database page friendly format that best supports their workloads.
(Timescale engineer)
That's right. Our approach is "no knobs". We think the system can make better decision on what should be offloaded to S3 vs kept on the pageservers which leads to zero configuration to the users.
While I always love to see these kind of storage optimization I find the lack of metrics disappointing.
Some metrics for cost saving vs query time increase would be nice (taking into account query time range and partitioning time range i.e. number of files).
From a technical stand point when looking at S3 there is a minimum request first-byte time of 200ms and if you have multiple files to query on top of the list requests for the files in a bucket paths it can add up (you can save file/index metadata data in cache to help with this).
if the query is run once a day and isn't very latency sensitive I guess it doesn't matter but some solution that looks on the query data patterns and pre-loads data from s3 to hot/local cache storage or saves the file locally on the node for a period of time would surly help.
One scenario for this is ML models refresh of time series data where you might want to add new data with historical data and create a new model (even if it's incremental you'd still want to do regression testing with historical data). Of course there are code optimizations for these without the need of the DB to the the heavy lifting but that's just one thing that comes to mind.
From a technical stand point when looking at S3 there is a minimum request first-byte time of 200ms and if you have multiple files to query on top of the list requests for the files in a bucket paths it can add up (you can save file/index metadata data in cache to help with this).
if the query is run once a day and isn't very latency sensitive I guess it doesn't matter but some solution that looks on the query data patterns and pre-loads data from s3 to hot/local cache storage or saves the file locally on the node for a period of time would surly help.
One scenario for this is ML models refresh of time series data where you might want to add new data with historical data and create a new model (even if it's incremental you'd still want to do regression testing with historical data). Of course there are code optimizations for these without the need of the DB to the the heavy lifting but that's just one thing that comes to mind.
that's pretty neat. I need to figure out a way to migrate ~10tb of data on a 5 year old postgres RDS instance that continues to balloon in disk usage. D:
(Timescale co-founder)
Let us know how we can help :-)
Also just curious: What's your application?
Let us know how we can help :-)
Also just curious: What's your application?
If you load that into a column store you’ll likely see that drop to 1TB or smaller.
Very much agree.
Even without this new capability of S3-based storage, Timescale's native columnar compression often gets like 95% storage reduction, even while staying fully in the PostgreSQL ecosystem.
(We often see our users amazed about how much less storage/disk they require after migrating from RDS.)
https://docs.timescale.com/timescaledb/latest/how-to-guides/...
Even without this new capability of S3-based storage, Timescale's native columnar compression often gets like 95% storage reduction, even while staying fully in the PostgreSQL ecosystem.
(We often see our users amazed about how much less storage/disk they require after migrating from RDS.)
https://docs.timescale.com/timescaledb/latest/how-to-guides/...
But isn't that the "wrong" store to use (as far as performance is concerned) when you want random, row-level access?
Columnstores can do row level access by trading off a bit in terms of compression. If you organize the columnstore files as an LSM tree and use incremental compression schemes (so you don't have to compress too many more rows then the one your after) it can get close to the performance of a B-Tree for point reads - it depends on the specific table schema.
[1] https://dl.acm.org/doi/abs/10.1145/3514221.3526055
[1] https://dl.acm.org/doi/abs/10.1145/3514221.3526055
Is this anything else than AWS Athena with extra steps?
My team ingests petabytes of data each day in S3 that is then queriable in Athena, and it supports all the same features that are mentioned here, such as interfacing with other types of datasources like an existing PostgreSQL database.
My team ingests petabytes of data each day in S3 that is then queriable in Athena, and it supports all the same features that are mentioned here, such as interfacing with other types of datasources like an existing PostgreSQL database.
With the data tiering feature, data moves from EBS -> S3. The database is fully aware of all the metadata and the location of any data that it manages. So a user can query any data that lives on storage managed by the Timescale database. S3 is just another storage device for the db.
Athena is not a database. Athena is query service for data lakes in S3 (e.g. load into S3 is not managed by Athena, it is not protected by any DB transactional semantics). Athena needs metadata info for the data in S3. (Timescale engineer)
Seamless queries that span different storage layers is a pretty big deal. This is like having a table with new data in RDS and old data in Athena.
The potential downside is unpredictable query performance. For example, suppose you have a query that calculates daily statistics from your time series data. The query takes 1 second to execute when you run it for yesterday's data, but 1 minute to run for a day six months in the past, because you've inadvertently shifted your work onto the slower storage layer.
The potential downside is unpredictable query performance. For example, suppose you have a query that calculates daily statistics from your time series data. The query takes 1 second to execute when you run it for yesterday's data, but 1 minute to run for a day six months in the past, because you've inadvertently shifted your work onto the slower storage layer.
Do you use AWS Athena as the "database/serving layer" for customer-facing applications where performance matters?
Our experience is that isn't a common use case for Athena, while it is the primary use case for TimescaleDB.
Our experience is that isn't a common use case for Athena, while it is the primary use case for TimescaleDB.
No, exposing the data to our customers through Athena is the use case.
So performance matters in the sense that querying data is our core offer, but we're talking about multiple seconds here, not milliseconds.
I think the distinction makes sense then, thanks.
So performance matters in the sense that querying data is our core offer, but we're talking about multiple seconds here, not milliseconds.
I think the distinction makes sense then, thanks.
What does the response time look like for those queries? And what is the cost?
I haven't heard great things about Athena, but I'd be curious to hear more about what works.
I haven't heard great things about Athena, but I'd be curious to hear more about what works.
I don't want to say too much, but the response time is not suitable to a real time application, queries will all take at least around a second to complete. But what users are querying explains why it takes a long time. Again, we're talking about a petabyte of data a day.
Then in terms of cost it's around $0.5 per query maybe? The average is a very bad number because most queries will be something like $0.0001, but then some will be hundreds of dollars. But that's the only number I have off the top of my head.
Then in terms of cost it's around $0.5 per query maybe? The average is a very bad number because most queries will be something like $0.0001, but then some will be hundreds of dollars. But that's the only number I have off the top of my head.
Probably not. Other than its slightly abstracted away from being solely ingrained in the AWS ecosystem.
It shouldn't be hard to use another block level storage.
It shouldn't be hard to use another block level storage.
Will this be available for the open-source/self-hosted version? Would be awesome if this makes it there and we can self-host Timescale within AWS with S3 backing the storage of older data.
I use Timescale for internal analytics tools -- time-series metrics, logs, etc from hardware systems. Our deployments have very low CPU usage, so running on a single EC2 instance is feasible. Our main concern is always just running out of disk space. It would be amazing if this ends up being a config option in the open source version, where we can point the instance at an S3 bucket and provide the right IAM access.
I use Timescale for internal analytics tools -- time-series metrics, logs, etc from hardware systems. Our deployments have very low CPU usage, so running on a single EC2 instance is feasible. Our main concern is always just running out of disk space. It would be amazing if this ends up being a config option in the open source version, where we can point the instance at an S3 bucket and provide the right IAM access.
I work as a Product Manager at Timescale. :)
The data tiering feature will be only available in the Timescale cloud offering, we do not have plans to support this feature in oss/self-hosted at the moment.
Thanks for sharing your use-case with TimescaleDB. I'm curious to understand how are you ingesting metrics today into TimescaleDB, do you manage your own schema, retention, compression and downsampling?
We at Timescale have built a product named Promscale for easy metrics and traces ingestion with automatic schema-management, compression, retention and downsampling capabilities with full SQL support. Have you tried Promscale (https://github.com/timescale/promscale) for your metrics use-case?
To learn more about Promscale join us in #promscale channel in our community slack (https://slack.timescale.com/)
The data tiering feature will be only available in the Timescale cloud offering, we do not have plans to support this feature in oss/self-hosted at the moment.
Thanks for sharing your use-case with TimescaleDB. I'm curious to understand how are you ingesting metrics today into TimescaleDB, do you manage your own schema, retention, compression and downsampling?
We at Timescale have built a product named Promscale for easy metrics and traces ingestion with automatic schema-management, compression, retention and downsampling capabilities with full SQL support. Have you tried Promscale (https://github.com/timescale/promscale) for your metrics use-case?
To learn more about Promscale join us in #promscale channel in our community slack (https://slack.timescale.com/)
I'd be curious about access patterns and transaction costs. S3 costs for storage, but you also pay per unit for GET and PUT. Are there cases or datasets where data motion between tiers starts adding unexpected costs?
Is any of this work open-source? And if so, can someone point me to it?
As far as I know, this is v2 of https://github.com/timescale/timescaledb
I'm curious about the tradeoffs. The article mentions increased latency of 10's of milliseconds. If you have a complex access (many joins or index hits), is that exacerbated from many round trips to load different files? From a cost perspective, I image that you now have to think about IOPS. If you have a very high usage database, would it make sense to have a more traditional file system where you aren't paying per operation?
Edit: On reread, I missed that this is only available for the hypertable concept, and is more of a "cold storage" for older metrics, presumably accessed infrequently. I am curious about a general "PG backed by S3"'s ramifications.