如何在PostgreSQL中回收未使用的LOB占用的空间 [英] How to reclaim space occupied by unused LOBs in PostgreSQL

问题描述

我有一个运行在PostgreSQL 8.3上的中型数据库集群。

I have a medium sized database cluster running on PostgreSQL 8.3.

数据库将数字文件（图像）存储为LOB。

The database stores digital files (images) as LOBs.

数据库集群中有相当多的活动，许多内容正在不断地创建和删除。

There is a fair bit of activity in the database cluster, a lot of content is created and deleted in an ongoing manner.

即使应用程序托管OID的表，由应用程序正确维护（删除图像文件时），数据库群集的大小持续增长。

Even though the application table which hosts the OIDs, gets maintained properly by the application (when an image file is deleted), the size of the database cluster grows continuously.

自动清理处于活动状态所以应该不会发生。

Auto-vacuuming is active so this shouldn't happen.

推荐答案

当应用程序表中的行（包含OID）时，LOB不会从数据库中删除

LOBs are NOT deleted from the database when the rows of your application table (containing the OIDs) get deleted.

这也意味着VACUUM进程不会回收空间。

This also means that space will NOT be reclaimed by the VACUUM process.

为了摆脱未使用的LOB，您已经在数据库上运行了VACUUMLO。 Vacuumlo将从数据库中删除所有未引用的LOB。

In order to get rid of unused LOBs, you have run VACUUMLO on the databases. Vacuumlo will delete all of the unreferenced LOBs from a database.

示例调用：

vacuumlo -U postgres -W -v <database_name>

（我只包含-v来使vacuumlo更加冗长，以便您看到有多少LOB

(I only included the -v to make vacuumlo a bit more verbose so that you see how many LOBs it removes)

在vacuumlo删除LOB之后，您可以运行VACUUM FULL（或运行自动真空过程）。

After vacuumlo has deleted the LOBs, you can run VACUUM FULL (or let the auto-vacuum process run).

这篇关于如何在PostgreSQL中回收未使用的LOB占用的空间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！