Hologres:Use Serverless Computing for data reads and writes

Supported features

Serverless Computing supports the following task types:

• Since V2.1.17: Data Manipulation Language (DML) tasks, including INSERT (excluding INSERT OVERWRITE), INSERT ON CONFLICT (UPSERT), DELETE, and UPDATE.

• Since V2.2.14: Data Query Language (DQL) tasks.

• Since V3.0.1: COPY tasks in DML.

• Since V3.0.9: CREATE TABLE AS (CTAS) tasks.

• Since V2.2.42/V3.0.19: RESHARDING tasks.

• Since V3.0.26: INSERT OVERWRITE tasks (via the hg_insert_overwrite stored procedure), and DML and DQL within stored procedures.

• Since V3.1.0: Native INSERT OVERWRITE syntax in DML, and REBUILD tasks.

• Since V3.1.3: Read and write operations on encrypted tables, including encrypted internal tables and encrypted MaxCompute foreign/external tables. To use this feature, enable the hg_serverless_computing_enable_encrypted_table GUC parameter. For more information, see Data encryption.

• Since V3.1.11: Compaction tasks. For more information, see Use Serverless Computing to execute Compaction tasks.

• Since 4.0: Read and write operations on Data Lake Formation (DLF) foreign tables. See Use DLF to read data from and write data to OSS.

Serverless Computing supports the following function extensions:

• ClickHouse

• Flow Analysis

• PostGIS

• RoaringBitmap

• BSI

• Proxima

Serverless Computing is not supported in the following scenarios:

• Queries on read-only secondary instances. Primary instances and virtual warehouse instances are not affected.

• Queries with multi-row DML transactions enabled, such as set hg_experimental_enable_transaction = on.

• SQL statements that use Fixed Plan.

• Cross-database queries or writes.

Use Serverless Computing resources to execute read and write tasks

Hologres lets you use Serverless Computing resources flexibly. You can run individual SQL statements on serverless resources on demand, or set up rules to execute SQL statements in batches on serverless resources.

To dispatch SQL statements to serverless resources before execution, use the following methods, in descending order of priority:

1. SQL level (session level): Set the hg_computing_resource parameter to 'serverless'. Read and write requests in the current connection use serverless resources.

2. User level: Set the hg_computing_resource parameter to 'serverless' for a specific user. All queries from this user use serverless resources.

3. Query Queue level: Configure all tasks in a query queue to use serverless resources.

4. Adaptive Serverless Computing: When enabled, the system automatically identifies large tasks and executes them by using serverless resources.

5. If none of these conditions are met, the query uses dedicated resources of the current instance.

You can also configure large queries that are already in progress to be automatically re-run on serverless resources. This helps reduce the load on your production services, lower the frequency of Out-of-Memory (OOM) errors, and improve system stability.

-- Use Serverless Computing resources to execute SQL statements. The parameter's default value is local, which indicates the dedicated resources of the current instance are used to execute SQL statements.
SET hg_computing_resource = 'serverless';

-- After you submit a DML statement, reset the configuration to ensure unnecessary SQL statements do not use Serverless resources.
RESET hg_computing_resource;

-- Prepare data --
    -- Create a source table.
CREATE TABLE source_tbl ( id int );
    -- Create a destination table.
CREATE TABLE sink_tbl ( id int );
    -- Write sample data to the source table.
INSERT INTO source_tbl
SELECT * FROM generate_series(1, 99999999);

-- Use serverless resources to execute the SQL statement --
    -- Enable serverless computing at the session level.
SET hg_computing_resource = 'serverless';
    -- Execute the SQL statement.
INSERT INTO sink_tbl SELECT * FROM source_tbl;
    -- Reset the configuration.
RESET hg_computing_resource;

-- Configure a user to use Serverless Computing resources by default for a specific database
ALTER USER "<role_name>" IN DATABASE <db_name> SET hg_computing_resource = 'serverless';

-- Reset the configuration
ALTER USER "<role_name>" IN DATABASE <db_name> RESET hg_computing_resource;

Set the priority for serverless computing tasks

Hologres lets you set priorities for SQL statements that enter the Serverless Computing queue. By defining the maximum amount of serverless resources a single SQL statement can use and implementing a priority-based queuing mechanism, you can ensure that SQL statements in the Serverless Computing queue execute stably and orderly.

• Use the following code to set the execution priority for SQL statements queueing for serverless resources.

  The priority ranges from 1 to 5, with a default value of 3. A higher value indicates a higher priority, meaning the task receives resources first, regardless of the amount requested.

  -- Configure at the session level
  SET hg_experimental_serverless_computing_query_priority = 5;
  -- Reset the configuration.
  RESET hg_experimental_serverless_computing_query_priority;
  
  -- Configure at the user level
  ALTER USER "<role_name>" IN DATABASE <db_name> SET hg_experimental_serverless_computing_query_priority = 5;
  -- Reset the configuration.
  ALTER USER "<role_name>" IN DATABASE <db_name> RESET hg_experimental_serverless_computing_query_priority;

• Example

  -- Use serverless resources to execute the SQL statement.
  SET hg_computing_resource = 'serverless';
  
  -- Set the priority of this SQL statement to 5.
  SET hg_experimental_serverless_computing_query_priority to 5;
  
  -- Execute the SQL statement
  INSERT INTO sink_tbl SELECT * FROM source_tbl;
   
  -- Reset the configuration
  RESET hg_computing_resource;
  RESET hg_experimental_serverless_computing_query_priority;

Verify the serverless computing configuration

Use the following code to view the execution plan for the preceding SQL statement.

-- Use serverless resources to execute the SQL statement.
SET hg_computing_resource = 'serverless';

-- Explain the execution statement.
EXPLAIN INSERT INTO sink_tbl SELECT * FROM source_tbl;

-- Reset the configuration.
RESET hg_computing_resource;

The code returns the following result. If the output contains Computing Resource: Serverless, it indicates that the SQL statement uses Serverless Computing resources.

                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Gather  (cost=0.00..5.18 rows=100 width=4)
   ->  Insert  (cost=0.00..5.18 rows=100 width=4)
         ->  Redistribution  (cost=0.00..5.10 rows=100 width=4)
               ->  Local Gather  (cost=0.00..5.10 rows=100 width=4)
                     ->  Decode  (cost=0.00..5.10 rows=100 width=4)
                           ->  Seq Scan on source_tbl  (cost=0.00..5.00 rows=100 width=4)
 Computing Resource: Serverless
 Optimizer: HQO version 2.1.0
(8 rows)

You can also view running and historical tasks on serverless resources. For more information, see Serverless Computing monitoring and O&M.