pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Calculating size of the database tables
4+ messages / 3 participants
[nested] [flat]

* Calculating size of the database tables
@ 2004-06-10 10:27  akuppachi@hss.hns.com
  0 siblings, 1 reply; 4+ messages in thread

From: akuppachi@hss.hns.com @ 2004-06-10 10:27 UTC (permalink / raw)
  To: pgsql-admin





Hi,

My application uses several database tables on Postgres backend and either
a postgres or an ODBC front-end. I need to calculate the total hard disk
space occupied by the database for my application. I started out by
calculating the size of each record (row) in each table and multiplying
that with the max. expected number of entries to get this data, but
realized soon that this will not give me the full size, since many of the
tables are indexed, so index size must also be considered. Apart from that,
i read somewhere that Postgres stores 4 extra bytes per character field.

I would appreciate any pointers to finding out the correct size occupied by
my database tables. Will the physical size of the "data" folder (as given
by 'ls' command) give me the true size, or is there any other sql command
that i can use to get this information?  I am using PostgreSQL, version
7.4.1 server libraries.

TIA,
Aparna.




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

* Re: Calculating size of the database tables
@ 2004-06-10 10:55  Devrim GUNDUZ <devrim@gunduz.org>
  parent: akuppachi@hss.hns.com
  0 siblings, 1 reply; 4+ messages in thread

From: Devrim GUNDUZ @ 2004-06-10 10:55 UTC (permalink / raw)
  To: akuppachi@hss.hns.com; +Cc: pgsql-admin

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Hi,

On Thu, 10 Jun 2004 akuppachi@hss.hns.com wrote:

<snip> 
> I would appreciate any pointers to finding out the correct size occupied by
> my database tables. Will the physical size of the "data" folder (as given
> by 'ls' command) give me the true size, or is there any other sql command
> that i can use to get this information?  I am using PostgreSQL, version
> 7.4.1 server libraries.

Please see contrib/dbsize. From README file:

"
This module contains two functions that report the size of a given
database or relation.  E.g.,
                                                                                
SELECT database_size('template1');
SELECT relation_size('pg_class');
                                                                                
These functions report the actual file system space.  Thus, users can
avoid digging through the details of the database directories.
"

Regards,
- -- 
Devrim GUNDUZ	       
devrim~gunduz.org				devrim.gunduz~linux.org.tr 
			http://www.tdmsoft.com
			http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFAyD48tl86P3SPfQ4RAu+dAJ9L4sfyaR9VRAA9jATcHxB/p9+lLQCg4/3a
jlDyuD1rOxNrij7UqX5xuKs=
=B0Mo
-----END PGP SIGNATURE-----




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

* Re: Calculating size of the database tables
@ 2004-06-11 04:10  akuppachi@hss.hns.com
  parent: Devrim GUNDUZ <devrim@gunduz.org>
  0 siblings, 1 reply; 4+ messages in thread

From: akuppachi@hss.hns.com @ 2004-06-11 04:10 UTC (permalink / raw)
  To: Devrim GUNDUZ <devrim@gunduz.org>; +Cc: pgsql-admin; pgsql-admin-owner@postgresql.org





Hi,

Thanks for the response. However, the README.dbsize says that "When
computing the size of a table, it does not include TOAST or index disk
space." So dbsize would not be giving me the total diskspace occupied by my
tables, right ? I need to know the full disk space so i can do some hard
disk size calculations. Is there any other way to do this?

TIA,
Aparna.




                                                                           
             Devrim GUNDUZ                                                 
             <devrim@gunduz.or                                             
             g>                                                         To 
             Sent by:                  akuppachi@hss.hns.com               
             pgsql-admin-owner                                          cc 
             @postgresql.org           pgsql-admin@postgresql.org          
                                                                   Subject 
                                       Re: [ADMIN] Calculating size of the 
             06/10/04 04:25 PM         database tables                     
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           




-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Hi,

On Thu, 10 Jun 2004 akuppachi@hss.hns.com wrote:

<snip>
> I would appreciate any pointers to finding out the correct size occupied
by
> my database tables. Will the physical size of the "data" folder (as given
> by 'ls' command) give me the true size, or is there any other sql command
> that i can use to get this information?  I am using PostgreSQL, version
> 7.4.1 server libraries.

Please see contrib/dbsize. From README file:

"
This module contains two functions that report the size of a given
database or relation.  E.g.,

SELECT database_size('template1');
SELECT relation_size('pg_class');

These functions report the actual file system space.  Thus, users can
avoid digging through the details of the database directories.
"

Regards,
- --
Devrim GUNDUZ
devrim~gunduz.org
devrim.gunduz~linux.org.tr
                                     http://www.tdmsoft.com
                                     http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFAyD48tl86P3SPfQ4RAu+dAJ9L4sfyaR9VRAA9jATcHxB/p9+lLQCg4/3a
jlDyuD1rOxNrij7UqX5xuKs=
=B0Mo
-----END PGP SIGNATURE-----


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings





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

* Re: Calculating size of the database tables
@ 2004-06-11 16:23  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: akuppachi@hss.hns.com
  0 siblings, 0 replies; 4+ messages in thread

From: Bruce Momjian @ 2004-06-11 16:23 UTC (permalink / raw)
  To: akuppachi@hss.hns.com; +Cc: Devrim GUNDUZ <devrim@gunduz.org>; pgsql-admin; pgsql-admin-owner@postgresql.org


Have you read the documentation section on managing disk space?

---------------------------------------------------------------------------

akuppachi@hss.hns.com wrote:
[ There is text before PGP section. ]
> 
> 
> 
> 
> Hi,
> 
> Thanks for the response. However, the README.dbsize says that "When
> computing the size of a table, it does not include TOAST or index disk
> space." So dbsize would not be giving me the total diskspace occupied by my
> tables, right ? I need to know the full disk space so i can do some hard
> disk size calculations. Is there any other way to do this?
> 
> TIA,
> Aparna.
> 
> 
> 
> 
>                                                                            
>              Devrim GUNDUZ                                                 
>              <devrim@gunduz.or                                             
>              g>                                                         To 
>              Sent by:                  akuppachi@hss.hns.com               
>              pgsql-admin-owner                                          cc 
>              @postgresql.org           pgsql-admin@postgresql.org          
>                                                                    Subject 
>                                        Re: [ADMIN] Calculating size of the 
>              06/10/04 04:25 PM         database tables                     
>                                                                            
>                                                                            
>                                                                            
>                                                                            
>                                                                            
>                                                                            
> 
> 
> 
> 
[ PGP not available, raw data follows ]
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> 
> Hi,
> 
> On Thu, 10 Jun 2004 akuppachi@hss.hns.com wrote:
> 
> <snip>
> > I would appreciate any pointers to finding out the correct size occupied
> by
> > my database tables. Will the physical size of the "data" folder (as given
> > by 'ls' command) give me the true size, or is there any other sql command
> > that i can use to get this information?  I am using PostgreSQL, version
> > 7.4.1 server libraries.
> 
> Please see contrib/dbsize. From README file:
> 
> "
> This module contains two functions that report the size of a given
> database or relation.  E.g.,
> 
> SELECT database_size('template1');
> SELECT relation_size('pg_class');
> 
> These functions report the actual file system space.  Thus, users can
> avoid digging through the details of the database directories.
> "
> 
> Regards,
> - --
> Devrim GUNDUZ
> devrim~gunduz.org
> devrim.gunduz~linux.org.tr
>                                      http://www.tdmsoft.com
>                                      http://www.gunduz.org
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.1 (GNU/Linux)
> 
> iD8DBQFAyD48tl86P3SPfQ4RAu+dAJ9L4sfyaR9VRAA9jATcHxB/p9+lLQCg4/3a
> jlDyuD1rOxNrij7UqX5xuKs=
> =B0Mo
> -----END PGP SIGNATURE-----
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 
[ End of raw data]

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073



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


end of thread, other threads:[~2004-06-11 16:23 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-10 10:27 Calculating size of the database tables akuppachi@hss.hns.com
2004-06-10 10:55 ` Devrim GUNDUZ <devrim@gunduz.org>
2004-06-11 04:10   ` akuppachi@hss.hns.com
2004-06-11 16:23     ` Bruce Momjian <pgman@candle.pha.pa.us>

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