Sample Header Ad - 728x90

Why are Amazon Aurora RDS Postgres queries with SET session variables taking forever to run?

1 vote
0 answers
71 views
I an trying to pass some extra metadata from my dotnet core api app alongside each DB query via a couple SET session variables (aka Customized Options ), EG:
SET app.name = 'Test';
SET app.user_id = '';
SET app.session_id = '';

SELECT now();
This works great locally when run from my API container in Docker against a local Postgres 16.4 instance also running in Docker. However, when deployed to our CI/CD AWS environment which is connected to an Amazon Aurora Postgres 16.4 RDS instance, the performance is terrible. It seems like each SET statement adds (a variable) ~1-4secs of delay... making even the most basic SQL statement take 10-20secs to complete. If I comment out the SET statements the impact disappears and the simple queries are instantaneous. This feels like either some major downside of AWS Aurora's flavor of Postgres, or some magic setting that I am missing that controls for how these values are processed. I can't find anything on the AWS-side that warns against this usage, and Postgres articles I find seem to promote using SET vars in this manner.
Asked by Ian (11 rep)
Oct 29, 2024, 09:32 PM