>>>>> "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/
