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!
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;