显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL research

公益是一辈子的事, I'm 德哥@Digoal, Just Do it!

 
 
 
 
 
 
为了让跟多的朋友了解PostgreSQL, 我将这些年对PG的使用和研究做成了一些体系的培训视频和一些专题视频.(免费观看和下载)

希望通过这些视频帮到一些朋友, 同时对视频中的错误点烦请读者指出以更正, 谢谢.

目前在更新的视频是<PostgreSQL 数据库管理 5天>和<PostgreSQL 性能优化 3天>.

在线观看地址 :

http://www.tudou.com/home/digoal/

培训视频源文件以及文档下载地址 : 

http://pan.baidu.com/share/home?uk=1982970774#category/type=0

转载请注明出处.

针对一些网友的反馈, 重新设计的培训内容如下, 由浅入深, 适合不同的人群. 包含

<PostgreSQL发展历程,社区介绍,资源介绍 2小时>

<PostgreSQL SQL基础 2天>

<PostgreSQL 数据库管理 3天>

<PostgreSQL 数据库备份与恢复, 数据复制, 负载均衡与高可用 3天>

作者  | 2014-2-22 10:19:30 | 阅读(13082) |评论(62) | 阅读全文>>

[置顶] 给PostgreSQL爱好者的参考资料

2011-8-21 8:32:39 阅读56323 评论32 212011/08 Aug21

推荐书籍:

概念书籍:

《PostgreSQL Introduction and Concepts》

开发书籍:

《PostgreSQL开发必备参考手册》

管理类书籍:

《PostgreSQL 9 Administration Cookbook》

《PostgreSQL 9.0 High Performance》

《PostgreSQL Server Programming》

《How PostgreSQL Processes a Query》

了解内核 :

http://www.postgresql.org/developer/backend/

http://wiki.postgresql.org/wiki/Backend_flowchart

《数据库查询优化器的艺术:原理解析与SQL性能优化》 - 海翔老师

《 PostgreSQL 内核分析》 - 彭老师

《从小工到专家》 -  唐成

官方手册:

作者  | 2011-8-21 8:32:39 | 阅读(56323) |评论(32) | 阅读全文>>

论count使用不当的罪名 和 分页的优化

2016-5-6 20:03:49 阅读8 评论0 62016/05 May6

分页是一个非常常见的应用场景,然而恐怕没有多少人想过其优化方法。

确一味的责怪为什么数据库用count(*)计算分页数是如此的慢。

很多开发人员喜欢用count先算一下结果集的大小,然后就知道需要排多少页。

然后再从数据库取出对应的数据,并展示给用户。

问题1

count会扫一遍数据,然后取数据又扫一遍数据。重复劳动。

问题2,很多人喜欢用order by offset limit来展示分页。

其实也是一个非常大的问题,因为扫描的数据也放大了,即使在order by 的列上用到了索引也会放大扫描的数据量。

因为offset的row也是需要扫的。

问题1的优化

使用评估行数,方法如下

创建一个函数,从explain中抽取返回的记录数

CREATE FUNCTION count_estimate(query text) RETURNS INTEGER AS $func$ DECLARE rec record; ROWS INTEGER; BEGIN FOR rec IN EXECUTE 'EXPLAIN ' || query LOOP ROWS := SUBSTRING(rec."QUERY PLAN" FROM ' rows=([[:digit:]]+)'); EXIT WHEN ROWS IS NOT NULL; END LOOP; RETURN ROWS; END $func$ LANGUAGE plpgsql;

作者  | 2016-5-6 20:03:49 | 阅读(8) |评论(0) | 阅读全文>>

本文将对一个任意范围按ID分组查出每个ID对应的最新记录的CASE做一个极致的优化体验。

优化后性能维持在可控范围内,任意数据量,毫秒级返回,性能平稳可控。

比优化前性能提升1万倍。

CASE如下:

有一张数据表,结构: CREATE TABLE target_position ( target_id varchar(80), time bigint, content text ); 数据量是 100 亿条左右 target_id 大约 20 万个 数据库使用的是 PostgreSQL 9.4 需求: 查询每个目标指定时间段的最新一条数据,要求1秒内返回数据。 时间段不确定 现在是使用窗口函数来实现,如下: select target_id,time,content from (select *,row_number() over (partition by target_id order by time desc) rid from target_position where time>开始时间 and time<=结束时间) as t where rid=1; 效果很差。

分析一下原理,这个case其实慢就慢在扫描的时间段,因为需要遍历整个时间段的数据,然后分组排序,取出该时间段内每个target_id的最新一条记录。

这个语句决定了时间段越大,可能的扫描量就越大,时间越久。

直奔最优方

作者  | 2016-5-6 20:03:16 | 阅读(8) |评论(0) | 阅读全文>>

中文模糊查询性能优化 by PostgreSQL trgm

2016-5-6 20:02:50 阅读7 评论0 62016/05 May6

前模糊,后模糊,前后模糊,正则匹配都属于文本搜索领域常见的需求。

PostgreSQL在文本搜索领域除了全文检索,还有trgm是一般数据库没有的,甚至可能很多人没有听说过。

对于前模糊和后模糊,PG则与其他数据库一样,可以使用btree来加速,后模糊可以使用反转函数的函数索引来加速。

对于前后模糊和正则匹配,则可以使用trgm,TRGM是一个非常强的插件,对这类文本搜索场景性能提升非常有效,100万左右的数据量,性能提升有500倍以上。

例子:

生成100万数据

postgres=# create table tbl (id int, info text); CREATE TABLE postgres=# insert into tbl select generate_series(1,1000000), md5(random()::text); INSERT 0 1000000 postgres=# create index idx_tbl_1 on tbl using gin(info gin_trgm_ops); CREATE INDEX postgres=# select * from tbl limit 10; id | info ----+---------------------------------- 1 | dc369f84738f7fa4dc38c364cef817d0 2 | 4912b0b16670c

作者  | 2016-5-6 20:02:50 | 阅读(7) |评论(0) | 阅读全文>>

PostgreSQL pg_backup_start_time() CST 时区转换 问题

2016-5-6 20:02:03 阅读8 评论0 62016/05 May6

PostgreSQL的物理备份方法之一 :

在使用pg_start_backup()函数新建备份点后,用户可以开始拷贝PG的数据文件。

postgres=# select pg_start_backup('a'),now(); pg_start_backup | now -----------------+------------------------------- 0/50000028 | 2016-05-06 11:03:30.917509+08 (1 row)

调用pg_start_backup后,会创建一个检查点,同时在$PGDATA中新建一个backup_label文件。

里面包含了START TIME的信息,是创建完检查点后的时间。

START WAL LOCATION: 0/50000028 (file 000000010000000000000014) CHECKPOINT LOCATION: 0/50000028 BACKUP METHOD: pg_start_backup BACKUP FROM: master START TIME: 2016-05-06 11:03:33 CST LABEL: a

但是,使用pg_backup_start_time得到的时间与之不符。

postgres=# select pg_backup_start_time(); pg_backup_start_time

作者  | 2016-5-6 20:02:03 | 阅读(8) |评论(0) | 阅读全文>>

PostgreSQL csvlog 源码分析

2016-5-6 20:01:30 阅读7 评论0 62016/05 May6

PostgreSQL csvlog日志格式记录了非常多的信息,通过CSV外部表,可以使用SQL对日志进行分析。

文档中的例子:

http://www.postgresql.org/docs/9.5/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG

CREATE TABLE postgres_log ( log_time timestamp(3) with time zone, user_name text, database_name text, process_id integer, connection_from text, session_id text, session_line_num bigint, command_tag text, session_start_time timestamp with time zone, virtual_transaction_id text, transaction_id bigint, error_severity text, sql_state_code text, message text,

作者  | 2016-5-6 20:01:30 | 阅读(7) |评论(0) | 阅读全文>>

论云数据库编程能力的重要性

2016-5-6 19:57:11 阅读9 评论0 62016/05 May6

云为我们提供了便利,降低了开发和运维的成本。

但是也必须思考一个问题,我们的云组件之间的的网络延迟?

(相比较局域网的服务器和服务器之间)

你可以用各种方法测试验证一下。

以往我们把数据放在数据库,数据库只提供简单的增删改查,大部分的业务逻辑放在应用服务器来完成。

但是在云时代,如果我们还这样的话,应用服务器和数据库间如果多次交互,会浪费大量的时间。

我们应该充分利用数据库的编程能力,例如PostgreSQL,是一个功能非常强大的数据库,我们完全可以把业务逻辑放在数据库处理,

例如使用plv8, plpython, plpgsql, plperl, pltcl等函数语言,

数据类型支持也非常的丰富,例如jsonb, GIS, text, 异构类型,Key-Value类型...等,

索引支持btree, hash, gin , gist, spgist, brin等索引类型,

SQL语法层面支持 窗口查询,递归查询,grouping set, 等高级语法。

JOIN方面,支持hash join , merge join , nestloop join ,

优化器方面,支持自定义成本因子, CBO , 遗传算法等。

另外PostgreSQL更强大之处,可以利用GPU加速运算,包括隐式加速,也可以显示加速。

隐式指数据库自身提供的custom scan provider编程接口,已经有实现了的插件。

作者  | 2016-5-6 19:57:11 | 阅读(9) |评论(0) | 阅读全文>>

PostgreSQL 备份链路sslcompression压缩

2016-5-6 19:56:39 阅读8 评论0 62016/05 May6

通过链路压缩,提高窄带网络PostgreSQL数据库的备份性能。

需要用到PostgreSQL的SSL支持,用法请参考

http://blog.163.com/digoal@126/blog/static/163877040201342233131835

流复制协议,pg_dump都支持ssl,因为它们都走libpq的调用,libpq是支持ssl的。

http://www.postgresql.org/docs/9.3/static/libpq-envars.html

PGSSLCOMPRESSION behaves the same as the sslcompression connection parameter.

http://www.postgresql.org/docs/9.5/static/libpq-connect.html#LIBPQ-CONNECT-SSLCOMPRESSION

sslcompression If set to 1 (default), data sent over SSL connections will be compressed (this requires OpenSSL version 0.9.8 or later). If set to 0, compression will be disabled (this requires OpenSSL 1.0.0 or later). This parameter is ignored if a connection without SSL is made, or if the

作者  | 2016-5-6 19:56:39 | 阅读(8) |评论(0) | 阅读全文>>

PostgreSQL serializable read only deferrable事务的用法背景

2016-5-6 19:56:09 阅读8 评论0 62016/05 May6

在开始讲serializable read only deferrable用法前,需要先了解一下serializable隔离级别。

https://wiki.postgresql.org/wiki/Serializable

http://www.postgresql.org/docs/9.5/static/transaction-iso.html#XACT-SERIALIZABLE

http://blog.163.com/digoal@126/blog/static/1638770402013111795219541

serializable是最高的隔离级别,模拟并行事务的串行处理,当不能实现串行一致时,后提交的会话需要回滚,因此serializable事务相互之间是有干扰的。

PostgreSQL在三个地方介绍到了serializable的deferrable用法。

作者  | 2016-5-6 19:56:09 | 阅读(8) |评论(0) | 阅读全文>>

查看所有日志>>

 
 
 
 
 
 
 
 

浙江省 杭州市 狮子座

 发消息  写留言

 
PostgreSQL 高校实验室,企业,社区大联盟才是未来的方向.
 
QQ276732431
E-Mail digoal@126.com
博客等级加载中...
今日访问加载中...
总访问量加载中...
最后登录加载中...
 
 
 
 
 

日志分类

 
 
日志分类列表加载中...
 
 
 
 
 

FlagCounter

 
 
模块内容加载中...
 
 
 
 
 

新浪微博

 
 
模块内容加载中...
 
 
 
 
 
 
 
模块内容加载中...
 
 
 
 
 
 
 
模块内容加载中...
 
 
 
 
 
 
 
心情随笔列表加载中...
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016

GACHA-脖子以下全是腿的妹子

二次元同好交流新大陆

扫码下载App

LOFTER-颜值最高的妹子都在这

汇聚2000万达人的兴趣社区
下载即送20张免费照片冲印

注册 登录  
 加关注