Thread: vacuum analyze GROWS db ?!

Hello everyone,

The app that created this db is written by me for a change. But I've
done simple VACUUM ANALYZE on the biggest table in db and got this:

before VACUUM ANALYZE:


hrs=# SELECT relpages * 8192 AS size_in_bytes, relname FROM pg_class
WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname =
'public') ORDER BY size_in_bytes DESC LIMIT 10;
  size_in_bytes |               relname
---------------+--------------------------------------
       30474240 | hosts
         548864 | reservation
         106496 | reservation_hosts
          49152 | reservation_businessneed_idx
          40960 | hosts_ip_idx
          40960 | hosts_hostname_idx
          40960 | hosts_location_idx
          40960 | hosts_additional_info_idx
          40960 | reservation_status_idx
          40960 | reservation_hosts_reservation_id_idx
(10 rows)


After:

hrs=# vacuum analyze hosts;
VACUUM

hrs=# SELECT relpages * 8192 AS size_in_bytes, relname FROM pg_class
WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname =
'public') ORDER BY size_in_bytes DESC LIMIT 10;
  size_in_bytes |          relname
---------------+---------------------------
       82206720 | hosts
        4194304 | hosts_ip_idx
        3842048 | hosts_pkey
        3522560 | hosts_hostname_idx
        3416064 | hosts_location_idx
        3022848 | hosts_additional_info_idx
        2482176 | hosts_os_update_idx
        2367488 | hosts_cpu_idx
        2359296 | hosts_up_n_running_idx
        2334720 | hosts_os_kind_id_idx
(10 rows)


W T F ?!


REINDEX helped:


hrs=# reindex table hosts;
REINDEX

hrs=# SELECT relpages * 8192 AS size_in_bytes, relname FROM pg_class
WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname =
'public') ORDER BY size_in_bytes DESC LIMIT 10;
  size_in_bytes |               relname
---------------+--------------------------------------
       82206720 | hosts
         548864 | reservation
         106496 | reservation_hosts
          49152 | reservation_businessneed_idx
          49152 | hosts_ip_idx
          40960 | reservation_status_idx
          40960 | reservation_hosts_reservation_id_idx
          40960 | reservation_hosts_host_id_idx
          40960 | hosts_hostname_idx
          40960 | hosts_location_idx

Marcin Krol <mrkafk@gmail.com> writes:
> The app that created this db is written by me for a change. But I've
> done simple VACUUM ANALYZE on the biggest table in db and got this:

Do you *know* that relpages was up to date before that?  If your system
only does manual vacuums then those numbers probably reflected reality
as of your last vacuum.  There are functions that will give you true
file sizes but relpages ain't it.

            regards, tom lane

Tom Lane wrote:
> Do you *know* that relpages was up to date before that?  If your system
> only does manual vacuums then those numbers probably reflected reality
> as of your last vacuum.  There are functions that will give you true
> file sizes but relpages ain't it.

Oh great. Another catch. What are those functions?

Regards,
mk

On Mon, Feb 15, 2010 at 05:04:14PM +0100, Marcin Krol wrote:
> Tom Lane wrote:
>> Do you *know* that relpages was up to date before that?  If your system
>> only does manual vacuums then those numbers probably reflected reality
>> as of your last vacuum.  There are functions that will give you true
>> file sizes but relpages ain't it.
>
> Oh great. Another catch. What are those functions?

Well, this "catch" is clearly described in pg_class description:
http://www.postgresql.org/docs/current/static/catalog-pg-class.html

as for those functions -
http://www.postgresql.org/docs/current/interactive/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007