Need for external compression methods in PostgreSQL

现下每个数据库系统都有一些方法压缩其数据，最直白的原因是尽可能缩小数据库存储空间，缩小存储成本。尤其是在当今数据量呈指数级增长的环境下，这一功能显得很重要。另有一个比较隐式的原因是提高查询性能；思路是：较小的数据大小意味着需要扫描的数据页较少，较少的磁盘I/O和更快的数据访问。因此，如果我们不能提高查询性能，至少需要保证数据解压缩都应该足够快，不应影响查询性能。

来看看压缩级别有：页压缩、行压缩、列压缩等。列存数据库的优点是其列的压缩比非常高，因为列中存在重复结构的连续数据。另一种情况是，在行存数据库中，列值区间和空间非常大，以至于压缩列的单个值是有意义的。如果这些值不适合在单个页中，我们甚至可以单独保留在其他地方，并且该行具有指向行外压缩数据的指针。在PostgreSQL中，这种技术被称为TOAST（超大属性存储技术），其中，对于可以包含可变长度数据的列，数据将透明压缩并存储在同一行中，如果数据仍然太大，它将以较小的块作为行存储在一个单独表中，我们称其为toast表，在那里这些块本身可以被压缩，也可以不被压缩。

压缩技术可被应用于不同的目的。它不限于仅数据压缩。例如，在DB replication环境中，redo日志从主到从的传输可能会成为一个巨大的网络瓶颈，因此许多RDBMS提供压缩redo日志功能。

然后是RDBMS使用或供用户选择的压缩算法。这些尤其适用于数据压缩。由于数据是用户数据，用户数据中的特定模式可能适合特定的压缩算法，而不同的模式可能适合另一种压缩算法。这意味着，如果RDBMS提供一个选项，从已知标准压缩库列表中选择特定列或特定用户自定义类型的压缩算法，如zlib、lz4、ztd，或者，库算法很可能是一个基于客户业务需求完全定制的算法。

其次，在特定平台的压缩算法优化方面，已经取得了许多进步，并为压缩、加密和SIMD提供硬件加速器，这些加速器与CPU内核紧密耦合，然后可以通过压缩或加密算法使用这些硬件加速器。其中一个例子是鲲鹏Zlib加速引擎，它提供了一个硬件使能的基础架构，用于在“鲲鹏920”ARM64处理器上进行压缩。现在我还没有机会测试这种能力，但听起来确实很有希望。

此外，压缩/加密算法往往对数据执行重复任务，这自然适合利用SIMD矢量化技术。ARM64和英特尔都有在zlib、lz4等知名库中进行相关特定平台的增强。查看此NEON Inrinsics案例研究使用NEON优化zlib内部的adler-32算法。

以上这些都直接表明，RDBMS服务迫切需要为用户提供特定表或特定列的native压缩算法/库的选择。在撰写本文时，PostgreSQL使用其自己的内置压缩算法基于LZ的toast表压缩。想象一下，如果有一个接口来选择zlib而不是内置算法。用户可以选择zlib压缩级别。再进一步，添加一个接口，供用户创建相关扩展，该扩展使用对于特定平台的硬件加速算法。

正是有这样一个proposal正在酝酿中。查看在PostgreSQL hackers Maillist中的讨论主题。这可能还有很长的路要走（在撰写本文时），但我非常希望这个功能能够进入，因为应用场景足够有说服力，如上文所述，社区对这个功能没有根本的反对意见，并且相关的开发者正在为之奋斗。

我提前merge了这个特性到我的环境，并尝试玩玩。下面是界面的外观。如果所有patch完全实现后，界面可能会有所不同，但我认为它的本质或多或少会保持不变。下面是我的测试输出；注意，这只是为了通过示例强调这个功能是多么的酷和有用，助于理解我在本博客中上面解释的内容。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

CREATE TABLE zlibtab(t TEXT COMPRESSION zlib WITH (level '4'));
CREATE TABLE lztab(t TEXT);                           
ALTER TABLE lztab ALTER COLUMN t SET COMPRESSION pglz;              
                                         
pgg:s2:pg$ time psql -c "\copy zlibtab from text.data"              
COPY 13050                                    
                                         
real  0m1.344s                                 
user  0m0.031s                                 
sys   0m0.026s                                 

pgg:s2:pg$ time psql -c "\copy lztab from text.data"               
COPY 13050                                    
                                         
real  0m2.088s                                 
user  0m0.008s                                 
sys   0m0.050s                                 
                                         
                                         
pgg:s2:pg$ time psql -c "select pg_table_size('zlibtab'::regclass), pg_table_size('lztab'::regclass)"
 pg_table_size | pg_table_size                          
---------------+---------------                         
    1261568 |    1687552                          

pgg:s2:pg$ time psql -c "select NULL from zlibtab where t like '0000'" > /dev/null

real  0m0.127s
user  0m0.000s
sys   0m0.002s

pgg:s2:pg$ time psql -c "select NULL from lztab where t like '0000'" > /dev/null

real  0m0.050s
user  0m0.002s
sys   0m0.000s

注意两种不同的压缩算法在压缩数据大小以及插入数据（压缩）和选择数据（解压缩）的速度方面有何不同。

你甚至可以使用与创建新索引相同的方式创建新的压缩访问方式：

1

CREATE ACCESS METHOD pglz1 TYPE COMPRESSION HANDLER my_compression_handler;

where my_compression_handler should be a PostgreSQL C function that could be created using a PostgreSQL extension. This function assigns its own implementation functions for a set of pre-defined hooks that define everything that the PostgreSQL core needs to know to make use of the compression access method :

其中my_compress_handler是可以使用PostgreSQL extension创建的PostgreSQL C函数。此函数为一组预定义的钩子分配自己的实现函数，这些钩子定义了PostgreSQL核心需要知道的压缩访问方法：

1
2
3
4
5
6
7
8
9
10
11
12
13

Datum
my_compression_handler(PG_FUNCTION_ARGS)
{
    CompressionAmRoutine *routine = makeNode(CompressionAmRoutine);

​    routine->cmcheck = my_cmcheck;
​    routine->cminitstate = my_cminitstate;
​    routine->cmcompress = my_cmcompress;
​    routine->cmdecompress = my_cmdecompress;
​    routine->cmdecompress_slice = NULL;

​    PG_RETURN_POINTER(routine);
}

This is PostgreSQL’s way of being highly extensible : Allow user to use built-in methods, but also provide a way for the user to define his/her own methods for doing the same job. All the above functions would be inside an PostgreSQL extension, that could be created using:

这是PostgreSQL高度可扩展的方式：允许用户使用内置方法，但也为用户提供了一种方法，以自定义的方法来执行相同的工作。上述所有函数都将位于PostgreSQL扩展中，可以用下面的命令创建：

1

CREATE EXTENSION my_compression;

Every modern database system has some way to compress its data at some level. The obvious reason for this feature is to reduce the size of it’s database, especially in today’s world where the data is growing exponentially. The less obvious reason is to improve query performance; the idea is: smaller data size means less data pages to scan, which means lesser disk i/o and faster data access. So, in any case, data de-compression should be fast enough so as not to hamper the query performance, if not improve it.

Compression is offered at different levels : page compression, row compression, column compression, etc. Columnar databases have the advantage of a very high compression ratio of its column because of presence of a repetetive pattern of contiguous data in a column. Another case is when, in a row oriented database, the column values are so large that it makes sense to compress individual values of the column. Such values can even be kept separately if they do not fit in a single page. And the row has pointers to the out-of-line compressed data. In PostgreSQL, such technique is called TOAST (The Oversized-Attribute Storage Technique), where, for columns that can contain variable-length data, the data is transparently compressed and stored in the same row, or else if it is still too large, it is stored in smaller chunks as rows in a separate table called a toast table, where these chunks themselves may or may not be compressed.

Compression is offered for different purposes. It may not be restricted for only data compression. E.g. in a replication system, the transfer of redo logs from the master to slave can become a huge network bottleneck, so many RDBMS offer to compress redo logs.

And then comes the compression algorithms that the RDBMS uses or gives options to choose. This applies especially more to data compression. Since data is user’s data, a specific pattern in the user data might suit a particular compression algorithm, while a different pattern might be suitable for another compression algorithm. Moreover, this implies that it would be far more beneficial if the RDBMS gives an option to choose a specific compression algorithm for a specific column or a specific user-defined type out of a list of well-known standard compression libraries such as zlib, lz4, ztd, snappy, gzip, etc. Or, the library algorithm may very well be a completely customized one.

Secondly, there has been a lot of advancements to optimize compression algorithms for specific platforms, and provide hardware accelerators for Compression, Encryption and SIMD that are closely coupled to CPU cores, which can then be levergaed by compression or encryption algorithms. One such example is the Kunpeng Zlib Acceleration Engine, which offers a hardware-enabled infrastructure for compression on a “Kunpeng 920” ARM64 processor. I haven’t got a chance to test this capability, but it does sound promising.

Furthermore, the compression/encryption algorithms inherently do repetitive tasks over the data, which is a natural fit for leveraging SIMD vectorization. There has been independent projects going on on both ARM64 and Intel to do such platform-specific enhancements in well known libraries like zlib, lz4 etc. Check out this NEON Intrinsics case study that optimizes zlib’s adler-32 algorithm using NEON intrinsics.

All this directly points to an urgent need for RDBMS servers to give users a choice for specific native compression algorithms/libraries for specific tables or specific columns. As of this writing, PostgreSQL uses its own built-in compression algorithm based on LZ for toast table compression. Imagine if there were an interface to select zlib instead of the built-in algorithm. Further, select the zlib compression level. Still further, add an interface for users to create an extension that uses a customized algorithm native to a specific platform that uses hardware acceleration.

Well, there is exactly such a proposed feature in the making. Check out this discussion thread in the PostgreSQL hackers community. It may be a long way to go (as of this writing), but I am very hopeful of this feature going in, because the use-cases are strong enough as shown above, there are no fundamental objections to this functionality, and there are work-in-progress patches submitted.

I went ahead and applied this patch, and played around it. Roughly, below is how the interface looks like. After the patch-set fully materializes, the interface might be different, but I think the essence of it would remain more or less the same. Below is the output of my tests; please note that it is just to emphasize with examples how cool and useful this feature would be, and to make sense of whatever I explained above in this blog.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

CREATE TABLE zlibtab(t TEXT COMPRESSION zlib WITH (level '4'));
CREATE TABLE lztab(t TEXT);                           
ALTER TABLE lztab ALTER COLUMN t SET COMPRESSION pglz;              
                                         
pgg:s2:pg$ time psql -c "\copy zlibtab from text.data"              
COPY 13050                                    
                                         
real  0m1.344s                                 
user  0m0.031s                                 
sys   0m0.026s                                 

pgg:s2:pg$ time psql -c "\copy lztab from text.data"               
COPY 13050                                    
                                         
real  0m2.088s                                 
user  0m0.008s                                 
sys   0m0.050s                                 
                                         
                                         
pgg:s2:pg$ time psql -c "select pg_table_size('zlibtab'::regclass), pg_table_size('lztab'::regclass)"
 pg_table_size | pg_table_size                          
---------------+---------------                         
    1261568 |    1687552                          

pgg:s2:pg$ time psql -c "select NULL from zlibtab where t like '0000'" > /dev/null

real  0m0.127s
user  0m0.000s
sys   0m0.002s

pgg:s2:pg$ time psql -c "select NULL from lztab where t like '0000'" > /dev/null

real  0m0.050s
user  0m0.002s
sys   0m0.000s

Notice how two different compression algorithms differ in the compressed size, and the speed of inserting data (compression) and selecting data (decompression).

You would even be able to create a new compression access method using the same way as we do for creating a new index :

1

CREATE ACCESS METHOD pglz1 TYPE COMPRESSION HANDLER my_compression_handler;

where my_compression_handler should be a PostgreSQL C function that could be created using a PostgreSQL extension. This function assigns its own implementation functions for a set of pre-defined hooks that define everything that the PostgreSQL core needs to know to make use of the compression access method :

1
2
3
4
5
6
7
8
9
10
11
12
13

Datum
my_compression_handler(PG_FUNCTION_ARGS)
{
    CompressionAmRoutine *routine = makeNode(CompressionAmRoutine);

​    routine->cmcheck = my_cmcheck;
​    routine->cminitstate = my_cminitstate;
​    routine->cmcompress = my_cmcompress;
​    routine->cmdecompress = my_cmdecompress;
​    routine->cmdecompress_slice = NULL;

​    PG_RETURN_POINTER(routine);
}

This is PostgreSQL’s way of being highly extensible : Allow user to use built-in methods, but also provide a way for the user to define his/her own methods for doing the same job. All the above functions would be inside an PostgreSQL extension, that could be created using:

1

CREATE EXTENSION my_compression;