Thread: Calculating size of the database tables

Calculating size of the database tables

From

akuppachi@hss.hns.com

Date:

10 June 2004, 10:27:50




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.

Re: Calculating size of the database tables

From

Devrim GUNDUZ

Date:

10 June 2004, 10:56:09

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

Re: Calculating size of the database tables

From

akuppachi@hss.hns.com

Date:

11 June 2004, 04:12:27

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

Re: Calculating size of the database tables

From

Bruce Momjian

Date:

11 June 2004, 16:24:08

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