F.51. pgpro_rp — resource prioritization #
pgpro_rp is a Postgres Pro Enterprise extension for resource prioritization.
On systems with limited resources or under heavy load, you may need to prioritize transaction execution, so that some transactions are executed more quickly than the others. For example, you may want to execute simple user queries as fast as possible, even if it delays less urgent tasks, such as complex OLAP queries that may be running at the same time. Postgres Pro Enterprise enables you to assign a resource prioritization plan to particular sessions, which can slow it down based on the amount of CPU, I/O read, and I/O write resources this session consumes as compared to other sessions. These parameters can take weight values 1, 2, 4, and 8. The higher the value, the more resources the session can use. By default, all sessions have weight 4 for all types of resources. The resource prioritization plan contains a set of these prioritization parameters as a jsonb and can be assigned to any user or role.
The plan is selected during session creation using a login event trigger as follows:
First, pgpro_rp calls the plan selection function if it has been set. Otherwise, it searches for a plan assigned to the current user.
If no plan is assigned to the user, pgpro_rp selects the plan with the maximum sum of priorities from the plans assigned to the roles that the user is a member of.
Finally, the priority settings of the found plan are applied.
If the plan cannot be determined, the default priority settings are applied: all priorities have the value of 4.
Note
Only superusers can manage and update plans.
F.51.1. Installation #
The pgpro_rp extension is a built-in extension included into Postgres Pro Enterprise. Once you have Postgres Pro Enterprise installed, execute the CREATE EXTENSION command to enable pgpro_rp as follows:
CREATE EXTENSION pgpro_rp;
F.51.2. The pgpro_rp_roles_plans_view View #
All roles and the plans assigned to them are available via a view named pgpro_rp_roles_plans_view. This view contains one row for each role with an assigned plan. The columns of the view are shown in Table F.108.
Table F.108. pgpro_rp_roles_plans_view Columns
| Name | Type | Description |
|---|---|---|
rolname | text | The name of the user/role |
plan_name | text | The plan used for the user/role |
F.51.3. Functions #
F.51.3.1. Plan Management Functions #
The functions described in this section provide the ability to create, modify, or delete a resource prioritization plan.
-
pgpro_rp_create_plan(a_plan_name text, a_plan_options jsonb) returns bigint# Creates a plan with the given name and options and returns the ID of the created plan.
-
pgpro_rp_rename_plan(a_plan_name_old text, a_plan_name_new text) returns void# Renames the specified plan.
-
pgpro_rp_update_plan(a_plan_name text, a_plan_options jsonb) returns void# Modifies the options of the specified plan.
-
pgpro_rp_delete_plan(a_plan_name text) returns void# Deletes the specified plan.
F.51.3.2. Plan Assignment Functions #
The functions described in this section provide the ability to manage plan assignment for roles.
-
pgpro_rp_create_role_plan(a_role_name text, a_plan_name text) returns void# Assigns the default plan to the specified role.
-
pgpro_rp_update_role_plan(a_role_name text, a_plan_name text) returns void# Changes the default plan for the specified role.
-
pgpro_rp_delete_role_plan(a_role_name text) returns void# Deletes the plan assigned to the specified role.
-
pgpro_rp_cleanup_roles_plans() returns void# Deletes the plans assigned to the non-existent roles from the
pgpro_rp_roles_planstable.
F.51.3.3. Plan Selection Functions #
You can declare a custom function to select resource prioritization plans, which takes no arguments and returns the plan ID, to be called at the session start using the functions described below.
F.51.4. Configuration Parameters #
If necessary, you can also manage priorities manually:
Configure the time interval for collecting usage statistics for all active backends by setting the usage_tracking_interval parameter in the
postgresql.conf. Avoid settingusage_tracking_intervalto small values as frequent statistics collection can cause overhead.Depending on the resources you need to control, modify one or more of the following parameters for the sessions you would like to prioritize:
session_cpu_weight — CPU usage.
session_ioread_weight — I/O read throughput.
session_iowrite_weight — I/O write throughput.
Sessions with the same weight have the same priority for resource usage, so if equal weights are assigned to all sessions, performance is not affected, regardless of the weight value.
For all possible ways of modifying configuration for a particular session, see Section 19.1.
F.51.5. Example #
The following example demonstrates using pgpro-rp.
CREATE EXTENSION pgpro_rp;
CREATE USER test_user;
SELECT pgpro_rp_create_plan(
'test_plan',
'{ "session_cpu_weight":8, "session_ioread_weight":8, "session_iowrite_weight":8 }'
);
SELECT pgpro_rp_create_role_plan('test_user', 'test_plan');
The next time that the test_user user logs in, the prioritization parameters will look as follows:
SHOW session_cpu_weight; session_cpu_weight -------------------- 8 (1 row) SHOW session_ioread_weight; session_ioread_weight ----------------------- 8 (1 row) SHOW session_iowrite_weight; session_iowrite_weight ------------------------ 8 (1 row)