prioritize

Overview

no pg 14 on el9

Version

Install

You can install pg_prioritize directly. First, make sure the PGDG repository is added and enabled:

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

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install pg_prioritize;          # Install for current active PG version

pig ext install -y pg_prioritize -v 18  # PG 18
pig ext install -y pg_prioritize -v 17  # PG 17
pig ext install -y pg_prioritize -v 16  # PG 16
pig ext install -y pg_prioritize -v 15  # PG 15
pig ext install -y pg_prioritize -v 14  # PG 14

dnf install -y pg_prioritize_18       # PG 18
dnf install -y pg_prioritize_17       # PG 17
dnf install -y pg_prioritize_16       # PG 16
dnf install -y pg_prioritize_15       # PG 15
dnf install -y pg_prioritize_14       # PG 14

apt install -y postgresql-18-prioritize   # PG 18
apt install -y postgresql-17-prioritize   # PG 17
apt install -y postgresql-16-prioritize   # PG 16
apt install -y postgresql-15-prioritize   # PG 15
apt install -y postgresql-14-prioritize   # PG 14

Create Extension:

CREATE EXTENSION prioritize;

Usage

prioritize: get and set the priority of PostgreSQL backends

The prioritize extension exposes getpriority() and setpriority() system calls for PostgreSQL backends, allowing you to renice backend processes from SQL.

Get Backend Priority

SELECT get_backend_priority(pg_backend_pid());

Any user may query the priority of any backend.

Set Backend Priority

SELECT set_backend_priority(pg_backend_pid(), 10);

Superusers can set the priority of any backend. Unprivileged users can only adjust backends with the same role.

Note: priority can only be raised (higher numeric value = lower OS priority). Only root can lower the numeric priority value, and PostgreSQL processes should not run as root.

Batch Operations

-- Increase priority of all current user's backends by 5
SELECT set_backend_priority(pid, get_backend_priority(pid) + 5)
  FROM pg_stat_activity
  WHERE usename = CURRENT_USER;