主页 / dba / 问题 / 311171
Accepted
Thomas
Asked: 2022-04-21 12:15:52 +0800 CST

更多列与更多行以优化存储和性能？

  • 772

我有一个具有这种结构的表：

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 如何在内部组织数据，以及是否会一次检索一整行（这是我的假设），然后提取其中的一部分，等等......

任何建议将不胜感激。

database-design postgresql

1 个回答

  1. Best Answer

    随着时间的推移，这将是很多行！

    基本

    是的，在存储和性能方面，将 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 秒输入一次，每天最多 6*60*24 = 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取决于实际长度，通常更大）。看：

    贮存

    这将占据：

    • (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 数据页的最小开销，并且没有死元组的开销（从不更新）。

    细节：

    如果我们可以在(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值比较！看：

    表现

    上的 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)))
    • 2

相关问题