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 almost identical to PostgreSQL. Amazon is using the PostgreSQL codebase and architecture as a starting point to add their improvements and features and most of the enhancements are added at the storage layer. That means that almost every tool and extension that works with PostgreSQL also works with Aurora PostgreSQL.

Do I need to do this?

If you have not tuned the vacuum settings on your DB or on specific high-load tables, then probably yes.

But you can confirm if a repack is needed for your main tables with this query taken from the Postgres wiki.

SELECT
  current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
  ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::float/otta END)::numeric,1) AS tbloat,
  CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbytes,
  iname, /*ituples::bigint, ipages::bigint, iotta,*/
  ROUND((CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages::float/iotta END)::numeric,1) AS ibloat,
  CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
FROM (
  SELECT
    schemaname, tablename, cc.reltuples, cc.relpages, bs,
    CEIL((cc.reltuples*((datahdr+ma-
      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta,
    COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
    COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
  FROM (
    SELECT
      ma,bs,schemaname,tablename,
      (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
      (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
    FROM (
      SELECT
        schemaname, tablename, hdr, ma, bs,
        SUM((1-null_frac)*avg_width) AS datawidth,
        MAX(null_frac) AS maxfracsum,
        hdr+(
          SELECT 1+count(*)/8
          FROM pg_stats s2
          WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
        ) AS nullhdr
      FROM pg_stats s, (
        SELECT
          (SELECT current_setting('block_size')::numeric) AS bs,
          CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
          CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
        FROM (SELECT version() AS v) AS foo
      ) AS constants
      GROUP BY 1,2,3,4,5
    ) AS foo
  ) AS rs
  JOIN pg_class cc ON cc.relname = rs.tablename
  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
  LEFT JOIN pg_index i ON indrelid = cc.oid
  LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
ORDER BY wastedbytes DESC

Look for tables with a high tbloat or indexes with a high ibloat.

Umm ok... how do I do this?

Pre 1 - Install pg_repack extension on RDS

Connect to the writer instance of the Aurora Postgres 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'.

Alternatively, you could use pgxn tool to install the version that you need.

$ sudo pgxn install pg_repack==1.4.6

Instructions

Run the repack and pass one table at a time for the -t argument, or one index at a time with the -i argument.

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

And also note that you can speed up pg_repack with the -j 8 parameter to increase the number of workers. Using multiple workers helps mainly with repacking multiple indexes at the same time.

You may also use -N argument to do a dry-run.

Tip 1

The recommended way to run pg_repack is with the default behavior where it kills off any blocking queries.

On the other hand, you could use --no-kill-backend flag for a more gentle approach that attempts to obtain an exclusive lock first and fails if it cannot. If you go down this path, keep an eye on blocking queries with the following SQL.

SELECT blocked_locks.pid     AS blocked_pid,
       blocked_activity.usename  AS blocked_user,
       blocked_activity.client_addr as blocked_client_addr,
       blocked_activity.client_hostname as blocked_client_hostname,
       blocked_activity.client_port as blocked_client_port,
       blocked_activity.application_name as blocked_application_name,
       blocked_activity.wait_event_type as blocked_wait_event_type,
       blocked_activity.wait_event as blocked_wait_event,
       blocked_activity.query    AS blocked_statement,
       blocking_locks.pid     AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocking_activity.client_addr as blocking_user_addr,
       blocking_activity.client_hostname as blocking_client_hostname,
       blocking_activity.client_port as blocking_client_port,
       blocking_activity.application_name as blocking_application_name,
       blocking_activity.wait_event_type as blocking_wait_event_type,
       blocking_activity.wait_event as blocking_wait_event,
       blocking_activity.query   AS current_statement_in_blocking_process
 FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.granted ORDER BY blocked_activity.pid

If you detect something that is blocking it, use SELECT pg_cancel_backend(pid) to terminate the blocking operation and allow pg_repack to proceed.

In practice, it is better to use the default mode and let pg_repack kill a few queries to get the benefit of improving overall database health.

Tip 2

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!

RDS waits

If you see this happening, you should identify blocking queries and kill them as described in the previous section.

Tip 3

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.

Tip 4

Large tables can take a long time to repack - even many days. During this time, Postgres may start to vacuum your table itself. This will just end up slowing things down.

So before we run the repack for a particularly large table, we usually want to disable autovacuum on it.

ALTER TABLE public.artifacts SET (autovacuum_enabled = false);

After pg_repack is done, make sure to enable the autovacuum again!

ALTER TABLE public.artifacts SET (autovacuum_enabled = true);

Note - this setting only stops regular autovacuums. It does not stop autovacuum to prevent wraparound.

Troubleshooting

I ran into an issue where the database was not accepting the password for pg_repack, even though psql accepted the same password. This is likely caused by a bug in the pg_repack implementation. 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-service.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

Cleanup

If pg_repack run and completed successfully, then there is nothing else to do!

If something does goes wrong, you should be prepared to clean up any remaining triggers from pg_repack so that you don't affect database performance.

The best way to do this is simply with -

DROP EXTENSION pg_repack CASCADE;