Overview

Updated on 2024-12-18 GMT+08:00

View PDF

Objective

This document outlines the rules for design and development that need to be followed when developing the GaussDB(DWS) database. The objective is to enhance development efficiency and ensure the continuity and stability of the service.

Application Scope

These specifications apply to all GaussDB(DWS) self-development scenarios, including designing and developing applications and database services.

Terms

Rule: a mandatory requirement that must be followed during database design and development.

Suggestion: an option that you need to consider for the design and development process.

Description: a detailed explanation of a rule or suggestion.

Overall Development and Design Specifications

The table below provides a list of development and design specifications that must be followed during GaussDB(DWS) development. You can click the links to access the corresponding rules for more details.

**Table 1** GaussDB(DWS) development and design specifications
No.	Category		Rule/Suggestion
1	Connection management regulations	-	Rule 1.1: Configuring Load Balancing for GaussDB(DWS) Clusters
2			Rule 1.2: Ending the Database Connection After Necessary Operations (Except in Connection Pool Scenarios)
3			Rule 1.3: Ensuring a Started Transaction Is Committed or Rolled Back
4			Rule 1.4: Ensuring the Idle Timeout Duration Is Shorter Than SESSION_TIMEOUT Value When Connection Pool Is Used for Applications
5			Rule 1.5: Restoring Parameters to Default Values in Connections Before Returning Them to the Pool
6			Rule 1.6: Manually Clearing Temporary Tables Created with a Connection Before Returning it to the Pool
7	Object design specifications	DATABASE object design	Rule 2.1: Avoiding Direct Usage of Built-in Databases Such As postgres and gaussdb
8			Rule 2.2: Selecting the Suitable Database Code During Database Creation
9			Rule 2.3: Choosing the Right Database Type for Compatibility with the Database to Be Created
10			Suggestion 2.4: Storing Objects with Associated Calculations in the Same Database
11		USER object design	Rule 2.5: Following the Least Privilege Principle and Avoiding Running Services Using Users with Special Permissions
12		USER object design	Rule 2.6: Avoiding the Use of a Single Database Account for All Services
13		SCHEMA object design	Suggestion 2.7: Avoiding the Creation of Objects Under Other Users' Private Schemas
14		TABLESPACE object design	Rule 2.8 Avoiding Tablespace Customization
15		TABLE object design (prioritized)	Rule 2.9: Selecting the Optimal Distribution Method and Columns During Table Creation
16			Rule 2.10 Selecting an Optimal Storage Type During Table Creation
17			Rule 2.11 Selecting an Optimal Partitioning Policy During Table Creation
18			Suggestion 2.12: Designing Table Columns for Fast and Accurate Queries
19			Suggestion 2.13: Avoiding the Usage of Auto-increment Columns or Data Types
20		INDEX object design (prioritized)	Rule 2.14: Creating Necessary Indexes and Selecting Optimal Columns and Sequences for Them
21		INDEX object design (prioritized)	Suggestion 2.15: Optimizing Performance by Choosing the Right Index Type and Avoiding Indexes for Column-Store Tables
22		VIEW object design	Suggestion 2.16: Limiting View Nesting to Three Layers
23	SQL development specifications	DDL operation specifications	Suggestion 3.1: Avoiding Performing DDL Operations (Except CREATE) During Peak Hours or in Long Transactions
24		DDL operation specifications	Rule 3.2: Specifying the Scope of Objects to Be Deleted When Using DROP
25		INSERT operation specifications	Rule 3.3: Replacing INSERT with COPY for Efficient Multi-Value Batch Insertion
26		INSERT operation specifications	Suggestion 3.4: Avoiding Performing Real-time INSERT Operations on Common Column-store Tables
27		UPDATE/DELETE operation specifications	Suggestion 3.5: Preventing Simultaneous Updates or Deletions of the Same Row in a Row-store Table
28		UPDATE/DELETE operation specifications	Suggestion 3.6: Avoiding Frequent or Simultaneous UPDATE and DELETE Operations on Column-store Tables
29		SELECT operation specifications	Rule 3.7: Avoiding Executing SQL Statements That Do Not Support Pushdown
30			Rule 3.8: Specifying Association Conditions when Multiple Tables Are Associated
31			Rule 3.9: Ensuring Consistency of Data Types in Associated Fields across Multiple Tables
32			Suggestion 3.10: Avoiding Function Calculation on Association and Filter Condition Fields
33			Suggestion 3.11: Performing Pressure Tests and Concurrency Control for Resource-intensive SQL Statements
34			Rule 3.12: Avoiding Excessive COUNT Operations on Large Row-store Tables
35			Suggestion 3.13: Avoid Getting Large Result Sets (Except for Data Exports)
36			Suggestion 3.14: Avoiding the Usage of SELECT * for Queries
37			Suggestion 3.15: Using WITH RECURSIVE with Defined Termination Condition for Recursion
38			Suggestion 3.16: Setting Schema Prefix for Table and Function Access
39			Suggestion 3.17: Identifying an SQL Statement with a Unique SQL Comment
40	Stored procedure development specifications	-	Suggestion 4.1: Simplifying Stored Procedures and Avoiding Nesting
41	Stored procedure development specifications	-	Rule 4.2: Avoiding Non-CREATE DDL Operations in Stored Procedures