我有一个表,我们称它为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的。
正如你所看到的,我添加了自动真空调谐来试图解决这个问题,但它没有帮助。
使用模式是大量的更新,最终改变时间戳。所以我想一定是自动吸尘器出了问题没赶上进度。这是一个非常大的数据库,自动真空可能很慢,但这太大了。
为什么会这样?有什么建议吗?不可能是索引太大了。
这种表的大小是CSV文件的两倍是正常的。
首先,数据被组织成8kB的块。有一个块头,一些空间通常必须保持空。还有更多的开销:每一行都有一个23字节的头。这将占大部分开销。除此之外,行和许多列必须与特定的内存边界对齐,这导致在列之前和列之间填充。详细信息请参见文档
加上三个索引,这很容易使空间需求翻倍。
您可以使用pg_relation_size()
来查找磁盘上索引的大小。使用pg_table_size()
计算不含索引的表的大小