Re: Commercial postgresql

On 2 Sep 2003 at 10:19, Vivek Khera wrote:

> >>>>> "SD" == Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes:
>
> >> second largest table, and 5 per index on the third largest, then about
> >> 90 seconds total for the rest of the tables ;-)
>
> SD> Umm.. Since you have only 2.7GB of data, all inclusive, would it
> SD> be real downtime if you reindex in a transaction, assuming the
> SD> "downtime" was not due to crunch of IO bandwidth..
>
> Reindexing a table takes an exclusive table lock.  If I did it inside
> a transaction, wouldn't it still take that lock and block out all
> other access?

Well, you donm't need to reindex as such. You can create a new index from
scratch and drop the old one inside a transaction.

That will be perfectly non-blocking I believe..

> I just did one index at a time, waited a few minutes did the next for
> my big tables, than just reindexed the others all in a row.  Last time
> I did this must have been 9 or 10 months.  One index went from 500000
> pages to under 220000.  Right now the system is screamingly fast.
>
> Perhaps I need to write an 'auto_reindex' script to notice when this
> is necessary and schedule one to run at the wee hours in the morning
> at the end of the week...

Once again, with 7.4, not needed anymore..

Bye
 Shridhar

--
Anthony's Law of the Workshop:    Any tool when dropped, will roll into the least accessible    corner of the
workshop.Corollary:   On the way to the corner, any dropped tool will first strike    your toes.