2016，你要掌握的十個Postgres技巧~
作為一款開源的對象—關系數據庫，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做些同樣的操作呢？下面這些設置都非常棒，你不妨試試：
通過默認\x auto來獲得更好的格式；
使用\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中的輸出也更具可讀性。