开发者学堂课程【PostgreSQL 云上开发实践:阿里云 PostgreSQL_开发实践_4】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/528/detail/7107
阿里云 PostgreSQL_开发实践_4
目录：
一、数据老化实践
二、分级存储
三、复杂业务逻辑延迟问题优化
四、权限体系
五、行级权限控制
六、为什么会膨胀
七、freeze 风暴、追溯
八、预测、预防 FREEZE 风暴
九、可定义 SLA 的备份恢复架构设计
十、分区建议
十一、快速构造海量测试数据
一.数据老化实践
https://github.com/digoal/blog/blob/master/201712/20171208_01.md
我们可以去安排一些这样的调度任务，把我们的数据根据业务上的一些访问的规则，相对较老的数据任务调度的形式，比如说每天或者每周又或者每月月初，我们可以把一些相对来说时间比较久远的数据从分区表里面把它给剥离出来，把它给放到 OSS 里面存储，那么做法的话，那就需要依赖 PG 里面的特性，叫做继承，
利用 PG 的分区表的特性，以及继承的特性，可以使我们的存到不同的存储空间的数据，把它给联合起来。
二.分级存储
l https://help.aliyun.com/knowledge_detail/43352.html
l 热数据
-实例本地存储
l 访问频次较低数据
- oss 外部表存储
- 压缩格式选择
l 继承与分区约束
- 每个 OSS 外部表负责一部分数据
- 使用约束
- 建立 OSS 外部表继承关系
三. 复杂业务逻辑延迟问题优化
比方说咱们有一些非常复杂的业务逻辑 那么事物的话涉及到很多次的数据库交互，那么你会发现说，如果每一次交互假设要耗掉在网络上消耗的时间，
比如说是毫秒，那我们要交互100次的话，实际上在网络上消耗的时间100个毫秒，那么100次100次的交互，如果在数据库里面，可以很快的完成。
一个事物当中假设有20条C口，包括基于组键的增删改查， 一个操作就设计20条C口，那么使用交互式的方法，我们发现说我们的 TPS 只能达到18000，如果我们把这样一些业务逻辑都放到数据库里面，就跟数据库只交互一次，那么我的 TPS 可以达到5万多。
四. 权限体系
实例权限：
修改 pg_hba.conf
数据库权限：
grant 赋予是否允许连接或创建 schema 的权限
revoke 回收
schema权限：
grant 赋予允许查询schema中的对象,或在 schema 中创建对象
revoke 回收
object 权限：
grant 赋予
revoke 回收
表空间：
grant 赋予允许在对应表空间创建表，物化视图,索引，临时表
revoke 回收
五. 行级权限控制
l RLS
-https://www.postgresql.org/docs/10/static/sql-createpolicy.html
-https://github.com/digoal/blog/blob/master/201602/20160203_03.md
-https://github.com/digoal/blog/blob/master/201504/20150409 _01.md
-CREATE POLICY name ON table_name [ AS { PERMISSIVE |
RESTRICTIVE } ] [ FOR {ALL | SELECT [ INSERT | UPDATE | DELETE} ][ To { role_name | PUBLIC | CURRENT_USER |SESSION_USER } [,.…]][USING ( using_expression ) ] [ WITH CHECK ( check_expression ) ]
六. 为什么会膨胀
l 表、索引、物化视图为什么会膨胀
l 如何检查膨胀
-https://github.com/digoal/blog/blob/master/201306/20130628_01.md
https://raw.githubusercontent.com/digoal/pgsql_admin_script/master/generate _report.sh
l 膨胀如何处理
https://github.com/digoal/blog/blob/master/201610/20161030_O2.m
l 如何预防膨胀
- https://github.com/digoal/blog/blob/master/201511/20151109_01.md
- https://github.com/digoal/blog/blob/master/201504/20150429_02.md
七. freeze 风暴、追溯
l 业务空闲、But lO\CPU 偏高。
- select age(a.relfrozenxid),last_autovacuum,last_vacuum,schemaname,a.relname,pg_size_pretty(pg_total_relation_size(relid))from pg_class a, pg_stat_all_tables b where
aoid=b.relid and a.relkind in ('r', 'm') order bylast_autovacuum nulls last;
八. 预测、预防 FREEZE 风暴
预防
表级 autovacuum freeze 参数
AUTOVACUUM_ANALYZE_SCALE_FACTOR AUTOVACUUM_MULTIXACT_FREEZE_MAX_AGE
AUTOVACUUM_VACUUM_THRESHOLD TOAST.AUTOVACUUM_FREEZE_MIN_AGE
TOAST.AUTOVACUUM_VACUUM_COST_LIMIT
AUTOVACUUM_ANALYZE__THRESHOLD AUTOVACUUM_MULTIXACT_FREEZE_MIN_AGE FILLFACTOR
TOAST.AUTOVACuUM_FREE2E_TABLE_AGE TOAST.AUTOVACUUM_VACUUM_SCALE_FACTOR
-AUTOVACUUM_ENABLED
九. 可定义SLA的备份恢复架构设计
https://github.com/digoal/blog/blob/master/201711/20171129_02.md
数据库监控、健康指标
l https://github.com/digoal/pgsql_admin_script/blob/maste
l 膨胀对象
l 操作系统信息(资源限制、防火墙、硬件错误、磁盘寿。
l 未引用大对象
l 剩余年龄
l 数据库
l 长事务、2PC
l 错误日志
l 归档状态
l 慢 SQL
l 备库延迟
l 空间利用率
l HA 状态
l 空间变化趋势
l 订阅延迟
l TOP 对象
l 简单密码
l 数据库连接(总、己使用、剩余、认证中、库级、用户级)
l TOP SQL (Io\cPU\CALLS\timing)
l 未使用索引
l 索引数超过N的对象
l 回滚比例
l QPS\TPS
l 垃圾比例
l 序列剩余
l 安全风险（触发器、事件触发器、规则、SQL 注入、密码错误尝试、对象名)
多实例管理、监控
https://github.com/dalibo/pgbadger
https://www.pgadmin.org/
http://pgstatsinfo.sourceforge.net/
十. 分区建议
l 单表多大建议使用分区表
- 非常频繁更新的表（考虑到 autovacuum 的速度)
·2亿
·指表中频繁被更新的记录数在2亿以内，表可以大到20亿不分区。
- 更新、删除不频繁或毫无的表（考虑到设计 rewrite 的 DDL ，建索引，逻辑备份等的速度)
·20亿
十一. 快速构造海量测试数据
有趣的功能：
1、SRF
返回多条记录的函数。
返回一批数值、时间戳、或者数组的下标。
例子，生成一批顺序值。
postgres=# select id from generate_series(1,10) t(id);
id
2、随机数
random()
例子，生成一批随机整型
postgres=# select (random()*100)::int from generate_series(1,10);
int4
3、随机字符串
md5(random()::text)
例子，生成一批随机字符串
postgres=# select md5(random()::text) from generate_series(1,10);
md5
4、重复字符串
repeat('abc', 10)
例子，生成重复2次的随机字符串
postgres=# select repeat(md5(random()::text),2) from generate_series(1,10);
5、随机中文
$$ language plpgsql strict;
postgres=# select gen_hanzi(10) from generate_series(1,10);
gen_hanzi
----------------------
騾歵癮崪圚祯骤氾準赔
縬寱癱办戾薶窍爉充環
鷊赶輪肸蒹焷尮禀漽湯
6、随机数组
create or replace function gen_rand_arr(int,int) returns int[] as $$
---------------------------------
{69,11,12,70,7,41,81,95,83,17}
{26,79,20,21,64,64,51,90,38,38}
7、连接符
postgres=# select concat('a', ' ', 'b');
concat
--------
a b
(1 row)
8、自定义函数
通过自定义函数，可以生成很多有趣的数据。
随机身份证号
l https://github.com/digoal/blog/blob/master/201711/20171121_01.md
l pgbench
