Create and Manage Warehouses

In SynxDB Cloud, warehouses are compute engines within an account to process queries. Each warehouse is composed of multiple segments, which can be scaled horizontally as needed. All warehouses within a single account have access to a shared pool of data and perform computing tasks independently. Key features of warehouses include:

Resource management: Allows pausing, resuming, and resizing on demand.
Scaling: Supports changing segment counts for online horizontal scaling.

This document introduces how to create and manage warehouses in SynxDB Cloud.

Check existing warehouses

To check the existing warehouses, you can use either of the methods:

Use psql to run the following SQL command:
```
TABLE gp_warehouse;
```
Use the SynxDB Cloud console. Click Warehouses in the left navigation menu to enter the warehouse management page, and you will see a list of existing warehouses.

Create a warehouse

To create a warehouse, you can use either of the methods:

Use psql to run the following SQL command:
```
CREATE WAREHOUSE <warehouse_name> [ SIZE <segment_count> ] [ PROFILE <specification> ];
```
Parameters:
- <warehouse_name>: The name of the warehouse to be created. This parameter is required.
- <segment_count>: The number of Segments to be allocated to the warehouse. This parameter is optional. The default value is 3.
- <specification>: The specification of the warehouse. A specification can define the CPU, memory and storage. of the machine. This parameter is optional. The default value is an empty string ''. Users can customize specifications, for example, 'standard'.
Note

Only users who have permissions in the user list of the current account can use this SQL statement to create a warehouse. Otherwise, a permission error is prompted.

For example:
```
CREATE WAREHOUSE my_warehouse SIZE 2;
```

Set and use a warehouse

Before updating data in a table or deleting database objects, you need to set and use a warehouse. To set a warehouse, you can use either of the methods:

Use psql to run the following SQL command:
```
SET WAREHOUSE to <warehouse_name>;
```
In the command above, replace <warehouse_name> with the name of the warehouse you want to use.

Change the size of a warehouse

You can elastically scale warehouses to meet dynamic business workloads. You can use either of the methods:

Use psql to run the following SQL command. Increase the value for scaling out or decrease it for scaling in based on your requirements.
```
ALTER WAREHOUSE <warehouse_name> SET WAREHOUSE_SIZE <new_segment_count>;
```
For example:
```
ALTER WAREHOUSE my_warehouse SET WAREHOUSE_SIZE 4;
```

Optimize query execution for warehouses

In high-concurrency scenarios, if a data warehouse has too many segment nodes (processes), frequent process scheduling and context switching can create significant overhead, affecting query performance. In some cases, reducing the number of segments involved in a query can actually lead to better execution performance. To address such scenarios, SynxDB Cloud allows you to set the number of segments to be used for a query at the session level.

You can control the number of segments used in the current session by setting the cloud.session_segments parameter. The system will randomly select the specified number of segments from the current data warehouse to execute the query.

SET cloud.session_segments = <number_of_segments>;

Setting number_of_segments to 0 (the default) means the query will use all segments of the current data warehouse. Setting it to a positive integer N means the query will be executed on N randomly selected segments from the current data warehouse.

Note

The value of number_of_segments cannot exceed the total number of segments in the current data warehouse.
This parameter cannot be set within a transaction block (BEGIN...COMMIT).
To restore the default behavior, execute RESET cloud.session_segments; or SET cloud.session_segments = 0;.

Example:

Suppose my_warehouse has a total of 4 segments. Now, you want to use only 2 of them to execute a query.

Switch to the target warehouse:
```
SET warehouse = my_warehouse;
```
Set the number of segments for the session:
```
SET cloud.session_segments = 2;
```
Execute the query. At this point, the query will run on only 2 randomly selected segments from my_warehouse:
```
SELECT * FROM my_table;
```
You can use the EXPLAIN command to view the query plan and confirm the number of segments used for execution.
Restore the use of all segments:
```
RESET cloud.session_segments;
```

Stop a warehouse

To stop a warehouse, you can use either of the methods:

Use psql to run the following SQL command:

ALTER WAREHOUSE <warehouse_name> SUSPEND;

Use the SynxDB Cloud console. Click Warehouses in the left navigation menu to enter the warehouse management page, and then click the stop button in the Start/Stop column of the warehouse you want to stop.

Resume a warehouse

To resume a warehouse, you can use either of the methods:

Use psql to run the following SQL command:

ALTER WAREHOUSE <warehouse_name> RESUME;

Use the SynxDB Cloud console. Click Warehouses in the left navigation menu to enter the warehouse management page, and then click the start button in the Start/Stop column of the warehouse you want to resume.

Delete a warehouse

To delete a warehouse, you can use either of the methods:

Use psql to run the following SQL command:
```
DROP WAREHOUSE <warehouse_name>;
```
Use the SynxDB Cloud console. Click Warehouses in the left navigation menu to enter the warehouse management page, and then click Delete in the Operation column of the warehouse you want to delete.