Workload routing

Workload routing routes client connections to subclusters based on their workloads. This lets you reserve subclusters for certain types of tasks.

When a client first connects to Vertica, they connect to a Connection node, which then routes the client to the correct subcluster based on the client's specified workload and the routing rules. If multiple subclusters are associated with the same workload, the client is routed to a random subcluster in that list.

In this context, "routing" refers to the connection node acting as a proxy for the client and the Execution node in the target subcluster. All queries and query results are first sent to the connection node and then passed on to the execution node and client, respectively.

The primary advantages of this type of load balancing are as follows:

• Database administrators can associate certain subclusters with certain workloads (as opposed to client IP addresses).
• Clients do not need to know anything about the subcluster they will be routed to, only the type of workload they have.

Workload routing depends on actions from both the database administrator and the client:

• The database administrator must create rules for handling various workloads.
• The client must specify the type of workload they have.

View the current workload

To view the workload associated with the current session, use SHOW WORKLOAD:

=> SHOW WORKLOAD;
   name   |  setting
----------+------------
 workload | my_worload
(1 row)

Create workload routing rules

To route client connections to a subcluster, create a routing rule, specifying the name of the workload and the list of subclusters that should handle clients with that workload. If you specify more than one subcluster, the client is randomly routed to one of them.

For example, when a client connects to the database and specifies the analytics workload, their connection is randomly routed to either sc_analytics or sc_analytics_2:

=> CREATE ROUTING RULE ROUTE WORKLOAD analytics TO SUBCLUSTER sc_analytics, sc_analytics_2;

To alter a routing rule, use ALTER ROUTING RULE. For example, to route analytics workloads to sc_analytics:

=> ALTER ROUTING RULE FOR WORKLOAD analytics SET SUBCLUSTER TO sc_analytics;

To drop a routing rule, use DROP ROUTING RULE and specify the workload. For example, to drop the routing rule for the analytics workload:

=> DROP ROUTING RULE FOR WORKLOAD analytics;

To view existing routing rules, see WORKLOAD_ROUTING_RULES.

Specify a workload

The workload for a given connection is reported by the client when they connect. The method for specifying a workload depends on the client.

The following examples set the workload to analytics for several clients. After you connect, you can verify that the workload was set with SHOW WORKLOAD.

vsql uses --workload:

$ vsql --dbname databaseName --host node01.example.com --username Bob --password my_pwd --workload analytics

JDBC uses workload:

jdbc:vertica://node01.example.com:5443/databaseName?user=analytics_user&password=***&workload=analytics

ODBC uses Workload:

Database=databaseName;Servername=node01.mydomain.com;Port=5433;UID=analytics_user;PWD=***;Workload=analytics

ADO.NET uses Workload:

Database=databaseName;Host=node01.mydomain.com;Port=5433;User=analytics_user;Password=***;Workload=analytics

vertica-sql-go uses Workload:

var query = url.URL{
    Scheme:   "vertica",
    User:     url.UserPassword(user, password),
    Host:     fmt.Sprintf("%s:%d", host, port),
    Path:     databaseName,
    Workload: "analytics",
    RawQuery: rawQuery.Encode(),
}

vertica-python uses workload:

conn_info = {'host': '127.0.0.1',
            'port': 5433,
            'user': 'some_user',
            'password': 'my_pwd',
            'database': 'databaseName',
            'workload': 'analytics',
            # autogenerated session label by default,
            'session_label': 'some_label',
            # default throw error on invalid UTF-8 results
            'unicode_error': 'strict',
            # SSL is disabled by default
            'ssl': False,
            # autocommit is off by default
            'autocommit': True,
            # using server-side prepared statements is disabled by default
            'use_prepared_statements': False,
            # connection timeout is not enabled by default
            # 5 seconds timeout for a socket operation (Establishing a TCP connection or read/write operation)
            'connection_timeout': 5}

vertica-nodejs uses workload:

const client = new Client({
    user:     "vertica_user",
    host:     "node01.example.com",
    database: "verticadb",
    password: "",
    port:     "5433",
    workload: "analytics",
})

Clients can also change their workload type after they connect with SET SESSION WORKLOAD:

=> SET SESSION WORKLOAD my_workload;