借助Pg最新版本(16.1)进行的基于lz4或zstd算法的TOAST压缩
查看当前版本PG的编译时开启的配置选项
使用apt安装好最新的PG后,使用以下命令查看PG支持的特性
pg_config
命令输出结果:
红框处就显示,当前的PG支持LZ4和ZSTD两种压缩算法,再加上PG默认自带的pglz算法,一共三种
PG中开启压缩功能方法
就是在建表时指定压缩算法或建好表后再指定压缩算法
建表时指定
参考这篇文档即可:https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-PARMS-STORAGE
主要是column_name data_type [ STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT } ] [ COMPRESSION compression_method ]这两个参数
注意,列必须是EXTENDED或MAIN这两种storage mode的一种才可以压缩,原因详见文档中的以下两段话:
STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT }
This form sets the storage mode for the column. This controls whether this column is held inline or in a secondary TOAST table, and whether the data should be compressed or not. PLAIN must be used for fixed-length values such as integer and is inline, uncompressed. MAIN is for inline, compressible data. EXTERNAL is for external, uncompressed data, and EXTENDED is for external, compressed data. Writing DEFAULT sets the storage mode to the default mode for the column's data type. EXTENDED is the default for most data types that support non-PLAIN storage. Use of EXTERNAL will make substring operations on very large text and bytea values run faster, at the penalty of increased storage space. See Section 73.2 for more information.
COMPRESSION compression_method
The COMPRESSION clause sets the compression method for the column. Compression is supported only for variable-width data types, and is used only when the column's storage mode is main or extended. (See ALTER TABLE for information on column storage modes.) Setting this property for a partitioned table has no direct effect, because such tables have no storage of their own, but the configured value will be inherited by newly-created partitions. The supported compression methods are pglz and lz4. (lz4 is available only if --with-lz4 was used when building PostgreSQL.) In addition, compression_method can be default to explicitly specify the default behavior, which is to consult the default_toast_compression setting at the time of data insertion to determine the method to use.
建表后指定
查看这篇文档:https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-DESC-SET-STORAGE
感兴趣的话可以研究其背后的原理:
https://www.postgresql.org/docs/current/storage-toast.html
以下方案是借助Timescaledb的,暂时用不到了
参考文章
1.https://medium.com/@SaiParvathaneni/a-complete-guide-for-postgres-timescale-db-ae75a4d45b8d
2.https://www.timescale.com/blog/building-columnar-compression-in-a-row-oriented-database/
3.https://www.timescale.com/blog/what-is-toast-and-why-it-isnt-enough-for-data-compression-in-postgres/
4.https://medium.com/@lk.snatch/postgresql-compression-854a4647ee43
5.https://mp.weixin.qq.com/s/3ylT02XZE0Z_O2ZHnKkPcg