我在mac osx上运行postgres 9.3,我有一个失控的数据库.我曾经有一个表,其中有一列存储了大量数据.然后我注意到,仅仅因为有一个pg_toast表,db的大小就增长到了19gb左右.然后我移除上面提到的列,并运行真空,以便使db再次变小,但它保持不变.那么如何缩小数据库的大小呢?
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
路由表的 struct
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)