Postgres Pro
Downloads
Home   >   mailing lists

Thread: How do I measure user disk usage on Postgresql table?

How do I measure user disk usage on Postgresql table?

From
"Dave"
Date:
Lets say I have a Postgresql table where I store uploaded data for all users, identified by user_id column. Is it possible to run a query to measure the storage a user is consuming?

Ideally this would be a SELECT query with WHERE clause on user_id and will only return size of the data field(s), rather then the overhead.

Any other ideas?
 
Thanks

Re: How do I measure user disk usage on Postgresql table?

From
Richard Huxton
Date:
Dave wrote:
> Lets say I have a Postgresql table where I store uploaded data for
> all users, identified by user_id column. Is it possible to run a
> query to measure the storage a user is consuming?

Not really. You could get the size of the whole table with
pg_relation_size() and then estimate a user's share by seeing what % of
rows have their user-id.

If it's the sort of thing you'll be checking constantly, then I'd keep a
summary table up-to-date using triggers instead.

Oh - if you're going to be asking questions regularly then you'll
probably get more answers if you have a valid email address.

--
   Richard Huxton
   Archonet Ltd

Re: How do I measure user disk usage on Postgresql table?

From
"Dave"
Date:
"Richard Huxton" <dev@archonet.com> wrote in message
news:4808991E.7060501@archonet.com...
> Dave wrote:
>> Lets say I have a Postgresql table where I store uploaded data for
>> all users, identified by user_id column. Is it possible to run a
>> query to measure the storage a user is consuming?
>
> Not really. You could get the size of the whole table with
> pg_relation_size() and then estimate a user's share by seeing what % of
> rows have their user-id.
>
> If it's the sort of thing you'll be checking constantly, then I'd keep a
> summary table up-to-date using triggers instead.
>
> Oh - if you're going to be asking questions regularly then you'll probably
> get more answers if you have a valid email address.
>
> --
>   Richard Huxton
>   Archonet Ltd
>
> --
Richard,

Thanks for the answer.

So what is the proper way of managing user quota on database (apart from
having separate table for each)?

> Oh - if you're going to be asking questions regularly then you'll probably
> get more answers if you have a valid email address.
Isn't that asking for spam (and I know the answer to that)? Why would it
matter for someone to answer my post based on my email address, if the
discussion is going over the groups? One might just as well use:
fake_user@gmail.com. At least I'm forthcoming by making it obvious that my
email address is not real. Just a thought.