【編者按】作為一款開源的對(duì)象—關(guān)系數(shù)據(jù)庫(kù)，Postgres 一直得到許多開發(fā)者喜愛照雁。近日阱持，Postgres 正式發(fā)布了9.5版本，該版本進(jìn)行了大量的修復(fù)和功能改進(jìn)规阀。而本文將分享10個(gè) Postgres 使用技巧恒序，旨在讓開發(fā)者能更加靈活和高效地使用這個(gè)數(shù)據(jù)庫(kù)。
放假期間谁撼，很多人會(huì)選擇去閱讀一些新書或者學(xué)一些新技術(shù)來充實(shí)自己歧胁。下面筆者將推薦一些Postgres技巧和技能給大家，這些技巧會(huì)幫助你更加靈活方便地使用 Postgres厉碟。如果你覺得這些技巧會(huì)對(duì)你產(chǎn)生幫助喊巍，你可以選擇訂閱 Postgres weekly，本周都會(huì)發(fā)布一些Postgres最新的資訊和技術(shù)干貨墨榄。
1.CTEs——Common Table Expressions
CTE 允許你做一些很棒的事情玄糟，比如遞歸查詢勿她，即使是用在一些最簡(jiǎn)單的語(yǔ)句操作上袄秩，CET 都會(huì)有很出色的表現(xiàn)。 CTE 可以認(rèn)為是在單個(gè) SELECT、INSERT之剧、UPDATE郭卫、DELETE 或 CREATE VIEW 語(yǔ)句的執(zhí)行范圍內(nèi)定義的臨時(shí)結(jié)果集。CTE 與派生表類似背稼，具體表現(xiàn)在不存儲(chǔ)為對(duì)象贰军，并且只在查詢期間有效。與派生表的不同之處在于蟹肘，CTE 可自引用词疼，還可在同一查詢中引用多次。這樣開發(fā)者就可以更容易地創(chuàng)建可讀查詢帘腹。
開發(fā)者在創(chuàng)建 SQL 語(yǔ)句的時(shí)候贰盗，往往會(huì)有很多行，有的甚至超過上百行阳欲，而通過使用4-5個(gè) CETs 后舵盈，語(yǔ)句會(huì)縮短很多，這樣就很容易提高語(yǔ)句的可讀性球化，尤其是對(duì)于新人來說秽晚。
2.安裝一個(gè).psqlrc
如果安裝了 bashrc、vimrc 等文件筒愚，那為什么不對(duì) Postgres 做些同樣的操作呢赴蝇？下面這些設(shè)置都非常棒，你不妨試試：
通過默認(rèn)來獲得更好的格式锨能；
使用\pset null ¤扯再，讓 null 更形象化；
默認(rèn)設(shè)置\timing on來顯示 SQL 執(zhí)行時(shí)間址遇；
自定義提示\set PROMPT1'%[3[33;1m%]%x%[3[0m%]%[3[1m%]%/%[3[0m%]%R%# '熄阻；
根據(jù)名稱來保存你常用的運(yùn)行語(yǔ)句。
下面是筆者的 psqlrc 設(shè)置：
\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 來查看需要進(jìn)行索引的地方
pg_stat_statements 可能是開發(fā)者提高數(shù)據(jù)庫(kù)性能最有價(jià)值的工具倔约。一旦啟用（還有extension pg_stat_statements）秃殉，它便會(huì)自動(dòng)記錄數(shù)據(jù)庫(kù)的所有查詢記錄以及它們所花費(fèi)的時(shí)間。這樣浸剩，你就很容易優(yōu)化查詢語(yǔ)句钾军，提高性能。
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;
當(dāng)然绢要，會(huì)因此付出一些性能代價(jià)吏恭，但對(duì)比其所帶來的性能提升簡(jiǎn)直微乎其微。在這篇文章里可以讀到更多關(guān)于 Postgres 性能方面的東西重罪。
4. ETL 有點(diǎn)慢樱哼，用 FDWs
如果有大量的微服務(wù)或不同的應(yīng)用程序哀九，那么可能需要很多不同的數(shù)據(jù)庫(kù)來支持它們。默認(rèn)情況是創(chuàng)建一些數(shù)據(jù)倉(cāng)庫(kù)并通過 ETL 連接搅幅，但是這樣做有時(shí)候太重度了阅束。這時(shí)候，你只需要將數(shù)據(jù)庫(kù)一次性集中在一起茄唐，或者在少數(shù)情況下息裸，外部數(shù)據(jù)封裝器可以允許你跨多個(gè)數(shù)據(jù)庫(kù)查詢，比如 Postgres 到Postgres沪编，或者是 Postgres 到 Mongo 或 Redis 數(shù)據(jù)庫(kù)之類呼盆。
5. array和array_agg
在開發(fā)應(yīng)用程序時(shí)，很少會(huì)完全不用 arrays蚁廓，而在數(shù)據(jù)庫(kù)中同樣如此宿亡。Arrays 可以看作是 Postgres 里的另一個(gè)數(shù)據(jù)類型，并擁有一些殺手級(jí)應(yīng)用纳令，比如博文標(biāo)簽這些挽荠。
但是，即使你不把 arrays 當(dāng)做數(shù)據(jù)類型使用平绩，也常常需要像一個(gè) array 那樣匯總一些數(shù)據(jù)圈匆，中間用逗號(hào)隔開。類似下面這樣捏雌，你可以輕松匯總用戶清單：
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 是包括一個(gè)查詢結(jié)果的數(shù)據(jù)庫(kù)對(duì)像。所以性湿，它是一些查詢或「view」的一個(gè)物化的或基本的快照版本纬傲。在最開始的物化版本中，會(huì)在 Postgres 建立一個(gè)常請(qǐng)求肤频，但整體是不可用的叹括。那是因?yàn)楫?dāng)你鎖定事務(wù)的時(shí)候，有可能會(huì)阻礙一些其它讀取和活動(dòng)宵荒。
現(xiàn)在已經(jīng)好很多汁雷，但仍然缺乏一些開箱即用的工具來進(jìn)行刷新。這也就意味著你必須安裝一些調(diào)度任務(wù)或 cron 作業(yè)來定期刷新物化視圖报咳。如果你目前正在開發(fā)一些報(bào)告或者 BI 應(yīng)用程序侠讯，那么你還是需要使用物化視圖的。它們的可用性正在不斷提升暑刃，所以厢漩，Postgres 已經(jīng)知道如何自動(dòng)化刷新它們。
7.窗口函數(shù)
窗口函數(shù)（Windows fuction）可能仍然是 SQL 中較復(fù)雜且很難理解的東西岩臣×锸龋總之柴底，它們會(huì)讓你排序一個(gè)查詢結(jié)果，然后進(jìn)行一行到玲一行的計(jì)算粱胜，如果沒有 SQL PL，這些東西會(huì)很難做狐树。不過焙压，你可以做一些非常簡(jiǎn)單的操作，比如排名抑钟，基于某些值對(duì)結(jié)果進(jìn)行排序涯曲；復(fù)雜些的，比如計(jì)算環(huán)比增長(zhǎng)數(shù)據(jù)在塔。
8.針對(duì)數(shù)據(jù)透視表的一個(gè)更簡(jiǎn)單方法
在 Postgres 中幻件，Table_func 通常是作為計(jì)算一個(gè)數(shù)據(jù)透視表的引用方式。不幸地是蛔溃，這個(gè)使用起來相當(dāng)困難的绰沥，更為基礎(chǔ)的用法是與原始 SQL 一起使用。在 Postgres 9.5 中已經(jīng)進(jìn)行了改進(jìn)贺待，用起來會(huì)方便很多徽曲。但在此之前，你匯總每個(gè)條件的結(jié)果不是 false 就是 true 麸塞，最后合計(jì)為更簡(jiǎn)單的推理：
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 數(shù)據(jù)庫(kù)中最好的一個(gè)了。事實(shí)上哪工，開發(fā)者獲得的所有 Postgres 標(biāo)準(zhǔn)會(huì)使它更加強(qiáng)大——一個(gè)最好的例子是來自 Postgres 近年來的 GiST 索引奥此，它給 PostGIS 提供了極大的性能提升。 如果你現(xiàn)在正在做一些與地理空間數(shù)據(jù)有關(guān)的事情雁比，并且需要一些比 earth_distance 擴(kuò)展更好用的工具稚虎，那么 PostGIS 就是你最佳選擇。
10.JSONB
從 Postgres 9.2 開始偎捎，Postgres 的每個(gè)版本中都有 JSON 的身影祥绞，在每個(gè)新版本功能都有所提升，并且正在逐步完善成一個(gè)更加完美的庫(kù)鸭限。在最新發(fā)布的9.5版本中蜕径，JSONB在psql中的輸出也更具可讀性。
原文地址：http://www.craigkerstiens.com/2015/12/29/my-postgres-top-10-for-2016/
本文系國(guó)內(nèi) ITOM 行業(yè)領(lǐng)軍企業(yè) OneAPM 工程師編譯整理败京。我們致力于幫助企業(yè)用戶提供全棧式的性能管理以及IT運(yùn)維管理服務(wù)兜喻，通過一個(gè)探針就能夠完成日志分析、安全防護(hù)赡麦、APM 基礎(chǔ)組件監(jiān)控朴皆、集成報(bào)警以及大數(shù)據(jù)分析等功能帕识。想閱讀更多技術(shù)文章，請(qǐng)?jiān)L問 OneAPM 官方技術(shù)博客
本文轉(zhuǎn)自 OneAPM 官方博客
