Database and Schema Design Specifications
- [Description] When you create a database, exercise caution when setting the ENCODING and DBCOMPATIBILITY configuration items. GaussDB supports the five compatibility modes of A, B, C, PG, and M, corresponding to compatibility with the Oracle syntax, MySQL syntax, Teradata syntax, Postgres syntax, and M-compatible syntax, respectively. The syntax behavior varies depending on the compatibility mode. By default, the A 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, grant the usage permission and the permissions on 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 on the schema to the user.
- [Description] By default, a schema owner has all permissions on 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 be able to 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.
Inconsistent encoding modes between the client and the database necessitate transcoding, which affects performance. In addition, kernel optimization for the same encoding cannot be triggered, reducing 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 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 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 . The difference lies in the following:
- 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';
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