From: Mike Schanne <mschanne@kns.com>
To: 'pgsql-performance@postgresql.org' <pgsql-performance@postgresql.org>
Subject: unexpected result for wastedbytes query after vacuum full
Date: Fri, 6 Dec 2019 17:18:20 +0000
Message-ID: <db92c5c1a0904209a4ef2d857276ab4b@USEPRDEX1.corp.kns.com> (raw)
Hi all,
This question is somewhat related to my previous question:
https://www.postgresql.org/message-id/0871fcf35ceb4caa8a2204ca9c38e330%40USEPRDEX1.corp.kns.com
I was attempting to measure the benefit of doing a VACUUM FULL on my database. I was using the query found here:
https://wiki.postgresql.org/wiki/Show_database_bloat
However, I got an unexpected result in that the "wastedbytes" value actually increased for some tables after doing the vacuum.
Before VACUUM FULL:
current_database | schemaname | tablename | tbloat | wastedbytes | iname | ibloat | wastedibytes
------------------+----------------+---------------------------+--------+-------------+-----------------------------------------------------------------+--------+--------------
postgres | myschema | mytableA | 1.1 | 74440704 | myindex1 | 0.2 | 0
postgres | myschema | mytableA | 1.1 | 74440704 | myindex2 | 0.2 | 0
postgres | myschema | mytableA | 1.1 | 74440704 | myindex3 | 0.2 | 0
postgres | myschema | mytableA | 1.1 | 74440704 | myindex4 | 0.2 | 0
postgres | myschema | mytableB | 1.0 | 63324160 | myindex5 | 0.0 | 0
...
After VACUUM FULL:
current_database | schemaname | tablename | tbloat | wastedbytes | iname | ibloat | wastedibytes
------------------+----------------+---------------------------+--------+-------------+-----------------------------------------------------------------+--------+--------------
postgres | myschema | mytableA | 1.1 | 74506240 | myindex4 | 0.2 | 0
postgres | myschema | mytableA | 1.1 | 74506240 | myindex3 | 0.2 | 0
postgres | myschema | mytableA | 1.1 | 74506240 | myindex2 | 0.2 | 0
postgres | myschema | mytableA | 1.1 | 74506240 | myindex1 | 0.2 | 0
postgres | myschema | mytableB | 1.0 | 63332352 | myindex5 | 0.0 | 0
...
This is the schema for mytableA above:
Column | Type | Modifiers
---------------+-----------------------------+----------------------------------------------------------------
colA | integer | not null default nextval('myschema.myseq'::regclass)
colB | integer |
colC | integer |
colD | timestamp without time zone |
colE | json |
colF | integer |
colG | integer |
I was wondering if the fact that we use a json column could be interfering with the wastedbytes calculation. Can anyone explain how wastedbytes could increase from a vacuum?
Thanks,
Mike
________________________________
This email is non-binding, is subject to contract, and neither Kulicke and Soffa Industries, Inc. nor its subsidiaries (each and collectively "K&S") shall have any obligation to you to consummate the transactions herein or to enter into any agreement, other than in accordance with the terms and conditions of a definitive agreement if and when negotiated, finalized and executed between the parties. This email and all its contents are protected by International and United States copyright laws. Any reproduction or use of all or any part of this email without the express written consent of K&S is prohibited.
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-performance@postgresql.org
Cc: mschanne@kns.com
Subject: Re: unexpected result for wastedbytes query after vacuum full
In-Reply-To: <db92c5c1a0904209a4ef2d857276ab4b@USEPRDEX1.corp.kns.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