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?
--
