Controlling resource utilization

Yes sir

SET statement_timeout TO '<milliseconds>'

Atte
JRBM

El mar, 16 abr 2024 a las 14:46, yudhi s (<learnerdatabase99@gmail.com>)
escribió:

On Wed, 17 Apr, 2024, 1:32 am Juan Rodrigo Alejandro Burgos Mella, <
rodrigoburgosmella@gmail.com> wrote:

Yes sir

SET statement_timeout TO '<milliseconds>'

Atte
JRBM

El mar, 16 abr 2024 a las 14:46, yudhi s (<learnerdatabase99@gmail.com>)
escribió:

Hi ,
We want to have controls around the DB resource utilization by the adhoc
user queries, so that it won't impact the application queries negatively.
Its RDS postgresql database version 15.4.

Saw one parameter as statement_timeout which restricts the queries to not
run after a certain time duration and queries will be automatically
killed/cancelled. However, I don't see any other options to set this at
user level, rather it's getting set for all or at session level. So I want
to know if there exists, anyway to control the database resource
utilization specific to users?

Regards
Yudhi

This will set the timeout at session level. However, We want to understand,
if it can be done at user/role level, so that any such adhoc user queries
can be auto killed or cancelled after the set time.

In postgreSQL, that can be done at a session level, or at a general level
(in the postgresql.conf configuration file)

Atte
JRBM

El mar, 16 abr 2024 a las 15:18, yudhi s (<learnerdatabase99@gmail.com>)
escribió:

De: "Juan Rodrigo Alejandro Burgos Mella" <rodrigoburgosmella@gmail.com>
À: "yudhi s" <learnerdatabase99@gmail.com>
Cc: "pgsql-general" <pgsql-general@lists.postgresql.org>
Envoyé: Mardi 16 Avril 2024 22:29:35
Objet: Re: Controlling resource utilization

In postgreSQL, that can be done at a session level, or at a general level (in
the postgresql.conf configuration file)

Atte
JRBM

El mar, 16 abr 2024 a las 15:18, yudhi s (< [ mailto:learnerdatabase99@gmail.com
| learnerdatabase99@gmail.com ] >) escribió:

On Wed, 17 Apr, 2024, 1:32 am Juan Rodrigo Alejandro Burgos Mella, < [
mailto:rodrigoburgosmella@gmail.com | rodrigoburgosmella@gmail.com ] > wrote:

Yes sir
SET statement_timeout TO '<milliseconds>'

Atte
JRBM

El mar, 16 abr 2024 a las 14:46, yudhi s (< [ mailto:learnerdatabase99@gmail.com
| learnerdatabase99@gmail.com ] >) escribió:

Hi ,
We want to have controls around the DB resource utilization by the adhoc user
queries, so that it won't impact the application queries negatively. Its RDS
postgresql database version 15.4.
Saw one parameter as statement_timeout which restricts the queries to not run
after a certain time duration and queries will be automatically
killed/cancelled. However, I don't see any other options to set this at user
level, rather it's getting set for all or at session level. So I want to know
if there exists, anyway to control the database resource utilization specific
to users?
Regards
Yudhi

This will set the timeout at session level. However, We want to understand, if
it can be done at user/role level, so that any such adhoc user queries can be
auto killed or cancelled after the set time.

Or also at role/user level :
ALTER ROLE < your - username > SET statement_timeout = '<time_unit>' ;
Regards
Gilles

On Wed, 17 Apr, 2024, 12:40 pm , <gparc@free.fr> wrote:

------------------------------

*De: *"Juan Rodrigo Alejandro Burgos Mella" <rodrigoburgosmella@gmail.com>
*À: *"yudhi s" <learnerdatabase99@gmail.com>
*Cc: *"pgsql-general" <pgsql-general@lists.postgresql.org>
*Envoyé: *Mardi 16 Avril 2024 22:29:35
*Objet: *Re: Controlling resource utilization

ALTER ROLE <your-username> SET statement_timeout = '<time_unit>';

Regards
Gilles

Thank you so much. That helps.

This statement is succeeding for user as I executed. So it's working I
believe.

But to immediately verify without manually running queries and waiting for
it to be auto killed to confirm, Is there any system table which we can
verify to see if this setting is effective, as because I don't see any such
columns in pg_user or pg_role which shows the statement_timeout.

And is there a way to put similar cap/restrictions on other db resources
like cpu, memory, I/O at specific user/role level?

De: "yudhi" <learnerdatabase99@gmail.com>
À: "gparc" <gparc@free.fr>
Cc: "Juan Rodrigo Alejandro Burgos Mella" <rodrigoburgosmella@gmail.com>,
"pgsql-general" <pgsql-general@lists.postgresql.org>
Envoyé: Mercredi 17 Avril 2024 09:42:49
Objet: Re: Controlling resource utilization

On Wed, 17 Apr, 2024, 12:40 pm , < [ mailto:gparc@free.fr | gparc@free.fr ] >
wrote:

De: "Juan Rodrigo Alejandro Burgos Mella" < [
mailto:rodrigoburgosmella@gmail.com | rodrigoburgosmella@gmail.com ] >
À: "yudhi s" < [ mailto:learnerdatabase99@gmail.com |
learnerdatabase99@gmail.com ] >
Cc: "pgsql-general" < [ mailto:pgsql-general@lists.postgresql.org |
pgsql-general@lists.postgresql.org ] >
Envoyé: Mardi 16 Avril 2024 22:29:35
Objet: Re: Controlling resource utilization

ALTER ROLE <your-username> SET statement_timeout = '<time_unit>';
Regards
Gilles

Thank you so much. That helps.

This statement is succeeding for user as I executed. So it's working I believe.

But to immediately verify without manually running queries and waiting for it to
be auto killed to confirm, Is there any system table which we can verify to see
if this setting is effective, as because I don't see any such columns in
pg_user or pg_role which shows the statement_timeout.

And is there a way to put similar cap/restrictions on other db resources like
cpu, memory, I/O at specific user/role level?

To verify the setting, you can use this command in psql : \drds <your-username>

Concerning system resources like CPUs it's not possible.
You can use pg_settings view to see which setting you can change and in which context : [ https://www.postgresql.org/docs/current/view-pg-settings.html | https://www.postgresql.org/docs/current/view-pg-settings.html ]

Regards
Gilles