Database and Schema Design Specifications
- [Description] When you create a database, exercise caution when you set ENCODING and DBCOMPATIBILITY configuration items. GaussDB supports the TD-, ORA-, MYSQL-, PG-compatible, and M-compatible modes which are compatible with the Teradata syntax, Oracle syntax, MySQL syntax, PG syntax, and M-compatible syntax, respectively. The syntax behavior varies according to the compatibility mode. By default, the MySQL-compatible 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, the 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 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 UTF8 character set is equivalent to the UTF8MB4 character set in MySQL 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 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 the CREATE DATABASE.CREATE DATABASE tester WITH ENCODING = 'UTF8';
The character set cannot be changed once the database is created.
- Set client connection parameters. Take JDBC as an example. Add characterEncoding and allowEncodingChanges to the URL.
- [Recommendation] In a cluster, the recommended number of user-defined databases is 3. It is recommended that the number of user-defined databases be less than or equal to 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 little 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 using 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';
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot