How to shrink pg_toast table?

Question

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?

 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)

Answer 1

You can use one of the two types of vacuuming: standard or 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.

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.

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 autovacuum and use standard VACUUM more often on my tables which are updated frequently.

If you need to use VACUUM FULL, you should do it when your users are less active. Also, do not turn off autovacuum.

You can get some additional information by adding verbose to your commands:

VACUUM FULL VERBOSE table_name;

Answer 2

Try the following:

vacuum full