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
Post a Comment