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:

postgres@debian11pg:/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)

postgres@debian11pg:/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:

postgres@debian11pg:/home/postgres/ [pgdev] mkdir /var/tmp/dump
postgres@debian11pg:/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
postgres@debian11pg:/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:

postgres@debian11pg:/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
postgres@debian11pg:/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?:

postgres@debian11pg:/home/postgres/ [pgdev] rm -rf /var/tmp/dump/lz4
postgres@debian11pg:/home/postgres/ [pgdev] rm -rf /var/tmp/dump/gzip/
postgres@debian11pg:/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
postgres@debian11pg:/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
postgres@debian11pg:/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
postgres@debian11pg:/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: 3,149