The LZ4 introduced in PostgreSQL 14 provides faster compression
postgresql.fastware.com
postgresql.fastware.com
https://github.com/postgres/postgres/commit/4035cd5d4eee4dae...
"Add support for LZ4 with compression of full-page writes in WAL The logic is implemented so as there can be a choice in the compression used when building a WAL record, and an extra per-record bit is used to track down if a block is compressed with PGLZ, LZ4 or nothing.
wal_compression, the existing parameter, is changed to an enum with support for the following backward-compatible values: - "off", the default, to not use compression. - "pglz" or "on", to compress FPWs with PGLZ. - "lz4", the new mode, to compress FPWs with LZ4.
Benchmarking has showed that LZ4 outclasses easily PGLZ. ZSTD would be also an interesting choice, but going just with LZ4 for now makes the patch minimalistic as toast compression is already able to use LZ4, so there is no need to worry about any build-related needs for this implementation.
Author: Andrey Borodin, Justin Pryzby Reviewed-by: Dilip Kumar, Michael Paquier"
Actually, that's not true. LZ4 is faster at compression and decompression, zstd (even at the minimum setting) is slower and has higher compression ratio.
The decompression load is an eye catching feature of lz4, but its compression ratio is not just marginally compromised, it is a much less powerful compressor in that prime characteristic than zstds weakest non-fast setting. I can appreciate why Btrfs is in no hurry to include lz4 while it supports zstd:1 to 15. Although it would be interesting to test if lz4 could have a niche on fast storage combined with slow cpus.
Of course zstd somewhat of a portfolio project of facebook developers, and they seem to have integrated it quite nicely into Btrfs to atone for their debt to society - I'll take that :D
[1] https://lore.kernel.org/lkml/1588791882.08g1378g67.none@loca...
If you're on your way down this rabbit hole, there's a bunch of old-machine-specific compression algorithms, developed by the emulator community, e.g. LZSA: https://github.com/emmanuel-marty/lzsa
With this, you are compressing everything, not just columns. And ZFS has dynamic block sizes which works really great together with compression. For example a 8kb PostgreSQL page, may be stored as a 1kb compressed block on disk.
No, postgres know better what to cache then arc.
Unless your read operations to the file system are somehow very cheap, I don't think disabling toast compression is a good idea.
You don’t have to do anything. Toast compression is enabled for compatible types by default but never kicks in until a cell hits 2K bytes in size (by default).
For high-speed pgsql (on ZFS) enable zfs-compression (lz4) just on the filesystem, and disable zfs-caching (the arc, just leave metadata on) because postgres has it's own cache.
>ZFS-Dataset:
atime=off
compression=lz4
recordsize=8k
logbias=throughput
xattr=sa
redundant_metadata=most <===That's a maybe
primarycache=metadata
>In postgres:
full_page_writes=off <===That's a maybe
>Plus maybe some pgtune:
Alternative tuning guide: https://postgresqlco.nf/tuning-guide
(Disclosure: part of the team behind it)
You can still snapshot them atomically by making them both children of a parent dataset and performing a recursive snapshot on the parent. so you have dataset:
myservice/pgdata
myservice/pgwal
or
myservice/pgdata
myservice/pgdata/pgwal
or whatever.
So question, given that this article is about LZ4 memory compression for postgres, would you want it enabled for both/either/none of those datasets? Obviously compression-on-compression doesn't generally help at all, but lz4 performance obviously doesn't hurt much at all either, so if there's anything that postgres doesn't compress, maybe it would be faster in a few niche situations.
Well zfs probes and stops when the data is non compressible, that why stuff like mp3 gets not compressed, so if the pg-datas are already compressed zfs would try it and give it up after some kb's.
My understanding is that ZFS CoW makes torn pages impossible, so it's safe to turn off full page writes.
The same slides mention `primary_cache` and the suggestion is use `metadata` if db working set fits in RAM and use the default of `all` if it doesn't.
[0]: https://people.freebsd.org/~seanc/postgresql/scale15x-2017-p...
On an innodb database, I get about 3x compress ratio with ZSTD compression, and ZFS still has to write about 2x more than EXT4.
For any data you want to keep, I'd say it's well worth the performance cost. Even for data you don't want to keep, so long as it isn't absolutely performance-critical I find it still worth the cost.
Not in my (although small) experience with ZFS vs. databases; the bottleneck is the disk (although it's correct that ZFS is quite CPU-intensive).
Databases have their logging strategies, and ZFS has its own too. So the expense of a write operation is (in principle) doubled.
AFAIK, the typical strategy to mitigate the ZFS overhead is to use a log on a separate, fast storage ("SLOG"). On the db side, in MySQL, it's possible to disable the doublewrite buffer (which is another form of overhead, which, in theory, ZFS should avoid), but nobody really knows if that is 100% reliable or not.
It is. Actually, that's the best thing about running MySQL on ZFS. The doublewrite buffer is not just another form of overhead, the doublewrite buffer is *the bottleneck* for any workload with moderate amount of write (until it got revamped in 8.0: https://dev.mysql.com/worklog/task/?id=5655).
There has been an interesting discussion on the MariaDB mailing list, questioning this, called "Is disabling doublewrite safe on ZFS?"¹.
The last post has been from the InnoDB lead, saying²:
> I believe that it is technically possible for a copy-on-write filesystem like ZFS to support atomic writes, but for that to be possible in practice, the interfaces inside the kernel must be implemented in an appropriate way.
It seems even he is not 100% sure on this topic.
I have a lot of respect for Marko Mäkelä, as he seems like the only one who tries to steer InnoDB away from an evolution dead end. For example, in https://jira.mariadb.org/browse/MDEV-24449, he fixed a corruption bug that had existed since the very first InnoDB commit. At the same time, he also tries to simplify the internals, unlike Oracle who just churns out features along with regressions.
But there are always more corruption bugs within InnoDB. I am not even sure the log checkpoint mechanism is sound, where it does a fsync of the data files first, before getting the checkpoint position from memory, which may have moved forward after the fsync. This behavior also exists since the very first InnoDB commit.
> he also tries to simplify the internals, unlike Oracle who just churns out features along with regressions.
Sadly, very true. I don't know how it was on 5.7 and before, however, at least since v8.0, even patch updates have an alarm probability of breaking existing installations, due to either bugs, or subtle changes in existing functionality.
Also ZFS supports zstd now too rather than just lz4
This is not just a nice-to-have but is the reason why it is viable at all. In comparison, compression in Btrfs for this sort of workload produces insane write amplification (20x or more), as it forces large blocks for compression
wal_init_zero = off # zero-fill new WAL files
wal_recycle = off # recycle WAL files
Otherwise, you will see some intermittent performance issues.
[1]https://www.postgresql.org/message-id/flat/49ijds_99IK_4iDOH...
It is derived from the LZ77 family of compression algorithms, which have papers published in 1977 and 1978 and thus US patent expiration around the year 2000. It is understandable that it would take time for discourse, inclusion in educational curriculum, and programmers feeling free to tinker with the concepts to produce useful new art.
It's almost like patents are a bad thing for the progress of science and skilled arts.
PNG was explicitly designed to be unencumbered by patents, and it was released in 1996.
https://en.wikipedia.org/wiki/GIF#Unisys_and_LZW_patent_enfo...
PNG uses deflate, which is based on a combination of Huffman coding and LZSS, which is derived from LZ77. LZ77 encodes repetitions using back references into a sliding window. To my understanding, LZSS also uses a dictionary based approach, but only for finding such repetitions during encoding.
So this does not answer the question if LZ77 was patented or not.
The Wikipedia article about LZ77 does cite a patent (https://en.wikipedia.org/wiki/LZ77_and_LZ78#cite_note-3) and somebody in the talk page mentions that there's been a law suite (https://en.wikipedia.org/wiki/Talk:LZ77_and_LZ78#Patent_cont...). However, quickly scanning over the patent, it seems to discuss an improved algorithm based on LZ77, perhaps it relates to LZS? (Note: not LZSS from above; this is again is a different algorithm)
The LZS page mentions a law suite sounding similar to the one from the talk page, but contains no direct reference to the patent: https://en.wikipedia.org/wiki/Lempel%E2%80%93Ziv%E2%80%93Sta... It also states that the LZS patent was filed in 1993 and expired in 2007 (due to not paying their fees).
http://fastcompression.blogspot.com/2011/05/lz4-explained.ht...
LZ77 emits verbatim text and instructions to go back x and copy y characters. A lot of compressors are based on this, including PNG, gzip, lz4, snappy. I think this is partially due to earlier patent issues with LZ78. (LZ4 or Snappy would be a good intro, gzip has extra stuff like huffman encoding tacked on.)
You might also be interested in the Burrows-Wheeler transform, which bzip2 is based on. It's completely different and kinda magical. The original paper is here:
https://www.hpl.hp.com/techreports/Compaq-DEC/SRC-RR-124.pdfWhen will PostgreSQL have full table compression for row-oriented tables?
When will PostgreSQL support column-oriented tables?
PostgreSQL is amazing - but these 2 missing pieces create quite a large functionality gap IMO.
Citus or Timescale extension?
- Timescale: https://news.ycombinator.com/item?id=21412596
- Citus: https://news.ycombinator.com/item?id=26369305
-----------
"Citus 10 brings columnar compression to Postgres"
https://www.citusdata.com/blog/2021/03/06/citus-10-columnar-...
"The following options are available: compression: [none|pglz|zstd|lz4|lz4hc] - set the compression type for newly-inserted data. Existing data will not be recompressed/decompressed. The default value is zstd (if support has been compiled in). compression_level: <integer> - Sets compression level. Valid settings are from 1 through 19. If the compression method does not support the level chosen, the closest level will be selected instead. stripe_row_limit: <integer> - the maximum number of rows per stripe for newly-inserted data. Existing stripes of data will not be changed and may have more rows than this maximum value. The default value is 150000. chunk_group_row_limit: <integer> - the maximum number of rows per chunk for newly-inserted data. Existing chunks of data will not be changed and may have more rows than this maximum value. The default value is 10000."
read more: https://github.com/citusdata/citus/tree/master/src/backend/c...
So, unlike in a columnar database, you still have to worry about splitting up your narrow+hot "fact" columns from your wide+cold "dimension" columns, and joining them back together later only when you need them, in order to not be polluting your page cache with the wide+cold column data.
To quote from the blogpost[1]:
What are the Benefits of Columnar?
- Compression reduces storage requirements - Compression reduces the IO needed to scan the table - Projection Pushdown means that queries can skip over the columns that they don’t need, further reducing IO - Chunk Group Filtering allows queries to skip over Chunk Groups of data if the metadata indicates that none of the data in the chunk group will match the predicate. In other words, for certain kinds of queries and data sets, it can skip past a lot of the data quickly, without even decompressing it!
Disclaimer: I work on the Citus team at Microsoft, but have not worked on the columnar storage.
[1]: https://www.citusdata.com/blog/2021/03/06/citus-10-columnar-...
Has anyone run into the same issue? I'm considering reopening this investigation (even though I'm very happy with Snappy).
Depending on the database, you might have compressed pages (sets of consecutive rows), rows, or columns in a row. Postgres does the latter (it's called TOAST) where the large columns are compressed and/or stored in a hidden table. So you first search the index for the key, figure out where your row is, read the row from the table, then, if needed, you read the columns you need from the TOAST table and uncompress them.
Now pbzip2, I just use defaults with multiple cores for compression.