2016 年开发者应该掌握的十个 Postgres 技巧

HitenDev 发布于2019-06-25 18:18 / 2729人阅读

摘要：近日，正式发布了版本，该版本进行了大量的修复和功能改进。事实上，开发者获得的所有标准会使它更加强大一个最好的例子是来自近年来的索引，它给提供了极大的性能提升。在最新发布的版本中，在中的输出也更具可读性。

【编者按】作为一款开源的对象—关系数据库，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 更形象化；

默认设置 iming 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
	iming

-- 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 官方博客

云服务器 idc机房托管学习深度学习前应该掌握什么 2016年淘宝数据分析前端开发2016 前端开发需要掌握

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/17513.html

2016 年开发者应该掌握的十个 Postgres 技巧

摘要：近日，正式发布了版本，该版本进行了大量的修复和功能改进。事实上，开发者获得的所有标准会使它更加强大一个最好的例子是来自近年来的索引，它给提供了极大的性能提升。在最新发布的版本中，在中的输出也更具可读性。【编者按】作为一款开源的对象—关系数据库，Postgres 一直得到许多开发者喜爱。近日，Postgres 正式发布了9.5版本，该版本进行了大量的修复和功能改进。而本文将分享10个 ...

jsbintask 2019-08-06 13:54 评论0 收藏0
使用Hadoop做数据分析前的十个替代准备

摘要：向数据分析师问几个简单的问题数据增长的有多快这个数据增长的步伐很快么数月或数年之后，这个数据将会达到什么样的尺寸许多公司的数据增长是以数年而非数月或数日计算的。如果你的数据分析师不懂应从中提取什么出来，就将几乎无用。为你的业务使用大数据技术是一个非常有吸引力的事情，现在Apache Hadoop使得它更加吸引人了。 Hadoop是一个大规模可伸缩的数据存储平台，被用作许多大数据项目的基础...

liukai90 2019-04-25 17:11 评论0 收藏0
盘点程序员的十个调试技巧

转自：https://www.pinlue.com/article/2020/03/2120/1110041569027.html

zebrayoung 2021-09-10 10:51 评论0 收藏0
前端每周清单第 47 期：NPM 年度报告与 2018 展望，Airbnb React Router

摘要：确定新的包命名规则为了尽可能避免包的误植域名现象，将不会再允许使用相似的包命名不过会进一步鼓励开发者使用自己的命名空间来发布包。本文是对其几十年来技术之路的回顾与展望，也是一代技术人的青春回忆。 showImg(https://segmentfault.com/img/remote/1460000012846628); 前端每周清单专注前端领域内容，以对外文资料的搜集为主，帮助开发者了...

makeFoxPlay 2019-08-22 13:50 评论0 收藏0
2016:我的编程知识体系结构:Web/ServerSideApplication/MachineL

摘要：我的编程知识体系结构从属于笔者的。延伸阅读服务端应用程序入门与实践入门与工程实践基于对资源配置动态生成并进行权限审查的平台数据科学与机器学习笔者只是菜鸟一枚，本部分只是笔者暂时的总结，更多信息参阅数据科学与机器学习思维脑图。 2016:我的编程知识体系结构:Web/ServerSideApplication/MachineLearning从属于笔者的Coder Knowledge Gr...

JaysonWang 2019-06-26 15:35 评论0 收藏0