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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
|
This module contains several functions that report the on-disk size of a
given database object in bytes:
int8 database_size(name)
int8 relation_size(text)
int8 indexes_size(text)
int8 total_relation_size(text)
int8 pg_database_size(oid)
int8 pg_relation_size(oid)
int8 pg_tablespace_size(oid)
text pg_size_pretty(int8)
setof record relation_size_components(text)
The first four functions take the name of the object (possibly
schema-qualified for the latter three) and returns the size of the
on-disk files in bytes.
SELECT database_size('template1');
SELECT relation_size('pg_class');
SELECT indexes_size('pg_class');
SELECT total_relation_size('pg_class');
These functions take object OIDs:
SELECT pg_database_size(1); -- template1 database
SELECT pg_relation_size(1259); -- pg_class table size
SELECT pg_tablespace_size(1663); -- pg_default tablespace
The indexes_size() function returns the total size of the indices for a
relation, including any toasted indices.
The total_relation_size() function returns the total size of the relation,
all its indices, and any toasted data.
Please note that relation_size and pg_relation_size report only the size of
the selected relation itself; any related indexes or toast tables are not
counted. To obtain the total size of a table including all indices and
toasted data, use total_relation_size().
The last function, relation_size_components(), returns a set of rows
showing the sizes of the component relations constituting the input
relation.
Examples
========
I've loaded the following table with a little less than 3 MB of data for
illustration:
create table fat ( id serial, data varchar );
create index fat_uidx on fat (id);
create index fat_idx on fat (data);
You can retrieve a rowset containing constituent sizes as follows:
# SELECT relation_size_components('fat');
relation_size_components
----------------------------------------------------
(2088960,65536,2891776,fat,r,59383,59383)
(32768,704512,737280,pg_toast_59383,t,59386,59386)
(0,32768,32768,pg_toast_59383_index,i,59388,59388)
(0,2039808,2039808,fat_idx,i,59389,59389)
(0,49152,49152,fat_uidx,i,59911,59911)
(5 rows)
To see a more readable output of the rowset:
SELECT *
FROM relation_size_components('fat') AS (idxsize BIGINT,
datasize BIGINT,
totalsize BIGINT,
relname NAME,
kind "char",
relid OID,
node OID)
ORDER BY totalsize;
idxsize | datasize | totalsize | relname | kind | relid | node
---------+----------+-----------+----------------------+------+-------+-------
0 | 32768 | 32768 | pg_toast_59383_index | i | 59388 | 59388
0 | 49152 | 49152 | fat_uidx | i | 59911 | 59911
32768 | 704512 | 737280 | pg_toast_59383 | t | 59386 | 59386
0 | 2039808 | 2039808 | fat_idx | i | 59389 | 59389
2088960 | 65536 | 2891776 | fat | r | 59383 | 59383
(5 rows)
To see the sum total size of a relation:
# select total_relation_size('fat');
total_relation_size
-------------------------
2891776
(1 row)
To see just the size of the uncompressed relation data:
# select relation_size('fat');
relation_size
---------------
65536
(1 row)
To see the size of all related indices:
# select indexes_size('fat');
indexes_size
--------------
2088960
(1 row)
To install, just run make; make install. Then load the functions
into any database using dbsize.sql.
|
