Query操作

DDL操作

• 业务高峰期禁止对已存在的表执行任何DDL操作

• 所有生产DDL操作必须经过开发测试环境验证

• 维护索引时应采用concurrently的方式

• 应该使用pg_repack替换vacuum full来重建表

• 大表添加带默认值的字段时，应拆分为：添加字段、填补默认值及添加非空约束三部分，如将alter table t add column col datatype not null default xxx;分解为如下，避免填充default值导致的过长时间锁表

  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；

DML操作

• 更新数据的SQL语句禁止出现where 1=1

• 单条DML语句操作的数据量不超过10万

• 清空表中的数据时，应使用truncate

• 对于风险性较高的操作，应该显示的开启事务，确认无误后在提交

• 事务中SQL逻辑尽量简单，操作执行完后要及时提交，避免idle in transaction状态

• 大批数据入库时应使用copy替换insert

• 数据导入前考虑先删除索引，导入完成后重建

DQL操作

• 禁止使用select *，应用具体所需字段替换

• 禁止使用where 1=1，避免全表扫描或笛卡尔积

• 检索条件值应该与字段类型保持一致，防止不走索引

• 等号左边的字段应该与索引保持一致，尤其是条件索引或函数索引

• 关注慢SQL的执行计划，如与预期不一致，尽快修改

• 使用count(*)或count(1)来统计行数，count(column)不会统计null行

• 限制join的数量，不建议超过3个

• 递归查询需要做好限制，防止无限循环

• 对于or运算，应该使用union all或union替换

数据导入

• 建议大批量的数据入库时，使用copy，不建议使用insert，以提高写入速度

• 导入数据前需要先删除相关索引，导入完成后重建，提高数据导入速度

事务操作

• 事务中的sql逻辑尽可能的简单，让每个事务的粒度尽可能小，尽量lock少的资源，避免lock、deadlock的产生，事务执行完及时提交

• 执行CRAETE、DROP、ALTER等DDL操作，尤其多条，不要显式的开transaction，因为加lock的mode非常高，极易产生deadlock

• state为idle in transaction的连接，如果出现在Master，会无谓的lock住相应的资源，可导致后续产生lock，甚至deadlock；出现在Slave，可导致卡住主从同步

其他

• 建议运行在SSD上的实例，random_page_cost（默认值为4）设置为1.0~2.0之间，使查询规划器更倾向于使用索引扫描

• 建议在需要使用explain analyze查看实际真正执行计划与时间时，如果是写入query，强烈建议先开启事务，然后回滚。

• 对于频繁更新，膨胀率较高的表，应找窗口期执行表重组，降低高水位