時間序列表（多列）設計建議

我將時間序列數據儲存在 Postgresql 數據庫中。

我想知道以下兩種提議的設計模式的性能影響是什麼。

選項 1：具有時間戳和許多列（可能 100 列）的單個表，其中包含各種測量數據。

TABLE measurement (id, timestamp, measurementA, measurementB, measurementC ...) 

選項 2：

兩張桌子。一個帶有 id 和時間戳，另一個表包含對測量表的引用，然後是描述記錄數據的多個欄位。

TABLE measurement (id, timestamp)
TABLE measurement_data (id, measurement_id, description, type, value)

我喜歡選項二的想法。因為我的表結構更小更簡單。但是，我擔心查詢對錶的影響。對於像下面這樣的簡單查詢，是否會對性能造成巨大影響？

使用選項 1 設計

SELECT measurementA 
FROM measurement 
WHERE timestamp > X AND timestamp < Y

使用選項 2s 設計

SELECT value 
FROM measurement_data 
WHERE description = 'measurementA' 
 AND measurement_id IS IN (SELECT id 
                           FROM measurement 
                           WHERE timestamp > X AND timestamp < Y )

（type選項 1 中沒有表示選項 2。使比較有點不公平，但幾乎不重要。）

這兩個選項之間的主要區別是儲存大小。假設real您的測量和bigintID 的數據類型（由於缺乏資訊），選項 1的行大小是：

8 + 8 + 100 x 4 + 24 + 4 = 444 字節。

選項2也是如此：

8 + 8 + 24 + 4 = 表中的 44 個字節measurement。加上 …

(8 + 8 + 13 + 4 + 4 + 24 + 4) x 100 = 6500 字節measurement_data。

因此，選項 1 中的一行在選項 2 中總共有 6544 個字節（！） 。

另外，選項 2 中需要更多索引，其中一半的索引也比選項 1 大。似乎沒有希望了。大小事項。

OTOH 選項 2 的查詢僅檢索每次命中 65 字節的行measurement_data，但它增加了至少額外的僅索引掃描的成本measurement。仍然可能適用於大量點擊和聚集（更多考慮！）數據。並且更新應該更便宜，產生更少的表格膨脹 - 但這樣的設計通常不會看到很多更新。許多相關的考慮取決於全貌。

在任何情況下，放棄選項 2，它會一遍又一遍地冗餘儲存一些屬性。請考慮選項 3，一個經典的 m:n 關係：

TABLE measurement (measurement_id bigint PK, ts timestamp)
TABLE measurement_kind (measurement_kind_id int PK, type int?, description text)
TABLE measurement_data (measurement_id bigint, measurement_kind_id int, value real, PK (measurement_id, measurement_kind_id))

仍然是 44 字節measurement。加：

(8 + 4 + 4 + 24 + 4) * 100 = 4400 字節，measurement_data

所以在選項 3 中，選項 1 中的一行總共有 4444 字節。

（並且一次可忽略不計的 100 x (4 + 4 + 13 + 3 + 24 + 4) = 5200 字節–> 8k 迷你表measurement_kind。）

與之對應的等效查詢：

SELECT md.value 
FROM   measurement m
CROSS  JOIN (
  SELECT measurement_kind_id
  FROM   measurement_kind
  WHERE  description = 'measurementA'
  ) mk
JOIN   measurement_data md USING (measurement_id, measurement_kind_id)
WHERE  m.ts > X
AND    m.ts < Y;

簡歷：可能是選項 1，可能是選項 3，絕不是選項 2。

相關（建議！）：

• 如何在 PostgreSQL 中實現多對多關係？
• 為讀取性能配置 PostgreSQL

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