# Select a Resource Pool Dynamically

You can use a user-defined scalar function ([UDF](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/create-function-udf.md)) to select the resource pool that an existing or new client connection uses.

## Enabling Dynamic Resource Pool Selection

To enable the current connection to select a resource pool dynamically, run `SET resource_pool = system_auto`. To enable all new connections to select a resource pool dynamically, run `SET GLOBAL resource_pool = system_auto`.

Groups and roles cannot have resource pools associated with them, only a user can have a default resource pool. This ensures each user's default resource pool is unique and unambiguous.

## Registering and Defining the Resource Pool Selector Function

After enabling dynamic resource pool selection, set the resource pool selector function to be called when the user runs a query:

`SET GLOBAL resource_pool_statement_selector_function = '<database name>.<function name>';`

Define the selector function as follows:

```sql
CREATE FUNCTION <database name>.<function name> RETURNS
  VARCHAR(n) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci AS
-- In VARCHAR(n), n can have a maximum length of 256 characters.
  DECLARE
    <Any needed variables declared here>
    BEGIN
      <Logic that determines the resource pool name to return in a RETURN statement>
    END

```

The selector function must return the name of a resource pool. The function’s return type should be `VARCHAR(n) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci`. You must specify a collation that is not case-sensitive for the return type.

Example selector functions are provided later in this section.

> **📝 Note**: If value of `collation_server` is not a case-sensitive collation such as `utf8mb4_general_ci`, you must specify the collation of the `VARCHAR` return value using `COLLATE` as show above. Use `SELECT @@collation_server;` to see the current value of `collation_server`.

## Resource Pool Selector Function Built-Ins that Provide Estimates

When you write the resource pool selector function, you can use the built-in functions [ESTIMATED\_QUERY\_LEAF\_MEMORY](https://docs.singlestore.com/db/v9.1/reference/sql-reference/information-functions/estimated-query-leaf-memory.md) and [ESTIMATED\_QUERY\_RUNTIME](https://docs.singlestore.com/db/v9.1/reference/sql-reference/information-functions/estimated-query-runtime.md) to help you select the resource pool that you return from the function.

To provide accurate estimates, statistics must first be collected on the queries that these functions operate on. Statistics on these queries are collected on ten minute intervals, after the queries have run once. You can trigger manual collection of statistics by running `ANALYZE MEMORY`.&#x20;

You can use `ESTIMATED_QUERY_LEAF_MEMORY()` and `ESTIMATED_QUERY_RUNTIME()` outside the resource pool selector function. However, they are intended to be used inside this function.

> **📝 Note**: At this time, `ESTIMATED_QUERY_LEAF_MEMORY()` and `ESTIMATED_QUERY_RUNTIME()` are experimental functions. They are not formally supported. However, feedback and suggestions are welcome.

## Disabling Dynamic Resource Pool Selection

To disable dynamic resource pool selection, set `resource_pool` to a value other than `system_auto`. The value must be an existing resource pool.

## Selector Function Security

When you create the resource pool selector function, you must have permission to run everything in the function's body. Functions use the Definer Security Model, which is explained in [Procedural Extensions Security](https://docs.singlestore.com/db/v9.1/developer-resources/procedural-extensions/procedural-extensions-security.md).

To set the `resource_pool_statement_selector_function` engine variable, you must have the `SUPER` permission and the `EXECUTE` permission on the resource pool selector function.

## Example UDFs that Select Resource Pools

> **📝 Note**: While the following examples use VARCHAR(64), you can return VARCHAR(n) with n ≤ 256, which is the maximum length of the resource pool name.Refer to [String Types](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types/string-types.md) for more information.

## Example 1: Selecting a Resource Pool Based on the Current User

The following example UDF selects a resource pool based on the user running the query. It assumes the resource pools `executive` and `general` have been created, as shown in the page [Work with Resource Pool Engine Variables](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/use-the-workload-manager-and-set-resource-limits/set-resource-limits/work-with-resource-pool-engine-variables.md).

```sql
DROP DATABASE IF EXISTS singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
USE singlestore_docs_example;
GRANT USAGE ON RESOURCE POOL * TO admin; // to be able to perform "set resource_pool = system_auto;"
CREATE RESOURCE POOL general WITH MEMORY_PERCENTAGE = 50; // so that SELECT 1, CURRENT_RESOURCE_POOL(); returns the *existing* pool
DELIMITER //
CREATE FUNCTION singlestore_docs_example.select_pool() RETURNS
  VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci AS
  BEGIN
    IF CURRENT_USER() = 'root@%' THEN
      RETURN 'executive';
    ELSE
      RETURN 'general';
    END IF;
  END //
DELIMITER ;

SET resource_pool = system_auto;

SET GLOBAL resource_pool_statement_selector_function = 'singlestore_docs_example.select_pool';

/* Return the executive resource pool from the select_pool UDF if the
current user is the root user. Otherwise, return the general resource pool. */

SELECT 1, CURRENT_RESOURCE_POOL();

```

## Example 2: Selecting a Resource Pool Based on the Current Database

The following example creates two resource pools with memory usage limits and defines a UDF selects a resource pool based on the current database.

```sql
DROP DATABASE IF EXISTS singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
USE singlestore_docs_example;

/* Resource pool used by users who are in training */
CREATE RESOURCE POOL training WITH MEMORY_PERCENTAGE = 5;

/* Resource pool used by users for other purposes */
CREATE RESOURCE POOL main WITH MEMORY_PERCENTAGE = 95;

CREATE TABLE simple_table (a INT);
INSERT INTO simple_table (a) VALUES (1, 2, 3, 4, 5);

DELIMITER //
CREATE FUNCTION select_pool() RETURNS
  VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci AS
  BEGIN
    IF DATABASE() = 'singlestore_docs_example' THEN
      RETURN 'training';
    ELSE
      RETURN 'main';
    END IF;
  END //
DELIMITER ;

SET resource_pool = system_auto;

SET GLOBAL resource_pool_statement_selector_function = 'singlestore_docs_example.select_pool';

/* The following query uses the training resource pool returned from the
select_pool UDF, since singlestore_docs_example is the current database. */

SELECT a FROM simple_table ORDER BY a;

/* The following SELECT query uses the main resource pool returned from
the select_pool UDF, since information_schema is the current database.
Prefacing simple_table with singlestore_docs_example does not make
singlestore_docs_example the current database. */

USE information_schema;
SELECT a FROM singlestore_docs_example.simple_table ORDER BY a;

```

## Example 3: Selecting a Resource Pool Based on the Estimated Memory Required to Run a Query

The following example creates three resource pools with memory usage limits and defines a UDF that selects a resource pool based on the amount of estimated memory required to run a query.

```sql
CREATE RESOURCE POOL low_memory WITH MEMORY_PERCENTAGE = 15;
CREATE RESOURCE POOL medium_memory WITH MEMORY_PERCENTAGE = 70;
CREATE RESOURCE POOL high_memory WITH MEMORY_PERCENTAGE = 15;

DROP DATABASE IF EXISTS singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
USE singlestore_docs_example;

DELIMITER //
CREATE FUNCTION select_pool() RETURNS
  VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci AS
  DECLARE            
    leaf_memory INT;
  BEGIN
    leaf_memory = ESTIMATED_QUERY_LEAF_MEMORY();
    IF leaf_memory = -1 THEN
      RETURN 'default_pool';                
    ELSIF leaf_memory < 1 THEN
      RETURN 'low_memory';
    ELSIF leaf_memory > 100 THEN
      RETURN 'high_memory';
    ELSE
      RETURN 'medium_memory';
    END IF;
  END //
DELIMITER ;

SET resource_pool = system_auto;
SET GLOBAL resource_pool_statement_selector_function = 'singlestore_docs_example.select_pool';

```

Next, do an initial run of the query, followed by `ANALYZE MEMORY` to collect statistics on the query.  The initial run returns `default_pool` because statistics have not yet been collected:

```sql
SELECT 1, CURRENT_RESOURCE_POOL();

```

```output

+---+-------------------------+
| 1 | CURRENT_RESOURCE_POOL() |
+---+-------------------------+
| 1 | default_pool            |
+---+-------------------------+

```

Collect statistics on the query:

```sql
ANALYZE MEMORY;

```

Re-run the `SELECT` query. The expected resource pool is returned when the `select_pool` UDF runs `ESTIMATED_QUERY_LEAF_MEMORY()`:

```sql
SELECT 1, CURRENT_RESOURCE_POOL();

```

```output

+---+-------------------------+
| 1 | CURRENT_RESOURCE_POOL() |
+---+-------------------------+
| 1 | low_memory              |
+---+-------------------------+

```

## Example 4: Selecting a Resource Pool Based on the Estimated, Elapsed Time Required to Run a Query

The following example demonstrates selecting a resource pool based on the [ESTIMATED\_QUERY\_RUNTIME](https://docs.singlestore.com/db/v9.1/reference/sql-reference/information-functions/estimated-query-runtime.md) required to run a query.

Create three resource pools with query timeouts and defines a UDF that selects a resource pool based on the estimated time required to run a query:

```sql
CREATE RESOURCE POOL low_run_time WITH QUERY_TIMEOUT = 1;
CREATE RESOURCE POOL medium_run_time WITH QUERY_TIMEOUT = 2;
CREATE RESOURCE POOL high_run_time;

DROP DATABASE IF EXISTS singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
USE singlestore_docs_example;

DELIMITER //
CREATE FUNCTION select_pool() RETURNS
  VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci AS
  DECLARE
    run_time double;
  BEGIN
    run_time = ESTIMATED_QUERY_RUNTIME();
    IF run_time = -1 THEN
      RETURN 'default_pool';
    ELSIF run_time < 1.0 then
      RETURN 'low_run_time';
    ELSIF run_time < 2.0 THEN
      RETURN 'medium_run_time';
    ELSE
      RETURN 'high_run_time';
    END IF;
  END //
DELIMITER ;

SET resource_pool = system_auto;

SET GLOBAL resource_pool_statement_selector_function = 'singlestore_docs_example.select_pool';

```

Next, do an initial run of three queries, followed by `ANALYZE MEMORY` to collect statistics on the queries.  The initial run of each query returns `default_pool` because statistics have not yet been collected:

```sql
SELECT SLEEP(0.5), CURRENT_RESOURCE_POOL();

```

```output

+------------+-------------------------+
| SLEEP(0.5) | CURRENT_RESOURCE_POOL() |
+------------+-------------------------+
|          0 | default_pool            |
+------------+-------------------------+

```

```sql
SELECT SLEEP(1.5), CURRENT_RESOURCE_POOL();

```

```output

+------------+-------------------------+
| SLEEP(1.5) | CURRENT_RESOURCE_POOL() |
+------------+-------------------------+
|          0 | default_pool            |
+------------+-------------------------+

```

```sql
SELECT SLEEP(3.0), CURRENT_RESOURCE_POOL();

```

```output

+------------+-------------------------+
| SLEEP(3.0) | CURRENT_RESOURCE_POOL() |
+------------+-------------------------+
|          0 | default_pool            |
+------------+-------------------------+

```

Collect statistics on the queries:

```sql
ANALYZE MEMORY;

```

Re-run the previous query that takes 0.5 seconds. It returns the `low_run_time` resource pool from the `select_pool` UDF:

```sql
SELECT SLEEP(0.5), CURRENT_RESOURCE_POOL();

```

```output

+------------+-------------------------+
| SLEEP(0.5) | CURRENT_RESOURCE_POOL() |
+------------+-------------------------+
|          0 | low_run_time            |
+------------+-------------------------+

```

Re-run the pervious query that takes 1.5 seconds. It returns the `medium_run_time` resource pool from the `select_pool` UDF.

```sql
SELECT SLEEP(1.5), CURRENT_RESOURCE_POOL();

```

```output

+------------+-------------------------+
| SLEEP(1.5) | CURRENT_RESOURCE_POOL() |
+------------+-------------------------+
|          0 | medium_run_time         |
+------------+-------------------------+

```

Re-run the previous query that takes 3.0 seconds. It returns the `high_run_time` resource pool from the `select_pool` UDF:

```sql
SELECT SLEEP(3.0), CURRENT_RESOURCE_POOL();

```

```output

+------------+-------------------------+
| SLEEP(3.0) | CURRENT_RESOURCE_POOL() |
+------------+-------------------------+
|          0 | high_run_time           |
+------------+-------------------------+

```

For more information, see [ALTER USER](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/alter-user.md).

***

Modified at: December 1, 2025

Source: [/db/v9.1/user-and-cluster-administration/use-the-workload-manager-and-set-resource-limits/set-resource-limits/select-a-resource-pool-dynamically/](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/use-the-workload-manager-and-set-resource-limits/set-resource-limits/select-a-resource-pool-dynamically/)

(An index of the documentation is available at /llms.txt)