CODEWITHSUNDEEP
postgresql
priestc
priestc

Reputation: 35170

how to quickly analyze a postgres database

I have a postgres database that I want to know some quick stats. For instance, which tables are taking up the most space? I don't need anything fancy, command line is all I need. What is a good tool for this?

Upvotes: 4

Views: 2914

Answers (3)

coder_tim
coder_tim

Reputation: 1720

From the client program psql, "\l" will list databases, add a "+" to also show the sizes: "\l+". Also, "\dt+" will give you information on specific table sizes.

Upvotes: 2

kgrittn
kgrittn

Reputation: 19471

The functions you want are here:

http://www.postgresql.org/docs/current/interactive/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE

A quick query to find the top 20 tables in terms of space usage might look like this:

SELECT oid::regclass, pg_size_pretty(pg_total_relation_size(oid))
  FROM pg_class
  WHERE relkind = 'r'
  ORDER BY pg_total_relation_size(oid) DESC
  LIMIT 20;

Upvotes: 5

Shankar
Shankar

Reputation: 89

Interesting question. I think you can query the information using psql. Here are few pointers.

http://securfox.wordpress.com/2009/09/02/how-to-find-the-postgresql-database-size/ and http://heatware.net/databases/find-postgresql-database-size-using-sql-select/ .

Hope this helps.

Thanks, Shankar

Upvotes: 1

Related Questions