postgresql - 如何缩小pg_toast表？

我在Mac OS X上的Postgres 9.3上运行，并且我的数据库已失去控制。我曾经有一个表，其中的一列存储大数据。然后我注意到由于pg_toast表的存在，db的大小增长到了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;

结果是

 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

路线表的结构

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)

最佳答案

请尝试以下操作:

vacuum full

关于postgresql - 如何缩小pg_toast表？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/28032850/