為什麼我的數據庫比插入的數據大 12 倍?
我有一個關於我的數據庫大小的簡短問題。我需要在數據庫中插入數據。在插入之前,需要進行一些計算。
關鍵是:從 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 索引從
FILLFACTOR
90 開始,所以 ~ + 10 % 。您的平均行大小約為 75 字節(50MB / 700000 - 假設“行”對應於行)。您的表和索引佔用原始文本大小的大約 6 倍。剩下的就是上面提到的其他成本。
這一切都假設沒有表膨脹(還)。一旦您使用數據庫(更新、刪除、事務回滾等),就會出現死行等,可能會增加總空間。這在很大程度上取決於寫入模式。
您可以通過刪除不必要的索引來節省很多。
如果將
enum
列移到表的頂部,則每行幾個字節:更少的對齊填充,應該平均為每行 1.5 個字節,總共大約 1 MB,幾乎不值得。看: