对于对齐优化的表比原来的表大-为什么？

Question

在另一个问题中，我了解到我应该从我的一个表中优化布局，以节省空间并具有更好的性能。我这样做了，但最终得到了一个比以前更大的桌子，性能也没有改变。当然我做了VACUUM ANALYZE。怎么来的？

(我看到，如果只对单个列进行索引，索引大小不会改变。)

这是我来自的表(我添加了大小+填充)：

                               Table "public.treenode"
    Column     |           Type           | Size |          Modifiers
---------------+--------------------------+------+-------------------------------
 id            | bigint                   | 8    | not null default nextval( ...
 user_id       | integer                  | 4+4  | not null
 creation_time | timestamp with time zone | 8    | not null default now()
 edition_time  | timestamp with time zone | 8    | not null default now()
 project_id    | integer                  | 4    | not null
 location      | real3d                   | 36   | not null
 editor_id     | integer                  | 4+4  |
 parent_id     | bigint                   | 8    |
 radius        | real                     | 4    | not null default 0
 confidence    | smallint                 | 2    | not null default 5
 skeleton_id   | integer                  | 4    | not null

将real3d定义为

CREATE TYPE real3d AS (
  x real,
  y real,
  z real);

我将此布局更改为：

                                Table "public.treenode_new"
    Column     |           Type           | Size |            Modifiers
---------------+--------------------------+------+--------------------------------
 id            | bigint                   | 8    | not null default nextval(' ...
 project_id    | integer                  | 4    | not null
 location_x    | real                     | 4    | not null
 location_y    | real                     | 4    | not null
 location_z    | real                     | 4    | not null
 editor_id     | integer                  | 4    | not null
 user_id       | integer                  | 4    | not null
 creation_time | timestamp with time zone | 8    | not null default now()
 edition_time  | timestamp with time zone | 8    | not null default now()
 skeleton_id   | integer                  | 4    | not null
 radius        | real                     | 4    | not null default 0
 confidence    | real                     | 4+4  | not null default 5
 parent_id     | bigint                   | 8    |

如果我没有弄错，我应该每一行保存66个字节(138是原始行，72是新行)。然而，这种情况并没有发生:在这些表中有7604913，原始表的大小为1020 MB。新表的大小为1159 MB。我用pg_size_pretty(pg_relation_size('<tablename>'))来测量尺寸。那我错过了什么？

注意:除了最后四列之外，所有列都是从另一个表继承的(当然，我也不得不更改布局)。

更新:按照Erwin的建议运行VACUUM FULL之后，新表只需要734 MB。

Answer 1

物理表的大小通常不会通过运行VACUUM (或VACUUM ANALYZE)来减少(除了从表末尾对可移动页进行机会主义的修剪除外)。您需要运行真空满来实际缩小表。

如果表上有写负载，这不一定是您想要定期执行的。死行为更新在同一数据页上放置更新行版本提供了回旋空间，从而允许更好的性能。缩小和增长关系的物理表也是有代价的。另外，VACUUM FULL在表上取出一个独占锁。

这就是为什么自动真空只运行VACUUM (和ANALYZE)而不是VACUUM FULL。

但是，只读(或大部分读)表最好保持在最小大小。对于表定义的更改(或其他原因)，过度膨胀也最好立即消除。

在两个版本的表上尝试VACUUM FULL，然后再测量一次。差别应该会显现出来。

您还可以尝试我的多个测试查询获取有关行/表大小的更多信息。