1

I am migrating my application from MySQL to PostgreSQL. In MySQL, I have used a query to calculate memory size of particular tables as per my requirement using the below Query,

SELECT SUM(ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024),2)) 'Size in MB' FROM  INFORMATION_SCHEMA.TABLES where TABLE_NAME like 'table_name';

But I can't do the same in PostgreSQL. If I run the same query it is displaying as,

ERROR:  type "sum" does not exist

And I have tried the solutions in various sites but I can't find the exact solution for my requirement. Please suggest me a solution. Thanks in advance.

Heyyou
  • 151
  • 1
  • 13

2 Answers2

1

Your query query is failing because you're using single quotes for alias - 'Size in MB'.

Further, there's no DATA_LENGTH and INDEX_LENGTH available in INFORMATION_SCHEMA.TABLES

In Postgres, you may use pg_total_relation_size function.

SELECT ROUND(pg_total_relation_size (oid) / ( 1024.0 * 1024.0 ), 2) AS 
       "Size in MB" 
FROM   pg_class c 
WHERE  relname = 'table_name';

Have a look at this answer to know the functions available to get various sizes.

DEMO

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
  • I have tried this query but the value differs when I compare it with mysql and postgresql. – Heyyou May 30 '19 at 07:14
  • @Kishore : I'm not sure how it works in MySQL, so can't comment about it.But, I can assure you that the value returned is true for a Postgres table. Check the SO answer I've linked in my answer and you may find various sizes listed there. Replace it in the query and check if any of those closely match with the value you're expecting to see. Also read [this answer](https://stackoverflow.com/a/3490952/7998591) where it is described how the storage size could vary across MySQL and Postgres – Kaushik Nayak May 30 '19 at 07:21
  • What is that "oid" parameter represented in your query? @Kaushik Nayak – Heyyou May 30 '19 at 10:06
  • @Kishore : Read about [OIDs](https://stackoverflow.com/a/5627085/7998591) and description of columns in [pg_class catalog](https://www.postgresql.org/docs/9.3/catalog-pg-class.html) – Kaushik Nayak May 30 '19 at 10:36
  • Let me tell you an example, from that you can help me out with the values changed in postgres and mysql. Consider, there is a table with same structure and same no of rows in both postgres and mysql. If I try to calculate their size in respective environment, whether there is any possibility in value difference? Hope you can understand my example and situation. @Kaushik Nayak – Heyyou May 30 '19 at 11:36
  • @Kishore : If you carefully read the answer in the link in my previous comment, which makes the comparison, that is what it says: there could be difference simply because of the way the two databases store data. What's the amount of difference you're seeing by the way? – Kaushik Nayak May 30 '19 at 11:53
  • In mysql it is displaying as 0.06MB and in postgres 106KB. @Kaushik Nayak – Heyyou May 30 '19 at 12:06
  • @Kishore : Ok. 60 KB and 106 KB is what it is. I can't really help you more on this as MySQL is not my expertise. You'll have to explore. I have answered based on what's mentioned in Postgres manual and I trust that it is a genuine result. – Kaushik Nayak May 30 '19 at 12:20
  • Thanks for giving your valuable suggestions. @Kaushik Nayak – Heyyou May 30 '19 at 12:39
0

PostgreSQL does not have this information in INFORMATION_SCHEMA.TABLES but you can try using the table SVV_TABLE_INFO to get the table size instead.

Hope it helps!

umair qayyum
  • 286
  • 3
  • 11
  • Nope! It just displays that there is no relation like svv_table_info. Any other suggestions please. – Heyyou May 30 '19 at 09:56