0

Basically i want to be able to get the size of a table in bytes used by a particular user.

I have a user table and i have the id of this table in every other table in the database.
This code gets me the size in bytes of all tables in the database.

SELECT relname as "Table", pg_size_pretty(pg_total_relation_size(relid)) As "Size" FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

But i want to add a where clause to get only for a particular user id in the tables.

EDITED: The possible duplicate answers only half of my question. I basically want a more specific size based on a column present in every table.

Something like this. SELECT relname as "Table", pg_size_pretty(pg_total_relation_size(relid)) As "Size" FROM pg_catalog.pg_statio_user_tables WHERE "table_name"."user_id" = 1 ORDER BY pg_total_relation_size(relid) DESC;

Melroy Fernandes
  • 371
  • 1
  • 5
  • 16
  • Have you checked [this other question](https://stackoverflow.com/questions/2596624/how-do-you-find-the-disk-size-of-a-postgres-postgresql-table-and-its-indexes) and [this page](https://wiki.postgresql.org/wiki/Disk_Usage)? – Emanuele Giona Sep 06 '17 at 06:05
  • @EmanueleGiona I forgot to mention that i have already checked the question and the page and unfortunately i get the same output as the above mentioned code. I wanted to know if it is possible to get the size used by a particular **user** by his **id** something like a `WHERE "table_name"."user_id" = 1` – Melroy Fernandes Sep 06 '17 at 06:21
  • @JorgeCampos Yes it answers only half my query. I need a little more specific size of table data used by a particular **user** based on his **id**. – Melroy Fernandes Sep 06 '17 at 06:25
  • This is a guess: Unless you have partitioned tables I think the answer is no. I would recommend you to ask it on http://dba.stackexchange.com though – Jorge Campos Sep 06 '17 at 06:34
  • @JorgeCampos thank you for your response and recommendation. – Melroy Fernandes Sep 06 '17 at 06:40

0 Answers0