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
When adding fields with default values to a large table, it should be split into three parts: adding fields, filling default values and adding non-null constraints, such as breaking alter table t add column col datatype not null default xxx; into the following, to avoid too long a table lock caused by filling default values
```
alter table t add column col datatype ；
alter table t alter column col set default xxx；
update table t  set column= DEFAULT where id in ( select id from t where column is null limit
1000 ) ; \watch 3
alter table t alter column col set not null；
```

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.

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

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.

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.

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.