菜单

最新资讯2016 年开发者应该控制的十独 Postgres 技巧

2018年10月5日 - 最新资讯

【编者按】作为同一舒缓开源的靶子—关系数据库,Postgres
一直获得不少开发者喜爱。近日,Postgres
正式宣布了9.5版本,该版进行了汪洋的修补和成效改进。而本文将享用10个
Postgres 使用技术,旨在给开发者能够更灵活和快地应用这数据库。

放假期间,很多总人口会见择去读书有新书或者学有新技术来多自己。下面笔者将推荐一些Postgres技巧与技术给大家,这些技巧会拉扯而越灵敏方便地使
Postgres。如果您以为这些技能会对君生出帮助,你可选择订阅 Postgres
weekly,本周都见面颁布有Postgres最新的讯息及技艺干货。

1.CTEs——Common Table Expressions

CTE
允许你开片百般棒的事务,比如递归查询,即使是用在有的极端简单易行的说话操作及,CET
都见面时有发生大精彩之展现。 CTE 可以认为是于么 SELECT、INSERT、UPDATE、DELETE
或 CREATE VIEW 语词之推行范围外定义之临时结果集。CTE
与派生表类似,具体表现在无存储吗目标,并且只是当查询期间有效。与派生表的不同之处在于,CTE
可自从援,还可在同等查询中援数。这样开发者就好重新爱地开创而读查询。

开发者在开创 SQL
语句的时刻,往往会生出成千上万执行,有的竟是超越上百实施,而由此运用4-5只 CETs
后,语句会缩短很多,这样便老大爱提高语句的可读性,尤其是对于新人来说。

2.装一个.psqlrc

使安了 bashrc、vimrc 等文件,那为什么不对准 Postgres
做来同样的操作为?下面这些设置都大棒,你不妨尝试:

经过默认来取重新好之格式;

使用\pset null ¤,让 null 更形象化;

默认设置\timing on来展示 SQL 执行时间;

自定义提示\set PROMPT1'%[3[33;1m%]%x%[3[0m%]%[3[1m%]%/%[3[0m%]%R%# '

据悉名称来保存你常用的运作语句。

下面是笔者之 psqlrc 设置:

\set QUIET 1
\pset null '¤'

-- Customize prompts
\set PROMPT1 '%[3[1m%][%/] # '
\set PROMPT2 '... # '

-- Show how long each query takes to execute
\timing

-- Use best available output format
\x auto
\set VERBOSITY verbose
\set HISTFILE ~/.psql_history- :DBNAME
\set HISTCONTROL ignoredups
\set COMP_KEYWORD_CASE upper
\unset QUIET

3. 通过 pg_stat_statements 来查阅需要进行索引的地方

pg_stat_statements
可能是开发者提高数据库性能最好有价的工具。一旦启用(还有extension
pg_stat_statements),它便会自动记录数据库的具备查询记录和她所花的流年。这样,你尽管很易优化查询语句,提高性能。

SELECT 
(total_time / 1000 / 60) as total_minutes, 
(total_time/calls) as average_time, 
query 
FROM pg_stat_statements 
ORDER BY 1 DESC 
LIMIT 100;

本来,会因此交到一些特性代价,但相比那所带动的性能提升简直微乎其微。在旋即篇文章里好读到重多关于
Postgres 性能方面的东西。

4. ETL 有点慢,用 FDWs

倘若出恢宏之微服务或不同之应用程序,那么可能需要多不一之数据库来支撑她。默认情况是创立有数据仓库并经过
ETL
连接,但是如此做有时候太重度了。这时候,你只是待用数据库一次性集中在联名,或者以少数情下,外部数据封装器可以允许你跨多单数据库查询,比如
Postgres 到Postgres,或者是 Postgres 到 Mongo 或 Redis 数据库之类。

5. array和array_agg

在付出应用程序时,很少会完全不用 arrays,而当数据库被千篇一律如此。Arrays
可以视作是 Postgres
里的别样一个数据类型,并拥有一些杀手级应用,比如博文标签这些。

可是,即使你免将 arrays 当做数据类型使用,也不时需要像一个 array
那样汇总一些多少,中间用逗号隔开。类似下面这样,你可以轻松汇总用户清单:

SELECT 
users.email,
array_to_string(array_agg(projects.name), ',')) as projects
FROM
projects,
tasks,
users
WHERE projects.id = tasks.project_id
AND tasks.due_at > tasks.completed_at
AND tasks.due_at > now()
AND users.id = projects.user_id
GROUP BY 
users.email

6.慎重新动 materialized views

乃可能未熟识 materialized views(物化视图),materialized views
是包括一个查询结果的数据库对像。所以,它是有些查询或「view」的一个物化的或骨干的快照版本。在无比初步的物化版本被,会于
Postgres
建立一个时不时要,但整是免可用的。那是以当您锁定事务之下,有或会见拦一些其他读取和运动。

现行早已好过多,但还不够有开箱即用的工具来进行刷新。这为就是表示你要装有调度任务或
cron 作业来定期刷新物化视图。如果你时着开发片报要 BI
应用程序,那么您要需要动用物化视图的。它们的可用性正在不停提升,所以,Postgres
已经知道哪些自动化刷新它们。

7.窗口函数

窗口函数(Windows fuction)可能依然是 SQL
中比复杂且十分不便理解的事物。总之,它们会给你排序一个询问结果,然后开展一行到玲一行的计算,如果没有
SQL
PL,这些东西会异常为难开。不过,你可以举行有非常简单的操作,比如排名,基于某些价值对结果开展排序;复杂些的,比如计算环比提高数量。

8.针对数据透视表的一个重复简短方法

在 Postgres 中,Table_func
通常是用作计算一个数目透视表的援方式。不幸地是,这个利用起来相当艰难的,更为基础的用法是跟原始
SQL 一起下。在 Postgres 9.5
中早已拓展了改进,用起来会方便广大。但在此之前,你汇总每个条件的结果莫是
false 就是 true ,最后说道为再次简明的推理:

select date,
   sum(case when type = 'OSX' then val end) as osx,
   sum(case when type = 'Windows' then val end) as windows,
   sum(case when type = 'Linux' then val end) as linux
from daily_visits_per_os
group by date
order by date
limit 4;

大家可通往Dimitri
Fontaine的博客翻看具体最新资讯示例。

9.PostGIS

PostGIS 可以说凡是具有 GIS 数据库被极度好之一个了。事实上,开发者获得的富有
Postgres 标准会如它们更是强硬——一个最为好的例证是出自 Postgres 近年来的 GiST
索引,它叫 PostGIS 提供了庞大的性能提升。
如果您本正值做有及地理空间数据有关的事体,并且要一些比
earth_distance 扩展更好用的家伙,那么 PostGIS 就是你最佳选择。

10.JSONB

从今 Postgres 9.2 开始,Postgres 的每个版本被还发生 JSON
的身形,在每个新本子功能还怀有升级,并且在逐步全面成一个越是圆满的堆栈。在新式发布之9.5本子中,JSONB在psql中的输出为再也兼具可读性。

**初稿地址:http://www.craigkerstiens.com/2015/12/29/my-postgres-top-10-for-2016/**

本文系国内 ITOM
行业领军企业OneAPM
工程师编译整理。我们从事为帮助企业用户提供全栈式的属性管理和IT运维管理服务,通过一个探针就可知好日志分析、安全预防、APM
基础零部件监控、集成报警及特别数目解析等职能。想读更多技术文章,请看
OneAPM
官方技术博客

本文转自 OneAPM 官方博客

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图