更多列與更多行以優化儲存和性能？

我有一個具有這種結構的表：

ticker         VARCHAR                     NOT NULL,
interval       VARCHAR                     NOT NULL,
ts             TIMESTAMP WITHOUT TIME ZONE NOT NULL,
price          FLOAT8                      NOT NULL,
UNIQUE (ticker, interval, ts)

有 40 個程式碼（最終將擴展到 130 個左右）和 8 個間隔。每 10 秒添加新行 (40 * 8) 作為大容量副本，這表示 115k 行/小時。它們只寫一次，從不修改。

讀取操作總是在相當大的時間範圍（多天）內完成，並為其請求一個程式碼和 3 個間隔，使用以下方法：

SELECT * FROM exchange.{tableName}
WHERE ticker = '{ticker}' AND \"interval\" IN ({intervalsText})
AND ts >= '{fromTime.Format}' AND ts < '{toTime.Format}'
ORDER BY ts

我的問題是，將所有間隔分組在每個行情中是否有益。像這樣：

ticker           VARCHAR                     NOT NULL,
ts               TIMESTAMP WITHOUT TIME ZONE NOT NULL,
price_interval0  FLOAT8                      NOT NULL,
price_interval1  FLOAT8                      NOT NULL,
...
price_interval7  FLOAT8                      NOT NULL,
UNIQUE (ticker, ts)

這意味著表中的行數減少了 8 倍，索引更小，但每個查詢可能需要載入整行以僅返回 3 個值並丟棄 5 個。

我不知道 Postgres 如何在內部組織數據，以及是否會一次檢索一整行（這是我的假設），然後提取其中的一部分，等等……

任何建議將不勝感激。

隨著時間的推移，這將是很多行！

基本

是的，在儲存和性能方面，將 8 行儲存float8在一行中將遠遠超過 8 行，每行 1 行。 但你可以做更多…float8

餐桌設計

優化儲存和性能：

CREATE TABLE ticker (
 ticker_id smallint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
, ticker text NOT NULL UNIQUE   
);

CREATE TABLE tbl (
 the_date        date     NOT NULL  -- columns in this order!
, timeslot        smallint NOT NULL
, ticker_id       smallint NOT NULL REFERENCES ticker
, price_interval0 int      NOT NULL
, price_interval1 int      NOT NULL
...
, price_interval7 int      NOT NULL
CONSTRAINT tbl_pkey PRIMARY KEY (ticker_id, the_date, timeslot);  -- columns in this order!
);

db<>fiddle here - 包括所有

解釋和輔助

每 10 秒輸入一次，每天最多 66024 = 8640 個不同的時隙。smallint範圍為 -2^15 到 2^15 的A可以輕鬆保持這一點。

當然，我們不會每次都儲存完整的股票名稱。smallint FK 列輕鬆涵蓋 40 - 130 個不同的程式碼，並引用一個ticker表。通常更好的儲存和性能：

• 索引：如果節點數相同，則整數與字元串的性能

天為date（4 個字節），一個時隙smallint（2 個字節）和一個smallint為股票程式碼 ID，按此順序排列佔用 8 個字節，沒有對齊填充！

不幸的是，我們不能同時完美地優化 PK 索引並產生 8 字節的對齊填充。儲存優化上的唯一污點。

為方便起見，您可以添加一個VIEW來獲取漂亮的數據：

CREATE VIEW tbl_pretty AS
SELECT ti.ticker, the_date + interval '10 sec' * timeslot AS ts, price_interval0, price_interval1
--   , price_interval2, ...
FROM   tbl
JOIN   ticker ti USING (ticker_id);

如您所見，此表達式會生成您的原始時間戳：

the_date + interval '10 sec' * timeslot

反向轉換將用於以下查詢：

trunc(EXTRACT(EPOCH FROM time '12:34:56'))::int / 10)

像“價格”這樣的貨幣價值不應儲存為浮點數。那是一把上膛的腳炮。使用numeric. 或者，由於我們正在優化儲存和性能，因此integer代表美分通常效果最好。這只是 4 個字節，而不是float8. （numeric取決於實際長度，通常更大）。看：

• 是否可以在 PostgreSQL 中為貨幣數據類型插入未格式化的數據
• PostgreSQL：貨幣應該使用哪種數據類型？

貯存

這將佔據：

• (24(tuple header) + 4(item identifier) + 4 + 2 + 2 + 4*8 + 4) =每個表行72個字節 - 根本沒有填充

（您對複合行的最初想法將佔用 (24 + 4 + (min. 8) + 8 + 8*8) = 每行 108 個字節或更多。）

• (8(index header) + 2 + 2(padding) + 4 + 2 + 6(padding)) =每個 PK 索引條目24字節

加上每 8kb 數據頁的最小成本，並且沒有死元組的成本（從不更新）。

細節：

• 理解 Postgres 行大小
• 在 PostgreSQL 中計算和節省空間
• 為讀取性能配置 PostgreSQL

如果我們可以在(the_date, timeslot, ticker_id). 但是我們需要它(ticker_id, the_date, timeslot)來最佳地支持您的查詢。範圍之前的平等。看：

• 複合索引是否也適用於第一個欄位的查詢？
• 多列索引和性能

詢問

您的查詢變為：

SELECT price_interval3, price_interval7  -- just the intervals you need
FROM   tbl
WHERE  ticker_id = (SELECT ticker_id FROM ticker WHERE ticker = 'ticker_3')
AND    (the_date, timeslot) >= (date '2022-04-20', trunc(EXTRACT(EPOCH FROM time '00:00:00'))::int / 10)
AND    (the_date, timeslot) <  (date '2022-04-20', trunc(EXTRACT(EPOCH FROM time '00:01:00'))::int / 10)
ORDER  BY the_date, timeslot;

或簡稱：

SELECT *
FROM   tbl
WHERE  ticker_id = 3
AND    (the_date, timeslot) >= ('2022-04-20', 0)
AND    (the_date, timeslot) <  ('2022-04-20', 6)
ORDER  BY the_date, timeslot;

注意使用ROW值比較！看：

• ‘WHERE (col1, col2) < (val1, val2)’ 的 SQL 語法術語
• 進行 > 或 < 比較的好方法是什麼，認為 NULL 首先或最後排序？

表現

上的 PK 索引完美地支持了這一點(ticker_id, the_date, timeslot)。不需要其他索引。你會得到一個類似的計劃：

Index Scan using tbl_pkey on tbl  (cost=0.27..8.29 rows=1 width=16)
 Index Cond: ((ticker_id = 3) AND (ROW(the_date, timeslot) >= ROW('2022-04-20'::date, 0)) AND (ROW(the_date, timeslot) < ROW('2022-04-20'::date, 6)))

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