Development Specifications

If the connection pool mechanism is used during application development, comply with the following specifications:

• If GUC parameters are set in the connection, run SET SESSION AUTHORIZATION DEFAULT;RESET ALL; to clear the connection status before you return the connection to the connection pool.
• If a temporary table is used, delete the temporary table before you return the connection to the connection pool.

If you do not do so, the connection in the connection pool will be stateful, which affects subsequent operations on the connection pool.

Overview

Introduction

Although ISO has issued SQL-92, SQL:1999, SQL:2006, and other standards for SQL, due to the characteristics of different databases, the same functions are not the same in the implementation of their products, which also makes the relevant grammatical rules different. Therefore, when formulating specific development specifications, it is necessary to write corresponding specifications for different databases.

This specification emphasizes practicability and operability. According to the common problems and mistakes easily made by developers in the coding process, detailed and clear specifications and constraints are carried out on all aspects of code writing. It mainly includes the following content:

• Naming specification
• Design specification
• Syntax specification
• Optimization-related specification
• PG compatibility

In addition, specific examples are given for each detailed rule of the specification.

Application Scope

This specification applies to MogDB 1.1.0 and later versions.

Naming Specification

Unified Object Naming Specification

The unified standards for naming database objects, such as database, schema, table, column, view, index, constraint, sequence, function, trigger, etc. are as follows:

• The length cannot exceed 63 characters.

• It is advised to use meaningful English vocabularies.

• It is advised to use a combination of lowercase letters, numbers, and underscores.

• It is not advised to use double quotation marks (") unless it must contain special characters such as uppercase letters or spaces.

• It is not advised to start with PG, GS (to avoid confusion with the system DB object), and it is not advised to start with a number.

• It is forbidden to use reserved words. Refer to official documents for reserved keywords.

• The number of columns that a table can contain varies from 250 to 1600 depending on the field type.

Temporary and Backup Object Naming

• It is recommended to add a date to the names of temporary or backup database objects (such as table), for example, dba.trade_record_1970_01_01(where dba is the DBA-specific schema, trade_record is the table name, and 1970_01_01 is the backup date).

Tablespace Naming

• The user tablespace of the database is represented by ts_<tablespace name>, where the tablespace name contains the following two categories:
  1. Data space: For the user's default tablespace, it is represented by default. For other tablespaces, it is represented according to the category of the tables hosted on the tablespace. For example, the table that stores code is represented by code. The table that stores customer information is represented by customer. Try to use one tablespace to host the tables of that category. If a table is particularly large, consider using a separate tablespace.
  2. Index space: add idx_ in front of the name of the corresponding data tablespace. For example, the index space for the user's default tablespace is represented by ts_idx_default. For index tablespace of code table, use ts_idx_code.

Index Naming

• Index object naming rules: table_column_idx, such as student_name_idx, the index naming method is the default naming method when the index name is not explicitly specified when an index is created for the MogDB database.

  Therefore, it is advised to create indexes without naming them explicitly, but using DBMS defaults.

create unique index on departments(department_id);
CREATE INDEX
 \di
+----------+-------------------------------+--------+---------+
| Schema  | Name              | Type  | Owner  |
|----------+-------------------------------+--------+---------|
| mogdb   | departments_department_id_idx | index  | mogdb  |
+----------+-------------------------------+--------+---------+
SELECT 1

Variables Naming

• English words should be used for naming, and pinyin should be avoided, especially pinyin abbreviations should not be used. Chinese or special characters are not allowed in the naming.

• If no complicated operations are involved, simple applications such as counting are always defined by number.

Partitioned Table Naming

• The name of the partitioned table follows the naming rules of ordinary tables.

• A table is partitioned by time range (one partition per month), and the partition name is PART_YYYYMM.

  For example, PART_201901 and PART_201902

Function Naming

• The name should be consistent with its actual function. A verb should be used as a prefix command to cause an action to take place.

  Example: The following naming conforms to the specification:

  func_addgroups (Add multiple groups)
  func_addgroup (Add one group)

Design Specification

Database Design

• It is recommended to name the database after the business function, which is simple and intuitive.

• The database is preferentially created using the PG compatibility type.

• The recommended database encoding is utf8.

Tablespace Design

• The frequently used tables and indexes are stored in a separate tablespace, which should be created on a disk with good performance.

• Tables and indexes that are dominated by historical data or are less active can be stored in tablespaces with poor disk performance.

• Tables and indexes can be stored separately in different tablespaces.

• Tablespaces can also be divided by database, by schema, or by business function.

• Each database/schema corresponds to a tablespace and a corresponding index tablespace.

Schema Design

• When you perform a user creation under a database, a schema with the same name will be created under that database by default.
• It is not recommended to create database objects under the default public schema.
• Create a schema that is different from the username for the business to use.

Table Design

• When designing the table structure, it should be planned to avoid adding fields frequently or modifying field types or lengths.

• Comment information must be added to the table, with the table name matching the comment information.

• The use of the unlogged/ temp/temporary keyword to create business tables is prohibited.

• The data type must be strictly consistent for the fields that are used as join relationships between tables to avoid indexes not working properly.

• It is forbidden to use VARCHAR or other character types to store date values, and if used, operations cannot be done on this field and need to be strictly defined in the data specification.

• For astore tables with frequent updates, it is recommended to specify the table fillfactor=85 when building the table to reserve space for HOT.

• Tables used for frequent updates should be placed separately in a tablespace with good storage performance.

• It is recommended to consider partitioning for tables with data volume over billion or occupying more than 10GB on disk.

• The data types defined in the fields in the table structure are consistent with those defined in the application, and the field proofreading rules are consistent between tables to avoid error reporting or the inability to use indexes.

  Note: For example, the data type of the user_id field of table A is defined as varchar, but the SQL statement is where user_id=1234;

Partitioned Table Design

• The number of partitioned tables is not recommended to exceed 1000.

• Partitioned tables can be selected with different tablespaces by frequency of use.

• The primary key or unique index must contain partitioned keys.

• For tables with larger data volume, partition according to the attributes of table data to get better performance.

• To convert a normal table into a partitioned table, you need to create a new partitioned table, and then import the data from the normal table into the newly created partitioned table. Therefore, when you initially design the table, please plan in advance whether to use partitioned tables according to your business.

• It is recommended that for businesses with regular historical data deletion needs, the tables are partitioned by time, and when deleting, do not use the DELETE operation, but DROP or TRUNCATE the corresponding table.

• It is not recommended to use global indexes in partitioned tables, because doing partition maintenance operations may cause global indexes to fail, making it difficult to maintain.

Use of Partitioned Table

Operate on the range partitioned table as follows.

• Create a tablespace

mogdb=# CREATE TABLESPACE example1 RELATIVE LOCATION 'tablespace1/tablespace_1';
mogdb=# CREATE TABLESPACE example2 RELATIVE LOCATION 'tablespace2/tablespace_2';
mogdb=# CREATE TABLESPACE example3 RELATIVE LOCATION 'tablespace3/tablespace_3';
mogdb=# CREATE TABLESPACE example4 RELATIVE LOCATION 'tablespace4/tablespace_4';

When the following information is displayed, it means the creation is successful.

CREATE TABLESPACE

• Create a partitioned table

mogdb=# CREATE TABLE mogdb_usr.customer_address
(
  ca_address_sk    integer          NOT NULL  ,
  ca_address_id    character(16)       NOT NULL  ,
  ca_street_number   character(10)            ,
  ca_street_name    character varying(60)        ,
  ca_street_type    character(15)            ,
  ca_suite_number   character(10)            ,
  ca_city       character varying(60)        ,
  ca_county      character varying(30)        ,
  ca_state       character(2)             ,
  ca_zip        character(10)            ,
  ca_country      character varying(20)        ,
  ca_gmt_offset    numeric(5,2)             ,
  ca_location_type   character(20)
)
TABLESPACE example1

PARTITION BY RANGE (ca_address_sk)
(
    PARTITION P1 VALUES LESS THAN(5000),
    PARTITION P2 VALUES LESS THAN(10000),
    PARTITION P3 VALUES LESS THAN(15000),
    PARTITION P4 VALUES LESS THAN(20000),
    PARTITION P5 VALUES LESS THAN(25000),
    PARTITION P6 VALUES LESS THAN(30000),
    PARTITION P7 VALUES LESS THAN(40000),
    PARTITION P8 VALUES LESS THAN(MAXVALUE) TABLESPACE example2
)
ENABLE ROW MOVEMENT;

When the following information is displayed, it means the creation is successful.

CREATE TABLE

It is recommended that the number of column-based partitioned tables does not exceed 1,000.

• Insert data

Insert data from table mogdb_usr.customer_address into table mogdb_usr.customer_address_bak. For example, you have created a backup table mogdb_usr.customer_address_bak of table mogdb_usr.customer_address in the database, and now you need to insert the data from table mogdb_usr.customer_address into table mogdb_usr. customer_address_bak, then you can run the following command.

mogdb=# CREATE TABLE mogdb_usr.customer_address_bak
(
  ca_address_sk    integer          NOT NULL  ,
  ca_address_id    character(16)       NOT NULL  ,
  ca_street_number   character(10)            ,
  ca_street_name    character varying(60)        ,
  ca_street_type    character(15)            ,
  ca_suite_number   character(10)            ,
  ca_city       character varying(60)        ,
  ca_county      character varying(30)        ,
  ca_state       character(2)             ,
  ca_zip        character(10)            ,
  ca_country      character varying(20)        ,
  ca_gmt_offset    numeric(5,2)             ,
  ca_location_type   character(20)
)
TABLESPACE example1
PARTITION BY RANGE (ca_address_sk)
(
    PARTITION P1 VALUES LESS THAN(5000),
    PARTITION P2 VALUES LESS THAN(10000),
    PARTITION P3 VALUES LESS THAN(15000),
    PARTITION P4 VALUES LESS THAN(20000),
    PARTITION P5 VALUES LESS THAN(25000),
    PARTITION P6 VALUES LESS THAN(30000),
    PARTITION P7 VALUES LESS THAN(40000),
    PARTITION P8 VALUES LESS THAN(MAXVALUE) TABLESPACE example2
)
ENABLE ROW MOVEMENT;
CREATE TABLE
mogdb=# INSERT INTO mogdb_usr.customer_address_bak SELECT * FROM mogdb_usr.customer_address;
INSERT 0 0

• Alter partitioned table row movement properties

mogdb=# ALTER TABLE mogdb_usr.customer_address_bak DISABLE ROW MOVEMENT;
ALTER TABLE

• Delete a partition

Delete partition P8。

mogdb=# ALTER TABLE mogdb_usr.customer_address_bak DROP PARTITION P8;
ALTER TABLE

• Add a partition

Add partition P8. The range is 40000<= P8<=MAXVALUE.

mogdb=# ALTER TABLE mogdb_usr.customer_address_bak ADD PARTITION P8 VALUES LESS THAN (MAXVALUE);
ALTER TABLE

• Rename a partition

Rename partition P8 as P_9.

mogdb=# ALTER TABLE mogdb_usr.customer_address_bak RENAME PARTITION P8 TO P_9;
ALTER TABLE

Rename partition P_9 as P8.

mogdb=# ALTER TABLE mogdb_usr.customer_address_bak RENAME PARTITION FOR (40000) TO P8;
ALTER TABLE

• Alter the tablespace of partition

Alter the tablespace of partition P6 to example3.

mogdb=#  ALTER TABLE mogdb_usr.customer_address_bak MOVE PARTITION P6 TABLESPACE example3;
ALTER TABLE

Alter the tablespace of partition P4 to example4.

mogdb=#  ALTER TABLE mogdb_usr.customer_address_bak MOVE PARTITION P4 TABLESPACE example4;
ALTER TABLE

• Query a partition

Query partition P6.

mogdb=# SELECT * FROM mogdb_usr.customer_address_bak PARTITION (P6);
mogdb=# SELECT * FROM mogdb_usr.customer_address_bak PARTITION FOR (35888);

• Delete a partitioned table and tablespace

mogdb=# DROP TABLE mogdb_usr.customer_address_bak;
DROP TABLE
mogdb=# DROP TABLESPACE example1;
mogdb=# DROP TABLESPACE example2;
mogdb=# DROP TABLESPACE example3;
mogdb=# DROP TABLESPACE example4;
DROP TABLESPACE

Column Design

• Avoid duplication of column names with system tables.

• Field meanings and data types should be consistent with the program code design.

• All fields must have comment information added.

• Do not use character types when you can use numeric types.

• It is forbidden to store date data in character types.

• Use timestamptz for time type fields.

• Try to require not null for fields and provide default values for fields.

• MogDB new database is compatible with oracle by default, not null constraint does not allow to pass empty string, empty string will be converted to null by default, compatible with PG mode database will not have this problem.

Sequence Design

• Manual creation of table-related sequences is prohibited and should be specified in the serial/bingserial type way.

• It is recommended to set the step size of the sequence to 1.

• It is not recommended to set minvalue and maxvalue.

• It is not recommended to set cache, the serial number is not consecutive after setting cache.

• It is prohibited to turn on cycle.

• Serial should be consistent with the type and range of variable definition in the code to prevent the inability to insert data.

Constraint Design

Primary Key Constraint

• Each table must include a primary key.

• It is not recommended that the name of the primary key has the service meaning, such as identification certificate or country name although the name is unique.

• It is recommended that a primary key is written as id serial primary key or id bigserial primary key.

• It is recommended that the primary key in a large-sized table can be written as follows, which is easy to maintain later.

  create table test(id serial not null );
  create unique index CONCURRENTLY ON test (id);

Unique Constraint

Apart from the primary key, unique constraint is needed. You can create a unique index with uk_ as the prefix to create unique constraint.

Foreign Key Constraint

• You'd better create foreign key constraints for a table with foreign key relationship.
• The use of foreign keys is not recommended for systems with high performance requirements and security under your control.
• When using the foreign key, you must set the action of the foreign key, such as cascade, set null, or set default.

Non-Empty Column

• All non-null columns must have the not null constraint added

Check Constraint

• For fields with the check constraint, it is required to specify the check rules, such as the gender and status fields.

Index Design

• The number of table indexes for frequent DML operations is not recommended to exceed 5.
• Add concurrently parameter when create/drop index.
• Virtual indexes can be used to determine the validity of indexes before actually creating them.
• Create indexes for fields that frequently appear after the keywords order by, group by, and distinguish.
• Fields that are often used as query selections to create indexes.
• Indexes on attributes that are often used as table joins.
• The number of fields in a composite index is not recommended to exceed 3.
• Composite indexes should have one field that is a common search condition.
• The first field of a composite index should not have a single-field index.
• For tables where data is rarely updated and only a few of the fields are frequently queried, consider using index overrides.
• Do not create indexes on fields that have a large number of identical fetch values.
• It is recommended to use unique index instead of unique constraints for subsequent maintenance.
• It is recommended to build compound indexes with multiple fields for high frequency queries with multiple fields and conditions in where, with reference to the data distribution.
• Useless indexes and duplicate indexes should be deleted to avoid negative impact on the execution plan and database performance.

View Design

• You'd better use simple views and use less complex views.

  Simple view: Data comes from a single table and a simple view does not contain groups of data and functions.

  Complex view: Data comes from multiple tables, or a complex view contains groups of data or functions. A complex view can contain three tables at most.

• You'd better not use nested views. If nested views have to be used, it is advised to have two levels of nesting at most.

Function Design

• A function must retrieve database table records or other database objects, or even modify database information, such as Insert, Delete, Update, Drop, or Create.
• If a function does not relate to a database, it cannot be realized using a database function.
• It is not advised to use DML or DDL statements in a function.

Syntax Specification

About NULL

• Description: NULL judgment: IS NULL, IS NOT NULL.

• Description: Beware of boolean types taking the values true, false, NULL.

• Description: Beware of NOT IN collections with NULL elements.

• Recommendation: Use count(1) or count(*) to count rows, but not count(col) to count rows, because NULL values will not be counted.

• Rule: When count(multi-column names), the multi-column names must be enclosed in parentheses, e.g. count( (col1,col2,col3) ).

• Note: With multi-column count, the row is counted even if all columns are NULL, so the effect is the same as count(*).

• Note: count(distingu col) counts the number of non-NULL non-repeats of a column, NULL is not counted; count(distingu (col1,col2,...) ) counts the unique values of multiple columns, NULL is counted, while NULL and NULL are considered the same.

• Note: Distinction between count and sum of NULL

  select count(1), count(a), sum(a)  from (SELECT * FROM (VALUES (NULL), (2) ) v(a)) as foo where a is NULL;
  count | count | sum
  -------+-------+-----
     1 |   0 |
  (1 row)

• Check whether two values are the same (NULL is considered as the same value).

  select null is distinct from null;
  ?column?
  \---------
  f
  (1 row)
  
  select null is distinct from 1;
  ?column?
  \---------
  t
  (1 row)
  
  select null is not distinct from null;
  ?column?
  \---------
  t
  (1 row)
  
  select null is not distinct from 1;
  ?column?
  \---------
  f
  (1 row)

About Invalid Indexes

• During SQL statement writing, functions and expressions are usually used in query operations. It is not recommended that functions and expressions are used in condition columns. Using a function or expression in a condition column will make indexes of the condition column unused, thereby affecting the SQL query efficiency. It is recommended that functions or expressions are used in condition values. For example,

  select name from tab where id+100>1000;

  This statement can be changed to the following:

  select name from tab where id>1000-100;

• Do not use left fuzzy query. For example,

  select id from tab where name like '%ly';

• Do not use the negative query, such as not in/like. For example,

  select id from tab where name not in ('ly','ty');

Ensuring That All Variables and Parameters Are Used

• Declare-variable also generates certain system overhead and makes code look loose. If some variables are not used in compilation, they will report alarms. Make sure that no any alarm is reported.

Query Operations

DDL Operation

• Any DDL operations on existing tables are prohibited during peak business periods

• All production DDL operations must be verified by the development test environment

• Concurrently should be used when maintaining indexes

• pg_repack should be used instead of vacuum full to rebuild the table

• When adding fields with default values to a large table, it should be split into three parts: adding fields, filling default values and adding non-null constraints, such as breaking alter table t add column col datatype not null default xxx; into the following, to avoid too long a table lock caused by filling default values

  alter table t add column col datatype ；
  alter table t alter column col set default xxx；
  update table t  set column= DEFAULT where id in ( select id from t where column is null limit
  1000 ) ; \watch 3
  alter table t alter column col set not null；

DML Operation

• The SQL statement for updating data is prohibited to appear where 1=1

• The amount of data operated by a single DML statement should not exceed 100,000

• When clearing the data in the table, truncate should be used

• For risky operations, you should open the transaction and confirm it before committing.

• The SQL logic in the transaction should be as simple as possible, and the operation should be submitted in time after execution to avoid idle in transaction status.

• Use copy instead of insert when importing a large amount of data.

• Consider deleting indexes before importing data, and rebuild them after importing.

DQL Operation

• Prohibit the use of select *, apply the specific required field substitution

• Prohibit the use of where 1=1 to avoid full table scan or Cartesian product

• The search condition value should be consistent with the field type to prevent not going to the index

• Fields to the left of the equal sign should be consistent with the index, especially conditional or functional indexes

• Pay attention to the execution plan of slow SQL, if it is not consistent with the expectation, change it as soon as possible

• Use count(*) or count(1) to count rows, count(column) will not count null rows

• Limit the number of join, no more than 3 are recommended

• Recursive queries need to be limited to prevent infinite loops

• For or operations, you should use union all or union instead

Data Import

• When a large amount of data needs to be stored in a table, it is recommended that COPY is used rather than INSERT. This can improve the data write speed.
• Before data is imported, delete related indexes. After the import is complete, recreate indexes. This can improve the data import speed.

Transaction Operation

• Make sure that the SQL logic in a transaction is simple, the granularity of each transaction is small, less resources are locked, lock and deadlock can be avoided, and transaction can be committed in a timely manner after being executed.
• For DDL operations, especially multiple DDL operations, including CRAETE, DROP, and ALTER, do not explicitly start a transaction because the lock mode value is very high and deadlock easily occurs.
• If the state of the master node is idle in transaction, related resources will be locked, thereby leading to lock, even deadlock. If the state of the slave node is idle in transaction, synchronization between the master and slave nodes will be suspended.

Others

• For instances running in SSDs, it is recommended that the value of random_page_cost (default value: 4) is set to a value ranging from 1.0 to 2.0. This can make the query planner preferably use the index to perform scanning.
• In the scenario where EXPLAIN ANALYZE needs to be used to view the actual execution plan and time, if a write query is to be performed, it is strongly recommended that a transaction is started first and then rollback is performed.
• For tables frequently updated and with the data size largely increased, table reorganization should be performed in appropriate time to lower the high water mark.

PostgreSQL Compatibility

Database Creation Specifications

During MogDB database creation, the following PG compatibility mode is used:

create database dbnam DBCOMPATIBILITY='PG' encoding=’utf8’;

Data Type

Value Type

During development and usage, MogDB supports only the smallint, integer, bigint, numeric[(p[,s])], serial, and bigserial value types.

Character Type

During the development, MogDB supports only the char(n), varchar(n), and text character types.

Time Type

During the development, MogDB supports only the timestamp[(p )][with time zone] and date time types.

JSON Type

MogDB supports only the JSON type.

Keywords

In the following table, Reserved indicates that keywords in a database are reserved and cannot be customized. Non-reserved or N/A indicates that keywords can be customized.

Implicit Conversion Comparison Table