Default Custom User Resource Pool

Posted May 9, 2023 by Sruthi Anumula, Senior Database Support Engineer

Tips in bright neon red letters on a dark background with touchscreens.

In Vertica versions 12.0.4 and later, you can set a default user resource pool for all users instead of defaulting users to a general pool, using the DefaultResourcePoolForUsers configuration parameter.

How to set it?
As a dbadmin user, create a user resource pool. The pool name can be used to configure the DefaultResourcePoolForUsers option.
In the following example, we create a pool called customdefault pool, make it the default resource pool for all users, and provide the user bob access to the resource pool.

dbadmin=> CREATE RESOURCE POOL customdefault_pool MEMORYSIZE '1G';
dbadmin=> select set_config_parameter('DefaultResourcePoolForUsers','customdefault_pool');
Parameter set successfully
(1 row)

dbadmin=> GRANT USAGE on RESOURCE POOL customdefault_pool to bob;

What happens if my existing users already have a default resource pool defined during user creation prior to Vertica 12.0.3? What happens if I create a new user with default resource pool with Vertica 12.0.4?
If a user has already configured a resource pool, that resource pool will take precedence, and that user will not be able to utilize the resource pool set to the ‘DefaultResourcePoolForUsers’ configuration parameter.

Now that I have upgraded to Vertica 12.0.4, how can I drop all the other resource pools that users are tagged to and start using the new feature?
The first step is to grant all users with USAGE Privilege on the custom user resource pool (customdefault_pool, in this example). After that, you will be able to drop all the other resource pools.

For more details, see Resource management in the Vertica documentation.