That section is the most relevant whenever compression in a DB is discussed.
The purpose of a database is to find, aggregate or update data - storage is where the trade-off gets expressed. There are no silver bullets here.
Any method of compression which speeds up either filter rejection or scan rate is better than something that only trades off IO for CPU usage.
For example, dictionary encoding can be slower to read (because you decompress the whole dictionary and not just the skip read after filter), but not if you can squeeze out an IN clause by turning string comparisons into O(1) dictionary followed by a simple integer filter. Remember, this can be arbitrarily complex (Druid is a great example of this) and then the bitmaps can be used because the dictionary index will be a dense 0-N.
Even better if that can feed a deterministic operation like UPPER() so that you do it over the dictionary hits once, instead of each row. You can even use it over the same hash slot, instead of another dictionary collision check or hash computation.
If anyone is looking at JSONB compression, go take a long look at the Variant encoding proposals from Databricks/Snowflake for Iceberg[1].
Turning a single column "payload" JSONB field into chunks which are columnarized and strictly typed allows you to do all the tricks mentioned here, but on loosely typed data but chunk by chunk.
Ha, we (my partner at our company) filed this issue 5 years ago. We have a large-ish (but not giant) json blob in one of our timescale tables that I'd love to get better compression on. It changes just frequently enough that we didn't split it into columns, but infrequently enough that it could (I think) be compressed quite nicely. Generally timescale has been great for us.
I have been working on another PG extension for timeseries (https://github.com/xataio/deltax) for a few months, and trying to make it score as good as possible on ClickBench.
This is a project that is simply lot of fun to work on. There are many tricks that can be used to speed-up analytics, besides just type-aware compression:
* for each segment you will keep things like max/min/sum, number of distinct values, bloom filters, etc. For a good amount of common queries, you can answer them just based on this metadata, so you don't need to decompress the columns at all.
* for text column, you compress them differently based on cardinality. Low cardinality (think labels or similar) is dictionary based compression. High cardinality is LZ4.
* Generally the smaller the data on disk, the higher the cold runs performance. This is because you need less IO to load it in memory. I have discovered that on top of the type-aware compression, it's worth doing another round of LZ4. There's also some research that it's sometimes worth doing multiple passes of LZ4.
* Partition and segment pruning. If you can tell from the metadata or bloom filters that the filter doesn't match a partition or segment, you skip the whole thing.
* Push down of filters in the decompression layer. Depending on the compression algorithm, while you decompress you can also filter out the values that you don't need. This avoids passing data and allocating memory for elements that will be later discarded anyway.
* Organization of data on disk is more important than almost anything else. Of course, that's the main point of columnar storage, but there are level of details on how to organize the data so that IO is minimized during queries. I have tried 3-4 different layouts before settling on one.
* For top N type of queries, which are really common in analytics, you want to stop the reading from disk / decompressed as soon as you have enough data to guarantee that you have a correct top N to satisfy the query.
* Parallelize everything: at least ClickBench runs on instances with a lot of CPU cores, so you need to parallelize every step of the way. This is done differently depending on the query type. For example for top N, each worker can take a subset of the segments and get the top N from each of them. Then you combine those in a single result.
I work a lot with databases, but only a couple times have I really messed with time series stuff. I've enjoyed reading your list of tricks and it's gotten me interested in playing around with writing my own toy database to just experiment with some of these ideas.
What is your idea on monetizing it? Besides Timescale the other providers have found issue on monetization AFAIK. And they all restrict the S3 offloading for Enterprise accounts.
For context, I'm CTO at Xata where we offer a Postgres service that differentiates via other ways (very fast CoW branching, scale-to-zero, anonymization, etc). We often recommend people keeping production where it is (RDS, Cloud SQL, on-prem, etc.) and use Xata only for staging + branching.
We can't offer Timescale because of the license (except for the version without compression), which blocks some potential customers from using us.
So there is currently no monetization plan beyond reducing this blocker for us. An explicit goal of DeltaX is to be pure OSS, offered on Xata, and also hopefully on all other Postgres providers. Without making any hard promises for the long-term future, I will say I care deeply about it being something that reduces lock-in in the Postgres ecosystem, rather than increasing it.
Isn't this feature useful for things besides timeseries data? Like in general if you expect some grouped-by value to be repeated so much that regular indexes on a normalized schema become slow. The motivation behind Spanner interleaved tables is similar.
If I understand your question correctly:
For storage, mostly yes. Swinging-door compression went lossy because byte compression on raw floats used to be useless, but with new method like Gorilla you can compress it lossless, so you can keep every sample and still afford it.
But there is another point that this Aveva historian adds filter that filters some points so I don't know how it impact ingest.
But in general now trend is to use normal databases from IT world in OT world to overcome some legacy solutions.
Based on us keeping a replica of our AVEVA data archive in delta tables which are just parquets; and only compressing the parquets in LZ4 and getting a fuck ton of more space savings. I would say yes. Especially when you're onprem with a dedupe storage appliance.
I think swinging door made sense when storage was slow and scrace, but nowadays storage is cheap (even with these prices)
At StackGres [1] we find Timescale to be one of the most used extensions. Timescale is quite a successful project!
StackGres is actually the first solution recommended by Timescale for self-hosting with Kubernetes operators [2].
So if you are into Kubernetes (or if not, consider it, using something like K3s [3] is quite straightforward and lightweight on resources), this is probably a great option to self-host which would avoid you all the hassle of manually doing all the setup involved for production-ready clusters.
I run it on VM the same way as a few separate PostgreSQL instances.
TimescaleDB is an extension to PostgreSQL, so there's a lot of information, guides, and community support online, and Postgres is one of the most widely used databases right now. There are some things to setup but Tiger data documentation is very good.
I've tested the migration of the main table (IoT data), from 300GB to about 25GB in a non-production environment. Everything seems right, but I'm still delaying/procrastinating the prod migration, so I wanted to know how it is going for other live projects.
> What does compression do to query performance?
That section is the most relevant whenever compression in a DB is discussed.
The purpose of a database is to find, aggregate or update data - storage is where the trade-off gets expressed. There are no silver bullets here.
Any method of compression which speeds up either filter rejection or scan rate is better than something that only trades off IO for CPU usage.
For example, dictionary encoding can be slower to read (because you decompress the whole dictionary and not just the skip read after filter), but not if you can squeeze out an IN clause by turning string comparisons into O(1) dictionary followed by a simple integer filter. Remember, this can be arbitrarily complex (Druid is a great example of this) and then the bitmaps can be used because the dictionary index will be a dense 0-N.
Even better if that can feed a deterministic operation like UPPER() so that you do it over the dictionary hits once, instead of each row. You can even use it over the same hash slot, instead of another dictionary collision check or hash computation.
If anyone is looking at JSONB compression, go take a long look at the Variant encoding proposals from Databricks/Snowflake for Iceberg[1].
Turning a single column "payload" JSONB field into chunks which are columnarized and strictly typed allows you to do all the tricks mentioned here, but on loosely typed data but chunk by chunk.
[1] - https://github.com/apache/parquet-format/blob/master/Variant...
There’s an issue tracking TimescaleDB JSONB compression: https://github.com/timescale/timescaledb/issues/2978
Ha, we (my partner at our company) filed this issue 5 years ago. We have a large-ish (but not giant) json blob in one of our timescale tables that I'd love to get better compression on. It changes just frequently enough that we didn't split it into columns, but infrequently enough that it could (I think) be compressed quite nicely. Generally timescale has been great for us.
Have you tried mongodb?
It is one of many columns. The other columns are what most of the analysis is done on.
[dead]
I have been working on another PG extension for timeseries (https://github.com/xataio/deltax) for a few months, and trying to make it score as good as possible on ClickBench.
This is a project that is simply lot of fun to work on. There are many tricks that can be used to speed-up analytics, besides just type-aware compression:
* for each segment you will keep things like max/min/sum, number of distinct values, bloom filters, etc. For a good amount of common queries, you can answer them just based on this metadata, so you don't need to decompress the columns at all.
* for text column, you compress them differently based on cardinality. Low cardinality (think labels or similar) is dictionary based compression. High cardinality is LZ4.
* Generally the smaller the data on disk, the higher the cold runs performance. This is because you need less IO to load it in memory. I have discovered that on top of the type-aware compression, it's worth doing another round of LZ4. There's also some research that it's sometimes worth doing multiple passes of LZ4.
* Partition and segment pruning. If you can tell from the metadata or bloom filters that the filter doesn't match a partition or segment, you skip the whole thing.
* Push down of filters in the decompression layer. Depending on the compression algorithm, while you decompress you can also filter out the values that you don't need. This avoids passing data and allocating memory for elements that will be later discarded anyway.
* Organization of data on disk is more important than almost anything else. Of course, that's the main point of columnar storage, but there are level of details on how to organize the data so that IO is minimized during queries. I have tried 3-4 different layouts before settling on one.
* For top N type of queries, which are really common in analytics, you want to stop the reading from disk / decompressed as soon as you have enough data to guarantee that you have a correct top N to satisfy the query.
* Parallelize everything: at least ClickBench runs on instances with a lot of CPU cores, so you need to parallelize every step of the way. This is done differently depending on the query type. For example for top N, each worker can take a subset of the segments and get the top N from each of them. Then you combine those in a single result.
I work a lot with databases, but only a couple times have I really messed with time series stuff. I've enjoyed reading your list of tricks and it's gotten me interested in playing around with writing my own toy database to just experiment with some of these ideas.
What is your idea on monetizing it? Besides Timescale the other providers have found issue on monetization AFAIK. And they all restrict the S3 offloading for Enterprise accounts.
For context, I'm CTO at Xata where we offer a Postgres service that differentiates via other ways (very fast CoW branching, scale-to-zero, anonymization, etc). We often recommend people keeping production where it is (RDS, Cloud SQL, on-prem, etc.) and use Xata only for staging + branching.
We can't offer Timescale because of the license (except for the version without compression), which blocks some potential customers from using us.
So there is currently no monetization plan beyond reducing this blocker for us. An explicit goal of DeltaX is to be pure OSS, offered on Xata, and also hopefully on all other Postgres providers. Without making any hard promises for the long-term future, I will say I care deeply about it being something that reduces lock-in in the Postgres ecosystem, rather than increasing it.
Up to 100% of articles using "up to" in the title are complete dross.
How do I get the job of benevolent dictator of title moderation?
Okay maybe title is not great but I really achieved this compression on real database where I have devices via MQTT connected.
Isn't this feature useful for things besides timeseries data? Like in general if you expect some grouped-by value to be repeated so much that regular indexes on a normalized schema become slow. The motivation behind Spanner interleaved tables is similar.
Old data historians use swinging-door compression algorithms (a lossy compression). Would something like TimescaleDB make this unnecessary for IoT?
https://docs.aveva.com/bundle/pi-server-s-da-admin/page/1022...
If I understand your question correctly: For storage, mostly yes. Swinging-door compression went lossy because byte compression on raw floats used to be useless, but with new method like Gorilla you can compress it lossless, so you can keep every sample and still afford it. But there is another point that this Aveva historian adds filter that filters some points so I don't know how it impact ingest.
But in general now trend is to use normal databases from IT world in OT world to overcome some legacy solutions.
>But in general now trend is to use normal databases from IT world in OT world to overcome some legacy solutions.
Someone tell my company this please ;-;
Based on us keeping a replica of our AVEVA data archive in delta tables which are just parquets; and only compressing the parquets in LZ4 and getting a fuck ton of more space savings. I would say yes. Especially when you're onprem with a dedupe storage appliance.
I think swinging door made sense when storage was slow and scrace, but nowadays storage is cheap (even with these prices)
Compression uses a different license than Apache.
In various distros, the timescaledb installed by the package manager doesn't support other licenses than Apache.
This means that there's a high chance your distro doesn't allow stock timescaledb to use compression. You probably will need to install it manually.
[dead]
Gorilla by Facebook had this. Value is stored as delta and time as delta of delta.
It's used in ClickHouse as well. CH supports all known compression algos and they are documented pretty well.
They say they are using “gorilla compression “
I’m still amazed every time I go back and read how the compression for floating point values works.
Is anyone running self hosted timescale? How is it going? I have a use case for it, but don’t know why I’m a bit afraid of using it.
At StackGres [1] we find Timescale to be one of the most used extensions. Timescale is quite a successful project!
StackGres is actually the first solution recommended by Timescale for self-hosting with Kubernetes operators [2].
So if you are into Kubernetes (or if not, consider it, using something like K3s [3] is quite straightforward and lightweight on resources), this is probably a great option to self-host which would avoid you all the hassle of manually doing all the setup involved for production-ready clusters.
[1] https://stackgres.io
[2] https://www.tigerdata.com/docs/get-started/choose-your-path/...
[3] https://k3s.io/
I'm not into k8s things, but thanks. Still run this project with Postgres in the same machine as the app. No issues so far after 8 years. :)
I run it on VM the same way as a few separate PostgreSQL instances. TimescaleDB is an extension to PostgreSQL, so there's a lot of information, guides, and community support online, and Postgres is one of the most widely used databases right now. There are some things to setup but Tiger data documentation is very good.
I've tested the migration of the main table (IoT data), from 300GB to about 25GB in a non-production environment. Everything seems right, but I'm still delaying/procrastinating the prod migration, so I wanted to know how it is going for other live projects.
[flagged]