關于如何在PostgreSQL中調整數據庫參數和配置的綜合指南

PostgreSQL是一個非常通用的數據庫系統，能夠在低資源環境和與各種其他應用程序共享的環境中高效運行。為了確保它將在許多不同的環境中正常運行，默認配置非常保守，不太適合高性能生產數據庫。加上地理空間數據庫具有不同使用模式的事實，數據往往由比非地理空間數據庫更少、更大的記錄組成，您可以看到默認配置不會完全適合我們的目的。

我想我們可以同意，每個人都想要一個快速的數據庫。問題是：在哪些方面快速？

在數據庫方面，“快”至少有兩個不同的方向：

• 每秒的事務數
• 吞吐量或數據處理量

這些是相互關聯的，但絕對不一樣。兩者在I/O方面的要求完全不同。一般來說，您希望不惜一切代價避免I/O。這是因為與訪問內存中的數據、不同級別的CPU緩存甚至CPU寄存器相比，I/O總是很慢。根據經驗，每一層都會減慢大約1:1000的訪問速度。

對于每秒大量事務的高需求系統，您需要盡可能多的并發IO。對于具有高吞吐量的系統，您需要一個每秒可以交付盡可能多字節的IO子系統。

這導致需要在CPU附近擁有盡可能多的數據——例如，在RAM中。至少工作集，即在可接受的時間內給出答案所需的數據集，應該合適。

每個數據庫引擎都有一個特定的內存布局，并為不同的目的處理不同的內存區域。

總結一下：我們需要避免IO，我們需要調整內存布局的大小，以便數據庫能夠有效地工作（并且，我假設所有其他任務都是根據正確的模式設計完成的）。

所有這些配置參數都可以在postgresql. conf數據庫配置文件中進行編輯。這是一個常規文本文件，可以使用記事本或任何其他文本編輯器進行編輯。在服務器重新啟動之前，更改不會生效。

注意：這些值僅為建議值。每個環境都會有所不同，需要進行測試以確定最佳配置。但是這一部分應該會讓你有一個好的開始。

數據庫參數

以下是一些參數，可以根據您的系統和工作負載進行調整以獲得最佳性能。

shared_buffer

PostgreSQL緩沖區稱為shared_buffer，是大多數操作系統中最有效的可調參數，該參數設置PostgreSQL將為緩存使用多少專用內存。

shared_buffer的默認值設置得很低，你不會從中得到多少好處。它設置得很低是因為某些機器和操作系統不支持更高的值。但是在大多數現代機器中，你需要增加這個值以獲得最佳性能。

如果您有一個1GB或更大內存的專用數據庫服務器，shared_buffers的合理起始值是系統內存的25%。您應該嘗試一些較低和較高的值，因為在某些情況下，要獲得最佳性能，您需要超過25%的設置。Windows系統上shared_buffers的有用范圍通常從64MB到512MB。配置取決于您的機器、工作數據集和機器上的工作負載。

在生產環境中，可以觀察到shared_buffer的較大值會提供非常好的性能，盡管您應該始終進行基準測試以找到正確的平衡。

# show shared_buffers;
shared_buffers
----------------
128MB

wal_buffers

PostgreSQL將其WAL（預寫日志）記錄寫入緩沖區，然后這些緩沖區被刷新到磁盤。緩沖區的默認大小（由wal_buffers定義）為16MB，但如果您有多個并發連接，則更高的值可以提供更好的性能。

effective_cache_size

effective_cache_size參數提供了磁盤緩存可用存儲器的估計值。它只是一個指南，而不是確切的分配內存或緩存大小。它不分配實際內存，而是告訴優化器內核中可用的緩存量。這是使用索引成本估計的一個因素；值越高，使用索引掃描的可能性越大，而值越低，使用順序掃描的可能性越大。如果值設置得太低，查詢規劃器可以決定不使用某些索引，即使它們會有幫助。還應該考慮不同表上并發查詢的預期數量，因為它們必須共享可用空間。因此，設置一個大的值總是有益的。

讓我們看看effective_cache_size的一些實際含義。<