Published at Jul 31, 2023
Storage Savings
Data compression is reducing the size of digital data while preserving the essential information contained in them. Data can be compressed using algorithms to remove redundancies or irrelevancies in the data, making it simpler to store and more effective to transmit. Compression is a cornerstone feature of Timescale, our fast and scalable cloud database built on the PostgreSQL you know and love. So much so that we even enable developers working with large datasets to compress immutable data, resulting in significant storage and cost savings.
Read our Docs to learn more about compression, or check out some of the stories told by our users who have benefited from this feature.
Data compression works by identifying and eliminating redundant patterns in digital information, similar to how we might use abbreviations or shorthand in writing. The process involves several key techniques:
Compression algorithms scan through data to identify recurring patterns or sequences. For example, in a time-series database storing temperature readings, you might have many repeated values or gradual changes that can be stored more efficiently.
Once patterns are identified, the algorithm replaces them with shorter representations. Think of it like creating a legend where "temperature_reading_celsius" becomes "t" – but much more sophisticated and operating at the binary level.
Many compression algorithms build a "dictionary" of common patterns they encounter. Instead of storing the same information multiple times, they store it once in the dictionary and reference it when needed. This is particularly effective for text and structured data.
Advanced compression algorithms analyze the frequency of data patterns and assign shorter codes to more frequent patterns—similar to how Morse code uses shorter signals for common letters.
The actual compression process happens behind the scenes through various algorithms, such as Gorilla compression (great for floating-point numbers) or delta-delta encoding (efficient for sequential values like timestamps). But more on that later. When you query your data, the compression is reversed automatically, giving you access to your original information without any manual decompression steps.
Let's now look at two fundamental approaches that form the backbone of many compression techniques:
Compression dictionaries are like creating a lookup table for your data. Imagine you're reading a novel where the phrase "the quick brown fox" appears hundreds of times.
Instead of storing that whole phrase each time, a dictionary compression algorithm would store it once and replace each occurrence with a much shorter code, like "ref_123".
When it's time to read the data again, the algorithm simply looks up "ref_123" in its dictionary and reconstructs the original phrase. This technique is particularly powerful for structured data, where similar patterns occur frequently.
Deduplication works by finding and eliminating exact copies of data sequences. Think of it as a copy-paste detector—when the algorithm encounters a sequence of data it has seen before, instead of storing it again, it simply places a pointer saying, "This is the same as what we saw back there."
For example, if you're storing daily backups of files, many files might be identical from one day to the next. Rather than storing multiple copies, deduplication stores one copy and uses pointers for the rest, significantly reducing storage requirements.
A compression ratio expresses the amount of data that has been saved by compressing. A 10x ratio would mean that 100 GB of data is compressed down to 10 GB of data. We can discover this value by dividing the initial data size by the compressed data size, so 100/10 in the case above.
There are two main types of data compression: lossless and lossy. Data compression is a technique used to minimize the volume of digital data, maximize storage efficiency, and improve data transmission speed. This is achieved through various algorithms designed to identify and eliminate redundant or insignificant data elements without sacrificing the core information they embody.
In database circles, these algorithms usually yield data that can be perfectly decompressed to obtain the original payload—this is called lossless compression. The two examples we looked at (compression dictionaries and deduplication) are both examples of lossless data compression. Lossless compression is often used when we are trying to store files or data in a smaller space, but we care about that data. Relational and time-series databases (like PostgreSQL and Timescale) almost exclusively use lossless compression to reduce total database size.
A compression algorithm can also be lossy—it creates a smaller (or compressed) version of the data, which sacrifices the ability to recreate the original dataset for greater compression rates. This is most often seen in audio and video processing, where quality is sacrificed for total size.
We can also see this happening in databases when we downsample data, taking raw data and reducing the size while retaining the statistical or visual properties (this is what Timescale achieves with continuous aggregates, our version of automatically updated and incremental materialized views).
Interestingly, this is very common in some areas; for example, in IoT workloads, historian databases compress data by simply removing points that are unlikely to be of interest to an operator. We can’t regain these points, but they fit within a threshold that allows us to reason that they are unimportant noise.
Data compression can be used in an array of scenarios, reducing image, video, or text files—and is particularly helpful when you’re dealing with large amounts of data that has an underlying pattern (even if you can’t see the pattern yourself!).
In a world where data is both ubiquitous and valuable, storing massive volumes of time-series data or time-series-like workloads is increasingly challenging. In the same way that time is always ticking forward, data that represents change over time is always relentlessly growing in size.
What starts as a month of data from one input can quickly grow to years of data from multiple inputs. Gigabytes can become terabytes, then quickly become petabytes of storage that need to be provisioned. This data can be valuable (it’s the new oil, after all), and we often don’t know what insights we will want to query for tomorrow.
Because of this, keeping our data around is of immense importance to a business, but it also comes at an immense cost, especially in cloud computing, where every byte of data has a direct price.
When we compress our data with a lossless algorithm (so we can get the original data back), we reduce the amount of storage we need while still retaining the ability to query the data. Thanks to modern compression techniques, we can handle compression in real time as we query our data. We have storage and I/O savings at the cost of some extra CPU while we decompress.
Because input/output (I/O) and disk speed can be one of the major bottlenecks on a system, reducing the amount we read and write can actually speed up queries even when we factor in the extra CPU cycles. It’s like magic (but it’s actually just math)!
PostgreSQL offers data compression options that you can use to reduce storage requirements, save disk space, and improve performance. These options include the following:
TOAST (The Oversized-Attribute Storage Technique): In PostgreSQL, a row has to fit into a single page, which by default is 8 kB (unless compiled with a different page size). Larger field values can be stored out of line and compressed to overcome this limitation. This mechanism, TOAST, happens transparently for the user but only supports data types with variable-length representation.
Another option is to use the external file system compression provided, for example, by ZFS or Btrfs, to compress the entire database or specific tablespaces.
To optimize storage compression in a PostgreSQL database, you can use PostgreSQL’s several tuning knobs to influence its TOAST behavior. You can choose one of the following strategies:
PLAIN: prevents compression or out-of-line storage.
EXTENDED (default): tries to compress but stores out-of-line if the row isn’t too big.
EXTERNAL: allows out-of-line storage but turns off compression.
MAIN: allows compression but not out-of-line storage.
Each strategy can be configured per-column with ALTER TABLE ... SET STORAGE.
PostgreSQL will only try to TOAST values when the row size exceeds TOAST_TUPLE_THRESHOLD (2 kB by default, defined at compile time). The target size for the TOASTed row can be controlled with TOAST_TUPLE_TARGET (2 kB by default).
Additionally, starting with PostgreSQL 14, you can choose the compression method with ALTER TABLE ... SET COMPRESSION.
Timescale is 100 % PostgreSQL but expands its functionality and scalability with advanced algorithms and optimizations for time series and time-series-like workloads.
A loved feature among customers, our compression powers have been impressing software engineers for some time now. It´s common to hear from customers who managed to compress their data up to 97 % or have a 26+ compression ratio.
“For one of our larger customers, we normally store about 64 GB of uncompressed data per day. With compression, we’ve seen, on average, a 97 % reduction.” Michael Gagliardo, Ndustrial
Here are some of the ways Timescale compression can boost your performance while saving on storage and costs:
Unlike PostgreSQL TOASTing, which compresses column values individually, Timescale compresses the entire column (native columnar compression), making the most of similarities between individual values to achieve greater compression rates.
Plus, Timescale also implements special compression algorithms for common data types in time series. For floating-point data types, we support Gorilla compression, and for integer and time data types, we support delta-delta compression. Additionally, column values can be grouped by secondary columns before compression to increase the compression rate further. Choosing a good value for compress_segmentby is essential for achieving the best compression performance possible.
Tip: Since compression algorithms are data-type-specific, your schema design can greatly influence your achieved compression rate. Following PostgreSQL's best practices for schema design will help you achieve that. Specifically for compression, the recommendations are not to use decimal/numeric data types as those do not have optimized compression algorithms in Timescale.
And let’s not forget that Timescale is also changing time-series data management by allowing you to modify compressed or immutable data.
Still not convinced? Check out our compression faceoff for a complete comparison of Postgres TOAST vs. Timescale Compression—the numbers speak for themselves!