postgresql – 更新为单个新列时,数据库大小加倍

概述我有一个相当简单的表用于驱动web映射应用程序的tile处理. Column | Type | Modifiers ---------------+--------------------------+----------------------------------------------- 我有一个相当简单的表用于驱动web映射应用程序的tile处理.

Column      |    Type                  |    ModifIErs                        ---------------+--------------------------+--------------------------------------------------------- ID            | integer                  | not null default                                             nextval('wmts_tiles_ID_seq'::regclass) tile_matrix   | integer                  |  rowx          | integer                  |  coly          | integer                  |  geom          | geometry(Geometry,27700) |  processed     | smallint                 | Indexes:   "wmts_tiles_pkey" PRIMARY KEY,btree (ID)   "wmts_tiles_wmts_tile_matrix_x_y" UNIQUE CONSTRAINT,btree (tile_matrix,rowx,coly)   "ix_spatial_wmts_tiles" gist (geom)   "ix_tile_matrix_processed" btree (tile_matrix,processed)

具有各种索引(一个空间)和如图所示的constaints.该表有2.4亿行,pg_relation_size和pg_total_relation_size表示该表为66 GB,其中一半是索引,一半是数据.

我添加了一个日期列,然后运行更新以填充它,

alter database wmts_tiles add column currency_date date;update wmts_tiles set currency_date = '2014-05-01'

在此之后,大小达到133 GB,即它增加了一倍.一旦我在桌面上运行VACUUM FulL,大小缩小到67 GB,即比以前大1GB,这是你在添加2.4亿行4字节字段(日期)后所期望的.

我知道在表中经常会有相当比例的死行,其中发生了大量的插入和删除,但为什么在单个更新下表的大小会翻倍并且我能做些什么来防止这种情况？请注意,此更新是唯一正在运行的事务,并且该表刚刚被转储并重新创建,因此数据页和索引在更新之前处于紧凑状态.

编辑：我已经看到这个问题,Why does my postgres table get much bigger under update?,我明白,虽然表正在更新,以支持MVCC表需要重写,我不明白的是为什么它保持两倍的大小,直到我明确运行VACUUM FulL .

其中大部分都在 this prior question之前.

它没有缩小的原因是Postgresql不知道你想要它.分配磁盘空间(增长表)然后重复释放(缩小表)是低效的. Postgresql更喜欢增长一个表,然后保留磁盘空间,将其标记为空并准备重新使用.

分配和发布周期不仅效率低下,而且OS也只允许在文件末尾释放空间*.因此,Postgresql必须将文件末尾的所有行移动到开始时现在可用空间,这是您在更新时可以写入的唯一位置.它无法执行此 *** 作,因为在更新事务提交之前,它无法覆盖任何旧数据.

如果您知道不再需要空间,可以使用VACUUM FulL压缩表格并释放空间.

autovacuum没有定期完成真空,部分原因是如果表必须再次扩展,可能会对性能造成很大影响,部分原因是因为真空充满了I / O密集(所以它会减慢其他所有内容)和部分因为vacuum full需要访问独占锁,以防止对表的任何并发访问. Postgresql需要一个增量表重新打包命令/功能,它还没有,因为这可以通过autovacuum实现.修补程序是受欢迎的…虽然这部分代码非常复杂,但正确的做法并不适合初学者.

*是的,我知道您可以将文件中的大区域标记为某些平台上的稀疏区域.随意提交Postgresql补丁.实际上你还是必须压缩,因为你没有在表中找到带有空闲页面的大区域.另外,您必须处理页眉.

总结

以上是内存溢出为你收集整理的postgresql – 更新为单个新列时,数据库大小加倍全部内容，希望文章能够帮你解决postgresql – 更新为单个新列时,数据库大小加倍所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。