Help Center/ GaussDB/ Developer Guide(Centralized_V2.0-2.x)/ Development and Design Proposal/ Database Design Specifications/ Database and Schema Design Specifications
Updated on 2025-02-27 GMT+08:00
View PDF
Share

Database and Schema Design Specifications

  • [Description] When you create a database, exercise caution when you set ENCODING and DBCOMPATIBILITY configuration items. GaussDB supports the A, B, C, and PG compatibility modes, which are compatible with the syntax of Oracle Database, MySQL, Teradata, and Postgres, respectively. The syntax behavior varies according to the compatibility mode. By default, the A compatibility mode is used.
  • [Description] By default, a database owner has all permissions for all objects in the database, including the deletion permission. Exercise caution when deleting a permission.
  • [Description] To let a user access an object in a schema, assign the usage permission and the permissions for the object to the user, unless the user has the SYSADMIN permission or is the schema owner.
  • [Description] To let a user create an object in the schema, grant the CREATE permission for the schema to the user.
  • [Description] By default, a schema owner has all permissions for all objects in the schema, including the deletion permission. Exercise caution when deleting a permission.
  • [Specification] The database name must be specified when the JDBC client is used to connect to the database. The format is as follows: 
    jdbc:postgresql://ip:port/database_name

    Once a JDBC instance is created, the database cannot be switched.

  • [Specification] The database does not support case-insensitive collation.
  • [Rule] Before using services, a system administrator must create databases, schemas, and users for services, and then grant object permissions to corresponding users.
  • [Rule] Create a service database before using a service. Do not use the Postgres database created by default after the database is installed to store service data. You are advised to create your own database based on service requirements.
  • [Rule] When creating a database, you must set the character set to UTF8 and must select the character set that is the same as that of the client.

    To meet globalization requirements, database encoding should store and identify most characters. Therefore, UTF8 is recommended. The UTF-8 character set is equivalent to the utf8mb4 character set in MySQL databases and supports emoji.

    If the encoding mode of the client is different from that of the database, transcoding is required, affecting performance. In addition, kernel optimization for the same encoding cannot be triggered, affecting the query efficiency.

    To change the character set of the client, perform the following steps:

    • Set client connection parameters. Take JDBC as an example. Add the parameters characterEncoding and allowEncodingChanges to the URL. 
      jdbc:postgresql://ip:port/database_name?characterEncoding=utf8&allowEncodingChanges=true
    • Modify the database GUC parameter. 
      SET client_encoding = 'UTF8';
    Set the database encoding when executing CREATE DATABASE. 
    CREATE DATABASE tester WITH ENCODING = 'UTF8';

    The character set cannot be changed once the database is created.

  • [Recommendation] In a database instance, it is recommended that the number of user-defined databases be 3 and no more than 10. If there are too many user-defined databases, O&M operations, such as upgrade and backup, will be inefficient.
  • [Recommendation] It is recommended that the number of schemas in the actual user environment be no more than 100. If there are too many schemas in a database, operations that depend on the number of schemas, such as gs_dump, becomes slow.
  • [Recommendation] You are advised to use schemas to isolate services for convenience and resource sharing.

    Both database and schema can be used to isolate services . Differences are as follows:

    • Databases share few resources. Connections to and permissions on them are also isolated. However, the databases cannot access each other. The database must be specified during JDBC connection establishment. After the connection is established, the database cannot be switched.
    • Schemas share more resources than databases do. User permissions on schemas and subordinate objects can be controlled over the GRANT and REVOKE syntax.
  • [Recommendation] You are advised to specify LC_CTYPE and LC_COLLATE when creating a database. These parameters will affect the data collation sequence.

    Example:

    CREATE DATABASE SAMPLE_DB WITH LC_CTYPE =  'zh_CN.gbk' LC_COLLATE = 'zh_CN.gbk';