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.
Only superusers can manage and update plans.
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;
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 |
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.
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_plans table.
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.
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
setting usage_tracking_interval to 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.
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)