# 常見問題

整理一些比較零碎的 PostgreSQL 資訊，可以快問快答的資訊。

想問什麼或提供資訊，請到[聊天室](https://gitter.im/pgsql-tw/Lobby)和我們討論！

常見問題另可參閱官方 [PostgreSQL wiki](https://wiki.postgresql.org) 網站，其中繁體中文頁面亦由台灣使用者社群所提供。

其他問題請聯絡文件編輯小組：<docs@postgresql.tw>

![](https://img.shields.io/badge/正體中文使用手冊-PostgreSQL.TW-blue.svg)

![](https://img.shields.io/badge/台灣使用者社群-PostgreSQL-blue.svg)


# Windows 中的 pgAdmin4 字太小怎麼辦？

由於新版的 pgAdmin 4 是以 QT5 開發，所以如果你是在高 DPI 的環境（如 4k 螢幕）下使用 pgAdmin 4 時，字母顯示可能會太小。

因為 QT5 處理 DPI 的方式與 Windows [不太一樣](http://doc.qt.io/qt-5/highdpi.html)，所以你需要做的是讓 pgAdmin 4 不要自動調整字型大小。

而簡單來說，你有兩種作法：（路徑目錄可能需要隨你的安裝環境調整）

## 一、在啓動指令加入參數：

```
cd "c:\Program Files\PostgreSQL\9.6\pgAdmin 4\bin"
.\pgAdmin4.exe -platform windows:dpiawareness=0
```

或

在捷徑按「內容」，修改「目標」，在後面加入：

```
-platform windows:dpiawareness=0
```

## 二、設定 qt.conf：

增加一個 qt.conf 設定檔給 pgAdmin 4 使用。

請使用**系統管理者權限**開啓 cmd

```
cd "c:\Program Files\PostgreSQL\9.6\pgAdmin 4\bin"
notepad qt.conf
```

內容加入：

```
[Platforms]
WindowsArguments = dpiawareness=0
```

再重啓你的 pgAdmin 4 即可。


# JDBC查詢資料時出現「Out of Memory」？

> ## 注意：
>
> 其他程式語言或資料庫也可能會有這個問題，請查閱相對應的驅動程式手冊。

簡單來說：

JDBC 在查詢資料時，預設會取回所有的資料，所以如果你的資料量過大，且AP端的資源不足時，就可能會產生這個錯誤。許多人以為 next() 就是在使用 cursor，其實不然。

詳細說明請參閱 [PostgreSQL JDBC 使用手冊第 5 章](https://jdbc.postgresql.org/documentation/head/query.html)的內容。以下為該章的編譯內容：

任何時候你想要將 SQL 查詢語句送到資料庫，你都需要一個 Statement 或 PreparedStatement。一旦你有一個 Statement 或 PreparedStatement 了，你可以就可以開始查詢。這將回傳一個 ResultSet 的實例，該實例包含全部的資料結果（請參閱「[以游標（cursor）取得結果](/jdbc-cha-xun-zi-liao-shi-chu-xian-out-of-memory#cursor)」一節以了解如何變更此行為）。 範例 1，「[在 JDBC 中進行一個簡單的查詢](/jdbc-cha-xun-zi-liao-shi-chu-xian-out-of-memory#ex1)」說明了這個過程。

### 範例 1. 在 JDBC 中進行一個簡單的查詢 <a href="#ex1" id="ex1"></a>

這個範例將送出一個簡單的查詢並使用一個 Statement 來輸出每一個資料列的第一個欄位：

```
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM mytable WHERE columnfoo = 500");
while(rs.next()){
    System.out.print("Column 1 returned ");
    System.out.println(rs.getString(1));
}
rs.close();
st.close();
```

此範例與之前一樣送出相同的查詢，但在查詢中使用 PreparedStatement 和設定值：

```
int foovalue = 500;
PreparedStatement st = conn.prepareStatement("SELECT * FROM mytable WHERE columnfoo = ?");
st.setInt(1,foovalue);
ResultSet rs = st.executeQuery();
while(rs.next()) {
    System.out.print("Column 1 returned ");
    System.out.println(rs.getString(1));
}
rs.close();
st.close();
```

## 以游標（cursor）取得結果 <a href="#cursor" id="cursor"></a>

預設的情況下，驅動程式會一次取回查詢的所有結果。這對於大數據來說可能會不方便，因此 JDBC 驅動程式提供了將 ResultSet 放在資料庫游標上的方法，只提取少量的資料列。

少量的資料列會被暫存在連線的用戶端，當資料取完時，再透過重新定位游標來檢索下一群的資料列。

## 注意

> 以游標的 ResultSet 查詢無法在所有情況下使用。有很多限制會讓驅動程式暗自地回到一次取得整個 ResultSet。

* 與伺服器的連線必須使用 V3 協定。這是伺服器 7.4 版或更高版本的設定（並且僅接受 V3）。
* 連線不得處於自動提交（autocommit）模式。後端在交易事務結束時才關閉游標，而在自動提交模式下，後端將在關閉交易之前關閉游標。 \* Statement 必須用 ResultSet型別的 ResultSet.TYPE\_FORWARD\_ONLY。這是預設值，因此不需要重寫程式就能使用，但這也意味著你無法倒退或以其他方式在 ResultSet 中跳轉。
* 送出的查詢必須是單一個查詢語句，而不是以多個分號串在一起的查詢語句。

### 範例 2. 以設定暫存大小來開關游標的使用

將程式更改為游標模式非常簡單，只需將語句的暫存大小設定為適當值即可。將暫存大小設定回 0，就會導致所有資料列被暫存（預設行為）。

```
// make sure autocommit is off
conn.setAutoCommit(false);
Statement st = conn.createStatement();
// Turn use of the cursor on.
st.setFetchSize(50);
ResultSet rs = st.executeQuery("SELECT * FROM mytable");
while(rs.next()) {
    System.out.print("a row was returned.");
}
rs.close();
// Turn the cursor off.
st.setFetchSize(0);
rs = st.executeQuery("SELECT * FROM mytable");
while(rs.next()) {
    System.out.print("many rows were returned.");
}
rs.close();
// Close the statement.
st.close();
```


# 我在哪裡可以獲得支援？

PostgreSQL社群透過電子郵件提供許多用戶的幫助。訂閱電子郵件列表的主要網站是<http://www.postgresql.org/community/lists/>。

一般說明或錯誤列表是一個作為開始的好地方。為了能更詳細地瞭解內容，請考慮閱讀 [guide to reporting problems](https://wiki.postgresql.org/wiki/Guide_to_reporting_problems)，在發佈之前確認你包括足夠的訊息，給予幫助您的人。

主要的IRC頻道是Freenode上的#postgresql (irc.freenode.net)。 台灣社群的討論社群在 Facebook Group 上，申請加入後即可參與討論。

還有一些提供支援服務的公司名單可在 <http://www.postgresql.org/support/professional_support>中找到。


# 最新版本是什麼？

最新版本的 PostgreSQL 會顯示在[官方網站](http://www.postgresql.org/)的最上端。

通常我們每年都會有一個主要版本，然後每隔幾個月發布一次次要版本。次要版本通常是針對所有支援的主要發布分支同時進行的。有關主要與次要版本的更多訊息，請參閱 <http://www.postgresql.org/support/versioning>。


# PostgreSQL 資料庫可以有多大？

| **限制**              | **範圍**                                 |
| ------------------- | -------------------------------------- |
| 資料庫（Database）大小限制   | 無限制                                    |
| 最大資料表（Table）（註）     | 32 TB (9.6及之前版本)、2 EB (10)、131 YB (11) |
| 最大資料列（Row）          | 1.6 TB                                 |
| 最大欄位（Field）內容       | 1 GB                                   |
| 資料表中最多可以有幾個資料列      | 無限制                                    |
| 資料表中最多可以有幾個欄位       | 250 - 1600 （與欄位型別有關）                   |
| 資料表最多可以有幾個索引（Index） | 無限制                                    |

* 最大資料表後來修正了宣告問題，所以在 10 之後的版本就不再受限於 32 TB 了。
* 額外再加上分割資料表（Partition Table）的支援，可以再進一步單一資料表的極限。
* 大型資料表只代表可以「儲存」的資料量，不一定是可以「運算」的資料量，仍然要保持資料表在適當的大小。


# 忘記密碼怎麼辦？

**解決的辦法就是使用密碼以外的方式登入再給一個新的密碼。先決條件是你擁有該系統的管理者權限。**

找到你的`pg_hba.conf`，可能會在`/etc/postgresql-9.1/pg_hba.conf`，像這樣的路徑下。

1. `cd /etc/postgresql-9.1/`
2. 先備份一下

   `cp pg_hba.conf pg_hba.conf-backup`
3. 把下面這行放進檔案中（可以把其他行都註解掉）：

   `local all all trust`
4. 重新啓動你的 PostgreSQL （以 Linux 為例）

   `sudo /etc/init.d/postgresql restart`

   如果服務無法順利啓動，且在 log 裡出現下面這個訊息的話

   > local connections are not supported by this build

   那就把這行

   `local all all trust`

   改成這行

   `host all all 127.0.0.1/32 trust`
5. 你現在可以以任何使用者登入了。那就以超級使用者 postgres 登入吧（如果沒有被特別改過的話，應該是 postgres，有些系統會是 pgsql）

   `psql -U postgres`

   或

   `psql -h 127.0.0.1 -U postgres`

   （前面那行可能不會被認為是從 localhost 登入，所以要特別指出。）
6. 重置你的密碼

   `ALTER USER my_user_name with password 'my_secure_password';`
7. 記得回存你的 pg\_hba.conf，不然你的系統還是處在風險之中

   `cp pg_hba.conf-backup pg_hba.conf`
8. 再一次重啓系統，確認原來你的 pg\_hba.conf 設定也有生效

   `sudo /etc/init.d/postgresql restart`
9. 用新的密碼登入，並且記好你的密碼


# 什麼是 PostgreSQL？ 怎麼發音？Postgres 又是什麼？

PostgreSQL 發音為 Post-Gres-Q-L。 （好奇如何說「PostgreSQL」，請聽：[MP3語音檔](http://www.postgresql.org/files/postgresql.mp3)。）

PostgreSQL 是一個物件導向關連式資料庫系統，具有傳統專業資料庫系統的特性，也包含在先進 DBMS 系統中的強化功能。PostgreSQL 是免費的，而且是完整開放原始碼的。

PostgreSQL 的開發大多是由遍布全球的志願者開發團隊進行的，並通過網際網路進行討論。這是一個社群專案，不受任何公司控制。想要參與的話，請參閱開發常見問題[開發常見問題](https://wiki.postgresql.org/wiki/Developer_FAQ).

Postgres 是 PostgreSQL 廣泛使用的暱稱。這是伯克萊專案的原始名稱，比其他暱稱更受歡迎。如果你發現「PostgreSQL」難以發音，請試試看「Postgres」。


# 誰控制著 PostgreSQL？

如果你正在尋找一個 PostgreSQL 的守衛，中央委員會或控股公司，請放棄吧 --- 完全不存在。

我們確實有一個核心委員會和 git 提交者，但是這些組織更多是為了管理目的而不是控制。

這個專案由開發者和使用者社群所引導，並且任何人都可以加入社群。你只需訂閱郵件列表並參與討論即可。

（請參閱[開發者常見問題](https://wiki.postgresql.org/wiki/Developer_FAQ)，可以得到如何加入 PostgreSQL 開發的資訊。）


# PostgreSQL全球開發組織是什麼？

「PGDG」是一個國際性的非公司組織的個人和公司的協會，他們為 PostgreSQL 專案做出貢獻。

PostgreSQL 核心團隊通常擔任 PGDG 的發言人。


# PostgreSQL核心團隊是什麼角色？

一個由五到七名（現在是六名）PostgreSQL 有經驗的貢獻者組成的委員會，他們為這個專案進行以下工作：

1. 決定發表日期。
2. 處理專案的機密事項。
3. 在需要時擔任 PGDG 的發言人。
4. 仲裁未能協商一致的社區決策。

目前核心團隊列的成員列在[貢獻者頁面](http://www.postgresql.org/community/contributors/)的最上面。


# 還有其他 PostgreSQL 的機構嗎？

目前 PostgreSQL 專案以非營利組織的方式，在美國、歐洲、巴西和日本進行籌款和專案協調，但這些組織並不擁有 PostgreSQL 原始碼的所有權。


# 什麼是 PostgreSQL 的使用許可（License）呢？

PostgreSQL 發行版的使用許可與 BSD 和 MIT 類似。 基本上，它允許使用者以原始碼來進行任何他們想要的事情，包括轉售沒有原始碼的編譯程式。 唯一的限制是你無法請求我們對軟體問題的法律責任。 並且這個要求，適用於所有的衍生的軟體。 這是我們的使用許可：(以下謹附上原文)

```
PostgreSQL Database Management System
(formerly known as Postgres, then as Postgres95)

Portions Copyright (c) 1996-2011, PostgreSQL Global Development Group

Portions Copyright (c) 1994, The Regents of the University of California

Permission to use, copy, modify, and distribute this software and its
documentation for any purpose, without fee, and without a written agreement
is hereby granted, provided that the above copyright notice and this
paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR
DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGE.

THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,
INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
AND FITNESS FOR A PARTICULAR PURPOSE.  THE SOFTWARE PROVIDED HEREUNDER IS
ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO
PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
```


# PostgreSQL vs MySQL

在考慮使用哪個資料庫時，不要認為所有的開源資料庫系統都是一樣的！

![](/files/-L9QOJ7y5I8Uwc1R1EQa)

PostgreSQL 和 MySQL 之間有一些根本上的差別。 你只有在評估兩個系統之間的差異和取捨之後，才做出明智的決定。

我們提供了PostgreSQL和MySQL之間最常見的特性和能力的相似點和不同點的總結：

* [Open Source](/postgresql-vs-mysql#open-source)
* [Acid Compliance](/postgresql-vs-mysql#acid-compliance)
* [SQL Compliance](/postgresql-vs-mysql#sql-compliance)
* [Replication](/postgresql-vs-mysql#replication)
* [Performance](/postgresql-vs-mysql#performance)
* [Security](/postgresql-vs-mysql#security)
* [Cloud Hosting](/postgresql-vs-mysql#cloud-hosting)
* [Community Support](/postgresql-vs-mysql#community-support)
* [Concurrency Support](/postgresql-vs-mysql#concurrency-support)
* [NoSQL/JSON Support](/postgresql-vs-mysql#nosql-featuresjson-support)
* [Materialized Views & Temporary Tables](/postgresql-vs-mysql#materialized-viewstemporary-tables)
* [GeoSpatial Data Support](/postgresql-vs-mysql#geospatial-data-support)
* [Programming Languages Support](/postgresql-vs-mysql#programming-languages-support)
* [Extensible Type System](/postgresql-vs-mysql#extensible-type-system)
* [Comparison Summary](/postgresql-vs-mysql#comparison-summary)

雖然兩個資料庫之間有許多相似之處和重疊，但也有非常明顯的差異。 我們試圖為你提供兩者之間的公平和準確的比較，但最終仍然有必要評估你的獨特使用情境，再確定哪個資料庫最適合你的特定使用情況。

很顯然，我們是以 PostgreSQL 為主，但也有可能出現一些對使用者最有利的 MySQL 方案。

## **Open Source** <a href="#open-source" id="open-source"></a>

開源軟體具有一些獨特的優勢—成本，靈活性，自由，安全性和問責性—這是商有軟體解決方案所無法比擬的。開源軟體是免費的，而且可以被任何人重新散佈和修改。開源軟體具有長期的生存能力，始終處於技術的領導位置。它由一個由全球性組織和個人開發者組成的社區所建立和支持，他們中的許多人也靠開放原始碼的價值來生活，像是一些協作和志願服務。

| PostgreSQL                                                                                                                                                                      | MySQL                                                                          |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------ |
| PostgreSQL 由 PostgreSQL 全球開發小組（PostgreSQL Global Development Group）開發，PostgreSQL 全球開發組由多個公司和個人貢獻者所組成。它是免費的開源軟體。PostgreSQL 是在 PostgreSQL 使用許可發布的，這是一個自由的開源許可，類似於 BSD 或 MIT 使用許可。 | MySQL 開發專案根據 GNU GPL 的條款提供了它的原始碼以及各種各樣的專有協議。它現在由 Oracle 公司擁有，並且提供了幾個商業使用的付費版本。 |

## **ACID Compliance**

ACID（Atomicity，Consistency，Isolation，Durability）是一組資料庫交易安全的特性。 ACID 要求確保在單一的資料交易中發生多個更新時也不會在整個系統中遺失數據或發生錯誤。

| PostgreSQL                         | MySQL                                        |
| ---------------------------------- | -------------------------------------------- |
| PostgreSQL 完全符合 ACID 特性，並確保滿足所有要求。 | 只有在使用 InnoDB 和 NDB 叢集儲存引擎時，MySQL 才符合ACID 標準。 |

## **SQL Compliance**

SQL 相容性是資料庫必須滿足並實現所有結構化查詢語言準則和標準。 當公司想要為應用程式使用多種同類型資料庫時，這一點非常重要。

具有 SQL 相容性使得將資料從一個 SQL 相容的資料庫移動到另一個時（例如 Oracle 到 PostgreSQL 或 SQL Server）非常容易。

| PostgreSQL                                                                                                                                                                                                                               | MySQL                                                                                                                                          |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------- |
| PostgreSQL在很大程度上是SQL相容的。 使用手冊的附錄D清楚地列出了每個功能的相容性情形，PostgreSQL手冊的“Reference”章節清楚地記錄了任何差異。 從文件中摘錄：PostgreSQL支持SQL：2011的大部分主要功能。 在滿足核心一致性所需的179個必要功能中，PostgreSQL至少符合160個。此外，還有一大串支持的可選功能。 值得注意的是，在撰寫本文時，沒有任何資料庫管理系統的當時版本聲明完全符合Core SQL：2011。 | MySQL對 SQL 相容較少的（例如不支持 CHECK 約束）。 從官方文冊中摘錄：我們的產品主要目標之一是繼續努力遵守 SQL 標準，但不會犧牲速度或可靠性。 如果這大大增加了我們用戶群中的大部分MySQL服務器的可用性，我們並不害怕增加 SQL 延伸功能或支持非 SQL 功能。 |

## Replication

資料庫複製是從一台主機或伺服器上的資料庫到另一台資料庫中資料的頻繁電子複製，以便所有資料庫服務提供一致性的資訊。其結果是一個分散式資料庫，使用者可以在其中存取與其事務相關的資料，而不會干擾他人的作業。

| PostgreSQL                                                                                                                                                                                                                                                                       | MySQL                                                                                                                                                                                                                                                                       |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| PostgreSQL 支援 Master-Standby 複製，並引入了先進的強化功能，提供極其快速的 WAL 處理，為備用伺服器提供了近乎即時的複製和熱備援功能。PostgreSQL 提供的功能：單主機到一個備用單主機（Single master to one standby）／多個備用主機（multiple standby）、雙向複製（Bi-Directional replication）、邏輯日誌串流複製（Logical log streaming replication）、及多層次複製（Cascading replication） | MySQL 支持 Master-Standby 複製。MySQL提供的備份：單一主機到一個備用主機（Single master to one standby）／單主機到多個備用主機（Single master to multiple standbys）、單主機到一個或多個備用主機（Single master to one standby to one or more standbys）、循環式備援（Circular replication）（ A 到 B到 C 和回到 A）、主要主機對主要主機（Master to master） |

## Performance

效能是一種只能通過評估潛在情景的領域，因為它取決於特定用戶或應用的單純化需求。

| PostgreSQL                                                                                                                                                                                                                                                              | MySQL                                                                                                                                                                                                                                     |
| ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| PostgreSQL 被廣泛應用於讀寫速度至關重要且資料需要驗證的大型系統中。 此外，它在商業解決方案中，還支持各種效能優化，（如地理空間資料支持，不需要讀取鎖定的資料一致性支援，如 Oracle、SQL Server）。 整體來看，PostgreSQL 的效能在需要執行複雜查詢的系統中會得到了最好的展現。 PostgreSQL 在 OLTP / OLAP 系統中運行良好，尤其在需要讀/寫速度和需要大量的資料分析時。 PostgreSQL 也適用於商業智慧應用服務，但更適合需要快速讀/寫速度的資料倉庫和資料分析應用服務。 | MySQL 被廣泛採用，是由於 Web 應用，只需要一個簡單的資料交易資料庫。然而，在一般的情況下，如果 MySQL 的表現不佳，都是當負載過重或試圖進行複雜查詢的時候。MySQL 在 OLAP / OLTP 系統只需要讀取速度時表現良好。MySQL + InnoDB為 OLTP 場景提供了非常好的讀/寫速度。 整體來說，MySQL在高度資料一致性需求的情況下表現良好。MySQL 是可靠的，並且適用於商業智慧應用服務，因為商業智慧應用服務通常是更重視讀取效能的。 |

## Security

資料庫安全性指的是用於保護資料庫或DBMS免於非法使用、惡意威脅和攻擊的綜合措施。這是一個廣泛的術語，包括確保資料庫環境中安全性的多種程序、工具和方法。

| PostgreSQL                                                                                                                                                                                                                      | MySQL                                                                            |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------- |
| PostgreSQL 具有角色並可繼承角色來設定和維護權限。 PostgreSQL 內建支援 SSL連線來加密客戶端/伺服器通訊。 它具有資料列級的安全性。除此之外，PostgreSQL 還附帶了一個名為 SE-PostgreSQL 的增強功能，可以根據 SELinux 安全策略提供額外的存取控制。更多細節請參考[這裡](https://wiki.postgresql.org/wiki/SEPostgreSQL_Introduction)。. | MySQL 為所有連線、查詢和用戶可能嘗試執行的其他操作實作了存取控制列表（ACL）的安全性。 對 MySQL 客戶端和伺服器之間的 SSL 加密連接也有支援。 |

## Cloud Hosting

隨著越來越多的企業選擇將資料轉移到公有雲中，尋找支援資料庫的公有雲服務商變得越來越重要。雲端主機允許伺服器的彈性擴展，使你能夠迅速擴大或縮小其容量。它還可以減少潛在的停機時間，同時輕鬆管理尖峰負載。

| PostgreSQL                  | MySQL                       |
| --------------------------- | --------------------------- |
| 所有主要雲端服務提供商都支持，包括亞馬遜、谷歌和微軟。 | 所有主要雲端服務提供商都支持，包括亞馬遜、谷歌和微軟。 |

## Community Support

| PostgreSQL                                                           | MySQL                                                   |
| -------------------------------------------------------------------- | ------------------------------------------------------- |
| PostgreSQL 擁有非常強大且活躍的社區，不斷改進現有功能，同時創新的提交者努力確保它保持在最先進的功能和安全性的最先進的數據庫。 | MySQL 擁有大量的貢獻者社區，特別是在甲骨文收購之後，他們主要關注於維護現有功能，並偶爾會出現一些新功能。 |

## Concurrency Support

Concurrency 意味著多個使用者可以同時存取資料。在開發需要多個使用者同時存取資料的系統時，這是核心功能之一，因為它增強了許多人同時在多個來源存取和使用資料庫的能力。

| PostgreSQL                                         | MySQL                       |
| -------------------------------------------------- | --------------------------- |
| PostgreSQL 透過實作 MVCC 來有效地處理同時間平行存取，從而達到非常高的平行處理等級。 | MySQL 只在使用 InnoDB 時支援 MVCC。 |

## NoSQL Features/JSON Support

NoSQL 和 JSON 都非常流行，NoSQL資料庫變得越來越普及。JSON 是一種簡單的資料格式，它允許程式設計師儲存和傳遞跨系統的資料內容、資料列表和 key-value 對應。

| PostgreSQL                                                                                  | MySQL                                            |
| ------------------------------------------------------------------------------------------- | ------------------------------------------------ |
| PostgreSQL 支援 JSON 和其他 NoSQL 的功能，如內建 XML 支援和 HSTORE 的 key-value 對應。 它還支援將 JSON 資料索引以加快存取速度。 | MySQL 具有 JSON 資料類型支援，但沒有其他 NoSQL功能，也不支援 JSON 索引。 |

## Materialized Views/Temporary Tables

Materialized view 是包含查詢結果的資料庫物件，可根據需要從原始資料表中更新查詢結果。它可以被認為是像資料庫的「快取」。

臨時資料表儲存的資料不需要超出資料庫連線的持續時間。與 materialized view 不同的主要方式是後者提供了定期更新資料的能力，從而提高了該資料案例的使用效率。

| PostgreSQL                                | MySQL                                        |
| ----------------------------------------- | -------------------------------------------- |
| 支援 materialized views 及 temporary tables。 | 支援 temporary tables 但不支援 materialized views。 |

## Geospatial Data Support

地理資訊資料是資料庫儲存並可用於分析的所有地理位置資訊。它是關於實體物件的訊息，可以用地理坐標系中的數值表示。

| PostgreSQL                                                                            | MySQL       |
| ------------------------------------------------------------------------------------- | ----------- |
| PostgreSQL 通過 PostGIS 延伸套件支持地理空間資料。 地理空間資料有專門的類型和功能，可直接在資料庫級別使用，使開發人員可以更輕鬆地進行分析和撰寫程式。 | 內建地理空間資料支援。 |

## Programming Languages Support

Programming languages support helps a wide range of developers to perform several tasks in the language in which they are most proficient. Developers can freely decide, on a case by case basis, whether to perform a given procedure in the server or in the client, because the server supports a wide range of different programming languages for database functions. Programming languages tend to give more power to the developers.

| PostgreSQL                                                                                                       | MySQL              |
| ---------------------------------------------------------------------------------------------------------------- | ------------------ |
| PostgreSQL 支援各種程式語言，包括：C / C ++，Java，JavaScript，.Net，R，Perl，Python，Ruby，Tcl 等等。 甚至可以在單獨的程序中執行用戶提供的程式（即作為背景執行程式）。 | 一些支援伺服器端不可擴展的程式語言。 |

## Extensible Type System

支援可延伸套件系統的資料庫可以通過多種方式進行強化其功能，如增加新的資料類型、函數、運算符、彙總函數、索引方法和程式語言。

| PostgreSQL                                          | MySQL      |
| --------------------------------------------------- | ---------- |
| PostgreSQL有幾個專用於延伸套件的功能。 可以增加新的資料型別、新的函數功能、新的索引類型等。 | 不支援增加延伸功能。 |

## Comparison Summary

以下是 PostgreSQL 與 MySQL 的簡要比較表：

| Feature                  | PostgreSQL                                                                                                                                                                                             | MySQL                                                                                                                                                                                                                  |
| ------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| *Open Source*            | Completely Open source                                                                                                                                                                                 | Open source, but owned by Oracle and offers commercial versions                                                                                                                                                        |
| *ACID Compliance*        | Complete ACID Compliance                                                                                                                                                                               | Some versions are compliant                                                                                                                                                                                            |
| *SQL Compliance*         | Almost fully compliant                                                                                                                                                                                 | Some versions are compliant                                                                                                                                                                                            |
| *Concurrency Support*    | MVCC implementation supports multiple requests without read locks                                                                                                                                      | Support in some versions.                                                                                                                                                                                              |
| *Security*               | Secure from ground up with SSL support                                                                                                                                                                 | SSL support in some versions                                                                                                                                                                                           |
| *NoSQL/JSON Support*     | Multiple supported features                                                                                                                                                                            | JSON data support only                                                                                                                                                                                                 |
| *Access Methods*         | Supports all standards                                                                                                                                                                                 | Supports all standards                                                                                                                                                                                                 |
| *Replication*            | Multiple replication technologies available:Single master to one standbySingle master to multiple standbysHot Standby/Streaming ReplicationBi-Directional replicationLogical log streaming replication | Standard master-standby replication:Single master to one standbySingle master to multiple standbysSingle master to one standby to one or more standbysCircular replication (A to B to C and back to A)Master to master |
| *Materialized Views*     | Supported                                                                                                                                                                                              | Not supported                                                                                                                                                                                                          |
| *Temporary Tables*       | Supported                                                                                                                                                                                              | Supported                                                                                                                                                                                                              |
| *GeoSpatial Data*        | Supported                                                                                                                                                                                              | Supported                                                                                                                                                                                                              |
| *Programming Languages*  | Supported                                                                                                                                                                                              | Not supported                                                                                                                                                                                                          |
| *Extensible Type System* | Supported                                                                                                                                                                                              | Not supported                                                                                                                                                                                                          |


# PostgreSQL vs MySQL vs SQL Server vs Oracle

對於哪個 SQL 資料庫系統比較優秀，總是存在著爭議。PostgreSQL 和 MySQL 之間的真正區別是什麼？每個管理者都有自己的偏好，每個程式設計師都有自己獨特的程式處理方式。因此，如果您詢問五個不同的系統管理員，他們更喜歡哪個平台，那麼你會得到五個不同的答案。

讓我們來看看我們是否能幫助你自己回答。

今天，我們將看看目前網路上可用的一些主要資料庫管理系統的優缺點。一旦我們完成了這一部分，我們將提供一些建議，以確定每個項目最適合哪個項目。讓我們直接來看看：

## Oracle

* 閉源軟體；免費版本的功能非常有限
* 暫時資料表在不同連線間是持續存在的，必須由使用者自行刪除
* 支援四種不同的字元/字串類型：CHAR，VARCHAR2，NCHAR，NVARCHAR2
* 提供資料表和資料列層級的鎖定
* 廣泛和靈活的指令來自訂儲存引擎，像是資料表空間（tablespace）、同義詞（synonym）和套件（package）
* 廣泛的備份機制
* 設計目標在於管理大規模的資料表和資料庫

## MySQL

* 開放原始碼
* 相容於各式各樣的引擎和介面；這是市場上最成熟的資料庫之一
* 輕量化
* 最流行的資料庫工具之一；很容易在網路上找到支援的資訊
* 暫時資料表只在目前資料庫連線中可以被看見，並在離線之後自動刪除。
* 缺乏全面的 ACID 合規性
* 分割資料表可以使用 LIST、HASH、RANGE 和 SET 等分割方法
* 支援兩種不同的字元/字串型別：CHAR 和 VARCHAR
* 只提供資料表層次的鎖定
* 資料表 view 的選項較少
* 有限的儲存引擎參數調整
* 管理工具非常強大
* 兩種備份機制：mysqlhotcopy 和 mysqldump
* 在大量使用時，經驗上明顯會感受到性能下降。
* 在效能方面，沒有提供什麼最佳化的方式
* 可靠性的問題
* 與其他資料庫系統相比，安全性有限。
* 專為事務性工作負載而設計，因此不適合分析工作負載

## MS SQL Server

* 封閉原始碼，目標為公司或企業的環境。
* 對通用的資料表表示式提供全面的支持
* 需要比其他工具更深入地理解有關於資料庫本身及資料庫的配置方式
* 可以微調安全功能，例如誰可以執行每個自訂函數，或誰可以存取資料等
* 使用稍慢、資源重的資料庫引擎，但完全符合ACID標準。
* 極其全面的回報系統及儲存引擎自訂化
* 高度的交易安全和程序控制
* 社群不是非常活躍，沒有像 MySQL 那樣線上支援。
* 透過一套工具提供簡單的資料庫功能：ETL工具，分析用 DBMS，關連式 DBMS和報表伺服器。
* 資料庫架構更改不需要鎖定資料表。
* 資源佔用相對較高

## DB2

* 閉源軟體。只有付費的企業版本
* 基於 schema 的資料表管理
* 不支援 XML
* 只能通過分割資料表來分散
* 沒有 In-memory 的功能
* 為關連完整性而設計
* 比MySQL更強大的資料表管理
* Materialized table views
* 缺少內建的字元/字串支援
* 多種災難復原選項，具可用性和可擴展性

## PostgreSQL

* 開放原始碼
* 堅持現有的 SQL 標準，並因此更容易學習
* 佔用空間大，不適合讀取繁重的操作
* 先進的商業及地理分析功能
* 豐富多樣的資料型別和字元類型
* 完全符合ACID標準
* 專為可靠性和資料完整性而設計；以開發者為中心設計
* 全文檢索，支持強大的伺服器端程式語言
* 完全支持先進的 SQL 功能，如表格表示式和窗函數功能
* 可以有效地進行大量資料表的交叉查詢
* 複製功能並不是很完整
* 不適合資料一致性較低的專案

## 那麼，你應該使用哪個？

毫無疑問，我們在這裡描述的每個資料庫工具在功能和設計方面都有很大的不同。因此，你選擇哪一個的問題取決於你計劃管理的應用類型。

### 你應該使用 Oracle 如果...

* 你在交易安全控制方面需要更多彈性
* 你計劃管理一個大型資料庫
* 你需要高度的可擴展性
* 你希望你的資料庫是與平台無關的

### 你應該使用 MySQL 如果…

* 你的資料庫不會擴展很大的程度
* 你正在計劃建立一個唯讀的網路應用程式或網站
* 你需要多重的資料複製
* 你的專案只需要簡單的查詢，並且較少處理資料一致性的問題

### 你應該使用 MS SQL Server 如果...

* 你正在 .NET 開發環境中工作。
* 你的資料庫服務於大型的企業環境
* 你關心的是負載量而不是開發應用程式
* 你需要對資料庫進行微調。

### 你應該使用 DB2 如果...

* 你的公司已經在內部建置了一個 DB2 環境
* 你希望串接來自多個來源的資料
* 你需要能夠高速存取資料
* 效能最佳化對你的專案非常重要

### 你應該使用 PostgreSQL 如果…

* 你規劃使用複雜的自訂程序（stored procedure）
* 你將要使用 Java 工作
* 你的資料庫是龐大而復雜的，需要高度的資料一致性，並且有許多種查詢類型
* 你將會進行許多寫入操作，而讀取速度並不是唯一的考量
* 你的專案是以開發人員為中心的

希望這些回答為這個問題提供了一個很好的基礎：PostgreSQL、MySQL 和其他更多資料庫之間有什麼差異？


# PostgreSQL 支援哪些平台呢？

一般來說，任何現代的 Unix 相容平台都應該能夠運行PostgreSQL。近年來有收到明確的測試平台，可以在[Build farm](http://buildfarm.postgresql.org/)中看到。 該文件中包含有關支持平台的更多細節：<http://www.postgresql.org/docs/current/static/supported-platforms.html>.

PostgreSQL 也在 Microsoft Windows NT 相容操作系統運行，像是 Windows XP、Vista、7、8、2003、2008 等系統。這裡有預先包裝好的安裝包：<http://www.postgresql.org/download/windows>.

有用於 Windows 的 Cygwin 版本，但通常不建議；請使用原生 Windows 版本。如果你確實需要用 Cygwin 作為用戶端的應用程序，則可以使用 PostgreSQL 用戶端庫（libpq）的 Cygwin 版本連接到原生的 Windows PostgreSQL。


# Replication 備忘錄

如此多的 Replication 類型，不過時間不多！ 你應該使用什麼樣的 PostgreSQL Replication 呢？

## 如果主服務器出現故障，我想要一個備用服務器來接管。

請使用 [streaming replication](https://www.postgresql.org/docs/current/static/warm-standby.html#STREAMING-REPLICATION)。

## 我想要做唯讀查詢的負載平衡。

使用 streaming replication，但請注意，接收查詢的副本主機可能落後於主節點。

## 我想在一個緩慢或不可靠的網絡連線進行複寫。

請使用 [WAL Shipping](https://www.postgresql.org/docs/current/static/warm-standby.html).

## 我想從 9.4 之前的版本升級到 9.4 或更新版本。

請使用 [Slony](http://www.slony.info/), [Londiste](https://wiki.postgresql.org/wiki/SkyTools), 或 [Bucardo](https://bucardo.org/Bucardo/).

## 我想從 9.4 或更新版本升級。

請使用 [pglogical](https://www.2ndquadrant.com/en/resources/pglogical/).

## 我想整合多個伺服器到一個單一的資料倉庫，或只複製一些資料表/資料庫/等等。

1. 如果你在 9.3 或更早版本，請使用 Slony、Bucardo 或 Londiste。 （但是你應該先升級吧。）
2. 如果你在 9.4、9.5 或 9.6，請使用 pglogical。
3. 如果你是版本 10，請使用內建的 logical replication。

## 我只想複製資料表中的一些資料列

1. 如果你在 9.3 或更早版本，請使用 Slony。
2. 如果你在 9.4 或更新版本，使用 pglogical。

## 我想複製沒有主鍵或其他唯一鍵的資料表。

升級到版本 10 並使用內建的 logical replication。

## 我應該使用 Slony, Bucardo, 或 Londiste 嗎？

1. Slony 整體表現上比 Bucardo 或 Londiste 有更好。
2. Slony 需要安裝 C-level 的延伸套件。
3. Bucardo 和 Londiste 更容易安裝和管理。
4. Bucardo 和 Londiste 分別使用 Perl 和 Python，如果你真的很關心這些工具使用什麼語言的話。

## 我需要多個主要主機，其中兩個伺服器可以互相寫入和複製對方。

請使用 Bucardo.

## 我希望 Amazon PostgreSQL RDS 伺服器作為邏輯複製的目標。

請使用 Bucardo 或 Londiste.

## 我想從 RDS 複製到非 RDS 資料庫。

目前沒有解決方案。

## 我想要一個 RDS資料庫作為邏輯複製的來源。

目前沒有解決方案。

## 等等，RDS 文件說它「supports logical replication」。

它只支援 PostgreSQL 作為 contrib / modules 的一部分提供的測試外掛元件，實際上它並沒有做複製。

## 我想使用 Amazon 的資料遷移服務來進行 PostgreSQL 到 PostgreSQL 的複製。

你可能不會。 它不支援主要的 PostgreSQL 資料型別，如 TIMESTAMP WITH TIME ZONE。

## 我想同時使用 streaming replication 和 logical replication。

這是可能的，但是這個作法的外圍還有很多複雜性，如果你的主要工作失敗，如何成功地進行故障切換。

## 這真的非常複雜。

不是這樣的！ 基本規則是使用 streaming replication 實現高可用性，以 logical replication 解決資料倉庫或其他資料分散問題。其餘的都是細節！


# Autovacuum Tuning Basics

本篇編譯自：[“Autovacuum Tuning Basics”，Tomas Vondra](https://blog.2ndquadrant.com/autovacuum-tuning-basics/)

A few weeks ago I covered the [basics of tuning checkpoints](https://blog.2ndquadrant.com/basics-of-tuning-checkpoints/), and in that post I also mentioned that the second common source of performance issues is autovacuum (based on what we see on the mailing list and at our customers under [support](https://2ndquadrant.com/en/support/support-postgresql/)). So let me follow-up on that with this post about the basics of `autovacuum` tuning. I’ll very briefly explain the necessary theory (dead tuples, bloat and how `autovacuum`deals with it), but the main focus of this blog post is tuning – what configuration options are there, rules of thumb, etc.

### Dead tuples

First, let’s briefly explain what are “dead tuples” and “bloat.” (If you want a more detailed explanation, perhaps read Joe Nelson’s post which discusses this in a bit more detail.)

When you do a `DELETE` in PostgreSQL, the row (aka tuple) is not immediately removed from the data file. Instead it is only marked as deleted by setting `xmax` field in a header. Similarly for `UPDATE`s, which may be seen as `DELETE` + `INSERT` in PostgreSQL.

This is one of the basic ideas behind PostgreSQL MVCC, as it allows greater concurrency with only minimal locking between the different processes. The downside of this MVCC implementation is of course that it leaves behind the deleted tuples, even after all the transactions that might see those versions finish.

If not cleaned up, those “dead tuples” (effectively invisible to any transaction) would stay in the data files forever, wasting disk space, and for tables with many `DELETE`s and `UPDATE`s the dead tuples might easily account for vast majority of disk space. Of course, those dead tuples would also be referenced from indexes, further increasing the amount of wasted disk space. This is what we call “bloat” in PostgreSQL. And naturally, the more data queries have to process (even if 99% of it is immediately thrown away as “dead”), the slower the queries.

### `VACUUM` and `autovacuum`

The most straightforward way to reclaim space occupied by dead tuples (and make it available for new rows) is by manually running `VACUUM` command. This maintenance command will scan the table and remove dead tuples both from the table and indexes – it will not generally return the disk space back to the operating system, but it will make it usable for new rows.

**Note:** `VACUUM FULL` would reclaim the space and return it to the OS, but is has a number of disadvantages. Firstly it acquires exclusive lock on the table, blocking all operations (including `SELECT`s). Secondly, it essentially creates a copy of the table, doubling the disk space needed, so it’s not very practical when already running out of disk space.

The trouble with `VACUUM` is that it’s entirely manual action – it only happens when you decide run it, not when it’s needed. You may put it into `cron` and run it every 5 minutes on all tables, but the chances are most of the runs will not actually clean anything, and the only effect will be higher CPU and I/O usage on the system. Or you may run it only once a day at night, in which case you’ll probably accumulate more dead tuples that you’d like.

Which leads us to the primary purpose of `autovacuum`; do the cleanup as needed to keep the amount of wasted space under control. The database does know how many dead tuples were produced over time (each transaction reports the number of tuples it deleted and updated), and so can trigger cleanup when the table accumulates a certain number of dead tuples (by default this is 20% of the table, as we’ll see). So it will be executed more often during busy periods, and less often when the database is mostly idle.

### `autoanalyze`

Cleanup of dead tuples is not the only task of `autovacuum`. It’s also responsible for updating data distribution statistics, used by the optimizer when planning queries. You may collect those manually by running `ANALYZE`, but it suffers similar issues as `VACUUM` – you’re likely to run it either too often or not often enough.

And the solution is also similar – the database can watch how many rows changed in the table, and run `ANALYZE` automatically.

**Note:** The negative effects are a bit worse for `ANALYZE`, because while the cost of `VACUUM` is proportional to the amount of dead tuples (so fairly low when there are few/none), `ANALYZE` has to rebuild the statistics from scratch on every execution. On the other hand, if you’re not running it often enough, the cost of poor plan choices may be just as severe.

I’ll mostly ignore this `autovacuum` task in the rest of this post for the sake of brevity – the configuration is fairly similar to the cleanup anyway, and follows roughly the same reasoning.

### Monitoring

Before doing any sort of tuning, you need to be able to collect relevant data – otherwise how could you say you need to do any tuning at all, or evaluate the impact of configuration changes?

In other words, you should have some basic monitoring in place, collecting metrics from the database. For cleanup, you need to be looking at least at these values:

\* \`pg\_stat\_all\_tables.n\_dead\_tup\` – number of dead tuples in each table (both user tables and system catalogs)\
\* \`(n\_dead\_tup / n\_live\_tup)\` – ratio of dead/live tuples in each table\
\* \`(pg\_class.relpages / pg\_class.reltuples)\` – space “per row”

If you already have a monitoring system deployed (and you should), chances are you’re already collecting such metrics. The overall goal is to get stable behavior, with no sudden/significant changes to any of those metrics.

There’s also a handy [pgstattuple](https://www.postgresql.org/docs/current/static/pgstattuple.html) extension, allowing you to perform analysis on tables and indexes, including computing the amount of free space, dead tuples etc.

### Tuning Goals

Before looking at the actual configuration parameters, let’s briefly discuss what are the high-level tuning goals, i.e. what we want to achieve when changing the parameters:

* **cleanup dead tuples** – Keep the amount of disk space reasonably low, not to waste unreasonable amount of disk space, prevent index bloat and keep queries fast.
* **minimize cleanup impact** – Don’t perform cleanup too often, as it would waste resources (CPU, I/O and RAM) and might significantly hurt performance.

That is, you need to find the right balance – running it too often may be just as bad as not running it often enough. The balance heavily depends on the amount of data you manage, the type of workload you are dealing with (number of `DELETE/UPDATE`s).

Most default values in `postgresql.conf` are quite conservative, for two reasons. Firstly, the default values were decided a few years ago, based on the resources (CPU, RAM, …) common at that time. Secondly, we want the default configuration to work everywhere, including tiny machines like Raspberry Pi or small VPS servers. For many deployments (particularly smaller ones and/or handling read-mostly workloads) the default configuration parameters will however work just fine.

As the database size and/or amount of writes increase, problems start to appear. The typical issue is that the cleanup does not happen often enough, and then when it happens it significantly disrupts performance, because it has to deal with a lot of garbage. If those cases you should follow this simple rule:

*If it hurts, you’re not doing it often enough.*

That is, tune the parameters so that the cleanup happens more often, and processes smaller amount of dead tuples every time.

**Note**: People sometimes follow a different rule – *If it hurts, don’t do it.* – and disable `autovacuum` entirely. Please don’t do that unless you really (really really) know what you’re doing, and have regular cleanup script in place. Otherwise you’re painting yourself in the corner, and instead of somewhat degraded performance you’ll have to deal with severely degraded performance or possibly even an outage.

So now that we know what we want to achieve by the tuning, let’s see the configuration parameters …

### Thresholds and Scale Factors

Naturally, the first thing you may tweak is when the cleanup gets triggered, which is affected by two parameters:

* **autovacuum\_vacuum\_threshold** = 50
* **autovacuum\_vacuum\_scale\_factor** = 0.2

and the cleanup is triggered whenever the number of dead tuples (which you can see as `pg_stat_all_tables.n_dead_tup`) exceeds

`threshold + pg_class.reltuples * scale_factor`

the table will be considered as in need of cleanup. The formula basically says that up to 20% of a table may be dead tuples before it gets cleaned up (the threshold of 50 rows is there to prevent very frequent cleanups of tiny tables).

The default scale factor works fine for small and medium-sized tables, but not so much for very large tables – on 10GB table this is roughly 2GB of dead tuples, while on 1TB table it’s \~200GB.

This is an example of accumulating a lot of dead tuples, and processing all of it at once, which is going to hurt. And per the rule mentioned before, the solution is to do it more often by significantly decreasing the scale factor, perhaps even like this:

`autovacuum_vacuum_scale_factor = 0.01`

which decreases the limit to only 1% of the table. An alternative solution is to abandon the scale factor entirely, and use solely the threshold

```
autovacuum_vacuum_scale_factor = 0
autovacuum_vacuum_threshold = 10000
```

which should trigger the cleanup after generating 10000 dead tuples.

One trouble is that these changes in `postgresql.conf` affect all tables (the whole cluster, in fact), and it may undesirably affect cleanups of small tables, including for example system catalogs.

When the small tables are cleaned up more frequently, the easiest solution is to simply ignore the problem entirely. Cleanup of small tables is going to be fairly cheap, and the improvement on large tables is usually so significant that even if you ignore small inefficiency on small tables, the overall effect is still very positive.

But if you decided to change the configuration in a way that would significantly delay cleanup on small tables (as for example with setting scale\_factor=0 and threshold=10000), it’s better to apply those changes only to particular tables using `ALTER TABLE`:

```
ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0);
ALTER TABLE t SET (autovacuum_vacuum_threshold = 10000);
```

Try to keep the configuration as simple as possible, and override the parameters for as few tables as possible. It’s also a good idea to include this into your internal documentation, including the reasoning for particular values.

### Throttling <a href="#throttling" id="throttling"></a>

A nice feature build into `autovacuum` is throttling. The cleanup is meant to be a maintenance task running in the background, with minimum impact on user queries etc. In other words, it should not consume too much resources (CPU and disk I/O), and this is exactly the purpose of the throttling built into `autovacuum`.

The cleanup process is fairly simple – it reads pages (8kB chunks of data) from data files, and checked if it needs cleanup. If there are no dead tuples, the page is simply thrown away without any changes. Otherwise it’s cleaned up (dead tuples are removed), is marked as “dirty” and eventually written out. The costing is based on defining cost for three basic operations:

```
vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20
```

That is, if the page is read from `shared_buffers`, it counts as 1. If it’s not found in `shared_buffers` and needs to be read from the OS, it counts as 10 (it might still be served from RAM, but we don’t know). And finally, if the page is dirtied by the cleanup, it counts as 20. That allows us to compute “cost of work” done by `autovacuum`.

The throttling is then done by limiting the amount of work that can be done in one go, which is by default set to 200, and every time the cleanup does this much work it’ll sleep for 20ms:

```
autovacuum_vacuum_cost_delay = 20ms
autovacuum_vacuum_cost_limit = 200
```

So, how much work does that actually allow? With 20ms delay, the cleanup can do 50 rounds per second, and with 200 tokens per round that means 10000 token per second. That means:

* 80 MB/s reads from `shared_buffers` (assuming it’s not dirtied)
* 8 MB/s reads from OS (possibly from disk)
* 4 MB/s writes (pages dirtied by the `autovacuum` process)

Considering the capabilities of current hardware, and that the reads/writes are mostly sequential, those limits are too low.

What we generally do is increasing the `cost_limit` parameter, e.g. to 1000 (or 2000), which increases the throughput by 5x (or 10x). You may of course tweak the other parameters (cost per page operation, sleep delay), but we do that only very rarely – changing the cost limit works well enough.

### Number of Workers

One configuration option not yet mentioned is `autovacuum_max_workers`, so what’s that about? Well, the cleanup does not happen in a single `autovacuum` process, but the database is allowed to start up to `autovacuum_max_workers` processes that actually do cleanup of different databases/tables.

That’s useful, because you for example don’t want to stop cleaning up small tables until a cleanup of a single large table finishes (which may take quite a bit of time, because of the throttling).

The trouble is users assume the number of workers is proportional to the amount of cleanup that can happen. If you bump the number of `autovacuum` workers up to 6, it’ll surely do twice as much work compared to the default 3 workers, right?

Well, no. The cost limit, described a few paragraphs ago, is global, shared by all `autovacuum` workers. Each worker process only gets `1/autovacuum_max_workers` of the total cost limit, so increasing the number of workers will only make them go slower.

It’s a bit like highway – doubling the number of cars but making them go half the speed will only give you about the same number of people reaching the destination per hour.

So if the cleanup on your database can’t keep up with user activity, increasing the number of workers is not a solution, unless you also tweak the other parameters.

### Per-table Throttling

Actually, when I said that the cost limit is global and shared by all `autovacuum` workers, I’ve been lying (a bit). Similarly to scale factor and threshold, it’s possible to set the cost limit and delay per table:

```
ALTER TABLE t SET (autovacuum_vacuum_cost_limit = 1000);
ALTER TABLE t SET (autovacuum_vacuum_cost_delay = 10);
```

Workers processing such tables are then not included in the global costing, and are throttled independently.

This gives you quite a bit of flexibility and power, but don’t forget – with great power comes great responsibility!

In practice, we almost never use this feature, for two basic reasons. Firstly, you generally do want to use a single global limit on the background cleanup. Secondly, having multiple workers that are sometimes throttled together and sometimes independently makes it much harder to monitor and analyze behavior of the system.

### Summary

So that’s how you tune `autovacuum`. If I had to sum it into a few basic rules, it’d be these five:

* Don’t disable `autovacuum`, unless your really know what you’re doing. Seriously.
* On busy databases (doing a lot of `UPDATE`s and `DELETE`s), particularly large ones, you should probably decrease the scale factor, so that cleanup happens more frequently.
* On reasonable hardware (good storage, multiple cores), you should probably increase the throttling parameters, so that the cleanup can keep up.
* Increasing `autovacuum_max_workers` alone will not really help in most cases. You’ll get more processes that go slower.
* You can set the parameters per table using `ALTER TABLE`, but think twice if you really need that. It makes the system more complex and more difficult to inspect.

I originally included a few sections explaining cases when `autovacuum` does not really work, and how to detect them (and what is the best solution), but the blog post is already too long so I’ll post that separately in a few days.