Re: [External] Postgres Database Disk Usage

Ok I am out of ideas :)
Sorry, I’ll let the experts step in here :)

btw the 43GB and 439MB look close :) I hope there is no conversion error
:)

On Fri, 8 Feb 2019 at 9:59 PM Jeremiah Bauer <jbauer(at)agristats(dot)com> wrote:

> We don't have any blobs or json objects in this database. I've sanitized
> the output of the query below, and toast doesn't show up until well down
> the list.
>
> FVDM=# SELECT nspname || '.' || relname AS "relation",
> FVDM-# pg_size_pretty(pg_relation_size(C.oid)) AS "size"
> FVDM-# FROM pg_class C
> FVDM-# LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
> FVDM-# WHERE nspname NOT IN ('pg_catalog', 'information_schema')
> FVDM-# ORDER BY pg_relation_size(C.oid) DESC
> FVDM-# LIMIT 20;
> relation | size
> ------------------------------------------------------+---------
> mart.*****_data | 301 MB
> mart.detailed_***** | 66 MB
> mart.major_***** | 58 MB
> public.temp_***** | 20 MB
> public.temp_***** | 13 MB
> mart.customer_***** | 11 MB
> public.temp_*****| 11 MB
> mart.major_***** | 1888 kB
> public.temp_***** | 1880 kB
> public.hold_***** | 1424 kB
> public.temp_***** | 1208 kB
> mart.ranking_***** | 1144 kB
> public.temp_***** | 1080 kB
> public.temp_***** | 984 kB
> mart.customer_***** | 696 kB
> mart.economic_***** | 456 kB
> public.hold_***** | 448 kB
> mart.expanded_***** | 384 kB
> pg_toast.pg_toast_2618 | 376 kB
> public.temp_***** | 232 kB
>
> I also queried just for toast tables:
>
> FVDM=# SELECT nspname || '.' || relname AS "relation",
> pg_size_pretty(pg_relation_size(C.oid)) AS "size"
> FROM pg_class C
> LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
> WHERE nspname NOT IN ('pg_catalog', 'information_schema') and c.relname
> ~* 'toast'
> ORDER BY pg_relation_size(C.oid) DESC
> LIMIT 20;
> relation | size
> -------------------------------+------------
> pg_toast.pg_toast_2618 | 376 kB
> pg_toast.pg_toast_2619 | 48 kB
> pg_toast.pg_toast_2618_index | 16 kB
> pg_toast.pg_toast_2619_index | 16 kB
> pg_toast.pg_toast_13104_index | 8192 bytes
> pg_toast.pg_toast_3596_index | 8192 bytes
> pg_toast.pg_toast_2606_index | 8192 bytes
> pg_toast.pg_toast_2620_index | 8192 bytes
> pg_toast.pg_toast_2609_index | 8192 bytes
> pg_toast.pg_toast_2396_index | 8192 bytes
> pg_toast.pg_toast_3592_index | 8192 bytes
> pg_toast.pg_toast_1255_index | 8192 bytes
> pg_toast.pg_toast_13109_index | 8192 bytes
> pg_toast.pg_toast_13114_index | 8192 bytes
> pg_toast.pg_toast_13119_index | 8192 bytes
> pg_toast.pg_toast_13124_index | 8192 bytes
> pg_toast.pg_toast_13129_index | 8192 bytes
> pg_toast.pg_toast_13134_index | 8192 bytes
> pg_toast.pg_toast_2604_index | 8192 bytes
> pg_toast.pg_toast_2964_index | 8192 bytes
>
> --
>
>
> Jeremiah
> ------------------------------
> *From:* Vijaykumar Jain <vjain(at)opentable(dot)com>
> *Sent:* Friday, February 8, 2019 11:21 AM
>
> *To:* Jeremiah Bauer
> *Cc:* pgsql-admin(at)lists(dot)postgresql(dot)org
> *Subject:* Re: [External] Postgres Database Disk Usage
>
> Do you have any blobs or large json objects as relnames?
> they generally end up as toast tables .
> Which may be the ones holding the space.
>
>
>
> On Fri, 8 Feb 2019 at 9:25 PM Jeremiah Bauer <jbauer(at)agristats(dot)com> wrote:
>
> Thank you for the response Vijaykumar,
>
> >Do you have replication active?
> No.
>
> >What is the Size of wal /pg_xlog directory ?
> [postgres(at)localhost pg_xlog]$ du --si
> 4.1k./archive_status
> 403M.
>
> Let me know if you you need any more information.
>
> --
> Sincerely,
>
> Jeremiah
>
>
> From: Vijaykumar Jain <vjain(at)opentable(dot)com>
> Sent: Friday, February 8, 2019 10:51 AM
> To: Jeremiah Bauer
> Cc: pgsql-admin(at)lists(dot)postgresql(dot)org
> Subject: Re: [External] Postgres Database Disk Usage
>
> Do you have replication active?
> is the size on the replica same as primary?
> no stale orphan replication slots?
> What is the Size of wal /pg_xlog directory ?
>
> On Fri, 8 Feb 2019 at 9:17 PM Jeremiah Bauer <jbauer(at)agristats(dot)com> wrote:
> Hello,
>
> We have a development database that reports a larger size than the sum of
> it's relations and I was hoping someone had some insight into this issue.
> The consumed space is reflected in the size of the data directories
> PostgreSQL is maintaining on disk, so it's just not a reported size issue.
>
> This is the version of Postgres we are using:
>
> FVDM=# select version();
> version
>
> -----------------------------------------------------------------------------------------------------------
> PostgreSQL 9.6.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7
> 20120313 (Red Hat 4.4.7-23), 64-bit
>
> pg_database_size reports this size:
>
> name | owner | size
> ------------+----------+---------
> FVDM | postgres | 43 GB
>
> Running a query to report the total relation size of each schema results
> in this:
>
> schemaname | pg_size_pretty
> --------------------+----------------
> information_schema | 152 kB
> pg_catalog | 7880 kB
> public | 52 MB
> mart | 439 MB
> (4 rows)
>
> The total size of the relations is less than a gigabyte, but the database
> is 43GB in size. I've checked each table and there aren't any added
> indexes, so I'm very confused.
>
> We've run vacuum full and reindex on the database with no reduction in
> size and a vacuum analyze verbose reports no recoverable rows. I cloned
> the machine for troubleshooting and even attempted truncating every table
> in the database to see if that remedied the issue. The only thing that has
> reclaimed the space is dropping the database and restoring from backup.
>
> What is using the space and how do we reclaim it?
>
> Any help or insight would be greatly appreciated, I'm at a loss as to what
> is consuming this space.
>
> --
>
>
> Jeremiah
>
> CONFIDENTIALITY NOTICE: The information contained in this email (and any
> attachments) is privileged and confidential and protected from disclosure.
> If you are not the intended recipient of this email or the attachments, be
> aware that any disclosure, copying, distribution or use of this email or
> any attachment is strictly prohibited and you should not read the message
> or read or open any attachment. If you have received this email by mistake,
> please immediately notify the sender and delete it permanently from your
> system. Agri Stats, Inc. and its subsidiaries will not be held liable to
> any person or entity resulting from the unintended or unauthorized use of
> any information contained in this email.
> --
>
> Regards,
> Vijay
> CONFIDENTIALITY NOTICE: The information contained in this email (and any
> attachments) is privileged and confidential and protected from disclosure.
> If you are not the intended recipient of this email or the attachments, be
> aware that any disclosure, copying, distribution or use of this email or
> any attachment is strictly prohibited and you should not read the message
> or read or open any attachment. If you have received this email by mistake,
> please immediately notify the sender and delete it permanently from your
> system. Agri Stats, Inc. and its subsidiaries will not be held liable to
> any person or entity resulting from the unintended or unauthorized use of
> any information contained in this email.
>
> --
>
> Regards,
> Vijay
> CONFIDENTIALITY NOTICE: The information contained in this email (and any
> attachments) is privileged and confidential and protected from disclosure.
> If you are not the intended recipient of this email or the attachments, be
> aware that any disclosure, copying, distribution or use of this email or
> any attachment is strictly prohibited and you should not read the message
> or read or open any attachment. If you have received this email by mistake,
> please immediately notify the sender and delete it permanently from your
> system. Agri Stats, Inc. and its subsidiaries will not be held liable to
> any person or entity resulting from the unintended or unauthorized use of
> any information contained in this email.
>
--

Regards,
Vijay