Find Out Table Size,Database Size,Tablespace Size,

Database Object Size Functions:-

The functions shows to calculate the disk space usage of database objects.

pg_column_size(any):- Number of bytes used to store a particular value (possibly compressed)

pg_database_size(oid):- Disk space used by the database with the specified OID

pg_database_size(name):- Disk space used by the database with the specified name

pg_indexes_size(regclass):- Total disk space used by indexes attached to the specified table

pg_relation_size(relation regclass, fork text):- Disk space used by the specified fork ('main', 'fsm', 'vm', or 'init') of the specified table or index

pg_relation_size(relation regclass):- Shorthand for pg_relation_size(..., 'main')

pg_size_pretty(bigint):- Converts a size in bytes expressed as a 64-bit integer into a human-readable format with size units

pg_size_pretty(numeric):- Converts a size in bytes expressed as a numeric value into a human-readable format with size units

pg_table_size(regclass):- Disk space used by the specified table, excluding indexes (but including TOAST, free space map, and visibility map)

pg_tablespace_size(oid):- Disk space used by the tablespace with the specified OID

pg_tablespace_size(name):- Disk space used by the tablespace with the specified name

pg_total_relation_size(regclass):- Total disk space used by the specified table, including all indexes and TOAST data

The storage space (in bytes) for one specific table:

ads=# select pg_relation_size('size_test');

pg_relation_size

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

5668864

(1 row)

Same query, but with the result in a human-readable format:

ads=# select pg_size_pretty(pg_relation_size('size_test'));

pg_size_pretty

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

5536 kB(1 row)

Remember that this is only the size of the table, not included an index or additional stuff.

The size including any index can be found out with:

ads=# select pg_size_pretty(pg_total_relation_size('size_test'));

pg_size_pretty

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

7656 kB

(1 row)

The size of a complete database:

ads=# select pg_size_pretty(pg_database_size('ads'));

pg_size_pretty

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

11 MB

(1 row)

You can even find out, how much space a specific value needs:

ads=# select pg_column_size(5::smallint);

pg_column_size

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

2

(1 row)

ads=# select pg_column_size(5::int);

pg_column_size

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

4

(1 row)

postgres=# SELECT pg_size_pretty(pg_tablespace_size('my_tablespace'));

SELECT pg_relation_filepath('tablename');

ads=# select length('This is a string'::varchar);

ads=# select pg_column_size('This is a string'::varchar);

ads=# select pg_column_size(5::bigint);

ads=# select pg_column_size(5::bigint);

pg_column_size

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

8

(1 row)

ads=# select pg_column_size('This is a string'::varchar);

pg_column_size

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

20

(1 row)

ads=# select length('This is a string'::varchar);

length

--------

16

(1 row)

Viewing the path of a relation

SELECT pg_relation_filepath('tablename');

size of a postgresql tablespace

postgres=# SELECT pg_size_pretty(pg_tablespace_size('my_tablespace'));

pg_size_pretty

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

118 MB

(1 row)