postgresql 如何缩小 pg_toast 表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28032850/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to shrink pg_toast table?
提问by dc10
I am running on postgres 9.3 on mac osx and I have a database which grew out of control. I used to have table which had one column which stored large data. Then I noticed that there the db size grew up to around 19gb just because of a pg_toast table. Then I remove the mentioned column and ran vacuum in order to get the db to a smaller size again, but it remained the same. So how can I shrink the database size?
我在 mac osx 上运行 postgres 9.3，我有一个失去控制的数据库。我曾经有一个表，其中有一列存储大数据。然后我注意到由于 pg_toast 表，数据库大小增加到 19gb 左右。然后我删除了提到的列并运行真空以使数据库再次变小，但它保持不变。那么如何缩小数据库大小呢？
SELECT nspname || '.' || relname AS "relation"
,pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 20;
results in
结果是
pg_toast.pg_toast_700305 | 18 GB
pg_toast.pg_toast_700305_index | 206 MB
public.catalog_hotelde_images | 122 MB
public.routes | 120 MB
VACUUM VERBOSE ANALYZE pg_toast.pg_toast_700305; INFO: vacuuming "pg_toast.pg_toast_700305"
INFO: index "pg_toast_700305_index" now contains 9601330 row versions in 26329 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.06s/0.02u sec elapsed 0.33 sec.
INFO: "pg_toast_700305": found 0 removable, 0 nonremovable row versions in 0 out of 2393157 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.06s/0.07u sec elapsed 0.37 sec.
VACUUM
structure of the routes table
路由表的结构
id serial NOT NULL,
origin_id integer,
destination_id integer,
total_time integer,
total_distance integer,
speed_id integer,
uid bigint,
created_at timestamp without time zone,
updated_at timestamp without time zone,
CONSTRAINT routes_pkey PRIMARY KEY (id)
采纳答案by Joe Love
Try the following:
请尝试以下操作：
vacuum full
回答by BatCat
You can use one of the two types of vacuuming: standardor full.
您可以使用两种类型的吸尘中的一种：标准或完全。
standard:
标准：
VACUUM table_name;
full:
满的：
VACUUM FULL table_name;
Keep in mind that VACUUM FULL locks the table it is working on until it's finished.
请记住， VACUUM FULL 会锁定它正在处理的表，直到它完成。
You may want to perform standard vacuum more frequently on your tables which have frequent upload/delete activity, it may not give you as much space as vacuum full does but you will be able to run operations like SELECT, INSERT, UPDATE and DELETE and it will take less time to complete.
您可能希望在频繁上载/删除活动的表上更频繁地执行标准真空，它可能不会为您提供像真空已满那样的空间，但您将能够运行诸如 SELECT、INSERT、UPDATE 和 DELETE 之类的操作，它将花费更少的时间来完成。
In my case, when pg_toast (along with other tables) got out of control, standard VACUUM made a slight difference but was not enough. I used VACUUM FULL to reclaim more disk space which was very slow on large relations. I decided to tune autovacuumand use standard VACUUM more often on my tables which are updated frequently.
就我而言，当 pg_toast（连同其他表）失控时，标准 VACUUM 会产生细微差别，但还不够。我使用 VACUUM FULL 来回收更多的磁盘空间，这在大型关系上非常慢。我决定调整 autovacuum并在我频繁更新的表上更频繁地使用标准 VACUUM。
If you need to use VACUUM FULL, you should do it when your users are less active. Also, do not turn off autovacuum.
如果你需要使用 VACUUM FULL，你应该在你的用户不太活跃的时候使用它。另外，不要关闭自动吸尘器。
You can get some additional information by adding verboseto your commands:
您可以通过在命令中添加详细信息来获取一些附加信息：
VACUUM FULL VERBOSE table_name;
