Re: Performance, vacuum and reclaiming space, fsm

>>>>> "SD" == Shridhar Daithankar <shridhar_daithankar(at)persistent(dot)co(dot)in> writes:

SD> If you have 150MB type of data as you said last time, you could
SD> take a pg_dump of database, drop the database and recreate it. By
SD> all chances it will take less time than compacting a database from
SD> 2GB to 150MB.

That's it? That's not so big of a disk footprint.

SD> Drop the indexes and recreate them. While creating the index, all
SD> the updates will be blocked anyways.

Be *very careful* doing this, especially with UNIQUE indexes on a live
system! My recommendation is to get a list of all indexes on your
system with \di in psql, then running "reindex index XXXX" per index.
Be sure to bump sort_mem beforehand. Here's a script I ran over the
weekend (during early morning low-usage time) on my system:

SET sort_mem = 131072;
SELECT NOW(); SELECT relname,relpages FROM pg_class WHERE relname LIKE 'user_list%' ORDER BY relname;
SELECT NOW(); REINDEX INDEX user_list_pkey ;
SELECT NOW(); REINDEX INDEX user_list_XXX ;
SELECT NOW(); REINDEX INDEX user_list_YYY ;
SELECT NOW(); SELECT relname,relpages FROM pg_class WHERE relname LIKE 'user_list%' ORDER BY relname;

The relpages used by the latter two indexes shrunk dramatically:

user_list_XXX | 109655
user_list_YYY | 69837

to

user_list_XXX | 57032
user_list_YYY | 30911

and disk usage went down quite a bit as well. Unfortunately, the pkey
reindex failed due to a deadlock being detected, but the XXX index is
most popular... This is my "hottest" table, so I reindex it about
once a month. My other "hot" table takes 45 minutes per index to
redo, so I try to avoid that until I *really* have to do it (about 6
months). I don't think you'll need a nightly reindex.

Of course, regular vacuums throughout the day on the busy talbes help
keep it from getting too fragmented.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera(at)kciLink(dot)com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/