Hope this information is useful;
Running Matomo with AWS RDS Serverless is for us a cost-effective way to handle our dev-requirements for Matomo. We did encounter an issue where partitioning the tables would have helped our usecase.

In some rare cases, our serverless DB ran out of locale storage: SQLSTATE[HY000]: General error: 3 Error writing file '/rdsdbdata/tmp/MYB7XJVs' (Errcode: 28 - No space left on device). This if of course hard to fix when it is serverless :)

AWS Support gave us two options to solve this:

• Increase the number of ACUs when expecting queries that consumes a lot of local storage
• Partition your tables into smaller sizes so they do not consume all the local storage

We opted for option 1 for now, option 2 would be nice to have upstream, and we might implement it but we would rather work on a as-close-to-upstream config as possible.

I'm not sure how option 2 this would impact write or read performance, since it probably depends on the storage backend and database engine. If the DB could read from multiple partitions when doing large queries I would imagine that would help the overall performance, given the storage backend is supportive of it.

You could try to partition based on log_visit.visit_last_action_time and log_link_visit_action.server_time for example but we haven't run any tests yet. Maybe you could ask in the forum or check if other people already talked about it: https://forum.matomo.org

Good post around this: http://mysql.rjweb.org/doc.php/partitionmaint