Database Usage Suggestions

Updated on 2025-01-26 GMT+08:00

View PDF

The names of objects (such as databases, tables, and indexes) must be no more than 63 bytes. Note that some characters (such as Chinese characters) may occupy multiple bytes.
Do not use reserved database keywords in object names or start an object name with pg or a digit.
A database name can contain 1 to 63 characters. Only letters, digits, and underscores (_) are allowed. It cannot start with pg or a digit and cannot be the same as PostgreSQL template database names. PostgreSQL template databases include postgres, template0, and template1.

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.

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.

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.
When creating a function, define the volatility of the function as the strictest category, instead of the default VOLATILE. Too many concurrent calls of VOLATILE functions may result in failures to establish new connections.

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.

Parent topic: Suggestions on Using FlexusRDS for PostgreSQL