Aurora Postgres - Database per Tenant architecture issues

Question

We have a multi-tenant SaaS app. We had been using a shared DB for all our tenants with TenantId as foreign key in all the tables. Everything had been working fine, till our business required DB (or Schema) per Tenant architecture.

We upgraded our backend (ASP.NET Core 8) to handle this architecture and ambitiously migrated all our tenants to a separate DB on a cluster. We have around 1000 DBs on the cluster and since our migration life has been very difficult. Where with the same amount of data and load even 32 GB 4 CPU r7g.xl instance seemed over-provisioned, now even 16 CPU 128 GB r7g.4xl AWS instance sometimes seems underprovisoned.

Out of 1000 DBs more than half won't even have more than 100 MB data. Few can have data in few GBs.

Since we had been using AWS DMS (serverless) also the replication had been inactive for some time. So came the warnings in vacuum after some time:

WARNING: oldest xmin is far in the past

When we checked all the possible reasons, it turned out replication slots are holding the xmin. Here is what the following query returns:

SELECT c.relnamespace::regnamespace as schema_name, c.relname as table_name,
greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age,
2^31-1000000-greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as remaining
FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm') ORDER BY 4;

Result:

The value is increasing, we tried deleting the replication slots two times, and each time it caused a downtime and cluster couldn't recover and we had to resort to creating a new cluster from backup. So the moment we delete the inactive replication slots this happens:

The insert/update queries which otherwise run fine, suddenly just after deleting the replicating slots start showing LW locks.

Here is how it looks like now (slots not deleted):

We have come to believe it is a mistake to have more than 300 DBs per cluster. So we will be using multiple clusters with around 300 DBs per cluster.

But what should we do now, and why deleting the replication slots triggers these locks?

Answer 1

The stale replication slots have prevented autovacuum from performing its maintenance job. Since your tables now contain unfrozen rows older than autovacuum_freeze_max_age transactions, autovacuum is launching anti-wraparound autovacuum runs which seem to consume enough resources to affect you application.

You could try the following: Set autovacuum_freeze_max_age to 1500000000 and vacuum_freeze_table_age and vacuum_freeze_min_age to 500000000. Then delete the stale replication slots. The changed settings will keep autovacuum from starting intense activity. Essentially, you are buying some time. Use that time to manually VACUUM (FREEZE) all tables for which age(pg_class.relfrozenxid) exceeds 100 million. See that you get done with that before you hit the increased limits. If you are successful, age(pg_database.datfrozenxid) will go below 200 million. Once you are done, reset the parameters to their default values.

If you don't take action, your database will at some point refuse new transactions, and you will have to take an extended down time to VACUUM the affected tables.