Thread: Updating table, precautions?

Updating table, precautions?

From

"Anjan Dave"

Date:

22 April 2005, 21:50:39

Hi there,

We need to update a table of about 1.2GB (and about 900k rows) size. I was wondering if I should let the regular cron job take care of clean up (vacuum db Mon-Sat, vacuum full on Sun, followed by Reindex script), or manually do this on the table followed by the update.

This is what I used to find the table size, which probably doesn’t include the index size. Is there a way to find out size of indexes?

select relpages * 8192 as size_in_bytes from pg_class where relnamespace = (select oid from pg_namespace where nspname = 'public') and relname = 'r_itemcategory';

Thanks,

Anjan

******************************************************************************************

This e-mail and any files transmitted with it are intended for the use of the

addressee(s) only and may be confidential and covered by the attorney/client

and other privileges.  If you received this e-mail in error, please notify the

sender; do not disclose, copy, distribute, or take any action in reliance on

the contents of this information; and delete it from your system. Any other

use of this e-mail is prohibited.

******************************************************************************************

Re: Updating table, precautions?

From

Josh Berkus

Date:

22 April 2005, 22:28:25

Anjan,

> This is what I used to find the table size, which probably doesn't
> include the index size. Is there a way to find out size of indexes?
>
> select relpages * 8192 as size_in_bytes from pg_class where relnamespace
> = (select oid from pg_namespace where nspname = 'public') and relname =
> 'r_itemcategory';

See the code in CVS in the "newsysviews" project in pgFoundry.   Andrew coded
up a nice pg_user_table_storage view which gives table, index and TOAST size.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco