It’s mostly DDOS attacks though, not the influx of normal users.

Really? As site_aggregates table getting 1500 rows updated on every single new comment and post local insert is just the tip of the iceberg of how nobody has scrutinized the PostgreSQL performance. Thank you to lemmy.ca last weekend for looking at AUTO_EXPLAIN on their data.