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 做些同樣的操作呢?下面這些設定都非常棒,你不妨試試:
通過預設來獲得更好的格式;
使用\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中的輸出也更具可讀性。
原文地址:http://www.craigkerstiens.com/2015/12/29/my-postgres-top-10-for-2016/
本文系國內 ITOM 行業領軍企業 OneAPM 工程師編譯整理。我們致力於幫助企業使用者提供全棧式的效能管理以及IT運維管理服務,通過一個探針就能夠完成日誌分析、安全防護、APM 基礎元件監控、整合報警以及大資料分析等功能。想閱讀更多技術文章,請訪問 OneAPM 官方技術部落格
本文轉自 OneAPM 官方部落格
相關文章
- Web開發者應掌握的12個Firebug技巧Web
- 學習 MySQL 應該掌握的 6 個技巧MySql
- 好的web開發者應該10個技巧Web
- 開發者應該掌握的3個發行步驟
- 每個MySQL開發者都應該瞭解的10個技巧MySql
- 程式設計師應該掌握的10個搜尋技巧程式設計師
- 重要!每個開發者都應該掌握的9個核心演算法演算法
- Android 開發應該掌握的 Proguard 技巧Android
- Android開發應該掌握的Proguard技巧Android
- 【譯】2019年JavaScript開發者應該都在用的9個棒的技巧JavaScript
- 前端開發者應該知道的 CSS 小技巧前端CSS
- 每個C++開發者都應該使用的十個C++11特性C++
- 一個自信的前端應該掌握的CDN操作前端
- ORACLE DBA應該掌握的9個免費工具Oracle
- 每個PHP開發者都應該看的書PHP
- 開發者應該避免使用的6個Java功能Java
- [譯]每個 iOS 開發者都該知道的 17 個 Xcode 小技巧iOSXCode
- 每個開發者都應該知道的33個JavaScript概念JavaScript
- 給Android應用開發者的十個建議Android
- 聘請移動應用開發者的10個技巧
- 每個 Java 開發者都應該知道的 5 個註解Java
- 每個Java開發者都應該知道的5個JDK工具JavaJDK
- Mac小白應該要知道的幾個小技巧Mac
- 每個極客都應該知道的Linux技巧Linux
- 前端應該掌握的nginx知識前端Nginx
- 作為一名Java開發者應該掌握的基礎知識彙總!Java
- WWDC 2017:高階開發應該掌握的自動佈局技巧
- WEB開發者應該反問自己的10個問題Web
- 每個 Java 開發者應該知道(並愛上)的 8 個工具Java
- 每個Android開發者應該知道的6個SDK和APIAndroidAPI
- 你應該瞭解的 5 個 JavaScript 除錯技巧JavaScript除錯
- 設計出色響應式網站的十個技巧網站
- [譯] 優秀 JavaScript 開發人員應掌握的 9 個技巧JavaScript
- 你應該知道的jQuery技巧jQuery
- 【Camera專題】Qcom-你應該掌握的Camera除錯技巧一除錯
- 新手應該掌握的Linux命令(轉)Linux
- 2016年你應該學習的語言和框架框架
- 2016年應該使用SQLite的5大原因SQLite