摘要：近日，正式發(fā)布了版本，該版本進行了大量的修復和功能改進。事實上，開發(fā)者獲得的所有標準會使它更加強大一個最好的例子是來自近年來的索引，它給提供了極大的性能提升。在最新發(fā)布的版本中，在中的輸出也更具可讀性。

【編者按】作為一款開源的對象—關(guān)系數(shù)據(jù)庫，Postgres 一直得到許多開發(fā)者喜愛。近日，Postgres 正式發(fā)布了9.5版本，該版本進行了大量的修復和功能改進。而本文將分享10個 Postgres 使用技巧，旨在讓開發(fā)者能更加靈活和高效地使用這個數(shù)據(jù)庫。

放假期間，很多人會選擇去閱讀一些新書或者學一些新技術(shù)來充實自己。下面筆者將推薦一些Postgres技巧和技能給大家，這些技巧會幫助你更加靈活方便地使用 Postgres。如果你覺得這些技巧會對你產(chǎn)生幫助，你可以選擇訂閱 Postgres weekly，本周都會發(fā)布一些Postgres最新的資訊和技術(shù)干貨。

1.CTEs——Common Table Expressions

CTE 允許你做一些很棒的事情，比如遞歸查詢，即使是用在一些最簡單的語句操作上，CET 都會有很出色的表現(xiàn)。 CTE 可以認為是在單個 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 語句的執(zhí)行范圍內(nèi)定義的臨時結(jié)果集。CTE 與派生表類似，具體表現(xiàn)在不存儲為對象，并且只在查詢期間有效。與派生表的不同之處在于，CTE 可自引用，還可在同一查詢中引用多次。這樣開發(fā)者就可以更容易地創(chuàng)建可讀查詢。

開發(fā)者在創(chuàng)建 SQL 語句的時候，往往會有很多行，有的甚至超過上百行，而通過使用4-5個 CETs 后，語句會縮短很多，這樣就很容易提高語句的可讀性，尤其是對于新人來說。

2.安裝一個.psqlrc

如果安裝了 bashrc、vimrc 等文件，那為什么不對 Postgres 做些同樣的操作呢？下面這些設(shè)置都非常棒，你不妨試試：

通過默認來獲得更好的格式；

使用pset null ¤，讓 null 更形象化；

默認設(shè)置 iming on來顯示 SQL 執(zhí)行時間；

自定義提示set PROMPT1"%[3[33;1m%]%x%[3[0m%]%[3[1m%]%/%[3[0m%]%R%# "；

根據(jù)名稱來保存你常用的運行語句。

下面是筆者的 psqlrc 設(shè)置：

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 可能是開發(fā)者提高數(shù)據(jù)庫性能最有價值的工具。一旦啟用（還有extension pg_stat_statements），它便會自動記錄數(shù)據(jù)庫的所有查詢記錄以及它們所花費的時間。這樣，你就很容易優(yōu)化查詢語句，提高性能。

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;

當然，會因此付出一些性能代價，但對比其所帶來的性能提升簡直微乎其微。在這篇文章里可以讀到更多關(guān)于 Postgres 性能方面的東西。

如果有大量的微服務或不同的應用程序，那么可能需要很多不同的數(shù)據(jù)庫來支持它們。默認情況是創(chuàng)建一些數(shù)據(jù)倉庫并通過 ETL 連接，但是這樣做有時候太重度了。這時候，你只需要將數(shù)據(jù)庫一次性集中在一起，或者在少數(shù)情況下，外部數(shù)據(jù)封裝器可以允許你跨多個數(shù)據(jù)庫查詢，比如 Postgres 到Postgres，或者是 Postgres 到 Mongo 或 Redis 數(shù)據(jù)庫之類。

在開發(fā)應用程序時，很少會完全不用 arrays，而在數(shù)據(jù)庫中同樣如此。Arrays 可以看作是 Postgres 里的另一個數(shù)據(jù)類型，并擁有一些殺手級應用，比如博文標簽這些。

但是，即使你不把 arrays 當做數(shù)據(jù)類型使用，也常常需要像一個 array 那樣匯總一些數(shù)據(jù)，中間用逗號隔開。類似下面這樣，你可以輕松匯總用戶清單：

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 是包括一個查詢結(jié)果的數(shù)據(jù)庫對像。所以，它是一些查詢或「view」的一個物化的或基本的快照版本。在最開始的物化版本中，會在 Postgres 建立一個常請求，但整體是不可用的。那是因為當你鎖定事務的時候，有可能會阻礙一些其它讀取和活動。

現(xiàn)在已經(jīng)好很多，但仍然缺乏一些開箱即用的工具來進行刷新。這也就意味著你必須安裝一些調(diào)度任務或 cron 作業(yè)來定期刷新物化視圖。如果你目前正在開發(fā)一些報告或者 BI 應用程序，那么你還是需要使用物化視圖的。它們的可用性正在不斷提升，所以，Postgres 已經(jīng)知道如何自動化刷新它們。

窗口函數(shù)（Windows fuction）可能仍然是 SQL 中較復雜且很難理解的東西。總之，它們會讓你排序一個查詢結(jié)果，然后進行一行到玲一行的計算，如果沒有 SQL PL，這些東西會很難做。不過，你可以做一些非常簡單的操作，比如排名，基于某些值對結(jié)果進行排序；復雜些的，比如計算環(huán)比增長數(shù)據(jù)。

8.針對數(shù)據(jù)透視表的一個更簡單方法

在 Postgres 中，Table_func 通常是作為計算一個數(shù)據(jù)透視表的引用方式。不幸地是，這個使用起來相當困難的，更為基礎(chǔ)的用法是與原始 SQL 一起使用。在 Postgres 9.5 中已經(jīng)進行了改進，用起來會方便很多。但在此之前，你匯總每個條件的結(jié)果不是 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 可以說是所有 GIS 數(shù)據(jù)庫中最好的一個了。事實上，開發(fā)者獲得的所有 Postgres 標準會使它更加強大——一個最好的例子是來自 Postgres 近年來的 GiST 索引，它給 PostGIS 提供了極大的性能提升。 如果你現(xiàn)在正在做一些與地理空間數(shù)據(jù)有關(guān)的事情，并且需要一些比 earth_distance 擴展更好用的工具，那么 PostGIS 就是你最佳選擇。

從 Postgres 9.2 開始，Postgres 的每個版本中都有 JSON 的身影，在每個新版本功能都有所提升，并且正在逐步完善成一個更加完美的庫。在最新發(fā)布的9.5版本中，JSONB在psql中的輸出也更具可讀性。

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

本文系國內(nèi) ITOM 行業(yè)領(lǐng)軍企業(yè) OneAPM 工程師編譯整理。我們致力于幫助企業(yè)用戶提供全棧式的性能管理以及IT運維管理服務，通過一個探針就能夠完成日志分析、安全防護、APM 基礎(chǔ)組件監(jiān)控、集成報警以及大數(shù)據(jù)分析等功能。想閱讀更多技術(shù)文章，請訪問 OneAPM 官方技術(shù)博客

本文轉(zhuǎn)自 OneAPM 官方博客