Using pg_repack with AWS Postgres RDS

If you are using PostgresSQL as your database, you will be familiar with the concepts of table bloat and using CLUSTER indexes to improve scan performance. pg_repack is a very useful tool that allows tables to be repacked (equivalent to a CLUSTER or VACUUM FULL depending on configuration) without taking any exclusive locks, and therefore leaving the table online for both reads and writes.

For a while, users of Amazon’s RDS postures service were left unable to use pg_repack, as the extension was not whitelisted by RDS. Luckily support for the pg_repack extension was added to RDS mid 2017. However, multiple users have reported issues running pg_repack on RDS due to the unusual superuser permission setup on RDS (its not true superuser, it’s a proxy rds_superuser that’s almost the same but not quite). Therefore, we experienced this issue as well, when pg_repack was running, some other transactions would fail with this error:

1
2
3
ProgrammingError: permission denied for relation log_29334438
CONTEXT: SQL statement "INSERT INTO repack.log_29334438(pk, row)
...

pg_repack works by creating a temporary ‘log’ table in the repack schema for each repack operation, that holds updates applied to the source table during the operation. A database trigger is used to log these entries, and at the end of the table rebuild these logged modifications are applied to the repacked table. The core of the issue appears to be that the log tables did not have the right permissions when created on RDS via the rds_superuser role. For us the following fix works well:

1
2
3
CREATE EXTENSION pg_repack;
ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT INSERT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT USAGE, SELECT ON SEQUENCES TO PUBLIC;

Therefore, assigning PUBLIC write access to repack log tables created in the future.

If you’re interested in solving problems like this to help make the way retailers communicate with their customers more personalised and engaging, we are hiring! Check out our careers page.