Data compression is the process of 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.
There are two main types of data compression: lossless and lossy.
Data compression is a technique used to minimize the volume of digital data, maximizing storage efficiency and improving 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. 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 pretty much 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 also comes at an immense cost, especially in cloud computing, where every byte of data bears a direct cost.
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. Using modern compression techniques, this doesn’t mean manually compressing and decompressing; this is handled 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)!
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.
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 8k8 (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.
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 on a per-column basis 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 is not uncommon to hear from customers who managed to compress their data up to 97 % or have a 26+ compression ratio.
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 further increase the compression rate. 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.