2018年PostgreSQL中国技术大会 主要业务场景 • IM工具+SCRM系统（高并发、低延迟、稳定） • 大量客户需要私有化云服务器部署（云更新） • 需求变更频繁，不适合关系型数据库 2018年PostgreSQL中国技术大会 目前服务器架构 2018年PostgreSQL中国技术大会 MySQL应用史 • 最开始做的站群系统WEB端使用MySQL： • copy简单快速，批量复制，替换修改简单； MongoDB存储，减轻MySQL负担； • 这种方式虽然可以暂时解决问题，我们自己用也没有任何 问题，可以结合各自数据库优点，发挥性能。但是我们有 很多客户是需要私有化部署数据库，让每家客户都接受这 种方式，会为甲方增加很多服务器以及运维成本，可能客 户数据量很少，也需要使用双数据库，更新运维都比较麻 烦。 2018年PostgreSQL中国技术大会 PgSQL≈MySQL+MongoDB • 个人认为，PgSQL结合了MySQL关系型数据库事务处理能力

'*' port = 5432 # vi /var/lib/pgsql/13/data/pg_hba.conf #pg-13版本，添加以下一行表示允 许所有客户端ip远程连接 #若修改了存储路径，则在新的路径下/data_pg/data/pg_hba.conf host all all 0.0.0.0/0 password '*' port = 5432 # vi /var/lib/pgsql/data/pg_hba.conf #pg-10版本，添加以下一行表示允许 所有客户端ip远程连接 #若修改了存储路径，则在新的路径下/data_pg/data/pg_hba.conf host all all 0.0.0.0/0 password #提示符=>前的postgres表示当前数据库名 # psql -h x.x.x.x -p 5432 -U cof -d db_test #使用cof用户登录pg，连接 db_test这个库 db_test=> #提示符 => 表示普通账号，=#表示管理员账号 db_test=>

rollback; • create index CONCURRENTLY idx on tbl(id); • 密码复杂度、认证方法 • 业务系统，开发测试账号，不要使用数据库超 级用户。 • 长连接relcache , syscache • 数据隔离(cluster -> database -> schema) • 主备切换(prewarm) 2016Postgres中国用户大会 hash index, unlogged table使用注意 • AD LOCK，秒杀、消除并发数据冲突 • count(*) 是否存在数据? perform limit 1(FOUND) • 短连接，连接池 • 频繁创建、删除临时表(10.0 ROADMAP) • explain评估结果集, instead count(*)（分页数） • 分页优化cursor instead order by

.........................996 34.61. view_column_usage ..............................................................................................997 34.62. view_routine_usage ...................... ......................................................................997 34.63. view_table_usage.................................................................................................998 34 Rules Work.............................................................................1112 38.2.2. View Rules in Non-SELECT Statements .......................................................1117 38.2.3

.............................940 34.61. view_column_usage .......................................................................................941 34.62. view_routine_usage ......................... ............................................................942 34.63. view_table_usage..........................................................................................942 34.64. views ...... Rules Work......................................................................1050 xxi 38.2.2. View Rules in Non-SELECT Statements ................................................1054 38.2.3. The Power

.................................. 1029 36.61. view_column_usage ....................................................................... 1029 36.62. view_routine_usage ................................ ..................................... 1030 36.63. view_table_usage ......................................................................... 1031 36.64. views ......................................... ......................................................................... 1399 ALTER MATERIALIZED VIEW .................................................................... 1400 ALTER OPERATOR ........

..................................... 977 36.61. view_column_usage ................................................................... 977 36.62. view_routine_usage ................................... ............................... 978 36.63. view_table_usage ..................................................................... 978 36.64. views ..................................................... ......................................................................... 1328 ALTER MATERIALIZED VIEW ............................................................... 1329 ALTER OPERATOR .............

.........................965 34.60. view_column_usage ..............................................................................................966 34.61. view_routine_usage ...................... ......................................................................966 34.62. view_table_usage.................................................................................................967 34 Rules Work.............................................................................1075 38.2.2. View Rules in Non-SELECT Statements .......................................................1080 38.2.3

.............................913 34.60. view_column_usage .......................................................................................913 34.61. view_routine_usage ......................... ............................................................914 34.62. view_table_usage..........................................................................................914 34.63. views ...... SELECT Rules Work......................................................................1016 38.2.2. View Rules in Non-SELECT Statements ................................................1020 38.2.3. The Power

.................................... 1101 37.63. view_column_usage .................................................................. 1101 37.64. view_routine_usage ................................... ............................. 1102 37.65. view_table_usage .................................................................... 1102 37.66. views ...................................................... ......................................................................... 1497 ALTER MATERIALIZED VIEW ............................................................... 1498 ALTER OPERATOR .............