Is it possible to change workmem for postgres?

[Sharoon Thomas]

I see that it's not one of the supported flags and the defaults are not optimized to the memory on the spinned up database instance.

[Karthick (Cloud Platform Support)]

Hello Sharoon,

Please note that Cloud SQL for PostgreSQL is still in Beta. And as you are aware already that currently the work_mem parameter is not in the list of settable flags . You can  still edit by Alter System as per this document, but we do not guarantee as it could affect the behavior of the instance. And the parameter change is per session basis but not at system level. You can also visit this document for further understanding.   

[George Lubaretsi]

Do you mean the following command?

ALTER SYSTEM SET work_mem;

Because ALTER SYSTEM requires superuser privileges. Which is not available in Cloud SQL for Postgres.

[Karthick (Cloud Platform Support)]

Hello George,

My apologies if I misspoke about ALTER SYSTEM command. It does require superuser access. I performed the test in my lab system and it is possible to set work_mem  “per-session” without superuser privileges, Below is the output :

postgres=> set work_mem = '5MB';

SET

postgres=> show work_mem;

work_mem

----------

5MB

(1 row)

As you mentioned, since Cloud SQL is a managed instance it is not possible to perform this setup at the system level. Please note changing the “work_mem” might drive the system into an undesirable state.