以下内容为开发设计规范，包括命名规范、COLUMN设计、Constraints设计、Index设计、关于NULL和开发相关规范。
- DB object：database， schema， table， column， view， index， sequence， function， trigger 等名称。
-
建议使用小写字母、数字、下划线的组合。
-
建议不使用双引号即"包围，除非必须包含大写字母或空格等特殊字符。
-
长度不能超过63个字符。
-
不建议以 pg_ 开头（避免与系统 DB object 混淆），不建议以数字开头。
-
禁止使用 SQL 关键字，如 type， order 等。
-
table 能包含的 column 数目，根据字段类型的不同，数目在250到1600之间。
-
临时或备份的DB object：table、view 等，建议加上日期， 如 dba_ops.b2c_product_summay_2014_07_12 (其中 dba_ops 为 DBA 专用 schema)。
-
index命名规则为：普通索引为 表名_列名_idx，唯一索引为 表名_列名_uidx，如 student_name_idx，student_id_uidx。
-
建议能用数值类型的，就不用字符类型。
-
建议能用varchar(N)就不用char(N)，以利于节省存储空间。
-
建议能用varchar(N)就不用text，varchar。
-
建议使用default NULL，而不用 default ''，以节省存储空间。
-
建议如有国际货业务的话，使用timestamp with time zone(timestamptz)，而不用 timestamp without time zone，避免时间函数在对于不同时区的时间点返回值不同，也为业务国际化扫清障碍。
-
建议使用NUMERIC(precision，scale)来存储货币金额和其它要求精确计算的数值， 而不建议使用real，double precision。
- 建议建表时一步到位把主键或者唯一索引也一起建立。
-
OpenTeleDB提供的index类型：B-tree，Hash，GiST (Generalized Search Tree)，SP-GiST (space-partitioned GiST)，GIN (Generalized Inverted Index)，BRIN (Block Range Index)，目前不建议使用Hash，通常情况下使用B-tree，Xstore使用xbtree。
-
建议用unique index代替unique constraints，便于后续维护。
-
建议对where中带多个字段and条件的高频query，参考数据分布情况，建多个字段的联合index。
-
建议对固定条件的（一般有特定业务含义）且选择时数据占比低的query，建议带 where的Partial Indexes。
select * from test where status=1 and col=?; -- 其中status=1为固定的条件 create index on test (col) where status=1; -
建议对经常使用表达式作为查询条件的query，可以使用表达式或函数索引加速 query。
select * from test where exp(xxx); create index on test ( exp(xxx) ); -
建议不要建过多index，一般不要超过6个，核心table（产品，订单）可适当增加 index个数。
-
NULL的判断：IS NULL，IS NOT NULL。
注意 boolean 类型取值 true，false，NULL。 NOT IN 集合中带有 NULL 元素。
teledb=# select * from t_oids; id | name | birth | city ----+------+---------------------+------ 1 | 张三 | 2000-12-01 00:00:00 | 北京 2 | 李四 | 1997-03-24 00:00:00 | 上海 3 | 王五 | 2004-09-01 00:00:00 | 广州 (3 rows) teledb=# select * from t_oids where id not in (null); id | name | birth | city ----+------+-------+------ (0 rows) -
建议对字符串型 NULL 值处理后，再进行 || 操作。
teledb=# select id,name from t_oids limit 1; id | name ----+------ 1 | 张三 (1 row) teledb=# select id,name||null from t_oids limit 1; id | ?column? ----+---------- 1 | (1 row) teledb=# select id,name|| coalesce(null,'') from t_oids limit 1; id | ?column? ----+---------- 1 | 张三 (1 row) -
建议使用 count(1) 或 count(*) 来统计行数，而不建议使用 count(col) 来统计行数，因为 NULL 值不会计入。
说明
count(多列列名)时，多列列名必须使用括号，例如count( (col1，col2，col3) )，注意多列的count，即使所有列都为NULL，该行也被计数，所以效果与count(*)一致。
teledb=# select * from t_oids ; id | name | birth | city ----+------+---------------------+------ 1 | 张三 | 2000-12-01 00:00:00 | 北京 2 | 李四 | 1997-03-24 00:00:00 | 上海 3 | 王五 | 2004-09-01 00:00:00 | 广州 4 | 陈六 | 2022-01-01 00:00:00 | (4 rows) teledb=# select count(city) from t_oids; count \------- 3 (1 row) teledb=# select count(1) from t_oids; count \------- 4 (1 row) teledb=# select count(*) from t_oids; count \------- 4 (1 row) teledb=# select count(id) from t_oids; count \------- 4 (1 row) teledb=# select count((id,city)) from t_oids; count \------- 4 (1 row) -
count(distinct col) 计算某列的非 NULL 不重复数量，NULL 不被计数。
count(distinct (col1，col2，...) ) 计算多列的唯一值时，NULL 会被计数，同时 NULL 与 NULL 会被认为是相同的。
teledb=# select count(distinct city) from t_oids; count \------- 3 (1 row) teledb=# select count(distinct (id, city)) from t_oids; count \------- 4 (1 row) -
两个 NULL 的对比方法。
teledb=# select null is not distinct from null; ?column? \---------- t (1 row)
-
建议对DB object尤其是COLUMN加COMMENT，便于后续了解业务及维护
注释前后的数据表可读性对比，有注释的一看就明白。
teledb=# \d+ t_oids; Table "public.t_oids" Column | Type | Collation | Nullable | Default | Storage | Stats target | Descripti on --------+--------------------------------+-----------+----------+---------+----------+--------------+---------- \--- id | integer | | not null | | plain | | name | character varying | | | | extended | | birth | timestamp(0) without time zone | | | | plain | | city | character varying | | | | extended | | Indexes: "t_oids_pkey" PRIMARY KEY, btree (id) Has OIDs: yes Distribute By: SHARD(id) Location Nodes: ALL DATANODES ^ teledb=# comment on column t_oids.name is '姓名'; COMMENT teledb=# comment on column t_oids.city is '居住城市'; COMMENT teledb=# \d+ t_oids; Table "public.t_oids" Column | Type | Collation | Nullable | Default | Storage | Stats target | Descripti on --------+--------------------------------+-----------+----------+---------+----------+--------------+---------- \--- id | integer | | not null | | plain | | name | character varying | | | | extended | | 姓名 birth | timestamp(0) without time zone | | | | plain | | city | character varying | | | | extended | | 居住城市 Indexes: "t_oids_pkey" PRIMARY KEY, btree (id) Has OIDs: yes Distribute By: SHARD(id) Location Nodes: ALL DATANODES -
建议非必须时避免select *，只取所需字段，以减少包括不限于网络带宽消耗。
teledb=# explain select * from t_oids; QUERY PLAN \---------------------------------------------------------------- Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0) Node/s: dn01, dn02 -> Seq Scan on t_oids (cost=0.00..16.30 rows=630 width=76) (3 rows) teledb=# explain select id from t_oids; QUERY PLAN \--------------------------------------------------------------- Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0) Node/s: dn01, dn02 -> Seq Scan on t_oids (cost=0.00..16.30 rows=630 width=4) (3 rows) -
建议update时尽量做<>判断，如update table_a set column_b = c where column_b <> c；
teledb=# update t_oids set city = '测试'; UPDATE 4 teledb=# select xmin,* from t_oids; xmin | id | name | birth | city ------+----+------+---------------------+------ 1181 | 1 | 张三 | 2000-12-01 00:00:00 | 测试 1147 | 3 | 王五 | 2004-09-01 00:00:00 | 测试 1147 | 4 | 陈六 | 2022-01-01 00:00:00 | 测试 1181 | 2 | 李四 | 1997-03-24 00:00:00 | 测试 (4 rows) teledb=# update t_oids set city = '测试'; UPDATE 4 teledb=# select xmin,* from t_oids; xmin | id | name | birth | city ------+----+------+---------------------+------ 1182 | 1 | 张三 | 2000-12-01 00:00:00 | 测试 1182 | 2 | 李四 | 1997-03-24 00:00:00 | 测试 1148 | 3 | 王五 | 2004-09-01 00:00:00 | 测试 1148 | 4 | 陈六 | 2022-01-01 00:00:00 | 测试 (4 rows) teledb=# update t_oids set city = '测试' where city != '测试'; UPDATE 0 teledb=# select xmin,* from t_oids; xmin | id | name | birth | city ------+----+------+---------------------+------ 1182 | 1 | 张三 | 2000-12-01 00:00:00 | 测试 1182 | 2 | 李四 | 1997-03-24 00:00:00 | 测试 1148 | 3 | 王五 | 2004-09-01 00:00:00 | 测试 1148 | 4 | 陈六 | 2022-01-01 00:00:00 | 测试上面的效果是一样的，但带条件的更新不会产生一个新的版本记录，不需要系统执行 vacuum 回收垃圾数据。
-
建议将单个事务的多条SQL操作，分解、拆分，或者不放在一个事务里，让每个事务的粒度尽可能小，尽量lock少的资源，避免lock 、dead lock的产生。
会话1 把所有数据都更新但不提交，锁住了所有数据 teledb=# begin; BEGIN teledb=# update t_oids set city = 'city'; UPDATE 4 会话2 等待 teledb=# update t_oids set city = 'session2'; 会话3 等待 teledb=# update t_oids set city = 'session3';如果会话1分批更新的话，则会话2和会话3中就能部分提前完成，这样可以避免大量的锁等待和出现大量的session占用系统资源，在做全表更新时请使用这种方法来执行。
-
建议大批量的数据入库时，使用copy，不建议使用insert，以提高写入速度。
-
建议复杂的统计查询可以尝试窗口函数。
-
对于频繁更新的表，建议建表时指定表的fillfactor=85，每页预留15%的空间给HOT更新使用。
create table test123(id int, info text) with(fillfactor=85); -
使用外键时，一定要设置fk的action，例如cascade，set null，set default。
create table tbl2(id int references tbl(id) on delete cascade on update cascade, info text); -
建议有定期历史数据删除需求的业务，表按时间分区，删除时不要使用DELETE操作，而是DROP或者TRUNCATE对应的表。
-
设计时应尽可能选择合适的数据类型，能用数字的坚决不用字符串，能用树类型的，坚决不用字符串。 使用好的数据类型，可以使用数据库的索引，操作符，函数，提高数据的查询效率。
-
对于固定条件的查询，可以使用部分索引，减少索引的大小，同时提升查询效率。
select * from test where id=1 and col=?; -- 其中id=1为固定的条件 create index idx on tbl (col) where id=1; -
对于经常使用表达式作为查询条件的语句，可以使用表达式或函数索引加速查询。
select * from test where exp(xxx); create index idx on tbl ( exp ); -
当用户有规则表达式查询，或者文本近似度查询的需求时，建议对字段使用trgm的gin索引，提升近似度匹配或规则表达式匹配的查询效率，同时覆盖了前后模糊的查询需求。如果没有创建trgm gin索引，则不推荐使用前后模糊查询例如like %xxxx%。