Help Center/ GaussDB/ Centralized_8.x/ Development and Design Proposal/ Database Object Design/ Database and Schema Design
Updated on 2024-06-03 GMT+08:00
View PDF

Database and Schema Design

In GaussDB, services can be isolated by databases and schemas. Databases share little resources and cannot directly access each other. Connections to and permissions on them are also isolated. Schemas share more resources than databases do. User permissions on schemas and subordinate objects can be controlled using the GRANT and REVOKE syntax.

  • You are advised to use schemas to isolate services for convenience and resource sharing.
  • It is recommended that system administrators create schemas and databases and then assign required permissions to users.

Database Design

  • Create databases as required by your service. Do not use the default postgres database of a database instance.
  • 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.
  • To make your database compatible with most characters, you are advised to use the UTF-8 encoding when creating a database.
  • When you create a database, exercise caution when you set ENCODING and DBCOMPATIBILITY configuration items. GaussDB supports the A-, B-, C-, PG-, and M-compatible modes, which are compatible with the Oracle syntax, MySQL syntax, Teradata syntax, Postgres syntax, and M-compatible syntax, respectively. The syntax behavior varies according to the compatibility mode. By default, the A-compatible mode is used.
  • By default, a database owner has all permissions for all objects in the database, including the deletion permission. Exercise caution when deleting a permission.

Schema Design

  • 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.
  • 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.
  • To let a user create an object in the schema, grant the CREATE permission for the schema to the user.
  • By default, a schema owner has all permissions for all objects in the schema, including the deletion permission. Exercise caution when deleting a permission.
Parent topic: Database Object Design