Hi there,
I'm running PostgreSQL 9.6.10 on Debian and reported some performance issues with "SET ROLE" a while ago:
Those long running queries, especially "SET ROLE", still persist until today.
I just recently increased the number of PostgreSQL instances and added an AWS Aurora Cluster (9.6.10 with db.r4.2xlarge). AWS offers a tool named "Performance Insights" and shows some really high CPU usage for "SET ROLE" queries. See attached image or just click here:
The setup is the same as reported in the above mentioned post: I use more than a thousand roles per PostgreSQL instance and set the role for every connection before executing actual statements. My pg_class consists of 1,557,824 rows as every role has its own schema with more than 300 tables.
I'm currently building a simple docker test setup with pg_bench to reproduce the decreased performance when executing "SET ROLE".
I'm aware that AWS Aurora is a proprietary version of PostgreSQL. But I somehow have the feeling that my experienced abnormalities with "SET ROLE" in vanilla PostgreSQL occur in AWS Aurora as well. And the high CPU usage reported by "Performance Insights" may be a hint of a performance issue in PostgreSQL.
I'm quite lost when reading the PostgreSQL source code (due to my inability to read it :)) but maybe one of you guys has an idea about that?
Regards,
Ulf
Attachment:
set-role.png
Description: PNG image
