pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCalculating 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