Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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)

Comments

Popular posts from this blog

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

Script to kill ALL IDLE Connection In postgreSQL

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform

How to configure Replication Manager (repmgr) ?