Calculating size of the database tables

[a***@hss.hns.com]

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.


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Devrim GUNDUZ]

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


Hi,

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

<snip>

Post by a***@hss.hns.com
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

[a***@hss.hns.com]

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
<***@gunduz.or
g> To
Sent by: ***@hss.hns.com
pgsql-admin-owner cc
@postgresql.org pgsql-***@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 ***@hss.hns.com wrote:

<snip>

Post by a***@hss.hns.com
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

[Bruce Momjian]

Have you read the documentation section on managing disk space?

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

***@hss.hns.com wrote:
[ There is text before PGP section. ]

Post by a***@hss.hns.com
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
g> To
pgsql-admin-owner cc
@postgresql.org pgsql-***@postgresql.org
Subject
Re: [ADMIN] Calculating size of the
06/10/04 04:25 PM database tables

[ PGP not available, raw data follows ]

Post by a***@hss.hns.com
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi,
<snip>

Post by a***@hss.hns.com
I would appreciate any pointers to finding out the correct size occupied

by

Post by a***@hss.hns.com
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.

"
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]