為時間序列優化表 Postgres 數據表

我有下表，它維護一個時間序列結果。該行僅在信號為真時變得相關，當信號為假時，它僅標記對於該特定時間戳我們得到了結果但它不是有效的，因此 res 和其他列僅包含空值。當信號為空時，它標誌著我們還沒有收到這個時間戳的結果。信號在本質上非常稀疏，只有不到 7% 的記錄才是正確的。此外，對該表的插入不是根據時間戳排序的，較早的日期可能會在以後到達。

CREATE TABLE public.res
(
pid integer NOT NULL,
aid integer NOT NULL,
cid integer NOT NULL,
"time" timestamp without time zone NOT NULL,
signal boolean,
price numeric,
res double precision[] NOT NULL,
...<Many more columns of numeric/numeric array data types>
CONSTRAINT res_pkey PRIMARY KEY (pid, aid, cid, "time")
)

該表可以包含數百萬條記錄，並且隨著我的數據庫的增長呈指數增長。我想優化這張表。所以有以下問題

• 每行的大小是否相同？或者由於該行僅在 Signal 為​​真時才有意義，它可以動態調整大小嗎？因此保持桌子的整體尺寸很小？最小行大小將包含 (pid,aid,cid,time,signal,price)，而最大行大小將另外包含 (res 和其餘列)？在 Postgres 中使用它的 dataTypes 可以做到這一點嗎？我不想創建單獨的表，因為當有數百萬條記錄時，執行時連接可能非常昂貴。
• 這個 SO 答案說“實際上 NULL 儲存對於最多 8 列的表是完全免費的。” , 我還有很多列
• 還有其他建議您可能必須處理此類問題嗎？
• 我讀到了timescaleDB，但是由於記錄不是按時間戳順序插入的，在這個案例中它比Postgres有什麼優勢嗎？

謝謝

如果您正在認真考慮切換數據庫系統，我可以告訴您，Microsoft SQL Server 已經具有適合您的案例的稀疏列的開箱即用功能。但我的建議是不要僅僅為了優化數據儲存而改變數據庫系統。PostgreSQL 本身就是一個非常強大的數據庫系統。

要回答您的問題，無論有多少列，NULL值都只佔用1 位數據，因此非常輕量級，因此可以準確反映您的稀疏性。

正如@JD 回答NULL的值在 PostgreSQL 中很便宜。所以它應該對你的案例有好處。

關於TimescaleDB：TimescaleDB 是 PostgreSQL 的擴展。與 PostgreSQL 的不同之處在於 TimescaleDB 自動將稱為超表的表分區為稱為塊的子表。塊仍然是 PostgreSQL 表，所以儲存NULL值是一樣的。

當大部分數據插入目前時間範圍時，對塊的分區是按時間範圍完成的，這有利於時間序列數據。TimescaleDB 支持舊數據的插入和回填。但是，總是在時間上隨機插入數據不會有利於 TimescaleDB。

TimescaleDB 在社區許可下對時間序列工作負載的其他優勢：

• 時間序列函式，例如time_bucket, time_bucket_gapfill;
• 連續聚合，允許在超表上連續實現聚合查詢；
• 壓縮，這大大減少了數據大小。但是，目前的實現不允許將新數據插入到壓縮塊中，這對於問題中的案例來說可能是不可接受的。要插入數據，必須先解壓縮，然後再壓縮。
• 保留策略，允許自動刪除舊數據。
• 支持分佈式超表，因此不同的塊儲存在不同的 TimescaleDB 實例上。它的目前版本有很多限制。

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