PostgreSQL bytea vs smallint

我希望將大型（100Mb - 1 GB）多通道時間序列數據導入 PostgreSQL 數據庫。數據來自EDF 格式文件，這些文件將數據分塊成“記錄”或“紀元”，每個通常只有幾秒鐘。每個時期的記錄將每個數據通道的信號保存為短整數的順序數組。

我被要求將文件儲存在數據庫中，在最壞的情況下儲存為 BLOB。鑑於此，我想研究允許我對數據庫中的數據做更多事情的選項，例如促進基於信號數據的查詢。

我最初的計劃是將數據儲存為每個紀元記錄一行。我要權衡的是是否將實際信號數據儲存為 bytea 或 smallint

$$ $$（甚至 smallint$$ $$$$ $$) 類型。有人可以推荐一個嗎？我對儲存和訪問成本感興趣。用法很可能是插入一次，偶爾讀取，從不更新。如果一個更容易包裝為自定義類型，以便我可以添加用於分析比較記錄的功能，那就更好了。 毫無疑問，我的細節很少，所以請隨意添加您希望我澄清的評論。

在沒有任何答案的情況下，我自己進一步探索了這個問題。

看起來使用者定義的函式可以處理所有基本類型，包括 byteaand smallint[]，所以這不會對錶示的選擇產生太大影響。

我在具有 vanilla 配置的 Windows 7 筆記型電腦上本地執行的 PostgreSQL 9.4 伺服器上嘗試了幾種不同的表示。儲存該實際信號數據的關係如下。

整個文件的大對象

CREATE TABLE BlobFile (
   eeg_id INTEGER PRIMARY KEY,
   eeg_oid OID NOT NULL
);

每個通道的 SMALLINT 數組

CREATE TABLE EpochChannelArray (
   eeg_id INT NOT NULL,
   epoch INT NOT NULL,
   channel INT,
   signal SMALLINT[] NOT NULL,
   PRIMARY KEY (eeg_id, epoch, channel)
);

每個 epoch 中每個通道的 BYTEA

CREATE TABLE EpochChannelBytea (
   eeg_id INT NOT NULL,
   epoch INT NOT NULL,
   channel INT,
   signal BYTEA NOT NULL,
   PRIMARY KEY (eeg_id, epoch, channel)
);

每個 epoch 的 SMALLINT 2D 數組

CREATE TABLE EpochArray (
   eeg_id INT NOT NULL,
   epoch INT NOT NULL,
   signals SMALLINT[][] NOT NULL,
   PRIMARY KEY (eeg_id, epoch)
);

每個時代的 BYTEA 數組

CREATE TABLE EpochBytea (
   eeg_id INT NOT NULL,
   epoch INT NOT NULL,
   signals BYTEA NOT NULL,
   PRIMARY KEY (eeg_id, epoch)
);

然後我通過 Java JDBC 將選擇的 EDF 文件導入到每個關係中，並比較每次上傳後數據庫大小的增長。

這些文件是：

• 文件 A：16 個通道的 2706 個 epoch，每個通道 1024 個樣本（每個 epoch 16385 個樣本），85 MB
• 文件 B：18 個通道的 11897 個 epoch，每個通道 1024 個樣本（每個 epoch 18432 個樣本），418 MB
• 文件 C：20 個通道的 11746 個 epoch，每個通道 64 到 1024 個樣本（每個 epoch 17088 個樣本），382 MB

就儲存成本而言，以下是每種情況下佔用的大小（以 MB 為單位）：

相對於原始文件大小，大對像大約大 30-35%。相比之下，將每個 epoch 儲存為 BYTEA 或 SMALLINT

$$ $$$$ $$大了不到 10%。將每個通道儲存為單獨的元組會增加 40%，如 BYTEA 或 SMALLINT$$ $$，所以並不比儲存為大對像差多少。 我最初沒有意識到的一件事是PostgreSQL中的“多維數組必須具有每個維度的匹配範圍” 。這意味著該SMALLINT[][]表示僅在一個 epoch 中的所有通道具有相同數量的樣本時才有效。因此，文件 C 無法處理該EpochArray關係。

在訪問成本方面，我沒有玩過這個，但至少在最初插入數據方面，最快的表示是EpochBytea和BlobFile，EpochChannelArray最慢的，大約是前兩個的 3 倍。

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