Extension allows to set upper limit to amount of tuples sent from Scan nodes to upper nodes. I.e. SeqScan node can give no more than 100 tuples per second to the upper nodes.

Throttling is set as number of tuples returned by scan node per second. Supported ScanNodes are:

Also, there is support for other DML statements, which can modify table. I.e. INSERT, UPDATE, DELETE, but only if RETURNING clause specified.

There are 2 work-modes for throttling:

Throttle speed is measured as number of tuples returned by corresponding node in 1 second. Extension only tracks amount of tuples and if it exceeds the limit (for less than 1 second) it just sleeps rest time. For example, throttling time is 100 (100 tuples per second):

Extension must be loaded using session_preload_libraries. During loading it looks up it’s configuration table (see below) and setup hooks if necessary. Add extension to postgresql.conf

# postgresql.conf
session_preload_libraries = 'pg_throttle'

Last, run DB and create extension:

CREATE EXTENSION pg_throttle;

Install extension as in discussed in setting up section.

After that, you may want to throttle yourself to test extension. Run this:

CREATE TABLE tbl(
    value int
);
INSERT INTO tbl SELECT * FROM generate_series(1, 200);
SELECT throttle_user_per_node_all(current_user, 100);

Now, this user (which you use to login) will be throttled. Exit, login again and run this query:

SELECT * FROM tbl;

Duration of the query should be about 2 seconds: 200 tuples / 100 tuples per second = 2 seconds of delay.

Extension allows to assign backend processes to a pre-created Cgroup version 2 (control group version 2). Using the script pg_throttle_setup_cgroup.sh, you can create and configure a control group with specified limits. After installing this extension, the script will be located in the directory share/postgresql/extension/pg_throttle_setup_cgroup.sh.

The script allows you to configure the following limits:

You can find out more about all the parameters of the script via --help.

To create a control group without limits ttcg, run the command:

sudo ./pg_throttle_setup_cgroup.sh --group-name "ttcg"

Creating new cgroup 'ttcg'...
Group 'ttcg' configured with parameters:
 cpu.max      = max 100000
 cpu.weight   = 100
 memory.max   = max
 pids.max     = max
 cpuset.cpus  = 
 io.max       = 

To set the CPU consumption limit to 50% for this control group, run the command:

sudo ./pg_throttle_setup_cgroup.sh --group-name "ttcg" --update --cpu-max-percent 50

Group 'ttcg' exists. Updating specified parameters...
Group 'ttcg' configured with parameters:
 cpu.max      = 800000 100000
 cpu.weight   = 100
 memory.max   = max
 pids.max     = max
 cpuset.cpus  = 
 io.max       = 

You can similarly set or update other limits for the control group.

The format of the I/O limits provides two options for specifying:

In the first case, you can specify the path to the PGDATA directory or the path to the tablespace directory and then the values ​​of the I/O limits (the parameters are separated by the symbol :).

In the second case, you can specify the major and minor numbers of the block device on which the PGDATA directory or tablespace directory is located and then the values ​​of the I/O limits (the parameters are separated by the symbol :)

When specifying the path to the PGDATA directory or tablespace directory, the script will attempt to automatically determine the major and minor numbers of the block device on which these directories are located. The Cgroup API requires specifying the major and minor numbers of the block device to set I/O limits, the script helps simplify determining the block device numbers by providing the ability to specify only the path to the desired directory.

To get the Cgroup settings in the script parameters format, run the command:

sudo ./pg_throttle_setup_cgroup.sh --group-name "ttcg" --show

Cgroup settings for group 'ttcg':
--cpu-max-percent -1
...

To reset all Cgroup settings, run the command:

sudo ./pg_throttle_setup_cgroup.sh --group-name "ttcg" --show

Resetting all limits in group 'ttcg'...

To delete Cgroup, run the command:

sudo ./pg_throttle_setup_cgroup.sh --group-name "ttcg" --delete

Group 'ttcg' deleted

In order for backend processes executing queries of a specific user to be automatically assigned to the Cgroup, you need to call the throttle_user_cgroup function, where you specify the user name and the name of the Cgroup:

SELECT throttle_user_cgroup('analytics', 'ttcg');

It is important to know that after restarting the operating system, the required Cgroup will be deleted. To automatically create the required Cgroup, you can create a systemd unit that will create the required Cgroup at the time of operating system startup.

An example of a template systemd unit can be found in pg-throttle-cgroup.service.

Based on the systemd unit template, you need to create a link to the template:

sudo systemctl link ./pg-throttle-cgroups@.service

The systemd unit template accepts a parameter that specifies the name of the Cgroup. To create a systemd service that will recreate the ttsg Cgroup when the operating system starts, you need to run the command:

sudo systemctl enable pg-throttle-cgroups@ttcg.service
sudo systemctl start pg-throttle-cgroups@ttcg.service

Check the result of starting the systemd service:

sudo systemctl status pg-throttle-cgroups@ttcg.service

If you need to delete this systemd service and the link to the unit template, you need to run the command:

sudo systemctl disable pg-throttle-cgroup@ttcg.service
sudo systemctl daemon-reload

Stopping or deleting the systemd service does not delete the created Cgroup. To delete the Cgroup, you need to run the script pg_throttle_setup_cgroup.sh with the parameter --delete.

If the mount point of the Cgroup file system cgroup2fs is not located at /sys/fs/cgroup, then the extension has a GUC pg_throttle.cgroup_mount_point to specify the path of the mount point:

ALTER SYSTEM SET pg_throttle.cgroup_mount_point TO '/custom/mount/point/to/cgroup2fs';
SELECT pg_reload_conf();

The pg_throttle_setup_cgroup.sh script also has a parameter --cgroup-root for explicitly specifying the mount point of the Cgroups file system cgroup2fs.

pg_throttle.cgroup_mount_point - the mount point of the Cgroup file system cgroup2fs (default /sys/fs/cgroup).

FUNCTION throttle_user_per_node(username text, 
                                seq_scan_max int
                                idx_scan_max int,
                                idx_only_scan_max int
                                bitmap_heap_scan_max int,
                                bitmap_index_scan_max int
                                custom_scan_max int,
                                foreign_scan_max int
                                tid_range_scan_max int,
                                modify_scan_max int,
                                values_scan_max int,
                                result_scan_max int,
                                func_scan_max int);

This is main function to set user throttling. But, usually you will use other short-hand functions below.

username - name of user who should be throttled. If specified user does not exists it will throw exception.

XXX_max - other arguments specify throttling per corresponding node:

For XXX_max arguments allowed value:

Example - throttle ‘analytics’ user.

SELECT throttle_user_per_node('analytics',
                              100,          -- SeqScan
                              1000, 1000,   -- Index/IndexOnly Scan
                              1000, 1000,   -- Bitmap Heap/Index Scan
                              NULL,         -- Custom Scan (disable throttling)
                              10,           -- Foreign Scan
                              100,          -- TID Range Scan
                              NULL,         -- DML (disable throttling)
                              NULL, NULL,   -- Values/Result Scan (disable throttling)
                              1000);        -- Function Scan

FUNCTION throttle_user_per_node_all(username text, max int);

This is short-hand function for throttle_user_per_node which sets all XXX_max to max.

Example:

SELECT throttle_user_per_node_all('analytics', 100);

FUNCTION throttle_user_query(username text, max int);

This function throttle all Scan nodes using shared state. That means that all nodes has same throttling counter. Counter applies to all supported nodes - you can not set specific nodes

Example:

SELECT throttle_user_query('analytics', 100);

FUNCTION unthrottle_user(username text);

This function deletes throttling from specified user.

Example:

SELECT unthrottle_user('analytics');

FUNCTION throttle_user_cgroup(username text, cgroup_name text);

This function sets for a specific user the name of the Cgroup in which the backend processes servicing queries for this user will be launched.

Example:

SELECT throttle_user_cgroup('analytics', 'ttcg');

TABLE throttlings(
    username text,
    query_throttle int,
    XXX_throttle int
);

This is a main configuration table of extension - stores throttling configuration for users:

This table is read by extension at startup of backend, during extension initialization. So user should have access to it, but it creates security risks. To address this issue see security section below.

Extension is implemented using decorators.

When ExecutorStart_hook fires we search it’s PlanState for interesting Nodes (which are supported). If such node is found we:

Then during execution:

This applies to every supported node, except CustomScan and ForeignScan. We can not do the same for them, because extension create their own structures above them (also decorator). So doing this again - we overwrite their data. To overcome this we create such decorator for it’s interface - CustomScanMethods and FdwRoutine.

There are many caveats that you must be aware of

REVOKE ALL ON TABLE throttlings FROM public;
GRANT SELECT ON TABLE throttlings TO public;