pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Updating table, precautions?
2+ messages / 2 participants
[nested] [flat]

* Updating table, precautions?
@ 2005-04-22 21:50 Anjan Dave <adave@vantage.com>
  2005-04-22 22:33 ` Re: Updating table, precautions? Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Anjan Dave @ 2005-04-22 21:50 UTC (permalink / raw)
  To: pgsql-performance

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.
************************************************************************
******************

 

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Updating table, precautions?
  2005-04-22 21:50 Updating table, precautions? Anjan Dave <adave@vantage.com>
@ 2005-04-22 22:33 ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Josh Berkus @ 2005-04-22 22:33 UTC (permalink / raw)
  To: Anjan Dave <adave@vantage.com>; +Cc: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2005-04-22 22:33 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-22 21:50 Updating table, precautions? Anjan Dave <adave@vantage.com>
2005-04-22 22:33 ` Josh Berkus <josh@agliodbs.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox