5

As an analyst I love having the ability to turn up the work_mem on occasion when a big sort or index build is in the offing.

As a DBA, the idea of random users being able to request an arbitrarily large amount of system resources at run-time is a little scary. I confirmed that a basically permission-less fresh-created user, could request as much work_mem as they wished. It would presumably take only a few greedy users at once to starve out other users, which is scary.

Is there any way to limit the work_mem a user can request?

  • 1
    No there is no way to limit that. And in the end it's not that important. A user can run select * from one_billion_table cross join one_billion_table cross join one_billion_table order by some_column and use up an arbitrarily large amount of system resource as well. – a_horse_with_no_name Sep 17 '18 at 20:24
0

According to the PostgreSQL Wiki this sort of thing isn't possible.

PostgreSQL has no facilities to limit what resources a particular user, query, or database consumes, or correspondingly to set priorities such that one user/query/database gets more resources than others. It's necessary to use operating system facilities to achieve what limited prioritization is possible.

As suggested in the text above, you could try using operating system facilities to achieve limits such as that. For example, ulimit can be used to create process-specific resource limits.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.