表大小比Postgres中的实际数据大得多

Question

我有一个表，我们称它为Roads(更改为不显示公司内容)。该表存储了地方之间非规范化的路径，模式如下:

CREATE TABLE public.roads (
id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
'from' varchar NOT NULL,
'to' varchar NOT NULL,
updated_at timestamp NOT NULL,
metadata_id int8 NULL,
CONSTRAINT roads_pkey PRIMARY KEY (id),
CONSTRAINT roads_from_to_key UNIQUE (from, to)
)
WITH (
autovacuum_vacuum_cost_delay=0,
autovacuum_vacuum_cost_limit=1500
);
CREATE INDEX from_idx ON public.roads USING btree ('from');
CREATE INDEX updated_at_idx ON public.roads USING btree (updated_at);
CREATE INDEX to_idx ON public.roads USING btree ('to');

在查询时，出于性能原因，位置是非规范化的。

如果我将所有数据导出为CSV，则文件仅为6MB。但是在DB上，这个表是12GB的。

正如你所看到的，我添加了自动真空调谐来试图解决这个问题，但它没有帮助。

使用模式是大量的更新，最终改变时间戳。所以我想一定是自动吸尘器出了问题没赶上进度。这是一个非常大的数据库，自动真空可能很慢，但这太大了。

为什么会这样?有什么建议吗?不可能是索引太大了。

Answer 1

这种表的大小是CSV文件的两倍是正常的。

首先，数据被组织成8kB的块。有一个块头，一些空间通常必须保持空。还有更多的开销:每一行都有一个23字节的头。这将占大部分开销。除此之外，行和许多列必须与特定的内存边界对齐，这导致在列之前和列之间填充。详细信息请参见文档

加上三个索引，这很容易使空间需求翻倍。

您可以使用pg_relation_size()来查找磁盘上索引的大小。使用pg_table_size()计算不含索引的表的大小