dbi Blog

The last releases of PostgreSQL already introduced new options for compression, see here and here. PostgreSQL 16 will build on top of that and will come with LZ4 compression for pg_dump. As always, there is no better way of showing a feature than a small demo.

To have something to dump, we’ll create a small database using pgbench:

[email protected]:/home/postgres/ [pgdev] psql -c "create database dump" -c "select version()"
CREATE DATABASE
                                               version                                                
------------------------------------------------------------------------------------------------------
 PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)

[email protected]:/home/postgres/ [pgdev] pgbench -i -s 100 dump
dropping old tables...
creating tables...
generating data (client-side)...
10000000 of 10000000 tuples (100%) done (elapsed 28.91 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 36.32 s (drop tables 0.00 s, create tables 0.02 s, client-side generate 29.17 s, vacuum 0.57 s, primary keys 6.56 s).

Let’s dump that using the traditional way using gzip at the highest level:

[email protected]:/home/postgres/ [pgdev] mkdir /var/tmp/dump
[email protected]:/home/postgres/ [pgdev] time pg_dump --format=d --compress=gzip:9 --file=/var/tmp/dump/gzip dump

real    0m12.332s
user    0m7.687s
sys     0m0.271s
[email protected]:/home/postgres/ [pgdev] ls -lha /var/tmp/dump/gzip/
total 27M
drwx------ 2 postgres postgres 4.0K Feb 24 09:02 .
drwxr-xr-x 3 postgres postgres 4.0K Feb 24 09:02 ..
-rw-r--r-- 1 postgres postgres   25 Feb 24 09:02 3347.dat.gz
-rw-r--r-- 1 postgres postgres 1.8K Feb 24 09:02 3348.dat.gz
-rw-r--r-- 1 postgres postgres  27M Feb 24 09:02 3349.dat.gz
-rw-r--r-- 1 postgres postgres  208 Feb 24 09:02 3350.dat.gz
-rw-r--r-- 1 postgres postgres 4.0K Feb 24 09:02 toc.dat

This took around 12 seconds and the result is around 27MB. Let’s do the same using lz4:

[email protected]:/home/postgres/ [pgdev] time pg_dump --format=d --compress=lz4:9 --file=/var/tmp/dump/lz4 dump

real    0m10.803s
user    0m6.246s
sys     0m0.271s
[email protected]:/home/postgres/ [pgdev] ls -lha /var/tmp/dump/lz4
total 48M
drwx------ 2 postgres postgres 4.0K Feb 24 09:06 .
drwxr-xr-x 4 postgres postgres 4.0K Feb 24 09:06 ..
-rw-r--r-- 1 postgres postgres   20 Feb 24 09:06 3347.dat.lz4
-rw-r--r-- 1 postgres postgres 4.4K Feb 24 09:06 3348.dat.lz4
-rw-r--r-- 1 postgres postgres  48M Feb 24 09:06 3349.dat.lz4
-rw-r--r-- 1 postgres postgres  415 Feb 24 09:06 3350.dat.lz4
-rw-r--r-- 1 postgres postgres 4.0K Feb 24 09:06 toc.dat

This was a bit faster, but also took more space. How does it look like using a lower level?:

[email protected]:/home/postgres/ [pgdev] rm -rf /var/tmp/dump/lz4
[email protected]:/home/postgres/ [pgdev] rm -rf /var/tmp/dump/gzip/
[email protected]:/home/postgres/ [pgdev] time pg_dump --format=d --compress=gzip:2 --file=/var/tmp/dump/gzip dump

real    0m7.819s
user    0m3.191s
sys     0m0.203s
[email protected]:/home/postgres/ [pgdev] time pg_dump --format=d --compress=lz4:2 --file=/var/tmp/dump/lz4 dump

real    0m5.426s
user    0m0.906s
sys     0m0.236s
[email protected]:/home/postgres/ [pgdev] ls -lha /var/tmp/dump/gzip
total 28M
drwx------ 2 postgres postgres 4.0K Feb 24 09:10 .
drwxr-xr-x 4 postgres postgres 4.0K Feb 24 09:10 ..
-rw-r--r-- 1 postgres postgres   25 Feb 24 09:10 3347.dat.gz
-rw-r--r-- 1 postgres postgres 2.3K Feb 24 09:10 3348.dat.gz
-rw-r--r-- 1 postgres postgres  28M Feb 24 09:10 3349.dat.gz
-rw-r--r-- 1 postgres postgres  206 Feb 24 09:10 3350.dat.gz
-rw-r--r-- 1 postgres postgres 4.0K Feb 24 09:10 toc.dat
[email protected]:/home/postgres/ [pgdev] ls -lha /var/tmp/dump/lz4/
total 50M
drwx------ 2 postgres postgres 4.0K Feb 24 09:10 .
drwxr-xr-x 4 postgres postgres 4.0K Feb 24 09:10 ..
-rw-r--r-- 1 postgres postgres   20 Feb 24 09:10 3347.dat.lz4
-rw-r--r-- 1 postgres postgres 4.5K Feb 24 09:10 3348.dat.lz4
-rw-r--r-- 1 postgres postgres  50M Feb 24 09:10 3349.dat.lz4
-rw-r--r-- 1 postgres postgres  426 Feb 24 09:10 3350.dat.lz4
-rw-r--r-- 1 postgres postgres 4.0K Feb 24 09:10 toc.dat

The same story: gzip gives you a better compression but lz4 gives you faster compression. Now you have the choice.

Post Views: 544