Postgres作为一款开源的对象—关系数据库，一直得到许多开发者喜爱。最近，Postgres正式发布了9.5版本，该版本进行了大量的修复与功能改进。本文将分享10个Postgres使用技巧，希望能让开发者能更加灵活与高效地使用这个数据库。

　　下面是译文：

　　很多人会选择去阅读一些新书或者一些新技术来充实自己。下面将推荐一些Postgres技巧与技能给大家，这些技巧能够帮助你更加灵活方便地使用Postgres。若你觉得这些技巧会对你产生帮助，你可以选择订阅 Postgres weekly，本周都回发布一些Postgres最新的资讯与技术干货。

　　一.CTEs——Common Table Expressions

　　CTE允许你做一些很棒的事情，如递归查询，即便是用在一些最简单的语句操作上，CET都会有很出色的表现。CTE可以认为是在单个SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。CTE 与派生表类似，具体表现在不存储为对象，且只在查询期间有效。CTE与派生表的不同之处在于，它可以自引用，还可以在同一查询中引用多次。这样开发者就可以更容易地创建可读查询。

　　开发者在创建SQL语句的时候，很多时候会有很多行，有的时候甚至超过上百行，而通过使用4-5个CETs后，语句会缩短很多，这样就很容易提高语句的可读性，尤其是对于新人来说。

　　二.安装一个.psqlrc

　　若安装了bashrc、vimrc等文件，那为什么不对Postgres做些同样的操作呢?下面这些设置都非常棒，你不妨试试，如一下步骤：

　　1.通过默认\x auto来获得更好的格式;

　　2.使用\pset null ¤，让null更形象化;

　　3.默认设置\timing on来显示SQL执行时间;

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

　　5.根据名称来保存你常用的运行语句。

　　下面是笔者的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

　　三. 通过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性能方面的东西。

　　四.ETL有点慢，用FDWs

　　若有大量的微服务或不同的应用程序，那么很可能需要很多不同的数据库来支持它们。默认情况是创建一些数据仓库并通过ETL连接，但是这样做有时候太重度了。在这个时候，你只需将数据库一次性集中在一起，或者在少数情况下，外部数据封装器可以允许你跨多个数据库查询，比如Postgres到Postgres，或者是Postgres 到Mongo或Redis数据库之类。

　　五. 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

　　六.慎重使用materialized views

　　你可能不熟悉materialized views(物化视图)，materialized views是包括一个查询结果的数据库对像。因此，它是一些查询或“view”的一个物化的或基本的快照版本。在最开始的物化版本中，会在Postgres建立一个常请求，但是整体是不可用的。那是因为当你锁定事务的时候，有可能会阻碍一些其它读取和活动。

　　现在已经好很多，但是仍然缺乏一些开箱即用的工具来进行刷新。这也就意味着你必须安装一些调度任务或者cron作业来定期刷新物化视图。若你目前正在开发一些报告或者BI应用程序，那么你还是需要使用物化视图的。它们的可用性正在不断提升，所以，Postgres已经知道如何自动化刷新它们。

　　七.窗口函数

　　可能窗口函数(Windows fuction)仍然是SQL中较复杂且很难理解的东西。总而言之，它们会让你排序一个查询结果，然后进行一行到玲一行的计算，若没有SQL PL，这些东西会很难做。不过，你可以做一些非常简单的操作，比如排名，基于某些值对结果进行排序;复杂些的，比如计算环比增长数据。

　　八.针对数据透视表的一个更简单方法

　　在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的博客查看具体示例。

　　九.PostGIS

　　PostGIS可以说是所有GIS数据库中最好的一个了。实际上，开发者获得的所有Postgres标准会使它更加强大——一个最好的例子是来自Postgres近年来的GiST索引，它给PostGIS提供了极大的性能提升。 若你现在正在做一些与地理空间数据有关的事情，并且需要一些比earth_distance扩展更好用的工具，那么PostGIS就是你最佳选择。

　　十.JSONB

　　从Postgres 9.2开始，Postgres的每个版本中都有JSON的身影，在每个新版本功能都有所提升，并正在逐步完善成一个更加完美的库。在最新发布的9.5版本中，JSONB在psql中的输出也更具可读性。

　　原文地址：http://www.craigkerstiens.com/2015/12/29/my-postgres-top-10-for-2016/

　　以后会恒信关于mssql的相关信息，关注mssql的朋友敬请期待。