減少簡單 PostgreSQL 數據庫中每行的表大小字節數

我在 postgresql 11 中有一個非常簡單的 3 列表。它是時間序列數據，表可以包含數十億行。我擔心我的表大小和總大小，並希望優化我的設計以提高字節/行。

我已經找到了幾個關於這個主題的非常有用的問題和答案

測量 PostgreSQL 表行的大小

為讀取性能配置 PostgreSQL

通過執行這些討論中顯示的一些查詢，我相信還有改進的餘地，但我對做出這些改進的理解還不夠：)

我的創建腳本如下：

-- table
CREATE TABLE public.vector_events
(
   vector_stream_id integer NOT NULL,
   event_time timestamp without time zone NOT NULL,
   event_data0 real NOT NULL
)
WITH (
   OIDS = FALSE
)
TABLESPACE pg_default;

-- index
CREATE INDEX vector_events_stream_id_event_time_index
ON public.vector_events USING btree
(vector_stream_id, event_time DESC)
TABLESPACE pg_default;

我相信我的列寬是最佳的——vector_stream_id 可能超過 100000，事件時間需要毫秒精度，並且我們的數據可以儲存在浮點數中。

我選擇索引是因為我們的查詢只會是以下形式：

SELECT event_time, event_data 
FROM vector_events 
WHERE stream_id=@streamId 
AND event_time >= @lowerBound 
-- (optionally with upper bound) AND event_time <= @upperBound 
ORDER BY event_time DESC -- (sometimes ASC)

當表至少有一百萬行（可能是數億行）時，上述查詢必須是高效的。TBH 選擇二叉樹索引是一個最好的猜測。

如果我使用 Erwin Brandstetter 的查詢來檢查表大小：（為簡潔起見省略了查詢，但在此處找到：Answer to ‘Measure the size of a PostgreSQL table row’）我得到以下資訊（這是來自一個較小的範例表）：

metric                            bytes/ct|bytes_pretty|bytes_per_row
core_relation_size                9076736   8864 kB     52
visibility_map                    8192      8192 bytes  0
free_space_map                    24576     24 kB       0
table_size_incl_toast             9109504   8896 kB     52
indexes_size                      9256960   9040 kB     53
total_size_incl_toast_and_indexes 18366464  18 MB       106
live_rows_in_text_representation  5685353   5552kB      32
------------------------------
row_count                         172800
live_tuples                       172800
dead_tuples                       0

一個簡單的表格視圖會說我有一個 int（4 個字節）、一個沒有 tz 的時間戳（8 個字節）和一個浮點數（4 個字節），所以 16 個字節的實際數據。

我知道這不是那麼簡單，但是 52 字節的表大小似乎太大了。

此外，索引大小甚至更大，為 53 字節（這只是索引，它不包括事件數據，對吧？）

所以我每行的總大小為 105 字節 - 肯定有我可以做些什麼來改進它？

我似乎能夠通過應用這種“俄羅斯方塊”技術（首先放置更寬的列）來節省幾個字節（大約 8 個字節），將我的列順序更改為 event_time、stream_id、event_data 不過，我怎樣才能在下面得到這個97 字節？對於設計良好的表和索引，我應該期望什麼大小？

筆記：

目前我在 Windows 上使用 postgresql 11，我正在準備一個 linux 盒子進行比較。

我的“真實”數據庫正在使用 timescaledb，但我在普通的 postgresql 表中看到了相同的表大小/索引大小模式，所以我相信表大小過大的原因在於我的 postgresql 模式或索引設計。（時間尺度會將我的數十億個事件拆分為每個包含數百萬個的塊表，但我對架構和索引的選擇對於有效的磁碟使用和性能仍然至關重要）我希望我也可以改進伺服器配置，但首先我只想以獲得最佳的桌子大小。

我現在的 3 個考慮因素是（按重要性排序）

1. 從包含數百萬的表中獲取數万行時讀取性能。也聚合查詢。
2. 磁碟使用，隨著事件總數達到數十億，這變得非常昂貴。
3. 寫入性能，通常按任何流的時間順序排列，儘管某些流可能落後於其他流，有時我們可能會回填數據。

處理此類問題的最佳方法是衡量：

CREATE TABLE public.vector_events (
  vector_stream_id integer NOT NULL,
  event_time timestamp without time zone NOT NULL,
  event_data0 real NOT NULL
);

INSERT INTO vector_events
SELECT i,
      current_timestamp + i * INTERVAL '1 second',
      3.1415
FROM generate_series(1, 200000) AS i;

SELECT pg_relation_size('public.vector_events');

pg_total_relation_size 
------------------------
              10461184
(1 row)

test=> SELECT 10461184 / 200000.0;

     ?column?       
---------------------
52.3059200000000000
(1 row)

所以每行 52 個字節幾乎是正確的。

關於指數：

CREATE INDEX vector_events_stream_id_event_time_index                          
ON public.vector_events (vector_stream_id, event_time DESC);

SELECT pg_total_relation_size('vector_events_stream_id_event_time_index');                      

pg_total_relation_size 
------------------------
               6324224
(1 row)

test=> SELECT 6324224 / 200000.0;

     ?column?       
---------------------
31.6211200000000000
(1 row)

這對我來說似乎很正常。

DELETE如果您的工作負載中有s 和s，您可以預期數據最終會佔用更多空間UPDATE，因為這些會導致一定的內部碎片（膨脹）；特別是索引可以變成兩倍或三倍。

要回答您的問題：

1. 您的索引非常適合您的查詢，無論您聲明它ASC還是DESC. 所以訪問速度應該是最佳的。
2. 正如您所說，您可以通過event_time將第一行或最後一行保存為每行 4 個字節。這就是可能的極限。
3. 為獲得良好的寫入性能，請使用快速磁碟並設置為max_wal_size高。

您將需要表的主鍵索引。最便宜的方法是使用你的索引（如果它可以設置為UNIQUE），但是你必須擺脫DESC.

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