prioritize

pg_prioritize : get and set the priority of PostgreSQL backends

Overview

Packages

Source

Install

Make sure PGDG repo available:

pig repo add pgdg -u    # add pgdg repo and update cache

Install this extension with pig:

pig install pg_prioritize;		# install via package name, for the active PG version
pig install prioritize;		# install by extension name, for the current active PG version

pig install prioritize -v 18;   # install for PG 18
pig install prioritize -v 17;   # install for PG 17
pig install prioritize -v 16;   # install for PG 16
pig install prioritize -v 15;   # install for PG 15
pig install prioritize -v 14;   # install for PG 14

Create this extension with:

CREATE EXTENSION prioritize;

Usage

Sources:

• Official README
• SQL functions
• Control file

prioritize exposes operating-system priority controls for PostgreSQL backend processes. Use it to lower the scheduling priority of selected sessions; it is not a PostgreSQL query scheduler.

Inspect and Adjust a Backend

CREATE EXTENSION prioritize;
SELECT get_backend_priority(pg_backend_pid());
SELECT set_backend_priority(pg_backend_pid(), 10);

Any user may query a backend’s priority. A PostgreSQL superuser may request adjustments for any backend; other users may adjust only backends running under the same database role.

Adjust Related Sessions

SELECT set_backend_priority(pid, get_backend_priority(pid) + 5)
FROM pg_stat_activity
WHERE usename = CURRENT_USER;

Increasing the numeric nice value lowers operating-system scheduling priority. The example therefore reduces those backends’ priority, rather than speeding them up.

Privileges and Limits

Installation requires a superuser, with no preload or restart. Operating-system permissions still apply: ordinary PostgreSQL processes normally cannot lower the numeric nice value to increase priority. Database superuser privileges do not confer root privileges. Review platform scheduling policy and process identity before using bulk adjustments. The control file uses SQL version 1.0; distribution package version 1.0.4 is separate.