Help Center/ Relational Database Service/ User Guide/ Working with RDS for PostgreSQL/ Suggestions on Using RDS for PostgreSQL/ Database Usage Suggestions
Updated on 2024-07-18 GMT+08:00
View PDF

Database Usage Suggestions

Naming

  • The names of objects (such as databases, tables, and indexes) must be no more than 63 bytes. Note that some characters may occupy multiple bytes.
  • Do not use reserved database keywords in object names or start an object name with pg, a digit, or an underscore (_).

Table Design

  • The table structure must be designed in advance to avoid frequent structure changes, such as adding fields or changing data types.
  • There cannot be more than 64 fields in a single table.
  • Create partitioned tables for the tables whose data needs to be deleted periodically. For example, you can create partitions by time and delete data from the partitions using DROP or TRUNCATE.
  • Use appropriate data types for table fields. For example, do not use the character type for numeric or date data.
  • When using the numeric data type, ensure that the values are within allowed ranges and meet precision requirements.

Index Design

  • Design primary keys or unique keys for tables that require logical replication.
  • When creating a foreign key, specify the action for deleting or updating the foreign key, for example, ON DELETE CASCADE.
  • Create indexes for fields that are frequently used (such as fields for data query and sorting).
  • Create partial indexes for queries using fixed conditions.
  • Create expression indexes for queries using conditional expressions.
  • A single table cannot contain too many indexes because indexes also occupy storage. For example, there should be fewer than 5 single-column indexes and fewer than 3 composite indexes.

SQL Design

  • Specify the required fields to be returned in a query.
  • Only use IS NULL or IS NOT NULL to determine whether a field is NULL.
  • Use NOT EXISTS instead of NOT IN in a query.
  • Use UNION ALL instead of UNION to concatenate result sets.
  • Use TRUNCATE instead of DELETE to delete an entire table.
  • Submit data changes in large transactions in batches to prevent high pressure during transaction commit or rollback.

Security

  • Do not assign the public role to the owner of an application database object. Assign a specific role to the owner.
  • A database password must meet complexity requirements.
  • Allocate a unique database account for each service.
  • When accessing an object, explicitly specify the schema of the object to avoid accessing objects with the same name in other schemas.