From: MichaelDBA <MichaelDBA@sqlexec.com>
To: Vijaykumar Jain <vjain@opentable.com>
Cc: Jeremiah Bauer <jbauer@agristats.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: [External] Postgres Database Disk Usage
Date: Fri, 08 Feb 2019 12:12:06 -0500
Message-ID: <5C5DB866.30206@sqlexec.com> (raw)
In-Reply-To: <CAE7uO5izj_+sqBWvC7KeNmzDg-gDVTjCt3R9sHtvjjo3DwHF4g@mail.gmail.com>
References: <DM5PR19MB1418873B4EFF26F9CABAD90FC2690@DM5PR19MB1418.namprd19.prod.outlook.com>
<CAE7uO5iLRzzFvq80Y1vAniY0UVsLpwbObS9bn3K+t3z49=7DwQ@mail.gmail.com>
<DM5PR19MB1418E8C931F5EFE7DBED2F72C2690@DM5PR19MB1418.namprd19.prod.outlook.com>
<CAE7uO5gjtEj=nEU=_5NK4EzOkizbfZAQYWRXcNubEVRq-Af8cw@mail.gmail.com>
<DM5PR19MB1418F001A6C3FF38A90B8FC2C2690@DM5PR19MB1418.namprd19.prod.outlook.com>
<CAE7uO5izj_+sqBWvC7KeNmzDg-gDVTjCt3R9sHtvjjo3DwHF4g@mail.gmail.com>
Would you use pg_total_relation_size, not pg_relation_size in your
sizing query? This will capture associated indexes and such.
Regards,
Michael Vitale
> Vijaykumar Jain <mailto:vjain@opentable.com>
> Friday, February 8, 2019 12:05 PM
> 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 :)
>
> --
>
> Regards,
> Vijay
> Jeremiah Bauer <mailto:jbauer@agristats.com>
> Friday, February 8, 2019 11:29 AM
> 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@opentable.com>
> *Sent:* Friday, February 8, 2019 11:21 AM
> *To:* Jeremiah Bauer
> *Cc:* pgsql-admin@lists.postgresql.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@agristats.com
> <mailto:jbauer@agristats.com>> wrote:
> --
>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: MichaelDBA@sqlexec.com, vjain@opentable.com, jbauer@agristats.com, pgsql-admin@lists.postgresql.org
Subject: Re: [External] Postgres Database Disk Usage
In-Reply-To: <5C5DB866.30206@sqlexec.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox