数据库三十六计 - PostgreSQL 三十六计(下)
背景
PostgreSQL 三十六计 - 下
25. 数据库端编程，处理复杂业务逻辑。
在传统企业、电商、运营商等涉及用户交互、或者多个系统交互的业务场景中，通常一个事务涉及到很复杂的业务逻辑，需要保证数据的一致性，同时还需要与数据库多次交互。
比如银行开户，涉及的业务系统多，逻辑复杂。在传统企业中，通常也使用商业数据库的过程函数，实现此类复杂的逻辑。
PostgreSQL的数据库过程函数支持的语言非常丰富，比如plpgsql（可与Oracle pl/sql功能比肩)，另外还支持语言的扩展，编程语言可以热插拔，例如支持python,perl,java,c,r 等等作为数据库的过程函数语言。
对于开发人员来说，几乎可以在PostgreSQL数据库中处理任何业务逻辑。使用数据库端编程，可以在保证一致性的前提下，处理复杂的业务逻辑，减少数据库与程序的交互次数，降低整个事务的RT。
https://www.postgresql.org/docs/10/static/plpgsql.html
https://www.postgresql.org/docs/10/static/pltcl.html
https://www.postgresql.org/docs/10/static/plperl.html
https://www.postgresql.org/docs/10/static/plpython.html
26. 善用ECPG，C嵌入式开发
在一些对性能要求非常高的场景，同时考虑开发效率，C嵌入式开发是一个非常好的选择。
比如在很多金融行业的开发商，ECPG和Oracle Pro*C一样具有同等地位。
https://www.postgresql.org/docs/10/static/ecpg.html
27. 数据库水平拆分、跨平台数据融合
PostgreSQL 从 2011年的9.1版本引入FDW开始，发展到现在已经支持几乎所有的外部数据源读写操作，例如mysql,oracle,pgsql,redis,mongo,hive,jdbc,odbc,file,sqlserver,es,S3,……。
https://wiki.postgresql.org/wiki/Fdw
开放的接口，允许用户自己添加外部数据源的支持。
9.6针对postgres_fdw（即PostgreSQL外部数据源）再次增强，开始支持对sort, where, join的下推，支持remote cancel query, 用户使用FDW可以对应用透明的实现数据库的sharding，单元化需求。
内核层支持sharding，这种分片技术相比中间件分片技术的好处：
1. 支持跨库JOIN
2. 支持绑定变量
3. 支持master(coordinator)节点水平扩展
4. 支持segment(datanode)节点水平扩展
5. 支持函数和存储过程
6. 支持sort, where, join的下推，支持remote cancel query，10.x支持聚合算子的下推。
7. 支持单一SQL，在shard节点并行RUN QUERY。
ps: 目前还不支持分布式事务（需要用户干预2PC） ，10.x的版本会增加内核层面的分布式事务控制。
《PostgreSQL 最佳实践 - 水平分库(基于plproxy)》
《PostgreSQL 9.6 sharding based on FDW & pg_pathman》
《PostgreSQL 9.6 sharding + 单元化 (based on postgres_fdw) 最佳实践 - 通用水平分库场景设计与实践》
《阿里云ApsaraDB RDS for PostgreSQL 最佳实践 - 4 水平分库 之 节点扩展》
《阿里云ApsaraDB RDS for PostgreSQL 最佳实践 - 3 水平分库 vs 单机 性能》
《阿里云ApsaraDB RDS for PostgreSQL 最佳实践 - 2 教你RDS PG的水平分库》
28. 开发规约 - 命名
1. 长度不要超过63个字符，如果要超过的话，需要在编译PostgreSQL前修改头文件
src/include/pg_config_manual.h
/*
* Maximum length for identifiers (e.g. table names, column names,
* function names). Names actually are limited to one less byte than this,
* because the length must include a trailing zero byte.
*
* Changing this requires an initdb.
*/
#define NAMEDATALEN 64
2. 不要使用关键字作为对象名。
| [《PostgreSQL Oracle兼容性 之 - parser SQL保留 | 关键字(keywrods)大全》](../201710/20171024_06.md) |
3. 不要使用小写字母、数字和下划线以外的字符作为对象名。
否则需要使用双引号。
create table "表aA-f3" (id int);
4. 不建议使用多字节字符作为注释。
29. 开发规约 - 设计
1. 同类属性，将来可能涉及JOIN，FK，务必对齐数据类型，包括数据长度。
2. BTREE索引字段，长度不建议超过2000字节，否则请使用hash索引或表达式索引。
create index idx on tbl using hash (long_col);
create index idx on tbl (hashtext(long_col));
3. 频繁更新的表、索引，建议设置合适的fillfactor。
create table ttt (id int) with (fillfactor=80);
4. 定期删除历史数据的场景，建议使用分区表或继承表的方式，TRUNCATE或DROP或断开继承的方式删除分区或继承的数据。
5. 全球化业务，建议使用UTF-8字符集。
6. 使用能描述清楚数据的类型。能不用字符串的时候，尽量不要用字符串。
7. 不建议使用无序的UUID，也不建议在无序的UUID上建立索引，包括PK。
8. 根据不同的数据类型，数据的查询需求，使用合适的索引方法（Btree, GiST, GIN, SP-GiST, HASH, rum, BRIN, bloom）。
9. 建议不需要的大对象，要清理，否则会造成空间泄露。
vacuumlo removes unreferenced large objects from databases.
Usage:
vacuumlo [OPTION]... DBNAME...
Options:
-l LIMIT commit after removing each LIMIT large objects
-n don't remove large objects, just show what would be done
-v write a lot of progress messages
-V, --version output version information, then exit
-?, --help show this help, then exit
Connection options:
-h HOSTNAME database server host or socket directory
-p PORT database server port
-U USERNAME user name to connect as
-w never prompt for password
-W force password prompt
10. 不建议使用public schema(不同业务共享的对象可以使用public schema)，应该为每个应用分配对应的schema，schema_name最好与username一致。
create role user1;
create schema user1 authorized user1;
11. 用户应该对频繁访问的大表（例如超过16GB的表，或者超过1亿记录的表，视IOPS能力）进行分区，从而提升查询的效率、更新的效率、备份与恢复的效率、建索引的效率等等，（PostgreSQL 10支持多核创建索引，可以适当将这个限制放大）。
https://www.postgresql.org/docs/10/static/ddl-partitioning.html
12. 多个业务共用一个PG集群时，建议为每个业务创建一个数据库。 如果业务之间有数据交集，建议使用同一个数据库，使用schema将其隔离。
30. 开发规约 - QUERY
1. 返回大量结果集的时候，建议使用游标的方式流式返回。
begin;
declare cur1 cursor for select * from tbl where xxx order by xxx;
fetch 1000 from cur1;
close cur1;
end;
2. 翻页时，不建议使用order by offset的方式，因为越到末尾，消耗越大。建议使用游标的方式翻页。
或者使用OFFSET偏移条件。
select * from tbl where xxx and xx>last_offseted_val order by xxx limit xxx;
3. 对于经常使用表达式作为查询条件的语句，应使用表达式或函数索引加速查询。
create index idx on tbl (udf(x,xxx));
select * from tbl where udf(x,xxx) ...;
4. 中文分词的查询，建议使用PostgreSQL的分词插件zhparser或jieba，用户还可以通过接口自定义词组。
create extension zhparser;
《使用阿里云PostgreSQL zhparser中文分词时不可不知的几个参数》
5. 规则表达式查询，文本近似度查询，前后模糊查询。建议对字段使用trgm的gin、gist索引。中文的支持确保数据库的lc_ctype不等于C。
create index idx on tbl using gin (txt gin_trgm_ops);
6. 不要使用count(列名)或count(常量)来替代count()，count()就是SQL92定义的标准统计行数的语法，跟数据库无关，跟NULL和非NULL无关。
7. 任何地方都不要使用 select * from t ，用具体的字段列表代替*，不要返回用不到的任何字段。另外表结构发生变化也容易出现问题。
31. 管理规约 - 安全与审计
1. 所有的DDL均应该被审计。
postgres=# alter system set log_statement TO ddl;
ALTER SYSTEM
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
2. 数据库的非应用程序用户（例如DBA，分析师，开发账号），所有的操作均应该被审计。
postgres=# alter role user_name set log_statement to ddl;
3. 所有的登陆和退出操作必须被审计。
vi postgresql.conf
log_connections = on
log_disconnections = on
4. 关键表（例如账务表、密码表等）的所有操作（包括DML）应该被审计。
《USE hstore store table’s trace record》
5. 为数据库访问账号设置复杂密码。
https://www.postgresql.org/docs/10/static/chkpass.html
6. 业务系统，开发测试账号，不要使用数据库超级用户。
Command: CREATE ROLE
Description: define a new database role
Syntax:
CREATE ROLE name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password'
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid
7. 应该为每个业务分配不同的数据库账号，禁止多个业务共用一个数据库账号。
8. 敏感数据应该加密存储，建议在服务端加解密，如果加密后的数据有等值查询以外的查询需求，建议使用数据库的透明加密技术。
《固若金汤 - PostgreSQL pgcrypto加密插件》
https://www.postgresql.org/docs/10/static/pgcrypto.html
9. 对安全要求较高的业务，网络传输应该被加密，使用数据库的SSL连接。
《PostgreSQL 如何实现网络压缩传输或加密传输(openssl)》
10. 不建议将数据库的监听端口暴露在公网，防止破解密码，DDoS攻击。
11. 有公网访问需求时，建议开启SSL连接，前置防火墙并使用来访白名单，数据库PG_HBA.CONF也要配置白名单。
12. 不要泄露用户密码的MD5值，目前这么做有危险，(10.0 SASL上线后安全性会有提高)。
《PostgreSQL 10.0 preview 安全增强 - SASL认证方法 之 scram-sha-256 安全认证机制》
13. 使用auth_delay插件，可以防止暴力破解。
https://www.postgresql.org/docs/10/static/auth-delay.html
32. 管理规约 - 诊断
1. 统计信息活动视图（表、索引、库、STANDBY、SLOT、流复制、后台任务、bgwriter、归档、事务、活动会话等），均以pg_stat开头。
《PostgreSQL 统计信息pg_statistic格式及导入导出dump_stat - 兼容Oracle》
《PostgreSQL pg_stat_ pg_statio_ 统计信息(scan,read,fetch,hit)源码解读》
2. auto_explain插件，可以帮助用户分析慢SQL的执行计划。
《PostgreSQL 函数调试、诊断、优化 & auto_explain》
3. pg_stat_statements插件，可以帮助用户找出资源开销的TOP SQL，（调用次数、IO耗时、CPU耗时、内存消耗）。优化数据库，应该从开销的TOP 1 SQL开始。
《PostgreSQL 如何查找TOP SQL (例如IO消耗最高的SQL)》
4. 建议配置慢SQL的时间阈值，超过阈值的SQL将被记录到日志文件中。
vi postgresql.conf
log_min_duration_statement = '1s'
5. 建议将日志输出格式配置为CSV格式。
vi postgresql.conf
log_destination = 'csvlog'
6. explain (analyze,verbose,timing,costs,buffers) 可以执行SQL，并输出详细的执行计划，帮助分析SQL的瓶颈，优化SQL。
7. explain analyze会真实的执行SQL，因此如果要用在DML语句上，请使用begin; explain analyze; rollback;的模式。
8. auto_explain插件，还可以帮助用户分析函数内部执行的SQL，以及他们的执行计划。
10. 数据库隐藏了一些开发参数，DEBUG参数，可以帮助用户进行跟深入的诊断。
《PostgreSQL Developer Options (debug, trace, system table mod and so on…) 详解》
11. perf可以从内核层面，帮助用户分析数据库软件本身的瓶颈。
《PostgreSQL 源码性能诊断(perf profiling)指南》
33. 管理规约 - 优化
1. 大批量数据入库的优化，如果有大批量的数据入库，建议使用copy语法批量写入（减少解析和交互），或者 insert into table values (),(),…(); 的方式。 提高写入速度。
2. 不要使用delete 全表，性能很差，请使用truncate代替，（truncate是DDL语句，注意加锁等待超时）。
set lock_timeout='1s';
truncate table only tbl; -- 不清理子表，只清理当前表
3. 两阶段提交的事务或者普通事务，要及时提交或回滚，否则可能导致数据库膨胀，年龄无法降低等问题。
postgres=# select * from pg_prepared_xacts ;
transaction | gid | prepared | owner | database
-------------+-----+----------+-------+----------
(0 rows)
4. 应用程序一定要开启autocommit，同时避免应用程序自动begin事务，并且不进行任何操作的情况发生，某些框架可能会有这样的问题。
5. 高并发的应用场合，务必使用绑定变量(prepared statement)，防止数据库硬解析消耗过多的CPU资源。
6. 秒杀场景，一定要使用 advisory_lock先对记录的唯一ID进行锁定，拿到AD锁再去对数据进行更新操作。 拿不到锁时，可以尝试重试拿锁。
update tbl set x=? where id=? and pg_try_advisory_xact_lock(?);
7. 在函数中，或程序中，不要使用count(*)判断是否有数据，很慢。 建议的方法是select 1 from table where … limit 1; 然后使用FOUND变量的值判断前面这条QUERY有没有结果返回来判断是否有数据。
create or replace function .....
declare
...
begin
...
perform 1 from tbl where ... limit;
if FOUND then
...
else
...
end if;
...
8. 分页评估，不需要精确分页数时，请使用快速评估分页数的方法(explain的评估)。
《妙用explain Plan Rows快速估算行 - 分页数估算》
9. 避免频繁创建和删除临时表，以减少系统表资源的消耗，因为创建临时表会产生元数据，频繁创建，元数据可能会出现碎片。
10. 可以预估SQL执行时间的操作，建议设置语句级别的超时，可以防止雪崩，也可以防止长时间持锁。
set statement_timeout = '10ms';
....
11. TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但是TRUNCATE是DDL，锁粒度很大，故不建议在开发代码中使用DDL语句，除非加了lock_timeout锁超时的会话参数或事务参数。
12. PostgreSQL支持DDL事务，支持回滚DDL，建议将DDL封装在事务中执行，必要时可以回滚，但是需要注意事务的长度，避免长时间堵塞DDL对象的读操作。
begin;
...
DDL;
...
end;
13. 如果用户需要在插入数据和，删除数据前，或者修改数据后马上拿到插入或被删除或修改后的数据，建议使用insert into .. returning ..; delete .. returning ..或update .. returning ..; 语法。减少数据库交互次数。
insert into .. returning ..;
delete .. returning ..;
update .. returning ..;
14. 自增字段建议使用序列，序列分为2字节，4字节，8字节几种(serial2,serial4,serial8)。按实际情况选择。 禁止使用触发器的方式产生序列值。
15. 树形查询应该使用递归查询，尽量减少数据库的交互或JOIN。
with recursive ....
;
16. 批量导入、迁移数据时。推荐的步骤：
建表（不要建索引，约束，主键）；
关闭autovacuum；
导入；
改大maintenance_work_mem；
创建索引、约束、主键；
analyze 表；
打开autovacuum。
17. 在函数中处理批量数据时，中间结果集尽量使用数组变量来代替临时表。如果临时表有非常庞大的数据时，才考虑使用临时表。
18. GIN索引的写优化，因为GIN的索引列通常是多值列，所以一条记录可能影响GIN索引的多个页，为了加快数据插入和更新删除的速度，建议打开fastupdate，同时设置合适的gin_pending_list_limit(单位KB)。autovacuum,vacuum,或者cache满时会自动合并，为了提高查询效率，可以将cache合并后进行查询。
19. BRIN索引优化，根据数据的相关性，以及用户需求的查询的范围，设置合适的pages_per_range=n。
20. b-tree索引优化，不建议对频繁访问的数据上使用非常离散的数据，例如UUID作为索引，索引页会频繁的分裂，重锁，重IO和CPU开销都比较高。
《PostgreSQL 优化CASE - 无序UUID性能问题诊断》
34. 管理规约 - 备份与恢复
1. 备份分为逻辑备份和物理备份，逻辑备份支持跨版本恢复，物理备份不支持跨版本恢复。
2. 逻辑备份不支持时间点恢复，逻辑备份为库级一致性备份。
3. 逻辑备份需要持有表的共享锁，与DDL会冲突，因此如果业务系统有可能执行DDL时，不建议使用逻辑备份。
4. 物理备份需要开启归档，备份时需要备份数据文件以及归档。
5. 物理备份不会堵塞任何SQL操作。
6. 小库，没有时间点恢复需求时，可以使用逻辑备份。
7. 大库，有时间点恢复需求时，请使用物理备份。
8. 物理备份的恢复速度取决于需要APPLY多少归档日志，有恢复时间SLA要求的场景，请评估好全量备份与归档备份的时间间隔。
9. 对于归档生成速度很快的数据库实例，建议使用快照的方式备份全量数据，或者使用数据库的块级别增量的方式备份。加快恢复的速度。
35. 管理规约 - 日常维护
1. 长事务，两阶段事务。长事务，长时间未关闭的两阶段事务，会导致数据库膨胀，无法冻结，年龄无法下降的问题。务必监控，必要时杀死（例如不正常跑几天的事务的情况）。
2. TOP SQL。通过关注TOP SQL，逐个优化。
3. VACUUM freeze 风暴。数据库通过vacuum freeze降年龄，如果大表同时爆发freeze，会导致大量写REDO，数据文件，IO暴增。必须预测和freeze smooth化。
《PostgreSQL的”天气预报” - 如何预测Freeze IO风暴》
4. 年龄。必须关注数据库的年龄，不能大于20亿。（虽然数据库会自动降年龄）。
select relkind, relname, age(relfrozenxid), pg_size_pretty(pg_total_relation_size(oid)) from pg_class where relkind in ('r','m','p') order by age(relfrozenxid) desc limit 100;
5. 膨胀。必须关注表、索引的膨胀。超出设置的阈值时，应该找原因，同时vacuum full(DDL)或者使用pg_repack处理膨胀。
《PostgreSQL 收缩膨胀表或索引 - pg_squeeze or pg_repack》
6. sort(cluster)。如果频繁的访问范围数据，应该对该列执行cluster进行heap sort。减少离散IO和IO放大的问题。
cluster tbl using index_name;
7. 瓶颈分析。关注LONG SQL，TOP SQL，perf。找出瓶颈，分析原因。
8. 读写分离。有必要时，可以使用物理流复制，逻辑流复制的方式建立备库，使用中间件或业务程度多数据源的方式，实现读写分离。同时关注备库的健康和延迟。
9. 垂直拆分。不同的业务，不相干的业务，当发展到单库瓶颈时，建议垂直拆分。
10. 水平分库。同一个业务，不相干的数据，当发展到单库瓶颈时，建议水平拆分。
11. 任务。关注数据库任务的执行状态，成功与否，错误原因。
《PostgreSQL Oracle 兼容性之 - DBMS_JOBS - Daily Maintenance - Timing Tasks(pgagent)》
12. HA健康。关注HA的健康，心跳，备库的延迟等。
13. 监控。监控各项用户或业务关心的指标。
14. 死锁。关注日志中输出的死锁信息，找出业务逻辑问题。
15. QUERY响应时间变化。关注重点QUERY（TOP SQL）的响应时间的变化，找出问题。
16. 表空间增速。关注表空间增速，提前做好扩容，迁移对象或表空间的准备。
17. 数据库增速。关注数据库的增速，提前做好扩容，清理、迁移数据的准备。
18. 对象增速。关注对象增速，提前做好扩容，分区的准备。
19. 分区表。关注分区表的边界是否快到达，提前创建分区。
20. 日志文件增速。日志文件（指错误日志），及时压缩归档和清理。
21. 临时文件。数据库某些查询会使用临时文件，关注临时文件的大小，找到异常QUERY（例如递归死循环），通过加大work_mem或者其他方式减少临时文件的使用。使用参数控制单个QUERY的临时文件的上限。
22. 入侵检测。分析日志，找到SQL注入，找到密码暴力破解的安全问题。
23. 日志文件分析，错误类型统计。从日志中，分析出错误日志，找到业务逻辑的问题，数据库本身的问题等。
24. 不使用的索引。通过pg_stat*统计信息，找到不使用的索引，删除。
25. 不合理的全表扫描。通过auto_explain，慢SQL，TOP SQL，找出给系统带来负担的不合理全表扫描SQL，优化之。