Dont Do This in PostgreSQL

2020/02/01

Tags: pgsql

前几天看见个这个,翻译一下。

不要使用 SQL_ASCII

可以看德哥这篇文章,使用这个编码后就是给啥存啥,如果 db 用户瞎用,里面 gbk utf8 都往里放,那将来用的时候会比较麻烦,而且这样舍弃了数据库提供的数据检查功能,不是浪费么。

不要使用 psql -W or –password

主要就是 psql 实际会自动处理这个事情的,如果需要密码会找你要的。不用强制让他提示你输入密码。

不要使用 rules

可以使用 trigger 代替。

depesz 讲的 To rule or not to rule that is the question

depesz 的意思是不要使用 rule。即使是多年的 dba 也可能会用错。

rule 主要是通过修改 sql 来实现逻辑,而不是通过一定规则来执行另一个 sql。

PG 文档里面讲的 rules-triggers

用 rule 可以做很多 trigger 可以做的事情。一些约束,特别是外键不能使用 rule 完成。当一列里面的值在其他表里面不存在的时候可以使用 rule 重写命令为什么都不做,但是这样的话数据就丢掉了,这并不是个好主意。如果需要检查数据是否有效,在无效的时候还需要报个错什么的,那只能使用 trigger 完成。

这一章会讨论如何使用 rule 来更新 view。本例里面所有的 rule 都可以使用 view 的 trigger 代替。使用 trigger 会比 rule 简单一点,尤其那些在更新的时候逻辑比较复杂的。

当两个方法都可以的时候,哪种好取决于数据库的使用。trigger 会针对影响到的行每行触发一次。rule 通过修改 query 或者添加一些查询来完成。所以如果一条语句里面多条数据被影响,rule 会触发一条额外的命令,比 trigger 每行都执行一次快,并且这里可能还有很多重复性的计算好决定需要做什么。然而,trigger 概念上比 rule 简单,新手也不容易犯错。

举个例子:

1
2
3
4
5
6
7
8
9
CREATE TABLE computer (
    hostname        text,    -- indexed
    manufacturer    text     -- indexed
);

CREATE TABLE software (
    software        text,    -- indexed
    hostname        text     -- indexed
);

每个表都有几千数据,hostname 是唯一键。rule 或者 trigger 需要实现一个约束,从 computer 表删除数据的时候,同时从 software 表删除。trigger 会使用下面的命令

1
DELETE FROM software WHERE hostname = $1;

因为 trigger 在每行删除的时候都会执行,所以数据库可以保存执行计划传递一个 hostname 作为参数就行。rule 需要使用下面的命令

1
2
CREATE RULE computer_del AS ON DELETE TO computer
    DO DELETE FROM software WHERE hostname = OLD.hostname;

看看不同的删除情况。

1
DELETE FROM computer WHERE hostname = 'mypc.local.net';

数据库会使用索引查询,trigger 也会使用索引,所以都挺快。rule 增加额外的命令之后如下

1
2
DELETE FROM software WHERE computer.hostname = 'mypc.local.net'
                       AND software.hostname = computer.hostname;

根据前面的表定义,执行计划如下

1
2
3
Nestloop
  ->  Index Scan using comp_hostidx on computer
  ->  Index Scan using soft_hostidx on software

所以实际执行的时候 trigger 和 rule 实现并没有啥区别。

下面的删除命令会匹配 2000 条 computer 表里面 hostname 开头是 'old' 的数据,一种删除的方法如下:

1
2
DELETE FROM computer WHERE hostname >= 'old'
                       AND hostname <  'ole'

rule 修改增加之后的命令如下:

1
2
DELETE FROM software WHERE computer.hostname >= 'old' AND computer.hostname < 'ole'
                       AND software.hostname = computer.hostname;

执行计划

1
2
3
4
Hash Join
  ->  Seq Scan on software
  ->  Hash
    ->  Index Scan using comp_hostidx on computer

另外一种删除的方法:

1
DELETE FROM computer WHERE hostname ~ '^old';

rule 修改之后的执行计划

1
2
3
Nestloop
  ->  Index Scan using comp_hostidx on computer
  ->  Index Scan using soft_hostidx on software

查询规划器并没有意识到使用 AND 组合多个条件组合的时候也可以使用索引,而做类似正则表达式那条一样的处理。trigger 每 200 个匹配要删除的 computer 会执行一次,会对 software 表执行一次索引查询。使用 rule 的第二个实现会执行使用索引的命令。rule 使用全表扫描的方法是否会比较快取决于 software 表的大小。trigger 通过 SPI 管理器执行的 2000 次命令总归还是需要一些时间的,即使索引都会在内存里面缓存。

最后看一条命令

1
DELETE FROM computer WHERE manufacturer = 'bim';

这会删除 computer 里面很多行。所以 trigger 还是会执行很多次。rule 执行的命令如下

1
2
DELETE FROM software WHERE computer.manufacturer = 'bim'
                       AND software.hostname = computer.hostname;

执行计划是个 nestloop 两个索引查询

1
2
3
Nestloop
  ->  Index Scan using comp_manufidx on computer
  ->  Index Scan using soft_hostidx on software

在上面这些情况里面,rule 额外增加的命令不太依赖于影响的数据量。

总结下就是,rule 在数据量比较大并且是比较糟糕的 join 导致了查询规划器失败的情况下会比 trigger 明显的慢。

不要使用表继承

使用外键来代替表继承。

现在表分区已经是 native 支持了,不用使用表继承来实现了。在维护某些历史表的情况下使用表继承可以避免使用 union all 获得全部数据,但是也有一些坑,比如查询的时候可能会需要特别指定是不是需要子表的数据,要不即使只想查父表里面的数据的时候也可能会触发子表的扫描,浪费时间。

不要使用 Not In

  1. not in 语句里面如果有 null 那会导致一行数据都不能返回。

  2. 会导致查询规划器无法很好的执行 sql。一般会有两种情况,一种是哈希子查询(hashed subplan),一种是一般子查询(plain subplan)。哈希子查询比较快,但是限于数据了不能太大。一般子查询就很糟糕了,是个 O(n^2) 复杂度的操作。

不要用驼峰方式给表取名,使用下划线方式

就是不要用 NamesLikeThis 使用 names_like_this。

pg 里面会使用小写字母做表名,除非使用了 "" ,例如 Foo 会创建 foo,"Bar" 会创建 Bar 。这样查的时候对于 "Foo", Bar, bar 这几个名字都会报找不到表(那个 Bar 找不到是因为每家引号)。

有时候在报表里面可能想要名字显示的好看一点,那可以使用 as 来起个别名。

不要使用 between,特别是针对时间戳类型

例如下面这个

1
SELECT * FROM blah WHERE timestampcol BETWEEN '2018-06-01' AND '2018-06-08'

对于 2018-06-08 00:00:00.000000 也是符合的。使用下面的查询会好一点

1
SELECT * FROM blah WHERE timestampcol >= '2018-06-01' AND timestampcol < '2018-06-08'

对于 int 和 date 类型可能问题不大。

不要使用 timestamp 类型(就是不加时区的)

使用 timestamptz 来存时间。不带时区的一旦涉及到时区相关的运算就会比较麻烦了,你总是需要指定你时间的时区。如果你不打算进行什么运算,只是存一下,也可以使用 timestamp.

不要使用 tempstamp 类型存放 UTC 时间

和上面那个类似吧。

不要使用 timetz

使用 timestamptz 代替。

不要使用 CURRENT_TIME

使用下面的代替:

  1. 带时区的 timestamp:CURRENT_TIMESTAMP 或者 now()

  2. 不带时区的:LOCALTIMESTAMP

  3. 只需要日期:CURRENT_DATE

  4. 只需要时间:LOCALTIME

因为 CURRENT_TIME 会返回一个 timetz 类型的数据。

不要使用 timestamp(0) 或者 timestamptz(0)

不要指定精度,尤其是 0。使用 date_trunc('second', xxx) 来代替。

因为指定精度会使用 round 方式而不是通常认为的 truncate 方式来处理,例如你使用 now() 的时候可能会产生一个未来半秒之后的时间。

不要使用 char(n)

使用 text 代替。

char(n) 对于不足的部分会使用空数据不足,这会浪费空间,并且也并不会让查询变快。并且 char(n) 还不是个固定长度的类型,不同的字符的 byte 长度不同。

对于固定长度的标识符也不用使用 char(n)

即使是定长的字符串也不要用 char(n),例如国家代码,hash 值,标识符啥的。使用 text 或者 domain over text,加约束 CHECK(length(VALUE)=3) 或者 CHECK(VALUE ~ '^[[[[:alpha:]]]]{3}$') 或者其他类似的。

因为 char(n) 并不检查长度,只是会补齐。使用 char(n) 并不会比 varchar(n) 有什么性能上面的提升。反过来反而是会提升。另外,如果查询的时候传入的数据类型是个 text 或者 varchar 的话就用不上索引了。

不要使用 varchar(n)

使用不指定长度的 varchar() 或者 text 代替。

varchar(n) 在插入长度超过 n 的字符的时候会报错。varchar() 或者 text 没有长度限制。存相同长度的字符的时候,他们三个占用的空间是一样的,性能也没区别。

如果你确实需要长度限制,那么 varchar(n) 也挺好的,但是比如你用 varchar(20) 来存名字,某天可能一个名字巨长的人来了,他就不能注册了。

如果你需要限制长度,那通常不止需要最大长度,也会需要限制最小长度,那使用 check 约束比较好。

不要使用 money 类型

只处理一种货币类型,也不要处理分级别的 cents,并且只加减操作的话,可以试试看。

这个我补充下:一般有建议是把金额放大成整数,只操作整数。这样可以避免无意义的小数,比如 0.011 元这个钱在现实世界里面是不存在的,只有 0.01 是有意义的。另外计算机世界里面浮点数的乘法和除法可能会出来 0.010000008 这样的金额的,也是无意义的。所以有一个建议就是把金额映射成 100 倍,存成整数,比如 0.01 元,存成 1。按说未来只有通货膨胀了,紧缩出现更小面额的可能性也不大,或者也可以考虑放大 1000 倍什么的。。。

不要使用 serial

10 增加了 identity 类型,

1
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]

类似原来的 serial, 会创建一个 sequence 关联到这个字段,新插入的行会自动产生值。

ALWAYS 和 BY DEFAULT 用来指定用户如果指定了一个值的时候怎么处理。设置成 ALWAYS 的话只有在 INSERT 的时候指定 overriding system value 才能。对于 BY DEFAULT,用户指定的优先。对于 COPY 命令总是会使用用户指定的。

新的 identity 符合 sql 标准。老的 serial 类型有一些小问题,导致管理稍微有些麻烦。比如你需要单独给对应的 sequance 权限之后,用户才能真的对这个表插入。具体可以看这个

不要使用 trust 允许 tcp/ip 方式连接

特别是这样,会允许网络上的其他人连接你的数据库,还可以使用超级用户。

1
host all all 0.0.0.0/0 trust

Comments