Updated on 2025-09-17 GMT+08:00

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 (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 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.
  • Use the same data types for fields in the table structure as those defined in the application, and ensure that the field validation rules are consistent across tables to prevent errors and maintain index availability.
  • If the service logic is complex, use stored procedures (such as PL/pgSQL) or built-in functions to reduce the number of interactions between your database and applications. The PL/pgSQL function built in PostgreSQL is capable of processing complex service logic.

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.
  • For data stored in linear order (such as streaming data, time fields, or auto-increment fields), use BRIN indexes to reduce the index size and speed up data insertion.
  • A B-Tree index field can contain a maximum of 2,000 bytes. If a table contains any field with more than 2,000 bytes, use a function index (such as a hash index) or a word segmentation 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

  • Use a specific field list instead of * in a query to avoid returning unnecessary fields.
  • 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 use COUNT(column_name) to replace COUNT(*). According to the SQL-92 standard, COUNT(*) is the correct syntax for counting rows, even those with NULL values. In contrast, COUNT(column_name) does not count NULL values.
  • Avoid returning a large amount of data to a client. If the data volume is too large, the application performance may deteriorate.
  • For range queries, use range types and GiST indexes to enhance the performance of range-based data retrieval.
  • If your application frequently accesses data in large result sets, consider aggregating the data into a single record. For example, if you often access data by ID, periodically aggregate the data by ID. Reducing the number of records returned during a query can significantly improve the response speed.
  • C functions are not allowed 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 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.
  • If permissions are assigned by schema or role, follow the principle of least privilege when creating read/write or read-only accounts.