Query Operations

DDL Operation

• Any DDL operations on existing tables are prohibited during peak business periods

• All production DDL operations must be verified by the development test environment

• Concurrently should be used when maintaining indexes

• pg_repack should be used instead of vacuum full to rebuild the table

DML Operation

• The SQL statement for updating data is prohibited to appear where 1=1

• The amount of data operated by a single DML statement should not exceed 100,000

• When clearing the data in the table, truncate should be used

• For risky operations, you should open the transaction and confirm it before committing.

• The SQL logic in the transaction should be as simple as possible, and the operation should be submitted in time after execution to avoid idle in transaction status.

• Use copy instead of insert when importing a large amount of data.

• Consider deleting indexes before importing data, and rebuild them after importing.

DQL Operation

• Prohibit the use of select *, apply the specific required field substitution

• Prohibit the use of where 1=1 to avoid full table scan or Cartesian product

• The search condition value should be consistent with the field type to prevent not going to the index

• Fields to the left of the equal sign should be consistent with the index, especially conditional or functional indexes

• Pay attention to the execution plan of slow SQL, if it is not consistent with the expectation, change it as soon as possible

• Use count(*) or count(1) to count rows, count(column) will not count null rows

• Limit the number of join, no more than 3 are recommended

• Recursive queries need to be limited to prevent infinite loops

• For or operations, you should use union all or union instead

Data Import

• When a large amount of data needs to be stored in a table, it is recommended that COPY is used rather than INSERT. This can improve the data write speed.
• Before data is imported, delete related indexes. After the import is complete, recreate indexes. This can improve the data import speed.

Transaction Operation

• Make sure that the SQL logic in a transaction is simple, the granularity of each transaction is small, less resources are locked, lock and deadlock can be avoided, and transaction can be committed in a timely manner after being executed.
• For DDL operations, especially multiple DDL operations, including CRAETE, DROP, and ALTER, do not explicitly start a transaction because the lock mode value is very high and deadlock easily occurs.
• If the state of the master node is idle in transaction, related resources will be locked, thereby leading to lock, even deadlock. If the state of the slave node is idle in transaction, synchronization between the master and slave nodes will be suspended.

Others

• For instances running in SSDs, it is recommended that the value of random_page_cost (default value: 4) is set to a value ranging from 1.0 to 2.0. This can make the query planner preferably use the index to perform scanning.
• In the scenario where EXPLAIN ANALYZE needs to be used to view the actual execution plan and time, if a write query is to be performed, it is strongly recommended that a transaction is started first and then rollback is performed.
• For tables frequently updated and with the data size largely increased, table reorganization should be performed in appropriate time to lower the high water mark.