0

I have a PostgreSQL DB in which I'm importing many data files into separate tables (one table per file).

I would like to get the size (on disk byte size, including indexes, etc.) of the corresponding table after each import is done.

I don't want to list all tables with their size, but rather get the size of a target table which I could easily consume as a query result.

I'm looking for something like: SELECT table_size(*) as "size" FROM my_table;

Answer: Thanks @a_horse_with_no_name for guiding me in the right direction. The command I was looking for is pg_total_relation_size(). It also helps to use pg_size_pretty() to get the data in a human-readable format. So the command I ended up using is:

SELECT pg_size_pretty(pg_total_relation_size('my_table')) AS size;

And it returns something like:

 size 
----------------
 723 kB
(1 row)
Marco Roy
  • 4,004
  • 7
  • 34
  • 50
  • 1
    http://stackoverflow.com/questions/21738408/postgresql-list-and-order-tables-by-size – Roman Tkachuk Feb 01 '17 at 20:20
  • @RomanTkachuk Is there any way to get the size of one specific table rather than all of them at once? – Marco Roy Feb 01 '17 at 20:24
  • If you need all of them you can just sum sizes of all tales. Or you can look for `posdtresql database size ` on stackoverflow or even google. – Roman Tkachuk Feb 01 '17 at 20:26
  • 1
    @MarcoRoy: just use the function `pg_relation_size` with a single table: `select pg_relation_size('some_table')` see the manual for details: https://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE –  Feb 01 '17 at 20:28
  • @a_horse_with_no_name Thanks, that seems to work! Mind posting it as an answer so I can accept it? Also, I don't think it's a duplicate of http://stackoverflow.com/questions/21738408. It's similar, but not a duplicate... – Marco Roy Feb 01 '17 at 20:46
  • @a_horse_with_no_name It seems like `pg_total_relation_size()` might be what I'm looking for. – Marco Roy Feb 01 '17 at 22:57

0 Answers0