The LZ4 introduced in PostgreSQL 14 provides faster compression

https://www.postgresql.fastware.com/blog/what-is-the-new-lz4-toast-compression-in-postgresql-14

by pella • 5 years ago

309 85 5 years ago

postgresql.fastware.com

klodolph2• 5 years ago

LZ4 is just such a nice little algorithm for compression. It's downright simple, and fast, and still works. It's so simple seems like it should have been invented long ago, but it's only 10 years old.

mjevans• 5 years ago

Initial release (according to Wikipedia) 24 April 2011.

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.

speedgoose• 5 years ago

I'm very thankful we don't have software patents in Europe. And you should be thankful too if you use vlc or ffmpeg for example.

klodolph2• 5 years ago

Was LZ77 patented? As far as I can tell, patents related to various techniques in LZ77 were filed by various parties in the mid 80s and 90s, but that wouldn't matter, since LZ77 was already published at that point.

PNG was explicitly designed to be unencumbered by patents, and it was released in 1996.

cogman10• 5 years ago

At very least, tech patents really should be shortened to something like 5 years instead of being 20 years. It's nuts that the SSE2 instruction set has JUST come out of patent protection.

rosetremiere• 5 years ago

I'd like to implement it as an exercise. Any recommendation on a good resource for that purpose?

danbst• 5 years ago

This is explanation from author

http://fastcompression.blogspot.com/2011/05/lz4-explained.ht...

miohtama• 5 years ago

LZ4 explained for those who got interested http://fastcompression.blogspot.com/2011/05/lz4-explained.ht...

fwip• 5 years ago

Like sibling comment said, it's basically just LZ77 with some different parameters, tuned for speed.

pella2• 5 years ago

in the dev PG15 LZ4 WAL compression is enabled

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"

gigatexal• 5 years ago

Ooh very nice! Non-statement level replication should get a speed up here then

indeyets• 5 years ago

zstd would be great, indeed. it's much more performant and should be a great case for database

ahartmetz3• 5 years ago

zstd [...] much more performant

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.

adgjlsfhk1• 5 years ago

Would it be a good idea to use zstd's dictionary compression for databases? It seems like it could be a big win since you are compressing relatively small blocks with known structure.

drej• 5 years ago

I remember benchmarking fast compression algorithms a while back and had lz4 as one of the top contenders, but I remember going with Snappy over lz4, because I ran into some compatibility issues in lz4 land... I can't recall any specifics, but I think it was some Hadoop libraries not being able to read files lz4-compressed by some other versions of the library.

Has anyone run into the same issue? I'm considering reopening this investigation (even though I'm very happy with Snappy).

nonesuchluck• 5 years ago

Similar problem I've encountered is AWS Athena not supporting Parquet files compressed with Zstd. I re-wrote files with Snappy to get around it.

olavgg• 5 years ago

If you are using ZFS, I strongly recommend using LZ4 or ZSTD compression with PostgreSQL. Performance is still awesome. On average I get 2x compressionratio with LZ4 and 4x with ZSTD.

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.

RedShift12• 5 years ago

So disable compression in Postgres and do all compression in ZFS?

mattashii2• 5 years ago

Postgres' own compression helps with memory usage in buffers, saving IOps, so I'd keep my compression in PostgreSQL enabled

nix235• 5 years ago

I would recommend that (ATM?):

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:

https://pgtune.leopard.in.ua/#/

nikita22062• 5 years ago

Are you finding the benefits and features of ZFS worth the perf penalty? Or maybe even its compression makes it overall a faster alternative to say ext4 for a database? I’ve considered in the past running a DB in AWS with direct nvme drive used for SLOG and for L2ARC cache while the main storage would have been a mounted EBS volume, I haven’t tried it but am still wondering if it’s a better alternative to running a DB on top of bare EBS in Amazon (it should be a lot cheaper than say io2 type volume with provisioned IOPS)

throwdbaaway6• 5 years ago

With cloud network-based storage like EBS or pd-ssd, I doubt you will notice any IOPS-related perf penalty when running a RDBMS on top of ZFS. Assuming a mixed read/write workload, you will likely hit the disk write throughput limit first, as ZFS just writes ... a lot more, due to how blocks are stored internally: https://github.com/openzfs/zfs/issues/6584#issuecomment-3848...

On an innodb database, I get about 3x compress ratio with ZSTD compression, and ZFS still has to write about 2x more than EXT4.

Filligree• 5 years ago

There just isn't much of a perf penalty. It uses more CPU, yes, but with today's highly-threaded CPUs I can hardly tell there's a cost.

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.

wyager2• 5 years ago

I compared ZFS to XFS on a number of large database deployments and XFS was not uniformly faster. All databases were on fancy hardware with a bunch of U.2 SSDs, which is theoretically where XFS would be a big advantage. Postgres’ speed was very similar in both cases. Upsides of ZFS included vastly lower disk usage (with huge gains on poorly normalized databases) and also way easier/faster offsite backup than the non-ZFS Postgres backup strategy.

noctune• 5 years ago

I did something similar to this and saw a significant performance boost. It was OLAP though - so very read heavy and few to no random writes.

rob_c• 5 years ago

Was about to recommend the same. There a potentially big wins from db performance with mariadb+ZFS too, depends on the workload.

Also ZFS supports zstd now too rather than just lz4

kiririn• 5 years ago

And ZFS has dynamic block sizes which works really great together with compression

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

dekobon• 5 years ago

If you are going to be using PostgreSQL with ZFS, be sure to set:

wal_init_zero = off # zero-fill new WAL files

wal_recycle = off # recycle WAL files

Otherwise, you will see some intermittent performance issues.

traceroute66• 5 years ago

Regarding the general point about PostgreSQL and ZFS it might be noted that the community are not too keen on it (mostly from a performance perspective) as per recent mailing list thread[1].

[1]https://www.postgresql.org/message-id/flat/49ijds_99IK_4iDOH...

francoisdevlin• 5 years ago

Honest question here, slightly of topic. How do you handle indexing a compressed column? Or is this considered an anti-pattern?

roenxi• 5 years ago

An index is typically a mapping of values to database pages. Nothing about that cares whether the data has been compressed or not. It may or may not be compressed, but it is still on the page somewhere.

herehere1x• 5 years ago

To be clear, an index is compression agnostic, it's the uncompressed value that will appear in the index, or the value selected e.g. bigJsonbDoc->>'email' is that right?

iamflimflam1• 5 years ago

I implemented LZ77 when I was at university as I was trying to load gifs for an image processing project. I was absolutely blown away by the simplicity and elegance algorithm. Need to have a look at LZ4.

dunham• 5 years ago

I believe GIFs use LZW which is based on LZ78 - the one that builds a dictionary as it goes. (It's been decades since I looked at this one.)

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.pdf

minitoar• 5 years ago

Interana uses lz4 in its column store for certain data types as well. It uses the “hc” variant.

polskibus• 5 years ago

Not sure about postgresql, but in MS SQL turning on the page or row compression usually improves the performance AND storage, esp. on spinning discs, because it's faster to transfer compressed + decompress on the fly that transfer uncompressed - which is a win-win situation.

anonu• 5 years ago

What about compression for numeric-type columns?

When 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.

pella2• 5 years ago

When will PostgreSQL support column-oriented tables?

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."

derefr• 5 years ago

The columnar implementations are cool and all, but they don't really do what you'd expect columnar storage to do—i.e. make each column independently retrievable / make each column effectively its own "single data column plus shared CTID" table for the dataset. Instead, when you fetch one column of a row from e.g. a Citus "USING columnar" table, that still bakes down to the query engine fetching the entire row-tuple for that row (and then discarding most of it), which in turn means that the columnar storage engine must read in + decompress the equivalent compressed blocks for every column participating in that row.

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.

booleanbetrayal• 5 years ago

It would be nice if AWS built their PG14 offering for RDS with this supported. PG14 is available on their beta channel now, but I haven't had a chance to play. Has anyone had a chance to explore this yet?

IronWolve• 5 years ago

With limited cores or cpu speed, I've been using lz4 with bzip, gzip, pv, and to copy files over ssh.

Now pbzip2, I just use defaults with multiple cores for compression.

AtNightWeCode2• 5 years ago

The LZ77 family of compression is really nice. So many use cases. ZSTD has saved me many times. But it is perhaps not the right tool in this case. Too bad so many tools are ZIP exclusive.

AtNightWeCode2• 5 years ago

Another one of these magical down votes on this garbage forum. Seriously, what is there to down vote. LZ77 is not nice? ZSTD is the the right tool for Postgres compression even though it does not perform as well for smaller amounts of data? It is good to waste so much time in zipping things like the horrors of Docker?

unsigner2• 5 years ago

LZ4 is amazing - it's basically the same idea as LZ77, only implemented carefully, with all the dozens parameters and tradeoffs made in a certain way. Couple this with modern amounts of brute force thrown in an optimal parser (LZ4HC) and you get something quite extraordinary, on the "Pareto frontier" of compression efficiency. There's a tendency to focus on algorithms and scoff at implementation details, but LZ4 was possible as an algorithm 30 years ago, yet the implementation details crystallized to perfection only, say, five years ago.

kevin_thibedeau• 5 years ago

We also have voluminous amounts of cache and multicore processors that can decompress in cache faster than reading uncompressed data. The tech did need to catch up.

unsigner2• 5 years ago

True.

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

cma• 5 years ago

LZ4's choices may have also made less sense then due to memory latency, cache sizes, deep pipelining changing missed branch penalty, and other changes since the 70s.

The LZ4 introduced in PostgreSQL 14 provides faster compression

Related Stories