Re: Performance, vacuum and reclaiming space, fsm

>>>>> "SD" == Shridhar Daithankar <shridhar_daithankar@persistent.co.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@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/