时间戳

首页 > 解决方案 > Postgres表大小方式大于行总和

database - Postgres表大小方式大于行总和

问题描述

我想收集大量数据，因此需要非常高效的存储。现在我正在测试，我看到我的数据库表比它的行总和大得多，到目前为止我无法弄清楚为什么会这样。

该表具有以下列：

  1. bigint， 首要的关键
  2. numeric, 精度 10, 比例 7
  3. numeric, 精度 10, 比例 7
  4. timestamp without time zone
  5. timestamp without time zone
  6. bigint， 索引
  7. character varying
  8. json

我运行了以下查询来收集统计信息：

SELECT pg_size_pretty(pg_relation_size('table_name')); # 132MB
SELECT pg_size_pretty(pg_total_relation_size('table_name')); # 162MB
SELECT COUNT(*) FROM table_name; # 91706
SELECT pg_relation_size('table_name') / (SELECT COUNT(*) FROM table_name); # 1503
SELECT octet_length(table_name.*::text) FROM table_name ORDER BY 1 DESC LIMIT 1; # 312
SELECT AVG(octet_length(table_name.*::text)) FROM table_name; # 279.05

我现在在数据库中只有 ~91700 行——平均行大小为 279 字节——它们已经占用了 132MB（仅表数据）而不是 91700 * 279 字节 = ~25MB 的空间。生产系统上的行数应该达到数亿，而数据占用的空间是预期的五倍，这对我来说是一个巨大的问题，当我即将遇到实际限制时我不想解决.

一行中的大部分数据都是灵活的，不遵循固定的模式。它存储在 json 列中。由于 json 占用的空间比 jsonb 少，而且 json 列很少甚至永远不会被查询，这是可以接受的。每行大部分内容的缺失模式导致我最初从 MongoDB 开始。在 MongoDB 中进行测试时，我有大约 170 万个文档，占用了大约 660MB 的空间，包括索引。我认为我可以在 Postgres 中做得更好，但如果我的 Postgres 表线性增长，我将在 ~370000 行时达到收支平衡。

该数据库运行在一个共享的 ElephantSQL 实例上，统计数据显示有 86MB 的临时文件，总数据库大小为 172MB。

我有两个问题：

  1. 表大小和总表大小之间的差异为 30MB。除了索引，这还能是什么？（我假设这里不涉及临时文件）。此表上只有一个索引，它位于一bigint列上。
  2. 为什么实际平均行大小 (1503B) 比octet_length返回的平均行大小 (279,05B) 高五倍以上？

附加信息和示例：

我设置了一个专用的 ElephantSQL 实例并开始收集数据，但这里的比例更糟：

SELECT pg_size_pretty(pg_relation_size('table_name')); # 16MB
SELECT pg_size_pretty(pg_relation_size('table_name')); # 21MB
SELECT COUNT(*) FROM table_name; # 20672
SELECT pg_relation_size('table_name') / (SELECT COUNT(*) FROM table_name); # 819
SELECT AVG(octet_length(table_name.*::text)) FROM table_name; # 113.53

此处传入的非关系数据量实际上较低，因此行大小较小。在这种情况下，实际平均行大小是返回的平均行大小的七倍以上octet_length。这让我想到，每行的开销——无论它来自哪里——都是巨大的，每行包含的实际 json 数据越少，这两个指标之间的大小比率就越差。

标签： databasepostgresql

解决方案

PostgreSQL 的每行开销很大，但几乎没有这么大（除非大象 SQL 做了一些事情来进一步扩展它）。你的桌子一定非常臃肿。您可以通过运行 VACUUM FULL table_name 并查看它是否会缩小来确认这一点。

如果它臃肿，那将是由于您使用它的方式（例如，INSERT、UPDATE、DELETE 的模式）或您对它进行真空处理的方式。但你没有描述任何这些东西。

推荐阅读