Is there a way to assign PostgreSQL users' a priority level?

Question

Does postgresql have the ability to assign users of a database "priorities" in order to decide how much resources (memory, CPU,..) we allocate for the user?

Think about it like subscription modes in Cloud services: clients who has premium subscription should be allocated enough (larger) resources to meet SLA, and users who has lower subscription plans can experience some performance degradation because we don't allocate a lot of resources.

I know that we can tune some paramets like work_mem or maintenance_work_men in postgres but these are "process" level parameters, not user level.

• jjanes: Process are serving the queries of users, so what is the distinct you are you making in your last sentence?
• Frank Heikens: No, not possible. You can tune work_mem, but giving someone less work_mem could result in forcing their queries to use more IO. And that could hurt your entire system pretty bad. So what is your real (performance) problem?
• Sabaa: @FrankHeikens, I'm doing some research on resource allocation in PostgreSQL. My idea is to tailor memory allocation based on users' needs to avoid unnecessary resource wastage. I'm exploring prioritizing resource allocation for certain users.
• Frank Heikens: When you set for example work_mem to 1GB, each step in the query execution that needs memory is allowed to use 1GB. If your query needs just 1MB, it uses just 1MB and leaves the rest for others. There is no waste in resources. This is what the DBMS manages for you and it does a very good job.

Answer 1

You can set work_mem etc at a per-user level with ALTER USER ... SET ...

https://www.postgresql.org/docs/current/sql-alteruser.html

But - that may not do what you want. If user A's query now spills to disk and takes 5 minutes to run, you may be affecting user B's services more than if you had given A more resources. If user A takes locks then that could make everybody's life difficult...