Naming Rules
• The total length of an object name (e.g. library, table, index, etc.) must be less than or equal to 63 characters.
• Do not use the database reserved word as the object names (such as table names, column names, function names, view names, and sequence names). Do not start with pg, numbers, or underlines. For details, see the relevant content of database reserved words on the PostgreSQL official website.
• Do not use characters other than lowercase letters, underlines, or numbers (such as Chinese) for alias in query statements.
• It is recommended that primary key indexes start with pk_. The unique indexes must start with uk_ and common indexes must start with idx_.
• It is recommended that temporary tables start with tmp_ and sub-forms end with rules. For example, if a primary table by year is tbl, the child tables are tbl_2016 and tbl_2017.
Design Rules
• The same columns in multiple tables and columns with Join requirements must have the same column names and data types.
• The btree index fields should not exceed 2000 bytes. To create an index for fields exceeding 2000 bytes, it is recommended to use functional indexes (such as hash indexes) or word segmentation indexes.
• When using foreign keys, you must manually index the foreign key if the RDS-PostgreSQL version in use does not automatically do so. Failing to index these columns may affect the update or deletion performance of the references column.
• For globalization purposes, it is recommended to store and represent all characters using UTF-8 encoding.
• The table structure design should be planned to avoid frequent modification. It is recommended to limit the number of fields in a single table to no more than 64.
• Partition is recommended for business tables with periodic deletion requirements for historical data, such as by time. When cleaning data, it is recommended to operate the corresponding tables using DROP or TRUNCATE instead of DELETE.
SQL Rules
• During query, make sure that only required fields are returned.
• In the query condition, it is recommended to use NOT EXISTS instead of NOT IN.
• Only use IS NULL or IS NOT NULL conditions when querying or comparing whether a field is NULL.
Stability and Performance Rules
• When coding on a query with paging logic, it should return immediately once the count is 0, to avoid executing the paging query statement followed.
• Transactions committed in two stages must be committed or rolled back promptly to avoid database bloating.
• It is recommended to use truncate instead of delete to operate the full table.
• It is recommended that you enable autocommit in applications while avoiding automatic beginning of transactions by applications while making no operations. These issues may occur in some frameworks.
• In high-concurrency business scenarios, make sure that you use bound variables (prepared statements) to prevent high CPU usage from database hard parsing.
Management Rules
• When executing DDL operation data, especially deleting and modifying data records, make sure to select first to avoid accidental deletion. Do not submit for execution before confirmation.
• You can use Explain Analyze to view the actual execution plan. To view the change in the execution plan design data, make sure to execute Explain Analyze in the transaction before rollback.
• You are advised to set complex passwords for database access accounts.
• It is recommended that you control the account permissions to avoid a large number of high-permission user operations.