Skip to content

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.
  • Twitter
  • Bookmark table size, database size at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg table size, database size
  • Bloglines table size, database size
  • Technorati table size, database size
  • Fark this: table size, database size
  • Bookmark table size, database size at YahooMyWeb
  • Bookmark table size, database size at Furl.net
  • Bookmark table size, database size at reddit.com
  • Bookmark table size, database size at blinklist.com
  • Bookmark table size, database size at Spurl.net
  • Bookmark table size, database size at Simpy.com
  • Bookmark table size, database size at blogmarks
  • Bookmark table size, database size with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

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 ()

www.pg-forum.de on : PingBack

Show preview
Comments ()

Comments

Display comments as Linear | Threaded

Robert Maaskant on :

Very very cool post! Thanks!
Comments ()

Kris Jurka on :

Every installation has at least two tablespaces, pg_default and pg_global: jurka=# select spcname, pg_size_pretty(pg_tablespace_size(spcname)) from pg_tablespace ; spcname | pg_size_pretty ------------+---------------- pg_default | 20 GB pg_global | 10 MB (2 rows)
Comments ()

Priyanka on :

It was really helpful, thanks. My question is: How can I estimate the maximum size that is supported, row-wise, column-wise, datatype size-wise, tables-wise etc.. so that I can design my tables accordingly... for really really large data?
Comments ()

Andreas Scherbaum on :

Some of your questions can be answered by the documentation, as example the maximum number rows ect. The size for each data type can also be found in the docs. Size for tables and databases is more or less limited by your filesystem. Oh: and what do you mean with "really really large data"?
Comments ()

Priyanka on :

I am thinking of close to around 200 columns and 200 billion rows. But that is of course a ball park figure
Comments ()

Andreas Scherbaum on :

The 200 billion rows should not be a real problem if you have good hardware. But 200 columns? Ever heard about normalisation? ;-)
Comments ()

Priyanka on :

Yeah. I have to have a de-normalized DB
Comments ()

Andreas Scherbaum on :

You will - for sure - run into performance problems.
Comments ()

Priyanka on :

ok.. so what is the safe upper limit of columns I can think about. Keeping hardware out of the picture, for the time being?
Comments ()

Andreas Scherbaum on :

You can have up to 1500something columns per table. That's the technical limit in PostgreSQL. There are also perfectly legal reasons where you need several dozen columns in one table. But usually relational databases work much better with a normalised database scheme, so you should take this into account. You also have to remember that the column overhead with 200 columns is 1k (or more). So every time you read a single row from your table there is an additional 1k+ I/O overhead.
Comments ()

Priyanka on :

Thanks! I'll keep that in mind, for my star schema.
Comments ()

Andreas Scherbaum on :

Have fun. Let me know if i can help you further. Andreas
Comments ()

Priyanka on :

Hi could you please point me to the documentation you were referring to earlier?
Comments ()

Andreas Scherbaum on :

The CREATE TABLE documentation http://www.postgresql.org/docs/current/interactive/sql-createtable.html contains the information about max number columns. Look into the notes. http://www.postgresql.org/docs/current/interactive/functions-admin.html gives you an idea about the available functions for getting information about the size of almost everything in the database. Look into table 9-54.
Comments ()

akretschmer on :

to list the top-10 tables by size: select a.relname, pg_size_pretty(pg_total_relation_size(a.oid::regclass)) size from pg_class a left join pg_namespace b on a.relnamespace=b.oid where b.nspname != '^pg_' order by size desc limit 10;
Comments ()

akretschmer on :

Argh, that's wrong (wrong order because of pretty_size), better: select relname, pg_size_pretty(size) from (select a.relname, pg_total_relation_size(a.oid::regclass) size from pg_class a left join pg_namespace b on a.relnamespace=b.oid where b.nspname != '^pg_' order by size desc limit 10) foo;
Comments ()

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
To leave a comment you must approve it via e-mail, which will be sent to your address after submission.
Form options