How to Use the pg_qos Extension in PostgreSQL for Resource Governance

Modern PostgreSQL systems often serve multiple applications, users, and workloads in a single cluster. Without proper resource control, a single heavy query or misconfigured user can consume excessive CPU and memory, causing performance degradation for all other users. Native PostgreSQL does not provide built-in quality of service control at the role or query level.

The pg_qos extension introduces Quality of Service resource governance directly inside PostgreSQL. It allows administrators to enforce CPU limits, memory limits, and concurrency limits per role and per database. This blog explains what pg_qos is, how to install and verify it, how to configure it, and how to use all of its available functions with real working examples.

What is pg_qos

pg_qos is an open-source PostgreSQL extension that implements Quality of Service style resource governance for sessions and queries. It works using PostgreSQL hooks, shared memory, and planner integration. It allows administrators to control how much CPU, memory, and concurrency each user or database is allowed to consume.

Unlike external operating system tools, pg_qos applies controls natively within PostgreSQL using SQL configuration commands. This makes it highly suitable for production PostgreSQL environments such as SaaS platforms, ERP systems like Odoo, financial applications, reporting systems etc

Purpose of pg_qos

The primary purpose of pg_qos is to prevent resource abuse and ensure stable system performance. It provides protection against runaway queries, uncontrolled parallelism, excessive memory allocation, and connection storms. By enforcing strict limits at the role and database level, pg_qos ensures fair resource distribution across workloads.

System Requirements

pg_qos supports PostgreSQL versions 15 and higher. It requires PostgreSQL server development headers and standard build tools. CPU limiting works only on Linux because it relies on Linux CPU affinity. The extension must be loaded using shared_preload_libraries, so a PostgreSQL restart is mandatory.

Building and Installing pg_qos from Source

First, clone the official repository and move into the project directory.

git clone https://github.com/appstonia/pg_qos
cd pg_qos

If multiple PostgreSQL versions exist, explicitly point to the correct pg_config binary.

make PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config
sudo make install PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config

After installing the files, the extension must be preloaded at PostgreSQL startup.

Enabling pg_qos in PostgreSQL

Check the PostgreSQL configuration file location.

show config_file;

Your system output confirms the configuration file path as:

/etc/postgresql/18/main/postgresql.conf

Edit this file and add qos to shared_preload_libraries.

shared_preload_libraries = 'qos'

Restart PostgreSQL to activate the extension.

sudo systemctl restart postgresql

After restart, confirm that pg_qos is enabled.

show qos.enabled;

If it returns on, the extension is active.

Creating and Verifying the Extension

Create the extension inside your target database.

create extension qos;

Verify that PostgreSQL recognizes the extension.

select * from pg_available_extensions where name = 'qos';

You should see a result like this

 name | default_version | installed_version |                       comment                        
------+-----------------+-------------------+------------------------------------------------------
 qos  | 1.0             | 1.0               | QoS (Quality of Service) Resource Governor Extension
(1 row)

Verify the installed extension objects.

dx+ qos

The following objects are created by pg_qos:

 Objects in extension "qos"
     Object description     
----------------------------
 function qos_get_stats()
 function qos_reset_stats()
 function qos_version()
 type qos_settings
 type qos_settings[]
 view qos_settings

Verify the installed version using the function.

select * from qos_version();

Your system correctly returns:

PostgreSQL QoS Resource Governor 1.0

Understanding pg_qos Configuration Framework

pg_qos uses PostgreSQL’s standard configuration and catalog system. All limits are stored internally in the pg_db_role_setting catalog. Limits can be applied at the role level or at the database level. The effective limit applied to a session is always the lowest value between the role and database configuration.

The extension supports limits for work memory, CPU core usage, concurrent transactions, and concurrent select, insert, update, and delete statements.

Applying Work Memory Limits

To restrict the maximum effective work memory for a specific user:

Create user user_1;

alter role user_1 set qos.work_mem_limit = '64MB';

You can check the postgres system catalogue named pg_db_role_settings to see the current settings we applied

 select * from pg_db_role_setting ;

You get result like this

 setdatabase | setrole |                                           setconfig                                              
-------------+---------+----------------------------------------------------------------------------------------------------
           0 | 5745457 | {qos.work_mem_limit=64MB}

If this user tries to manually increase work_mem beyond 64MB:

Before checking this , you need to add the user credentials in pg_hba.conf file of postgres

 sudo nano /etc/postgresql/18/main/pg_hba.conf

Add this line for user_1

# IPv4 local connections:
host    all             user_1          127.0.0.1/32            trust
# IPv6 local connections:
host    all             user_1          127.0.0.1/32            trust

Then restart the postgres server

Sudo systemctl restart postgresql

Then login as the user_1 user in postgres like this

postgres@cybrosys:/home/cybrosys$ psql -p 5433 -U user_1 -d postgres

Try to change the value of work_mem like this

set work_mem = '512MB';

PostgreSQL will reject the command and enforce the configured limit automatically through pg_qos.

You get a result like this

postgres=> set work_mem = '512MB';
ERROR:  qos: work_mem limit exceeded
DETAIL:  Requested 524288 KB, maximum allowed is 65536 KB
HINT:  Contact administrator to increase qos.work_mem_limit

Applying CPU Core Limits

CPU limiting works using Linux CPU affinity. To restrict a user to two CPU cores:

alter role user_1 set qos.cpu_core_limit = '2';

All backend processes for this user, including parallel workers, will be bound to only two CPU cores.

Limiting Concurrent Queries

To control the number of concurrently running queries for a role:

alter role user_1 set qos.max_concurrent_select = '50';
alter role user_1 set qos.max_concurrent_insert = '20';
alter role user_1 set qos.max_concurrent_update = '15';
alter role user_1 set qos.max_concurrent_delete = '10';

When the active count reaches the configured limit, any new query of that type is immediately rejected by PostgreSQL.

Limiting Concurrent Transactions

To restrict the total number of open transactions for a user:

alter role user_1 set qos.max_concurrent_tx = '30';

Once the limit is reached, any new transaction attempt will fail until an active transaction completes.

Applying Limits at the Database Level

Database-level limits apply to all users connected to that database.

Example to limit total transactions for a database:

alter database odoo set qos.max_concurrent_tx = '200';

Example to limit memory usage for all users in a database:

alter database odoo set qos.work_mem_limit = '128MB';

If both role and database limits exist, the most restrictive value is automatically applied.

Viewing Active pg_qos Configuration

pg_qos provides a built-in view to inspect all active limits.

select * from qos_settings;

This view displays the effective limits currently applied to roles and databases without requiring any catalog queries.

You get result like this

postgres=# select * from qos_settings;
    rolname    |   datname    |           setting            
---------------+--------------+------------------------------
 qos_user      | cluster-wide | qos.work_mem_limit=16MB
 qos_user      | cluster-wide | qos.cpu_core_limit=1
 qos_user      | cluster-wide | qos.max_concurrent_select=1
 qos_user      | cluster-wide | qos.max_concurrent_tx=2
 user_1        | cluster-wide | qos.work_mem_limit=64MB
 user_1        | cluster-wide | qos.cpu_core_limit=2
 user_1        | cluster-wide | qos.max_concurrent_select=50
 user_1        | cluster-wide | qos.max_concurrent_insert=20
 user_1        | cluster-wide | qos.max_concurrent_update=15
 user_1        | cluster-wide | qos.max_concurrent_delete=10
 user_1        | cluster-wide | qos.max_concurrent_tx=30
 database-wide | postgres     | qos.max_concurrent_tx=200
 database-wide | postgres     | qos.work_mem_limit=128MB

Using pg_qos Functions

pg_qos provides three core utility functions for versioning and statistics.

qos_version Function

This function returns the installed pg_qos version.

select * from qos_version();

You get result like this

         qos_version              
--------------------------------------
 PostgreSQL QoS Resource Governor 1.0
(1 row)

This function is useful for auditing and compatibility verification.

qos_get_stats Function

This function is intended to return runtime statistics for pg_qos operations.

select * from qos_get_stats();

On the current beta release, this function returns like this

postgres=# select * from qos_get_stats();
    qos_get_stats    
---------------------
 not implemented yet
(1 row)

This indicates that runtime statistics collection is planned but not yet available in the current version.

qos_reset_stats Function

This function resets runtime statistics maintained by pg_qos.

select * from qos_reset_stats();

Monitoring and Debugging pg_qos Activity

To observe pg_qos behavior in real time, enable debug-level logging for the session.

set client_min_messages = 'debug1';

This displays messages related to cache refresh, CPU core binding, query blocking, and enforcement actions.

Uninstalling pg_qos

To remove the extension from a database:

drop extension if exists qos;

Remove qos from shared_preload_libraries in postgresql.conf and restart PostgreSQL to fully disable the extension.

Conclusion

The pg_qos extension introduces true Quality of Service governance directly within PostgreSQL, filling a long-standing gap in native resource control. By enabling precise limits on CPU usage, memory consumption, and concurrent workload execution at both the role and database levels, it allows administrators to maintain consistent performance even in heavily shared environments.

With its SQL-based configuration and tight integration into the PostgreSQL execution engine, pg_qos eliminates the need for external resource throttling tools and operating system–level workarounds. It offers a clean, transparent, and database-native approach to performance protection and workload isolation.

Although still in its early beta stage, pg_qos already demonstrates strong potential for production-grade use in SaaS platforms, ERP systems, financial applications, and high-concurrency reporting environments. As the extension continues to mature, it is well-positioned to become a foundational standard for resource governance in PostgreSQL ecosystems.