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:
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)
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.
PostgreSQL can give you this information.
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)
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.
Trackbacks
Perl-Blog on : PostgreSQL: Speicherplatz einer Tabelle
Show preview
Damit ich es nicht wieder vergesse (und beim nächsten mal die Suchmaschinen mit den falschen Suchbegriffen füttere): Um den physikalischen Speicherplatz einer Datenbank, Tabelle, Indexes usw. zu berechnen stellt PostgreSQL einige Funktionen bereit: System Administration Functions. Um zum Beispiel ergibt... Comments ()
PG-Forum.de on : Rollback verlangsamt scheinbar nachfolgende Operationen auf der Tabelle
Show preview
Vergleich mal die Gr... Comments ()
www.pg-forum.de on : PingBack
Show preview
Comments ()
Comments
Display comments as Linear | Threaded
Robert Maaskant on :
Andreas Scherbaum on :
Kris Jurka on :
Priyanka on :
Andreas Scherbaum on :
Priyanka on :
Andreas Scherbaum on :
Priyanka on :
Andreas Scherbaum on :
Priyanka on :
Andreas Scherbaum on :
Priyanka on :
Andreas Scherbaum on :
Priyanka on :
Andreas Scherbaum on :
akretschmer on :
akretschmer on :
vedika on :