table size, database size

I have seen this question more than once in the past: “How much storage space do I need for my table?”

PostgreSQL can give you this information.

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

1
2
3
4
5
ads=# select pg_relation_size('size_test');
pg_relation_size
------------------
 5668864
 (1 row)

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

1
2
3
4
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:

1
2
3
4
5
ads=# select pg_size_pretty(pg_total_relation_size('size_test'));
 pg_size_pretty
----------------
 7656 kB
(1 row)

The size of a complete database:

1
2
3
4
5
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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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)

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)

The 4 byte difference between the column size and the string length is the storage overhead needed for varchar columns.

The size for a tablespace can be found out with pg_tablespace_size(), but I don’t have a machine with configured tablespace at hand to show an example.

Categories: [Code-Snippets] [Postgresql-News] [Sql]

Tags: [Code-Snippets] [Postgresql] [Psql] [Sql]

Friends