Updated on 2026-10-16 GMT+08:00

Database Guidelines

Naming

  • The names of objects (such as databases, tables, and indexes) must be no more than 63 bytes. 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 RDS for PostgreSQL system database names. RDS for PostgreSQL system databases include postgres, template0, and template1. The system databases cannot be modified.

Table Design

  • The table structure must be designed in advance to avoid frequent structure changes, such as adding columns or changing data types.
  • A single table should not contain too many columns. It is recommended that a table contain no more than 64 columns.
  • For tables whose data must be cleaned up periodically, create partitioned tables (such as by time) and delete data by using DROP or TRUNCATE on the child partitions.
  • Use appropriate data types for table columns. 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.
  • Ensure that data types defined for columns in the table structure match those in the application, and that collation rules are consistent across tables to avoid errors and ensure that indexes can be used.
  • If the workload logic is lengthy, reduce the interaction frequency between the database and application by using stored procedures (such as PL/pgSQL) or built-in functions. The PL/pgSQL functional language built into PostgreSQL can handle complex workload logic.

Index Design

  • Design primary keys or unique keys for tables that require logical replication.
  • When using foreign keys, ensure that actions for deleting or updating foreign keys are set, such as ON DELETE CASCADE.
  • Create indexes for columns that are frequently used (such as columns for data queries and sorting).
  • Create partial indexes for queries using fixed conditions.
  • Create expression indexes for queries often using conditional expressions.
  • Indexes also occupy storage. The number of indexes on a single table should not be excessive. For example, keep the number of single-column indexes below 5 and composite indexes below 3.
  • For data stored in linear order (such as streaming data, time columns, or auto-increment columns), use BRIN indexes to reduce index sizes and speed up data insertion.
  • A B-Tree index column can be up to 2,000 bytes. If a column in a table exceeds 2,000 bytes and requires an index, create a function index (such as a hash index) or a full-text search index.
  • Avoid full table scans (except for analysis of large data volumes). PostgreSQL supports indexes of almost all data types (such as B-Tree, Hash, GIN, GiST, BRIN, and RUM).

SQL Design

  • Specify column lists instead of using asterisks (*) in queries to avoid retrieving unnecessary columns.
  • Only use the IS NULL or IS NOT NULL condition when querying or checking whether a column is NULL.
  • In query conditions, use NOT EXISTS instead of NOT IN.
  • When aggregating data, use UNION ALL instead of UNION.
  • When deleting data, use TRUNCATE instead of full-table DELETE.
  • Commit data modifications in large transactions in batches to prevent high pressure during transaction commit or rollback.
  • When creating functions, define the function volatility category as the strictly allowed category for them instead of selecting the default VOLATILE. Excessive concurrent calls to VOLATILE functions can prevent new connections from being established.
  • Do not use COUNT(column_name) as a substitute for COUNT(*). COUNT(*) is the standard SQL92 syntax for counting rows and it counts NULL values (representing actual row count), whereas COUNT(column_name) does not count NULL values.
  • Avoid returning large amounts of data to a client. Excessively large data volumes can cause application performance degradation.
  • For scenarios requiring range queries, use range types and GiST indexes to improve range search performance.
  • If your application frequently accesses data in large result sets, consider aggregating the data into a single record. For example, if data is frequently accessed by ID, periodically aggregate the data by ID. Returning fewer records during queries ensures faster responses.
  • C-language functions cannot be created in RDS for PostgreSQL 16.6, 15.10, 14.15, 13.18, 12.22, and later.

Security

  • Do not assign the public role to the owner of application database objects. Assign a specific role to the owner.
  • Database passwords must meet complexity requirements. Do not use simple passwords.
  • Assign different database accounts for different workloads. Do not share a single database account across multiple workloads.
  • When accessing an object, explicitly specify the schema of the object to avoid mistakenly accessing objects with the same name in other schemas.
  • If privileges are assigned by schema or role in a workload, grant minimum necessary privileges when creating read/write or read-only accounts in accordance with the principle of least privilege.