One thing we've seen with DuckDB, which they also mention in their docs, is that it's possible to 'over parallelize'. Beyond some number of cores, the overall workflow measured in wall time actually slows down when you add more CPUs, because of the overhead of parallelizing, despite them running at 100%.
We observe this even if we pull the data from S3 onto the local SSD before processing,
Duckdb might not be the reason, the real reason might be AWS.
It's been two years since I worked on this, but AWS Nitro might be triggering a high CPU Steal % when all the cores are busy at the same time.
You can do taskset -c 1-191 <server-process> and get a tiny bit of breathing room there.
I almost always do this above 32 cores, since this lets me always have a nice responsive ssh connection when the rest of the CPUs are churning high.
The more cores you have, the less of a problem sparing 1 core becomes.