This post details how to reduce bloat in Postgres/Aurora tables for the purposes of increasing performance and reducing costs.

Is pg_repack compatible with AWS Aurora?

First, let's answer the question "Is pg_repack compatible with AWS Aurora?"

The answer is yes! Amazon Aurora PostgreSQL Edition is compatible with the pg_repack tool. It has been confirmed in the popular AWS blog post.

I can say from my experience that the internals of AWS Aurora are generally very similar to PostgreSQL. I would guess that Amazon is using the PostgreSQL codebase and architecture as a starting point to add their improvements and features.

Do I need to do this?

Probably yes. Since you probably did not tune the vacuum settings on your DB. But you can confirm if a repack is needed for your main tables with this query from the Postgres wiki. Look for tables with a high tbloat.

Umm ok... how do I do this?

Pre 1 - Install pg_repack extension on RDS

Connect to the database using psql and install the extension.

service_db=> create extension pg_repack;
CREATE EXTENSION

Pre 2 - Check pg_repack version

Then we need to check the version of the extension.

service_db=> \dx pg_repack
List of installed extensions
Name    | Version | Schema |                         Description
-----------+---------+--------+--------------------------------------------------------------
pg_repack | 1.4.6   | public | Reorganize tables in PostgreSQL databases with minimal locks
(1 row)

And from the output, we can see that the version is 1.4.6.

Pre 3 - Install pg_repack client locally

Download the source code for the same version and build it.

$ cd pg_repack-1.4.6
$ sudo make install
Password:
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument  -Os -mmacosx-version-min=10.12 -I/Applications/Postgres.app/Contents/Versions/13/include -DREPACK_VERSION=1.4.6 -I. -I./ -I/Applications/Postgres.app/Contents/Versions/13/include/postgresql/server -I/Applications/Postgres.app/Contents/Versions/13/include/postgresql/internal -I/Applications/Postgres.app/Contents/Versions/13/share/icu -I/Applications/Postgres.app/Contents/Versions/13/include/libxml2  -I/Applications/Postgres.app/Contents/Versions/13/include  -c -o pg_repack.o pg_repack.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument  -Os -mmacosx-version-min=10.12 -I/Applications/Postgres.app/Contents/Versions/13/include -DREPACK_VERSION=1.4.6 -I. -I./ -I/Applications/Postgres.app/Contents/Versions/13/include/postgresql/server -I/Applications/Postgres.app/Contents/Versions/13/include/postgresql/internal -I/Applications/Postgres.app/Contents/Versions/13/share/icu -I/Applications/Postgres.app/Contents/Versions/13/include/libxml2  -I/Applications/Postgres.app/Contents/Versions/13/include  -c -o pgut/pgut.o pgut/pgut.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument  -Os -mmacosx-version-min=10.12 -I/Applications/Postgres.app/Contents/Versions/13/include -DREPACK_VERSION=1.4.6 -I. -I./ -I/Applications/Postgres.app/Contents/Versions/13/include/postgresql/server -I/Applications/Postgres.app/Contents/Versions/13/include/postgresql/internal -I/Applications/Postgres.app/Contents/Versions/13/share/icu -I/Applications/Postgres.app/Contents/Versions/13/include/libxml2  -I/Applications/Postgres.app/Contents/Versions/13/include  -c -o pgut/pgut-fe.o pgut/pgut-fe.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument  -Os -mmacosx-version-min=10.12 pg_repack.o pgut/pgut.o pgut/pgut-fe.o  -L/Applications/Postgres.app/Contents/Versions/13/lib  -L/Applications/Postgres.app/Contents/Versions/13/lib  -L/Applications/Postgres.app/Contents/Versions/13/lib -Wl,-dead_strip_dylibs   -L/Applications/Postgres.app/Contents/Versions/13/lib -lpq -L/Applications/Postgres.app/Contents/Versions/13/lib/postgresql -lpgcommon -lpgport -lssl -lcrypto -lz -lreadline -lm -o pg_repack
/bin/sh /Applications/Postgres.app/Contents/Versions/13/lib/postgresql/pgxs/src/makefiles/../../config/install-sh -c -d '/Applications/Postgres.app/Contents/Versions/13/bin'
/usr/bin/install -c  pg_repack '/Applications/Postgres.app/Contents/Versions/13/bin'
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument  -Os -mmacosx-version-min=10.12  -DREPACK_VERSION=1.4.6 -I. -I./ -I/Applications/Postgres.app/Contents/Versions/13/include/postgresql/server -I/Applications/Postgres.app/Contents/Versions/13/include/postgresql/internal -I/Applications/Postgres.app/Contents/Versions/13/share/icu -I/Applications/Postgres.app/Contents/Versions/13/include/libxml2  -I/Applications/Postgres.app/Contents/Versions/13/include  -c -o repack.o repack.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument  -Os -mmacosx-version-min=10.12  -DREPACK_VERSION=1.4.6 -I. -I./ -I/Applications/Postgres.app/Contents/Versions/13/include/postgresql/server -I/Applications/Postgres.app/Contents/Versions/13/include/postgresql/internal -I/Applications/Postgres.app/Contents/Versions/13/share/icu -I/Applications/Postgres.app/Contents/Versions/13/include/libxml2  -I/Applications/Postgres.app/Contents/Versions/13/include  -c -o pgut/pgut-spi.o pgut/pgut-spi.c
awk '/^[^#]/ {printf "_%s\n",$1}' exports.txt >exports.list
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument  -Os -mmacosx-version-min=10.12  -bundle -multiply_defined suppress -o pg_repack.so repack.o pgut/pgut-spi.o -L/Applications/Postgres.app/Contents/Versions/13/lib   -L/Applications/Postgres.app/Contents/Versions/13/lib  -L/Applications/Postgres.app/Contents/Versions/13/lib -Wl,-dead_strip_dylibs   -bundle_loader /Applications/Postgres.app/Contents/Versions/13/bin/postgres
sed 's,REPACK_VERSION,1.4.6,g' pg_repack.sql.in \
| sed 's,relhasoids,false,g'> pg_repack--1.4.6.sql;
sed 's,REPACK_VERSION,1.4.6,g' pg_repack.control.in > pg_repack.control
/bin/sh /Applications/Postgres.app/Contents/Versions/13/lib/postgresql/pgxs/src/makefiles/../../config/install-sh -c -d '/Applications/Postgres.app/Contents/Versions/13/lib/postgresql'
/bin/sh /Applications/Postgres.app/Contents/Versions/13/lib/postgresql/pgxs/src/makefiles/../../config/install-sh -c -d '/Applications/Postgres.app/Contents/Versions/13/share/postgresql/extension'
/bin/sh /Applications/Postgres.app/Contents/Versions/13/lib/postgresql/pgxs/src/makefiles/../../config/install-sh -c -d '/Applications/Postgres.app/Contents/Versions/13/share/postgresql/extension'
/usr/bin/install -c -m 755  pg_repack.so '/Applications/Postgres.app/Contents/Versions/13/lib/postgresql/pg_repack.so'
/usr/bin/install -c -m 644 .//pg_repack.control '/Applications/Postgres.app/Contents/Versions/13/share/postgresql/extension/'
/usr/bin/install -c -m 644  pg_repack--1.4.6.sql pg_repack.control '/Applications/Postgres.app/Contents/Versions/13/share/postgresql/extension/'
make[1]: Nothing to be done for `install'.

Instructions

Now everything is ready, and we are ready to speed up the database. Or it could all start on fire.

Run the repack from your client machine and pass one table at a time for the -t argument.

$ ./pg_repack -d service_db -h service-cluster.cluster-service.us-east-2.rds.amazonaws.com -U master -p 5432 -t public.artifacts -k --no-kill-backend

Note that you may also use -N argument to do a dry-run.

Also note that we use --no-kill-backend setting so that pg_repack doesn’t kill other queries. If not provided, the default behavior is to kill off other queries which may be interfering with the repack.

And also note that you can speed up pg_repack with the -j 8 parameter to increase the number of threads doing the work. Large tables take a long time to repack and your firewall or VPN may disconnect. So using more threads is a must.

Tip 1

While the pg_repack is designed to be run in a production environment, there is still a chance it may lock up and cause an outage. Make sure to keep an eye on the repack as it runs.

When I ran the tool in a production environment, it blocked almost all new connections that tried to query the table. Looking at RDS Performance Insights for anything unusual, I found that the main copy query from pg_repack was locked on another query. Even worse, new select statements that were coming in were all locked too. Yes, even simple selects!

If you see this happening, you should identify blocking queries and kill them.

Tip 2

Running this tool requires a long-lived connection to the database. If the connection dies due to a network issue, bad things will happen! You should be prepared to do manual cleanup. To minimize risk and keep your blood pressure within nominal levels, you should use something like screen or tmux.

Troubleshooting

I ran into an issue where the database was not accepting the password, even though it was being copy/pasted into the terminal. This is caused by differences in shells and their special character handling. The solution is to set the password in the PGPASSWORD env variable.

$ export PGPASSWORD='my~p@as\s'
$ time pg_repack -d service_db -h service-cluster.cluster-catsb00ws0jl.us-east-2.rds.amazonaws.com -U master -p 5432 -t public.artifacts -k
INFO: repacking table "public.artifacts"
pg_repack -d service_db -h  -U master -p 5432 -t  -k  0.02s user 0.01s system 0% cpu 58.263 total