I have a critical application deployed in AWS RDS; the DB engine is PostgreSQL version 10.18. The architecture is unusual, because we're talking about medical data. This means that all the doctors connecting the database (through a PGBouncer) have their own schema; arount 4000 doctors means around 4000 schemas, with the same structure but different data obviously. Around 2000 doctors are actually connecting every day.
The Instance type is db.r5.4xlarge and there's a total buffer of around 100 GB. Still, there are a lot of hits on the disk, this means that on the performance insight side, I can actually see that the greater AAS is because of a metric called "DataFileRead", which (as far as I know) means that the data couldn't be fetched from the buffer and the Engine went for the disk. There's an average value of 60 AAS on DataFileRead.
This is not really the problem; I'm trying to apply some optimizations creating the right indexes for example, the problem is that on the TOP SQL tab I cannot see any data right after the query (like Calls/sec, Rows/sec, Blk hits/sec, etc.).
Does this means that the limit of 5000 rows of the pg_stat_statements is too low? Also I can't find any information about the impact on the database performance about having the statistics enabled. Does it increase critically increasing the limit of 5000 records? Can I go up to 50000 for example?
