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

• Commonly used functions

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:

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

• It is advised to use meaningful English vocabularies.

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

• The length cannot exceed 63 characters.

• 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_2020_12_08 (where dba is the DBA-specific schema, trade_record is the table name, and 2020_12_08 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.
• The tablespace name is prohibited to start with PG_.

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

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

• The database encoding can use only utf8.

Tablespace Design

• Generally larger tables or indexes use a separate tablespace.

• The objects for which high frequency insert statements need to be run are divided into a group and stored in the corresponding tablespace.

• The objects added, deleted, and modified are divided into groups and stored in the corresponding tablespace.

• Tables and indexes are stored in separate tablespaces.

• In principle, each schema corresponds to a tablespace and a corresponding index tablespace; each large table under a schema corresponds to a separate tablespace and index tablespace.

Table Design

• When designing a table structure, you should plan well to avoid adding fields frequently, or modifying field types or lengths.

• You must add comment information to the table, and make sure that the table name matches the comment information.

• It is forbidden to use the unlogged keyword to create a new table. By default, a non-compressed row-based table is created.

• When each table is created, you must specify the tablespace where it is located. Do not use the default tablespace to prevent the table from being built on the system tablespace and thereby causing performance problems. For data tables with busy transactions, they must be stored in a dedicated tablespace.

• The data types of the fields used for the connection relationship between the tables must be strictly consistent to avoid the inability of the index to be used normally.

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

• The field must be added with a comment that can clearly indicate its meaning, and the description of each state value must be clearly listed in the comment of the state field.

• For frequently updated tables, it is advised to specify fillfactor=85 during table creation, and reserve 15% of the space on each page for HOT updates.

• The data type defined by the field in the table structure is consistent with that in the application, and the field collation rules between tables are consistent to avoid errors or 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 partitioned tables supported by MogDB database are range partitioned tables.

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

• The primary key or unique index must contain the partition key.

• For tables with a relatively large amount of data, they should be partitioned according to the properties of the table data to get a 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.

• For businesses with regular historical data deletion needs, it is recommended to partition the tables by time and not use the DELETE operation when deleting, but DROP or TRUNCATE the corresponding table.

• It is not recommended to use a global index in a partitioned table, because the partition maintenance operation may cause the global index to fail and make 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

• It is recommended to avoid using character types when numeric types can be used.

• It is recommended to avoid using char(N) if you can use varchar(N), and avoid using text and varchar if you can use varchar(N).

• Only char(N), varchar(N) and text character types are allowed.

• The newly created MogDB database is compatible with Oracle by default, and the not null constraint does not support empty strings. Empty strings will be converted to null by default. Databases compatible with the PG mode will not have this problem.

• It is recommended to use timestamp with time zone (timestamptz) instead of timestamp without time zone.

• It is recommended to use NUMERIC (precision, scale) to store currency amounts and other values that require precise calculations, but not to use real, double precision.

Sequence Design

• It is forbidden to manually add sequences related to the table.

• A sequence is created by specifying the serial or bigserial type of the column when a table is created.

• The sequence should be consistent with the variable definition type and range in the code to prevent data from being unable to be inserted.

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.
• 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-empty columns must be clearly marked as NOT NULL during database creation. After the database is used, no change can be performed. Additionally, you need to pay attention to the difference of the query results between NULL and "": null will be converted to NULL while "" does not display any character.

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

• MogDB provides the row-store and column-store tables. The row-store table supports the btree (default), gin, and gist index types. The column-store table supports the Psort (default), btree, and gin index types.
• It is recommended that the CONCURRENTLY parameter is added when you create or drop an index. This can achieve concurrency when data is written into a table. The column-store, partition, and temporary tables do not support index created CONCURRENTLY.
• It is recommended that "create index CONCURRENTLY" and "drop index CONCURRENTLY" are used to maintain the related indexes of a table whose columns included in the indexes are frequently updated and deleted.
• It is recommended that unique index is used to replace unique constraints, facilitating follow-up maintenance.
• It is recommended that a joint index of multiple fields are created based on data distribution for a high-frequency query in which there are multiple fields and conditions in the where statement.
• Each table can include five indexes at most.
• Deep analysis is required for creation of composite indexes.
  • The first field in a composite index needs to be correctly chosen. Generally, it has good selectivity and is a common field in the where clause.
  • If several fields in a composite index are usually presented in a where clause and linked with AND, and single-field query is less or even not involved, you can create a composite index. Otherwise, you can create a single-field index.
  • If several fields in a composite index are usually presented in a where clause individually, they can be divided into multiple single-field indexes.
  • If both single-field index and composite index with the single field as its first column, the single-field index can be deleted.
  • Typically, the first field in a composite index cannot be a time field because the time field is used to scan a range. However, when the former fields are scanned by range, the latter fields cannot be used for index filtration.
  • A composite index can include four fields at most.
• For a table with the number of write times significantly greater than that of read times, you'd better not create too many indexes.
• Unused indexes and duplicated indexes should be deleted so that the execution plan and database performance are not affected.

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

• Note: Check whether it is null or is not null.
• Note: The values of the boolean type can be true, false, and NULL.
• Note: Pay attention to that the NOT IN set includes some NULL elements.

mogdb=# SELECT * FROM (VALUES(1),(2)) v(a) ;  a

\---

 1

 2

(2 rows)

mogdb=# select 1 NOT IN (1,NULL);

?column?

\---------

f

(1 row)

mogdb=# select 2 NOT IN (1,NULL);

?column?

\---------

(1 row)

mogdb=# SELECT * FROM (VALUES(1),(2)) v(a) WHERE a NOT IN (1, NULL);  a

\---

(0 rows)

• Suggestion: It is recommended that count(1) or count(*) is used to count the number of rows. count(col) is not used to count the number of rows because the NULL value is not counted.
• Rule: For count(names of multiple columns), the names of multiple columns must be enclosed in brackets, for example count((col1,col2,col3)).
• Note: For count (names of multiple columns), even if the values of all columns are null, the columns will also be counted. Therefore, the calculating result of count(names of multiple columns) is consistent with that of count(*).
• Note: count(distinct col) is used to count the number of values that are distinct from each other and not null.

count(distinct (col1,col2,...)) is used to calculate the unique value of those of all columns where NULL is counted. Additionally, two NULL values 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

• Database object, especially columns with comments added can facilitate service learning and maintenance.
• DDL sent to DBAs, which is attached with common SQLs, such as SELECT, INSERT, DELETE, and UPDATE, can assist DBAs providing optimization suggestions, including creating index CONCURRENTLY.
• When columns need to be added to a large-sized table, "alter table t add column col datatype not null default xxx" can be processed as follows. This can prevent the table from being locked due to long time for filling in the 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

• When updating a table, the "<>" judgement is needed. For example, the statement "update table_a set column_b = c where column_b <> c" indicates that a table needs to be updated to make the value of column b equal to that of column c if the value of column b is not equal to that of column c. In the statement, it is prohibited that the value of column b is equal to that of column c in the where clause.
• A single DML statement can support a maximum of 100 thousand data records.
• When a table needs to be cleared, it is recommended that TRUNCATE is used rather than DELETE.

DQL Operation

• Typically, it is prohibited to use select *. Selecting only necessary fields can reduce the consumption of including but not limited to network bandwidth and prevent programs from being affected by table structure modification, such as some prepare queries.
• For report-based queries or basic data queries, materialized views can be used to periodically take data snapshots, so that multiple tables are not performed on the same query repeatedly, especially for tables with frequent write operations.
• Window functions can be used for complex statistics queries.
• Make sure that the data type of the associated fields are consistent. It is prohibited to use implicit type conversion.
• The or statements of different fields can be replaced with union.

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