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.

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');

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;