Aurora PostgreSQL CPU usage stuck at 100% for 8 hours
There is a long running query that keeps your CPU busy and keeps autovacuum from cleaning up dead tuples. A long queries implies a long transaction.

The query takes long because of all the dead tuples.

Kill the long running query with pg_terminate_backend, and things will go back to normal.

To prevent long running queries, set statement_timeout.