Search Postgresql Archives

index bloat WAS: reindexing pg_shdepend

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Tom Lane wrote:
Joseph S <jks@xxxxxxxxxxxxxxx> writes:
Me too. I don't change my db schema that much, but I experience bloat in the pg_tables that I don't expect. For instance pg_opclass needs a VACUUM FULL/REINDEX once a week or I notice the indexes are larger than the table itself. Could it be my heavy use of temp tables?

pg_opclass?  That's read-only for most people.  What are you doing with
operator classes?

I know. I can't figure it out. I barely know what operator classes are, but I'm pretty sure I'm not modifying them in any way.

Heavy use of temp tables would expand pg_class, pg_type, and especially
pg_attribute, but as long as you have a decent vacuuming regimen (do you
use autovac?) they shouldn't get out of hand.

I do use autovac. Like I said they don't get really out of hand, only up to 20 megs or so before I noticed that it was weird. The large indexes are what tipped me off that something strange was going on.

I only noticed this because I was making an effort to monitor index bloat on my regular tables. It could be there are a lot of people out there who are experiencing this but don't notice because 20 megs here and there don't cause any noticeable problems.

So how about it list? Do you know how bloated your indexes are getting? I use this sql:

select (select nspname FROM pg_catalog.pg_namespace where oid = relnamespace) AS schema, relname, CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type" ,CASE c.relkind IN ('i','r','S','') WHEN true THEN pg_relation_size(relname) END AS bytes, CASE relpages > 0 WHEN true THEN reltuples/relpages END AS tuplesperpage FROM pg_catalog.pg_class c WHERE pg_catalog.pg_table_is_visible(c.oid) order by schema, relname;

... and when I notice that the tuplesperpage for the indexes is low (or that the indexes are bigger then the tables themselves) I know it is time for a VACUUM FULL and REINDEX on that table.

If you really want to get fancy you can save the results of that into a table with a timestamp. Then every (insert time period here) run VACUUM FULL/REINDEXs on the individual tables and store the new sizes with timestamps.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faq

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux