Postgresql

為什麼我的數據庫比插入的數據大 12 倍?

  • May 3, 2020

我有一個關於我的數據庫大小的簡短問題。我需要在數據庫中插入數據。在插入之前,需要進行一些計算。

關鍵是:從 50 mb 普通數據(約 700,000 行),這將導致 600 mb db 大小。這是12倍!我確定我在這裡做錯了什麼。你能幫我縮小我的數據庫的大小嗎?數據庫大小的來源是 web postgres 管理界面。

這是插入:

CREATE TYPE CUSTOMER_TYPE AS ENUM
('enum1', 'enum2', 'enum3', '...', 'enum15');       ## max lenght of enum names ~15

CREATE TABLE CUSTOMER(
  CUSTOMER_ONE    TEXT PRIMARY KEY NOT NULL,       ## max 35 char String
  ATTRIBUTE_ONE   TEXT UNIQUE,                     ## max 35 char String
  ATTRIBUTE_TWO   TEXT UNIQUE,                     ## max 51 char String
  ATTRIBUTE_THREE TEXT UNIQUE,                     ## max 52 char String
  ATTRIBUTE_FOUR  TEXT UNIQUE,                     ## max 64 char String
  ATTRIBUTE_FIFE  TEXT UNIQUE,                     ## 1-80 char String
  CUSTOMER_TYPE   PRIVATEKEYTYPE                   ## see enum
);

我真的不需要那個列舉,因為我也可以在沒有的情況下插入它。列舉對數據庫大小有影響嗎?

有沒有辦法減小尺寸?是否有可能達到因子 x4(而不是 x12)?如果沒有,如有必要,我可以刪除一些列。

也許還有其他用於字元數據的 Postgres 數據類型?

在這裡回饋後,我更新的表格現在看起來像這樣:

CREATE TABLE CUSTOMER(
  CUSTOMER_ONE    TEXT PRIMARY KEY NOT NULL,       ## max 35 char String
  ATTRIBUTE_ONE   TEXT UNIQUE,                     ## max 35 char String
  ATTRIBUTE_TWO   TEXT,                            ## max 51 char String
  ATTRIBUTE_THREE TEXT,                            ## max 52 char String
  ATTRIBUTE_FOUR  TEXT,                            ## max 64 char String
  ATTRIBUTE_FIFE  TEXT,                            ## 1-80 char String
  CUSTOMER_TYPE   PRIVATEKEYTYPE                   ## see enum
);

之前:12x

現在:7x :)

還有更多可能的優化嗎?(除了刪除列?)也許其他數據類型使用更少的空間?

text是純文字的最佳類型。僅文本值在內部佔用大致相同(當然也取決於編碼細節:UTF-8?)。在您的情況下,每個欄位的小成本為 1 個字節。看:

enum列對總大小的貢獻很小。每行 4 個字節(real內部數量)。列舉名稱的長度實際上是無關緊要的,作為數據類型儲存一次name,因此最多NAMEDATALEN(通常為 63 個字元)。類型聲明的最小成本。因此,列舉列實際上在 DB 中佔用的空間比在文本文件中佔用的空間少。

Postgres 儲存中有各種類型的成本,它必須是這樣的。其中一些是一次性成本,另一些與行數或關係等有關。有目錄表(系統數據)、索引、關係、表統計資訊、簿記數據等。

對於 50 MB 的原始文本數據,大小似乎是平均大小的 12 倍。但這真的取決於細節。許多小行有很多成本,少數大行沒有那麼多,甚至可以壓縮和更小。a_horse 已經指出了一個主要原因:6 個索引——其中一些顯然也沒有必要。

表中的每一行都有一個佔用 23 個字節的元組標題(加上對齊填充,通常為 24 個字節),列和元組之間可能有對齊填充,4 個字節用於項目標識符,每個數據頁的一些成本(通常為 8kb 頁面大小)。在您的情況下,每行大約 35 個字節。

每個索引元組大約一樣多(標頭只有 8 個字節,但索引膨脹更多)。6 個索引,每個表行至少150 個字節。加上每個文本列被保存兩次,1x 表,1x 索引。btree 索引從FILLFACTOR90 開始,所以 ~ + 10 % 。

您的平均行大小約為 75 字節(50MB / 700000 - 假設“行”對應於行)。您的表和索引佔用原始文本大小的大約 6 倍。剩下的就是上面提到的其他成本。

這一切都假設沒有表膨脹(還)。一旦您使用數據庫(更新、刪除、事務回滾等),就會出現死行等,可能會增加總空間。這在很大程度上取決於寫入模式。

您可以通過刪除不必要的索引來節省很多。

如果將enum列移到表的頂部,則每行幾個字節:更少的對齊填充,應該平均為每行 1.5 個字節,總共大約 1 MB,幾乎不值得。看:

引用自:https://dba.stackexchange.com/questions/193126