Hello hackers, I would like to propose an extension for PostgreSQL that provides compression and decompression functions in userspace utilizing the Gzip, LZ4, and Zstd algorithms.
The main purpose of this functionality is to allow the optimization of network traffic between the DB server and the client app at the expense of DB server CPU. This extension originated in an environment that collects and serves huge (multi-megabyte) text documents (e.g., XML, JSON, TXT, etc.) which are great candidates for modern compression algorithms. In this environment, a client sends compressed data to an API gateway via HTTP with the "Content-Encoding" header set to "gzip", for example. The API gateway, which runs on an internal network with the DB server, dumps it into a DB table, where a per-row trigger decompresses it and saves it as TOAST. After that, the file system takes care of compression to better utilize disk space. It also allows access to the content of the document right through DML, as well as indexing it. When a remote client requests this document, the API gateway receives it from the DB via a view where a compression function is used over the document's content to get it in an optimized form for network transfer. All in all, in our case with multi-megabyte documents, these simple functions save a lot of network traffic between our system and remote clients. Due to the fact that we process such big documents, extra care was taken: - everything is done within MemoryContextCallback; - memory is allocated only once; - gunzip is protected from decompression bombs; - added support for static Huge Memory Pages optimizing performance; - memory is released after each tuple, allowing multi-million row queries; - all functions passed tests by valgrind for possible memory leaks; - most functions can work in parallel mode (except multi-threaded zstd). I realize that proposing to add these functions right into the core would be a big stretch. Hence, my proposal is to add this extension to the /contrib directory. If agreed, I would be happy to convert this extension into the proper format for the /contrib directory. Link to the pg_z extension: https://github.com/bmironov/pg_z Best regards, Boris
