>>>>> "SD" == Shridhar Daithankar <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
