PostgreSQL 三十六计 - 下
25. 数据库端编程，处理复杂业务逻辑。
在传统企业、电商、运营商等涉及用户交互、或者多个系统交互的业务场景中，通常一个事务涉及到很复杂的业务逻辑，需要保证数据的一致性，同时还需要与数据库多次交互。
比如银行开户，涉及的业务系统多，逻辑复杂。在传统企业中，通常也使用商业数据库的过程函数，实现此类复杂的逻辑。
PostgreSQL的数据库过程函数支持的语言非常丰富，比如plpgsql（可与Oracle pl/sql功能比肩)，另外还支持语言的扩展，编程语言可以热插拔，例如支持python,perl,java,c,r 等等作为数据库的过程函数语言。
对于开发人员来说，几乎可以在PostgreSQL数据库中处理任何业务逻辑。使用数据库端编程，可以在保证一致性的前提下，处理复杂的业务逻辑，减少数据库与程序的交互次数，降低整个事务的RT。
26. 善用ECPG，C嵌入式开发
在一些对性能要求非常高的场景，同时考虑开发效率，C嵌入式开发是一个非常好的选择。
比如在很多金融行业的开发商，ECPG和Oracle Pro*C一样具有同等地位。
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的版本会增加内核层面的分布式事务控制。
28. 开发规约 - 命名
1. 长度不要超过63个字符。
2. 不要使用关键字作为对象名。
3. 不要使用小写字母、数字和下划线以外的字符作为对象名。
4. 不要使用多字节字符作为注释。
29. 开发规约 - 设计
1. 同类属性，将来可能涉及JOIN，FK，务必对齐数据类型，包括长度。
2. BTREE索引字段，长度不建议超过2000字节，否则请使用hash索引或函数索引。
3. 频繁更新的表，建议设置足够大的fillfactor。
4. 定期删除历史数据的场景，建议使用分区表或继承表的方式，TRUNCATE或DROP或断开继承的方式删除分区或继承的数据。
5. 全球化业务，建议使用UTF-8字符集。
6. 使用能描述清楚数据的类型。能不用字符串的时候，尽量不要用字符串。
7. 不建议使用无序的UUID，也不建议在无序的UUID上建立索引，包括PK。
8. 根据不同的数据类型，数据的查询需求，使用合适的索引方法（Btree, GiST, GIN, SP-GiST, HASH, rum, BRIN, bloom）。
9. 建议不需要的大对象，要清理，否则会造成空间泄露。
10. 不建议使用public schema(不同业务共享的对象可以使用public schema)，应该为每个应用分配对应的schema，schema_name最好与username一致。
11. 用户应该对频繁访问的大表（通常指超过8GB的表，或者超过1000万记录的表）进行分区，从而提升查询的效率、更新的效率、备份与恢复的效率、建索引的效率等等，（PostgreSQL支持多核创建索引后，可以适当将这个限制放大）。
12. 多个业务共用一个PG集群时，建议为每个业务创建一个数据库。 如果业务之间有数据交集，或者事务相关的处理，强烈建议在程序层处理数据的交互。
30. 开发规约 - QUERY
1. 返回大量结果集的时候，建议使用游标的方式流式返回。
2. 翻页时，不建议使用order by offset的方式，因为越到末尾，消耗越大。建议使用游标的方式翻页。
3. 对于经常使用表达式作为查询条件的语句，应使用表达式或函数索引加速查询。
4. 中文分词的查询，建议使用PostgreSQL的分词插件zhparser或jieba，用户还可以通过接口自定义词组。
5. 规则表达式查询，文本近似度查询，前后模糊查询。建议对字段使用trgm的gin、gist索引。中文的支持确保数据库的lc_collate, lc_ctype不等于C。
6. 不要使用count(列名)或count(常量)来替代count()，count()就是SQL92定义的标准统计行数的语法，跟数据库无关，跟NULL和非NULL无关。
7. 任何地方都不要使用 select * from t ，用具体的字段列表代替*，不要返回用不到的任何字段。另外表结构发生变化也容易出现问题。
31. 管理规约 - 安全与审计
1. 所有的DDL均应该被审计。
2. 数据库的非应用程序用户（例如DBA，分析师，开发账号），所有的操作均应该被审计。
3. 所有的登陆和退出操作必须被审计。
4. 关键表（例如账务表、密码表等）的所有操作（包括DML）应该被审计。
5. 为数据库访问账号设置复杂密码。
6. 业务系统，开发测试账号，不要使用数据库超级用户。
7. 应该为每个业务分配不同的数据库账号，禁止多个业务共用一个数据库账号。
8. 敏感数据应该加密存储，建议在服务端加解密，如果加密后的数据有等值查询以外的查询需求，建议使用数据库的透明加密技术。
9. 对安全要求较高的业务，网络传输应该被加密，使用数据库的SSL连接。
10. 不建议将数据库的监听端口暴露在公网。
11. 有公网访问需求时，建议开启SSL连接，前置防火墙并使用来访白名单，数据库PG_HBA.CONF也要配置白名单。
12. 不要泄露用户密码的MD5值，目前这么做有危险，(10.0 SASL上线后安全性会有提高)。
13. 使用auth_delay插件，可以防止暴力破解。
32. 管理规约 - 诊断
1. 统计信息活动视图（表、索引、库、STANDBY、SLOT、流复制、后台任务、bgwriter、归档、事务、活动会话等），均以pg_stat开头。
2. auto_explain插件，可以帮助用户分析慢SQL的执行计划。
3. pg_stat_statements插件，可以帮助用户找出资源开销的TOP SQL，（调用次数、IO耗时、CPU耗时、内存消耗）。优化数据库，应该从开销的TOP 1 SQL开始。
4. 建议配置慢SQL的时间阈值，超过阈值的SQL将被记录到日志文件中。
5. 建议将日志输出格式配置为CSV格式。
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参数，可以帮助用户进行跟深入的诊断。
11. perf可以从内核层面，帮助用户分析数据库软件本身的瓶颈。
33. 管理规约 - 优化
1. 大批量数据入库的优化，如果有大批量的数据入库，建议使用copy语法，或者 insert into table values (),(),...(); 的方式。 提高写入速度。
2. 不要使用delete 全表，性能很差，请使用truncate代替，（truncate是DDL语句，注意加锁等待超时）。
3. 两阶段提交的事务或者普通事务，要及时提交或回滚，否则可能导致数据库膨胀，年龄无法降低等问题。
4. 应用程序一定要开启autocommit，同时避免应用程序自动begin事务，并且不进行任何操作的情况发生，某些框架可能会有这样的问题。
5. 高并发的应用场合，务必使用绑定变量(prepared statement)，防止数据库硬解析消耗过多的CPU资源。
6. 秒杀场景，一定要使用 advisory_lock先对记录的唯一ID进行锁定，拿到AD锁再去对数据进行更新操作。 拿不到锁时，可以尝试重试拿锁。
7. 在函数中，或程序中，不要使用count(*)判断是否有数据，很慢。 建议的方法是select 1 from table where ... limit 1; 然后使用FOUND变量的值判断前面这条QUERY有没有结果返回来判断是否有数据。
8. 分页评估，不需要精确分页数时，请使用快速评估分页数的方法(explain的评估)。
9. 避免频繁创建和删除临时表，以减少系统表资源的消耗，因为创建临时表会产生元数据，频繁创建，元数据可能会出现碎片。
10. 可以预估SQL执行时间的操作，建议设置语句级别的超时，可以防止雪崩，也可以防止长时间持锁。
11. TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但是TRUNCATE是DDL，锁粒度很大，故不建议在开发代码中使用DDL语句，除非加了lock_timeout锁超时的会话参数或事务参数。
12. PostgreSQL支持DDL事务，支持回滚DDL，建议将DDL封装在事务中执行，必要时可以回滚，但是需要注意事务的长度，避免长时间堵塞DDL对象的读操作。
13. 如果用户需要在插入数据和，删除数据前，或者修改数据后马上拿到插入或被删除或修改后的数据，建议使用insert into .. returning ..; delete .. returning ..或update .. returning ..; 语法。减少数据库交互次数。
14. 自增字段建议使用序列，序列分为2字节，4字节，8字节几种(serial2,serial4,serial8)。按实际情况选择。 禁止使用触发器的方式产生序列值。
15. 树形查询应该使用递归查询，尽量减少数据库的交互或JOIN。
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开销都比较高。
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化。
4. 年龄。必须关注数据库的年龄，不能大于20亿。（虽然数据库会自动降年龄）。
5. 膨胀。必须关注表、索引的膨胀。超出设置的阈值时，应该找原因，同时vacuum full(DDL)或者使用pg_repack处理膨胀。
6. sort(cluster)。如果频繁的访问范围数据，应该对该列执行cluster进行heap sort。减少离散IO和IO放大的问题。
7. 瓶颈分析。关注LONG SQL，TOP SQL，perf。找出瓶颈，分析原因。
8. 读写分离。有必要时，可以使用物理流复制，逻辑流复制的方式建立备库，使用中间件或业务程度多数据源的方式，实现读写分离。同时关注备库的健康和延迟。
9. 垂直拆分。不同的业务，不相干的业务，当发展到单库瓶颈时，建议垂直拆分。
10. 水平分库。同一个业务，不相干的数据，当发展到单库瓶颈时，建议水平拆分。
11. 任务。关注数据库任务的执行状态，成功与否，错误原因。
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，优化之。
