PostgreSQL QoS Resource Governor

pg_qos is a PostgreSQL extension that brings Quality of Service (QoS) style resource governance to your databases.

It improves overall stability and predictability by capping resource usage per role or per database, reducing cross-workload interference without application changes.

Purpose of the Extension

The goal of pg_qos is to isolate workloads in clusters where multiple databases share the same PostgreSQL instance, especially in vertical deployment setups.

Instead of splitting databases into separate instances just to protect resources, the extension lets you enforce boundaries inside the same cluster and keep operations simpler.

Capabilities

• Limit CPU usage by binding the backend to N CPU cores on Linux, while keeping parallel workers within that cap.
• Track and cap concurrent transactions and statements for SELECT, UPDATE, DELETE, and INSERT workloads.
• Cap throughput with rate limits: at most N transactions or statements per time window, counted per database and role across all backends.
• Limit work_mem per session.
• Enforce per-role and per-database limits through ALTER ROLE/DATABASE SET qos.*.
• Monitor what QoS is doing through the qos_stat* views and export it with qos_prometheus_metrics().
• Refresh configuration across sessions without reconnects through a shared epoch cache invalidation mechanism.

What's New in 1.1

The max_concurrent_* settings cap how many operations run at the same time, but not how fast they arrive: a single backend can run 50,000 SELECTs per second while never exceeding max_concurrent_select = 1. Version 1.1 closes that gap and adds the observability to go with it.

• Rate limits for transactions, SELECT, UPDATE, DELETE and INSERT, written as '<count>/<window>'.
• Statistics views — qos_stat, qos_stat_activity, qos_stat_rate and qos_stat_cpu, readable by pg_monitor.
• Prometheus metrics through qos_prometheus_metrics(), so scraping needs no custom exporter.
• Setting validation at ALTER ROLE / ALTER DATABASE ... SET time: unknown parameter names and malformed values are now rejected up front instead of being silently ignored later.

Upgrading from 1.0:

ALTER EXTENSION qos UPDATE TO '1.1';

Requirements

• PostgreSQL 15 or newer.
• Build toolchain and server headers when installing from source, with pg_config available.
• Linux for CPU affinity limiting; on other platforms, only parallel worker limiting is applied.

Installation From Package

Debian 13 Package

# Install
sudo dpkg -i postgresql-<version>-qos_1.1.0-1_debian13_amd64.deb
sudo apt-get install -f

Ubuntu 24.04 Package

# Install
sudo dpkg -i postgresql-<version>-qos_1.1.0-1-ubuntu24_amd64.deb
sudo apt-get install -f

RHEL/AlmaLinux/Centos 10 (PGDG) Package

# Install
sudo rpm -i postgresql<version>-qos-1.1.0-1.el10.x86_64.rpm

Installation From Source

Debian/Ubuntu Packages

Install the development package that matches your PostgreSQL version:

• postgresql-server-dev-15 for PostgreSQL 15
• postgresql-server-dev-16 for PostgreSQL 16
• postgresql-server-dev-17 for PostgreSQL 17
• postgresql-server-dev-18 for PostgreSQL 18

# Example (Ubuntu/Debian)
sudo apt update
# Choose the version you run (15/16/17/18)
sudo apt install postgresql-server-dev-18 build-essential

RHEL/AlmaLinux/Centos/Rocky Packages

Install the development package that matches your PostgreSQL version:

• postgresql15-devel for PostgreSQL 15
• postgresql16-devel for PostgreSQL 16
• postgresql17-devel for PostgreSQL 17
• postgresql18-devel for PostgreSQL 18

# Example (RHEL/AlmaLinux/Centos/Rocky)
# Setup pgdg repository
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-10-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Choose the version you run (15/16/17/18)
sudo dnf install -y gcc make autoconf libtool automake postgresql18-devel redhat-rpm-config

Build and Install

1. Build

make

2. Install

sudo make install

# Debian/Ubuntu
make clean
make PG_CONFIG=/usr/lib/postgresql/<version>/bin/pg_config
sudo make install PG_CONFIG=/usr/lib/postgresql/<version>/bin/pg_config

# RHEL/AlmaLinux/Centos/Rocky
make clean
make PG_CONFIG=/usr/pgsql-<version>/bin/pg_config
sudo make install PG_CONFIG=/usr/pgsql-<version>/bin/pg_config

Configure

Server restart required because pg_qos uses hooks and shared memory.

Edit postgresql.conf:

shared_preload_libraries = 'qos'

Restart PostgreSQL, then enable the extension in each database where QoS should be active:

CREATE EXTENSION qos;

Configuration: qos.* Settings

Configure limits per role and/or per database using standard GUC storage in pg_db_role_setting:

• qos.work_mem_limit (bytes) - max effective work_mem per session, for example 64MB or 1GB
• qos.cpu_core_limit (integer) - maximum CPU core count
• qos.max_concurrent_tx (integer) - maximum concurrent transactions
• qos.max_concurrent_select (integer) - maximum concurrent SELECT statements
• qos.max_concurrent_update (integer) - maximum concurrent UPDATE statements
• qos.max_concurrent_delete (integer) - maximum concurrent DELETE statements
• qos.max_concurrent_insert (integer) - maximum concurrent INSERT statements

Rate Limits

Rate limits cap throughput rather than concurrency: at most N operations per time window, counted per (database, role) across all backends. Each is written as '<count>/<window>'.

• qos.max_tx_rate - transactions per window
• qos.max_select_rate - SELECTs per window
• qos.max_update_rate - UPDATEs per window
• qos.max_delete_rate - DELETEs per window
• qos.max_insert_rate - INSERTs per window

• -1 (the default) disables the limit, so behaviour is unchanged until you set one.
• The window accepts ms, s and min; a bare number means milliseconds. The minimum is 100 ms, the maximum 1 day.
• Exceeding a limit raises ERROR with SQLSTATE 54000, and the hint reports how long is left until the window resets.

Examples

-- Per-role limits
ALTER ROLE app_user SET qos.work_mem_limit = '32MB';
ALTER ROLE app_user SET qos.cpu_core_limit = '2';
ALTER ROLE app_user SET qos.max_concurrent_select = '100';

-- Per-database limits (for all roles)
ALTER DATABASE appdb SET qos.max_concurrent_tx = '200';

-- Per-role limits for a specific database
ALTER ROLE app_user IN DATABASE appdb SET qos.work_mem_limit = '4MB';
ALTER ROLE app_user IN DATABASE appdb SET qos.max_concurrent_update = '10';

-- Rate limits: at most 100 transactions per second, 10 SELECTs per 500 ms
ALTER ROLE app_user SET qos.max_tx_rate = '100/1s';
ALTER ROLE app_user SET qos.max_select_rate = '10/500ms';

Effective limits are the most restrictive combination of role-level and database-level settings. Rate limits are merged as whole (count, window) pairs by comparing normalised rates, so 10/1s (10/sec) wins over 100/500ms (200/sec).

How It Works

work_mem Enforcement

Intercepts SET work_mem and rejects values above qos.work_mem_limit.

CPU Limiting

• On Linux, QoS binds the backend to N CPU cores using CPU affinity.
• The planner hook keeps Gather and Gather Merge workers inside the configured core budget.
• On non-Linux platforms, only the planner effect applies.

Concurrency Limits

Executor hooks track active transactions and statements per command type; caps are enforced against configured maxima.

Rate Limits

• Each (database, role, operation) gets a fixed window counter in shared memory: time is cut into window-long slices and each slice allows count operations, so '3/50s' means three operations in every 50 second slice and the whole allowance returns at once when the slice ends.
• Rollover is lazy, so the cost of a check does not depend on the window length — a 100 ms window costs the same as a one hour window. The check is O(1) and cheaper than the concurrency scan that already runs per statement.
• Known trade-off — the boundary effect: count operations at the end of one slice and count more at the start of the next put 2x count through in a short span.
• Fails open: if the shared slot table is exhausted, a warning is logged and traffic is allowed rather than blocked.

Observability

Four views expose what QoS is doing, readable by pg_monitor:

• qos_stat - cumulative counters per database and role, broken down by rejection cause
• qos_stat_activity - backends currently running a governed operation
• qos_stat_rate - live rate limit windows, with the configured limit
• qos_stat_cpu - CPU cores assigned per database and role (Linux)

-- Which roles are being rejected the most?
SELECT datname, rolname, total_statements, rejected_total
FROM qos_stat
ORDER BY rejected_total DESC;

-- How close is each rate limit to exhaustion right now?
SELECT datname, rolname, kind, used, rate_limit, window_reset_ms
FROM qos_stat_rate
WHERE rate_limit IS NOT NULL;

Counters live in shared memory only and reset when the postmaster restarts. Reset them manually with SELECT qos_reset_stats();, or SELECT qos_reset_stats('appdb', 'app_user'); for one database and role.

Prometheus

Every counter and gauge is also available in Prometheus exposition format, so scraping needs no custom exporter:

SELECT qos_prometheus_metrics();

Logging

Increase verbosity temporarily to trace QoS activity:

SET client_min_messages = 'debug1';

You'll see messages when cache is refreshed, CPU workers are adjusted, or limits are enforced.

Limitations

• CPU limiting is only available on Linux; other platforms only enforce parallel worker limits via the planner.
• Requires shared_preload_libraries and a server restart to activate.
• Official support targets PostgreSQL 15 and newer.

Uninstall

DROP EXTENSION IF EXISTS qos;

Remove from shared_preload_libraries and restart the server.

Development

• Builds with PGXS using the provided Makefile.
• Targets PostgreSQL 15 through PostgreSQL 18 server APIs.
• Includes modular components such as hooks.c, hooks_cache.c, hooks_resource.c, hooks_statement.c, hooks_transaction.c, hooks_rate.c, qos_stats.c, qos_shmem_slot.c, and qos.c/qos.h.

License

GPL-3.0 license. See the LICENSE file in the repository.