對所有列禁用 TOAST 壓縮

我在壓縮的 ZFS 文件系統上執行 PostgreSQL 。提到的一個技巧是禁用 PostgreSQL 的內聯 TOAST 壓縮，因為 ZFS 可以更好地壓縮數據。這可以通過將列儲存設置為 來完成EXTERNAL。

我可以通過以下方式逐列執行此操作：

ALTER TABLE my_table ALTER COLUMN my_column SET STORAGE EXTERNAL;

但是，這可能有點麻煩，因為每個模式都需要手動遷移到這裡。

有沒有簡單的方法

• 將所有列的預設設置STORAGE為EXTERNAL而不是MAIN
• 以其他方式禁用 TOAST 壓縮

我找到了default_toast_compression 選項，但文件不清楚我是否可以禁用它。

default_toast_compression僅確定烘烤列值時要使用的壓縮方法。它根本不決定值是否被烘烤。

值得注意的是，TOAST 機制適用於內聯和外聯儲存的值。就是“超大屬性儲存術”。

您想完全禁用壓縮，而不僅僅是選擇首選算法。在去那里之前：真的值得可能的並發症嗎？讓我從您的參考頁面引用完整的報價：

禁用 TOAST 壓縮

要不壓縮數據兩次，您可以通過將列儲存設置為來禁用 PostgreSQL TOASTEXTERNAL壓縮。但這並沒有太大區別：

• LZ4 速度極快。
• LZ4 和 ZSTD 都有特殊的邏輯來跳過不可壓縮（或已經壓縮）的數據部分。

大膽強調我的。考慮使用（Postgres 14 中的新功能）LZ4 壓縮方法並完成它。看：

• 查詢 json / jsonb 列超慢。我可以使用索引嗎？

你問什麼

您可以設置STORAGE 每個數據類型。類型範例varchar：

ALTER TYPE varchar SET (STORAGE = external);

然後將其複製到更改後創建的具有該類型的每個表列。手冊：

attstorage char

pg_type.typstorage通常是該列類型的副本。對於支持 TOAST 的數據類型，這可以在創建列後更改以控制儲存策略。

您必須另外更改現有列。

只能通過這種方式更改基本pg_type.typtype類型（參見手冊）。它可能會引入警告。手冊ALTER TYPE：

SET ( property = value [, ... ] )

此表單僅適用於基本類型。它允許調整可以在 中設置的基本類型屬性的子集CREATE TYPE。具體來說，可以更改這些屬性：

$$ … $$

• STORAGE可以設置為plain, extended, external, 或main（有關這些含義的更多資訊，請參見第 70.2 節）。但是，從plain另一個設置更改為需要超級使用者權限（因為它要求該類型的 C 函式都準備好 TOAST），並且根本不允許從另一個設置更改為純文字（因為該類型可能已經在數據庫）。請注意，更改此選項本身不會更改任何儲存的數據，它只是設置預設 TOAST 策略以用於將來創建的表列。請參閱ALTER TABLE 更改現有表列的 TOAST 策略。

也就是說，作為超級使用者，您可以使用以下查詢更改系統中的所有基本類型：

UPDATE pg_catalog.pg_type
SET    typstorage = 'e'
WHERE  typtype = 'b'
AND    typstorage = 'm';  -- "instead of MAIN"

我自己從未嘗試過，也不會去那裡，除非您確切知道自己在做什麼，否則**強烈建議不要這樣做。**亂用系統目錄可能會破壞您的數據庫集群。

我會去的default_toast_compression = 'lz4'。也許首先嘗試一個或兩個選定的數據類型ALTER TYPE ... SET (STORAGE = external);……

引用自：https://dba.stackexchange.com/questions/315063