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?
Today I noticed that pg_statistic (which I actually expect to be updated
in the normal course of operations) was over 20 meg (with large indexes
as well) so I gave it a VACUUM FULL/REINDEX and it now stands at 344,064.
These tables never get *really* large, so I've never noticed a big
performance hit, but they still get bigger than they should be and could
be slowing everything down a little.
around 3pm today I did a VACUUM FULL/REINDEX of all the trouble tables I
have in my list, and I didn't save the before/after sizes, bit it is
9:30 pm now and I can tell you how much they've grown since then:
BEFORE VACUUM FULL/REINDEX at 9:30:
pg_catalog pg_class table 196,608 21.526
pg_catalog pg_class_oid_index index 49,152 81.8
pg_catalog pg_class_relname_nsp_index index 172,032 21.526
pg_catalog pg_type table 180,224 15.045
pg_catalog pg_type_oid_index index 40,960 66.2
pg_catalog pg_type_typname_nsp_index index 106,496 25.462
AFTER:
pg_catalog pg_class table 81,920 41.1
pg_catalog pg_class_oid_index index 32,768 102.5
pg_catalog pg_class_relname_nsp_index index 57,344 58.714
pg_catalog pg_type table 65,536 41.375
pg_catalog pg_type_oid_index index 16,384 165.5
pg_catalog pg_type_typname_nsp_index index 49,152 55.167
My apologies for the tabs. That was a cut & paste from a web page I set
up to monitor the database size. The columns are: schema relname
Type bytes tuplesperpage
The list of tables I have in my list are:
pg_attribute pg_class pg_depend pg_index pg_shdepend pg_proc
pg_statistic pg_type pg_trigger pg_shdepend
I put them in my list bec. I once noticed that their indexes seemed big
relative to the size of the table itself. I didn't really analyze if
they were indeed recurring problems or just one time problems, but I
know pg_class and pg_opclass are ones where this is a recurring problem.
BTW Tom do you prefer the replies to go to you directly as well as to
the list? Most of the time I just hit 'Reply' and since this list
doesn't set the Reply-to: the replies go to the OP as well.
Tom Lane wrote:
Joseph S <jks@xxxxxxxxxxxxxxx> writes:
My pg_shdepend table has a size of 16,384, but
pg_shdepend_depender_index has a size of 19,169,280 and
pg_shdepend_reference_index has a size of 49,152.
I'd be interested to see the usage pattern that made it get like that
...
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
