Re: Newbie needs help with space issue

Jesse Josserand wrote:

> I apologize in advance for bothering you, but I've not been able to get
> answers from the community by searches I've done, nor have I figured out
> how to post a question there.
> 
> I'm working for a gov't entity here in Mississippi and while I have
> extensive Oracle and MySQL experience, as well as Linux admin experience
> (which is what I was hired for), I've been given PostgreSQL DBA
> responsibilities since the last DBA quit abruptly (probably due to poor
> architecture and documentation here -- another thing I'm resolving).
> 
> I'm having serious space issues in a production environment and psql
> commands and queries are not helping me isolate it nor determine the root
> cause... possibly due to my naivete.
> 
> Would you be so kind as to give me some insight as to how to best determine
> what is growing to fast and why? I have a 20G disk that recently was
> increased to 40G and am now in less than 3 months almost out of space there
> again.
> 
> Yours truly,
> Jesse Josserand, CTO
> IT Web Services, LLC <https://itwebservicesllc.com/> and
> Ace Consulting Solutions, Inc. <https://aceconsultingsolutionsinc.com/>

Hi,

Here's a query to list individual table sizes:

    select
        t.tablename as "object",
        pg_size_pretty(pg_relation_size(cast(t.tablename as text))) as "data",
        pg_size_pretty(pg_total_relation_size(cast(t.tablename as text))) as "total"
    from
        pg_tables t
    where
        t.schemaname = 'public'
    order by
        pg_total_relation_size(cast(t.tablename as text)) desc

I think the last column includes index sizes.

Running this repeatedly over time should show what's growing.

cheers,
raf